Homework III variant: SQL

Getting a variant

Contact René Pihlak via e-mail or during / after the scheduled class with the request for a variant. Variants are generated per request.

E-mail: rene.pihlak [ät] taltech.ee

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 SQLite 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):

Program requirements

  • Must create a command line based user interface program. All actions are repeatable until the user wishes to exit.
  • Must check if the query failed to execute (whenever possible) and report that to the user.
  • When executing UPDATE and INSERT queries, input data should be validated before it is fed to the DBMS.
  • All resources acquired must be freed before exiting the program.
  • All best practices learned over time – proper use of functions, coding style, avoiding magical numbers etc.

Documentation

  • Title page
  • Declaration of originality
  • Table of contents
  • Task description
  • Explanation of the program
    • All functions must be documented. This must be structured so that parameters, their data types and semantics can be clearly found. The same for return value.
    • 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
    • Draw a UML class diagram of your database structure which shows the PK-FK relationships
    • 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”
  • 3Screenshots of the program’s behavior in various situations
  • List of references. The list should include any help you got from a friend as well as codes we provide.

Submitting

Deadline: 08.01.2020

A defense might be required

The following files must be submitted:

  • Report (pdf document)
  • Source code (.c and .h files)
  • SQLite database file
  • Optionally a Makefile

Submitting details will be provided by René Pihlak alongside the variant.