IS5102 –Practical 2: Database management with SQLite

In this assignment, you are asked to convert an E–R model into an SQLite database, populate it with test data, and then run a number of SQL queries.

You are expected to have read and understood all the information in this specification at least a week before the deadline. You must contact the lecturer regarding any queries well in advance of the deadline.

Summary

In lectures and exercise classes, we have spent a fair amount of time on SQL, and its use in database creation, data insertion, and database querying. This assignment will help you to develop and practise skills in all of these areas:

    • converting E–R data models into SQL;

    • understanding the relationship between conceptual and logical data models;

    • enforcing database integrity;

    • confidently translating business queries to and from SQL.

This is an individual assignment. You are expected to work on your own to complete the tasks in this assignment.

E–R Model

We will work with the E–R Model for a bus company scenario. Bus services are identified by a number used by customers and drivers (like 42, 99A, or X59). Each service has a starting bus station (origin) and an ending bus station (destination). Bus stations have a name and town (like Seagate in Dundee or Buchanan in Glasgow). Each service runs multiple times a day and passes a number of stops. For each run of the service, it is necessary to record its arrival time to each stop on the route, as well as the fare from the origin to each stop.

The bus company employs bus drivers, who work on hourly wages. Bus drivers are assigned to one or more bus services. To calculate their pay, it is necessary to record their hourly wage, and how many hours they have worked on each service. The bus company also employs station managers, who are paid an annual salary.

The rest of the details must be inferred from the E–R diagram on Figure 1. In case of any ambigui-ties or unspecified details in the scenario above, your implementation should always match the model described by this E–R diagram.






1









































Figure 1: E–R Diagram for a bus company scenario

Tasks

Task 1: Translation

Translate the E–R model into the corresponding database schema, i.e. into the collection of relation schemas. In your report, present the relation schemas and provide a brief rationale for any design choices you make. Be sure to identify appropriate primary keys, foreign keys, and attribute types.

Task 2: SQL Data Definition

Using the Command Line Shell for SQLite (https://sqlite.org/cli.html) or the the DB Browser for SQLite (https://sqlitebrowser.org/), create an SQLite database, which corresponds to the database schema from Task 1.

To complete this task, you should create a plain text file called buses.sql, containing SQL code to define tables for each relational schema from Task 1, and then insert data into your tables.

You need to create about 10 rows per each table, and make sure that you insert rows which make each query from Task 3 to return at least 2 results (you may revisit this task later, and add extra data while working on Task 3, if necessary).


2

Make sure that integrity constraints are enforced. For the Command Line Shell For SQLite, you have to put the line

PRAGMA foreign keys = TRUE;


in the the file buses.sql above commands to create and populate the tables. In addition to specifying primary and foreign keys, and attribute types, you are encouraged to use additional SQL features to enforce database integrity, e.g. cascading actions, default values, etc.

Populating the database, you may find it useful to invest some efforts into making the values of at-tributes minimally meaningful and recognisable. This will help you to check that the queries developed in Task 3 are returning correct results.

You have to ensure that the buses.sql script is runnable in a clean new session in the Command Line Shell for SQLite or the the DB Browser for SQLite and, starting from an empty database, will create tables and populate them with data, without any error messages.

In your report, give the high-level overview of this process, describing your approach to developing and testing the SQL code to ensure the integrity of your data. If necessary, you may include fragments of the code from buses.sql to illustrate your statements (but you do not need to repeat the whole content of buses.sql, since this file will be submitted separately).

Task 3: SQL Data Manipulation

Extend the buses.sql script created in Task 2, adding to it (below the statements to create table and insert date), SQL code to perform the following high-level queries:

    1. List all services which have Seagate Bus Station in Dundee as their destination;

    2. List the names of all drivers of services which have St Andrews Bus Station in St Andrews as their origin or destination, in decreasing order of total hours driven;

    3. List the manager of the most connected station, measured by the number of services which have that station as their origin or destination.

    4. For the bus stop ”South Street, St Andrews” list in the chronological order arrival times at this stop, origins, destinations, and service numbers of all bus services passing this stop between 8 am and 6 pm.

Furthermore, create at least 3 new queries, stating them as precisely as you can in plain English, and including the corresponding seven SQL queries and their results in the report.

Finally, create at least two appropriate views, stating what the view represents (in plain English), and including the SQL statements used to create the views, and their output in the report.

Writing SQL code, you should adhere to some consistent coding style (for example, to the “SQL Style Guide” by Simon Holywell, https://www.sqlstyle.guide/ or any other consistent convention), and use sensible names of attributes, capitalisation, indentation, blank lines and comments to make your code readable and understandable.

Include all descriptions in plain English, corresponding SQL commands and their outputs in the report, either as copied and pasted text (recommended), or as screenshots.

When including SQL code and input/output from the Command Line Shell for SQLite, pay atten-tion to its formatting - in particular, use monospace font to preserve code indentation and tables layout. It is acceptable to use screenshots instead, provided that they are of high quality. To improve screen-shots readability, you can resize windows, change font size, and change settings to use dark text on a light background; note however that screenshots with SQL code are less usable since the text on them can not be searched and copied.

Task 4: Reflection

Describe briefly your experience of the process of converting the E–R model into SQL and working with data, linking back to the work done in Tasks 1–3. What did you feel you did well? What did you find challenging? What problems did you encounter, if any, and how did you try to resolve those? What would you do differently, if anything? This should be a short reflective section of your report. You can say as much or as little as you want, but as guidance, between half a page to a page should be enough.

3
Submission

Prepare a zip archive, containing two files:

    • the report in PDF format, with sections per task as above;

    • the buses.sql script in plain text format.

If you have correctly followed the requirements of Tasks 2 and 3, the buses.sql should contain complete SQL code needed to reproduce your work on these tasks. It is recommended to check before submission that your code is runnable on school systems by logging in via ssh into a lab machine, copying there the buses.sql script, running it with sqlite3 --init buses.sql and checking that it runs without errors (for further details on accessing lab machines, see CS Systems Wiki https: //systems.wiki.cs.st-andrews.ac.uk/index.php/Working_remotely).

Submit the archive as specified above via MMS by the deadline, checking to make sure that the version you submit is the one you mean to submit.

Marking

The basic requirements for this practical are:

    • A correct translation of the given E–R model into corresponding relational schema, together with primary and foreign keys;

    • Correct SQL implementing the schema (should work in SQLite);

    • SQL corresponding to the given queries, and to the self-defined queries (should work in SQLite);

    • Correct and appropriate view definitions (should work in SQLite);

    • Runnable without error messages, properly formatted and appropriately commented SQL code in the buses.sql script.

A competent work addressing all these requirements will get marks up to 16. Marks of 17 and above will be awarded for work addressing all the basic requirements and going further in terms of (one or more of):

    • Evidence of an in-depth consideration of data types and constraints;

    • Relevant and interesting new queries and views;

    • Good style in using SQL, perhaps by the use of joins or views;

    • Report demonstrating excellent understanding of SQL, of the relationship between conceptual and logical data models and their SQL implementations;

    • Any other work which goes out of the basic specifications given to demonstrate excellence in understanding and/or technique.

See also the standard mark descriptors in the School Student Handbook, which apply as usual: https://info.cs.st-andrews.ac.uk/student-handbook/learning-teaching/feedback. html#Mark_Descriptors


Policies and Guidelines

The following policies apply:

– The standard penalty for late submission (Scheme B: 1 mark per 8 hour period, or part thereof): https://info.cs.st-andrews.ac.uk/student-handbook/learning-teaching/assessment. html#Lateness_Penalties

– The University policy on Good Academic Practice: https://www.st-andrews.ac.uk/students/ rules/academicpractice/

4