CSC 370 - Data Model Checker

 CSC 370 - Data Model Checker

 Assignment Goals

In this assignment you will:

  * demonstrate data modelling skills with a combination of SQL and python

    + write SQL queries to inspect the model of a MySQL database
    + write python code to connect to a MySQL database and handle the output of queries, including exception handling

## Task

A well implemented relational database should enforce all the constraints that were identified (even implicitly) during the design phase, such as the functional dependencies, the check constraints, and the referential integrity. In this assignment, we will write generic testing software that confirms whether an implementation of a database design is correct. You will write a series of tester functions in python that connect to a MySQL database and probe it to check whether a given constraint is being enforced correctly.

As an example, imagine that you have two relations: *Employee(<ins>employee_id</ins>, employee_name, dept_id)* and *Department(<ins>dept_id</ins>, dept_name)*. Naturally, we might expect in this case that a SQL query like the following should only return one row per employee:

```sql
SELECT *
FROM `Employee`
    NATURAL JOIN `Department`;
```

If this is not the case, we can be confident that Department.dept_id has not been set up as a primary key; if it were, it should not be possible for more than one Department tuple to match each Employee on dept_id. If you designed a function that could take in two relations likes this and the foreign key relationship between them, you could connect to a database and check that this behaviour is as expected.

Unfortunately, this example above only shows that a primary key _does not_ exist; it is insufficient to conclude that the primary key _has_ been created. You would need a better test design to be confident that you will score full marks on this assignment. As a strong hint, you may find that queries that modify the contents of tables combined with exception handling in your python code will be a more robust approach, though it certainly isn't the only one.

Specifically, you are given a python library, `DataModelChecker.py`, to implement. The API has already been exposed but none of the functions contain bodies yet. You should implement these six functions so that they can be used to test the correctness of any MySQL implementation of a relational database design.

It is important to remember that this is an exercise in software testing and creative thinking. You will probably want to create your own sandbox MySQL database and try out different table definitions to improve test coverage.


## Submission

You should submit `DataModelChecker.py` _without renaming it_, after implementing the six functions that contain TODO comments in their bodies. You are welcome to add other classes and auxiliary functions to support your solution (and improve code quality), but these must all appear in the same `DataModelChecker.py` file that you submit.

## Evaluation

Your functions will be evaluated with a series of unit tests and your score on the assignment will be the number of unit tests that you pass. Each test will run your function with a boundary case input against a localhost MySQL database.

For example, a test of `confirmSuperkey()` might pass as input a table for which no primary key was ever defined in the database and ask if a particular set of attributes is an enforced superkey or not, i.e., allows insertions that violate the supposed key constraint. If the function returns false, the test passes; otherwise it fails. Approximately twenty such test cases will be defined and tested.

Note that we will modify our testing dataset to avoid hard-coded solutions, such as by changing constraints between the pre-test and test database. You should not make assumptions that a hard-coded solution that passes pre-testing will also pass in the final evaluation. In particular, the actual result (true or false) for each test case will be quasi-randomly chosen in the final test database by adding or removing particular constraints. Moreover, if you create temporary files, you are responsible for monitoring their existence: there is no guarantee that we will run test cases in a deterministic order.

A subset of the test cases will be provided; however, these are only _structurally_ correct. The underlying database and therefore also the correct answer will change with 50% probability between what is provided and the final grading. You will need to also identify some important boundary cases to test that have not been released in advance. The pre-released cases are available in `tests.py`, some of which can be tested by yourself using `build_test_database.sql`; the SQL script, in particular, shows the permissions that the test user will have, which you can use to gauge which approaches to the assignment are permitted. Marking will take place by running:

```bash
mysql -uroot < build_test_database.sql
python3 tests.py
```

You can safely make the following assumptions:
  * All tables and attributes exist
  * All attributes will be of INT type
  * All primary keys will contain one AUTO_INCREMENT attribute, namely the first one
  * Attributes that are not part of the function argument list be NULLABLE and will have default values
  * All tables will be empty before your code is marked, though they will not be cleared between unit tests
  * Each test case will use an independent set of tables that are not related to those in other test cases
  * Tests will be run using `mysql  Ver 8.0.33 for macos10.15 on x86_64 (Homebrew)` and `Python 3.11.3` on Mac OS Catalina 10.15.7.

Pre-marking will occur at an arbitrary point in the morning on the following dates. You should submit prior to midnight the night before to be certain to receive a pre-grading update:

  * Thursday, 27 July 2023
  * Friday, 28 July 2023
  * Saturday, 29 July 2023
  * Sunday, 30 July 2023
  * Monday, 31 July 2023
  * Tuesday, 1 August 2023

The instructor reserves the right to award a score of 0 to functions that have not been implemented at all (e.g., simply return True or return False with no dependence on the input parameters).

## Dataset

For this assignment, we will use random data to test your code, as exemplified in `build_test_database.sql`. It will be engineered to test particular boundary cases in the lessons. Functional dependencies may cross multiple tables, though these will share common attributes that facilitate NATURAL JOIN.
 dates in accordance with deadline extension; add sample test cases and database build script.