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

1. 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.
2. Creating a command line database interfacing program in C, that can
   1. Make SELECT queries – 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.
   2. Alter data (INSERT, DELETE, UPDATE)
   3. Find results – SUM, AVG etc queries.
3. Documenting the work

Environment

• Must use a SQLite3 database and libsqlite3-dev libraries.
• Must work under Linux and compile with GCC.
• Can use either C90 or C99. In case of C99, include a Makefile, so that “make all” would (only) compile the program.

Recommended packages for UNIX are:

• sqlite3 – the DBMS software
• libsqlite-dev – the C library to interface with the sqlite databases
• sqlitebrowser – graphical interface to administrate the contents of the sqlite database – for verification (all commands must be written in the C code)

To install this on a Debian based Linux system (includes Ubuntu, Linux mint):

Requirements

• Must create a command line based user interface program. All actions are repeatable until the user wishes to exit.
• When executing UPDATE and INSERT queries, input data should be validated before it is fed to the DBMS.
• To create SQL parameterized queries safely, you need to use  sqlite3_bind_  family of functions for attaching the parameters.
• All resources acquired must be freed before exiting the program.
• Project must be compiled together using a Makefile you provide.

Documentation

• Title page
• Declaration of originality
• List of abbreviations and terms (can be omitted if not present)
• Table of contents
• List of figures and tables (can be omitted if not present)
• Task description
• Explanation of the program
  • • 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.:
      

• Explanation of the database schema
  • Include the database design (from the task variant) as a figure (this must be listed as a reference)
  • Describe all PK-FK constraints
    E.g. PK students.student_code references FK grades.student_code.
  • 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.
    E.g. a sample row for students table:

Attribute	Description	Properties	Example data
student_code	unique identification code for each student, consisting of 6-digit matriculate number and 4-character curriculum code as a single string	Not NULL Unique	“123456MVEB”

• Summary
  • Write a few sentences about your approach to the task and progression during it
  • Write a few sentences about the task itself and the complexity of it
  • If possible, give an estimate on the workload
  • Would you recommend using this task in the future? Would you change anything about it and if so, what?
  • Additional comments, emotions, thoughts
• References (can be omitted if not present)
  • Use IEEE style for references
    English official reference: https://journals.ieeeauthorcenter.ieee.org/wp-content/uploads/sites/7/IEEE_Reference_Guide.pdf
    Reference help by TalTech (in Estonian): https://haldus.taltech.ee/sites/default/files/2024-05/IEEE_stiilis_viitamine_2024%20%281%29.pdf
  • Everything that wasn’t covered within the subject and that you didn’t create on your own.
  • This includes help from a friend or a colleague: https://libraryguides.vu.edu.au/ieeereferencing/personalcommunication
  • If you used AI tools (i.e. ChatGPT), they must also be referenced
  • All references must be precise. References such as stackoverflow.com or google.com are meaningless. It must be directly to the page where you got the information from.
• Screenshots of the program’s behavior in various situations

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.

Submitting your task

The supervisor for this task is René Pihlak (Contact on University MS Teams)

Submitting of the homework is in two stages:

1. Upload your solution to Moodle: https://moodle.taltech.ee/mod/assign/view.php?id=339483
2. Write a message to the task supervisor using their preferred method of contact to let them know that the solution has been uploaded. It’s also recommended to include the solution in the message!

You need to submit two files:

1. The documentation (pdf document)
2. Your project, as a .zip archive (source files, Makefile, SQLite3 database file).

Additional defense may be required!