{"id":4687,"date":"2019-11-19T22:45:21","date_gmt":"2019-11-19T20:45:21","guid":{"rendered":"https:\/\/blue.pri.ee\/ttu\/?page_id=4687"},"modified":"2026-04-23T16:26:15","modified_gmt":"2026-04-23T14:26:15","slug":"hw3-variant-sql","status":"publish","type":"page","link":"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/","title":{"rendered":"Homework III variant: SQL"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Task_description\" >Task description<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Variant\" >Variant<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Task_composition\" >Task composition<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Environment\" >Environment<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Requirements\" >Requirements<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Documentation\" >Documentation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/#Submitting_your_task\" >Submitting your task<\/a><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"Task_description\"><\/span>Task description<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>This task will introduce interfacing a command line C program with an SQL database. You will be writing both the SQL statements and C code. You will also be doing slight modifications to the database. The database will be local and does not require a network connection.<\/p>\n<h1><span class=\"ez-toc-section\" id=\"Variant\"><\/span>Variant<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n<!-- iframe plugin v.6.0 wordpress.org\/plugins\/iframe\/ -->\n<iframe loading=\"lazy\" src=\"https:\/\/blue.pri.ee\/ttu\/files\/iax0584\/kodutoo3\/index.html\" width=\"100%\" height=\"80\" scrolling=\"yes\" class=\"iframe-class\" frameborder=\"0\"><\/iframe>\n\n<h1><span class=\"ez-toc-section\" id=\"Task_composition\"><\/span>Task composition<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<ol>\n<li>Adding a table to a preexisting database structure and specifying its parameters. The specification of the additional table is up to you to figure out. The table must be created through your C program at startup, if necessary.<\/li>\n<li>Creating a command line database interfacing program in C, that can\n<ol>\n<li>Make SELECT queries &#8211; from a single table and multiple tables using various JOIN operations. This includes filtering by WHERE or HAVING, reordering data (ORDER BY) and so forth.<\/li>\n<li>Alter data (INSERT, DELETE, UPDATE)<\/li>\n<li>Find results &#8211; SUM, AVG etc queries.<\/li>\n<\/ol>\n<\/li>\n<li>Documenting the work<\/li>\n<\/ol>\n<h1><span class=\"ez-toc-section\" id=\"Environment\"><\/span>Environment<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<ul>\n<li>Must use a SQLite3 database and libsqlite3-dev libraries.<\/li>\n<li>Must work under Linux and compile with GCC.<\/li>\n<li>Can use either C90 or C99. In case of C99, include a Makefile, so that &#8220;make all&#8221; would (only) compile the program.<\/li>\n<\/ul>\n<p>Recommended packages for UNIX are:<\/p>\n<ul>\n<li>sqlite3 &#8211; the DBMS software<\/li>\n<li>libsqlite-dev &#8211; the C library to interface with the sqlite databases<\/li>\n<li>sqlitebrowser &#8211; graphical interface to administrate the contents of the sqlite database &#8211; for verification (all commands must be written in the C code)<\/li>\n<\/ul>\n<p>To install this on a Debian based Linux system (includes Ubuntu, Linux mint):<\/p>\n<pre class=\"lang:default decode:true\">sudo apt update\r\nsudo apt install sqlite3 libsqlite3-dev sqlitebrowser<\/pre>\n<h1><span class=\"ez-toc-section\" id=\"Requirements\"><\/span>Requirements<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<ul>\n<li>Must create a command line based user interface program. All actions are repeatable until the user wishes to exit.<\/li>\n<li>When executing UPDATE and INSERT queries, input data should be validated before it is fed to the DBMS.<\/li>\n<li>To create SQL parameterized queries safely, you need to use\u00a0 <span class=\"lang:c highlight:0 decode:true crayon-inline\">sqlite3_bind_<\/span>\u00a0 family of functions for attaching the parameters.<\/li>\n<li>All resources acquired must be freed before exiting the program.<\/li>\n<li>Project must be compiled together using a Makefile you provide.<\/li>\n<\/ul>\n<h1><span class=\"ez-toc-section\" id=\"Documentation\"><\/span>Documentation<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<ul>\n<li>Title page<\/li>\n<li>Declaration of originality<\/li>\n<li>List of abbreviations and terms (can be omitted if not present)<\/li>\n<li>Table of contents<\/li>\n<li>List of figures and tables (can be omitted if not present)<\/li>\n<li>Task description<\/li>\n<li>Explanation of the program\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>All SQL queries must be listed in the document. They must be grouped together by the type of query (E.g. INSERT queries, UPDATE queries etc.). Any preconditions must be listed for the query to work (e.g. in order to add a grade to a student, the student with such a student code must exist). Queries must be copied in with syntax highlighting. E.g.:<br \/>\n<a href=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/sql.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-8517\" src=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/sql.png\" alt=\"\" width=\"262\" height=\"114\" srcset=\"https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/sql.png 628w, https:\/\/blue.pri.ee\/ttu\/wp-content\/uploads\/2023\/04\/sql-300x131.png 300w\" sizes=\"auto, (max-width: 262px) 100vw, 262px\" \/><\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Explanation of the database schema\n<ul>\n<li>Include the database design (from the task variant) as a figure <strong>(this must be listed as a reference)<\/strong><\/li>\n<li>Describe all PK-FK constraints<br \/>\nE.g. PK students.student_code references FK grades.student_code.<\/li>\n<li>Create a table for each database table, describing each attribute (column). Must include the name of the attribute, any properties (PK, Not NULL, Unique), description and an example.<br \/>\nE.g. a sample row for students table:<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<table style=\"width: 100%;\" width=\"537\">\n<tbody>\n<tr>\n<td style=\"width: 22.4101%;\" width=\"101\"><strong>Attribute<\/strong><\/td>\n<td style=\"width: 33.1924%;\" width=\"244\"><strong>Description<\/strong><\/td>\n<td style=\"width: 18.6047%;\" width=\"78\"><strong>Properties<\/strong><\/td>\n<td style=\"width: 25.7928%;\" width=\"113\"><strong>Example data<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 22.4101%;\" width=\"101\">student_code<\/td>\n<td style=\"width: 33.1924%;\" width=\"244\">unique identification code for each student, consisting of 6-digit matriculate number and 4-character curriculum code as a single string<\/td>\n<td style=\"width: 18.6047%;\" width=\"78\">Not NULL<br \/>\nUnique<\/td>\n<td style=\"width: 25.7928%;\" width=\"113\">&#8220;123456MVEB&#8221;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<ul>\n<li>Summary\n<ul>\n<li>Write a few sentences about your approach to the task and progression during it<\/li>\n<li>Write a few sentences about the task itself and the complexity of it<\/li>\n<li>If possible, give an estimate on the workload<\/li>\n<li>Would you recommend using this task in the future? Would you change anything about it and if so, what?<\/li>\n<li>Additional comments, emotions, thoughts<\/li>\n<\/ul>\n<\/li>\n<li>References (can be omitted if not present)\n<ul>\n<li>Use IEEE style for references<br \/>\nEnglish official reference:\u00a0<strong><a href=\"https:\/\/journals.ieeeauthorcenter.ieee.org\/wp-content\/uploads\/sites\/7\/IEEE_Reference_Guide.pdf\">https:\/\/journals.ieeeauthorcenter.ieee.org\/wp-content\/uploads\/sites\/7\/IEEE_Reference_Guide.pdf<\/a><br \/>\n<\/strong>Reference help by TalTech (in Estonian):\u00a0<strong><a href=\"https:\/\/haldus.taltech.ee\/sites\/default\/files\/2024-05\/IEEE_stiilis_viitamine_2024%20%281%29.pdf\">https:\/\/haldus.taltech.ee\/sites\/default\/files\/2024-05\/IEEE_stiilis_viitamine_2024%20%281%29.pdf<\/a><\/strong><\/li>\n<li>Everything that wasn\u2019t covered within the subject and that you didn\u2019t create on your own.<\/li>\n<li>This includes help from a friend or a colleague:\u00a0<strong><a href=\"https:\/\/libraryguides.vu.edu.au\/ieeereferencing\/personalcommunication\">https:\/\/libraryguides.vu.edu.au\/ieeereferencing\/personalcommunication<\/a><\/strong><\/li>\n<li>If you used AI tools (i.e. ChatGPT), they must also be referenced<\/li>\n<li>All references must be precise.\u00a0<em>References such as stackoverflow.com or google.com are meaningless. It must be directly to the page where you got the information from.<\/em><\/li>\n<\/ul>\n<\/li>\n<li>Screenshots of the program\u2019s behavior in various situations<\/li>\n<\/ul>\n<p>Note: Function documentation also a part of the documentation requirement, which must be written into the code. For every function, detail its purpose, parameters, requirements for parameters, return value and possible side effects.<\/p>\n<h1><span class=\"ez-toc-section\" id=\"Submitting_your_task\"><\/span>Submitting your task<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>The supervisor for this task is Ren\u00e9 Pihlak (Contact on University MS Teams)<\/p>\n<p>Submitting of the homework is in two stages:<\/p>\n<ol>\n<li>Upload your solution to Moodle<\/li>\n<li>Write a message to the task supervisor using their preferred method of contact to let them know that the solution has been uploaded. It&#8217;s also recommended to include the solution in the message!<\/li>\n<\/ol>\n<p>You need to submit two files:<\/p>\n<ol>\n<li>The documentation (pdf document)<\/li>\n<li>Your project, as a .zip archive (source files, Makefile, SQLite3 database file).<\/li>\n<\/ol>\n<p>Additional defense may be required!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Task description This task will introduce interfacing a command line C program with an SQL database. You will be writing both the SQL statements and C code. You will also be doing slight modifications to the database. The database will be local and does not require a network connection. Variant Task composition Adding a table &hellip; <a href=\"https:\/\/blue.pri.ee\/ttu\/programming-ii\/homework-iii\/hw3-variant-sql\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">Homework III variant: SQL<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":2569,"menu_order":5,"comment_status":"closed","ping_status":"closed","template":"page-templates\/code-width.php","meta":{"footnotes":""},"class_list":["post-4687","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/pages\/4687","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/comments?post=4687"}],"version-history":[{"count":11,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/pages\/4687\/revisions"}],"predecessor-version":[{"id":11386,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/pages\/4687\/revisions\/11386"}],"up":[{"embeddable":true,"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/pages\/2569"}],"wp:attachment":[{"href":"https:\/\/blue.pri.ee\/ttu\/wp-json\/wp\/v2\/media?parent=4687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}