Cosc210 - database management systems - normalise a

Question

Assignment 1:

Aims

Revise and apply the concepts relating functional Dependencies.

Normalise a database schema to a desired normal form.

Construct a 3NF database schema, based upon a universal relation and a set of functional dependencies.

Optimise a query using a query-tree representation.

Questions Set

Question 1

a) Discuss insertion, deletion and modification anomalies and illustrate with simple examples why they are bad.

b) Why are normal forms alone not sufficient as a condition to ensure good database design?

Consider the following database for a system that keeps track of suppliers (S), parts(P) and projects(J) with sample values supplied:

The table (SPJ) represents shipments of parts from suppliers to individual projects. (The SNO, PNO and JNO fields are foreign keys that reference the S, P and J tables respectively.

Construct a relational algebra expression using symbolic notation for the following queries on this database. A Microsoft Word Document containing all of the relational algebra symbols is available here.

c) Get the names of projects that have at least one red part supplied to them.

d) The average quantity of each part supplied by each supplier to projects in London.

Question 2

The Scenario:

Suppose we have the following data set that contains information about students, the units they are studying and the degrees that they are enrolled in:

student_id	first_name	last_name	date_of_birth	degree	school	unit_code	unit_name	degree_length
55643	John	Smith	23/01/84	Bachelor of Science	Science and Technology	SCI100	Science in Practice	3
55643	John	Smith	23/01/84	Bachelor of Science	Science and Technology	COSC110	Introduction to Programming and the UNIX Environment	3
55643	John	Smith	23/01/84	Bachelor of Science	Humanities	PHIL102	The Art of Good Thinking	3
76423	Jane	Doe	4/6/1991	Bachelor of Science	Science and Technology	COSC100	Introduction to Informatics	3
76423	Jane	Doe	4/6/1991	Bachelor of Arts	Humanities	PHIL102	The Art of Good Thinking	3
76423	Jane	Doe	4/6/1991	Bachelor of Arts	Behavioural, Cognitive and Social Sciences	GEPL111	Earth in Crisis?	3
96744	Bob	Smith	7/5/1967	Bachelor of Arts	Humanities	PHIL102	The Art of Good Thinking	3
96744	Bob	Smith	7/5/1967	Bachelor of Arts	Humanities	CLLA101	Introduction to Classical Languages	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC110	Introduction to Programming and the UNIX Environment	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC100	Introduction to Informatics	3
22342	Alex	Wise	24/09/77	Bachelor of Computer Science	Science and Technology	COSC210	Database Management Systems	3
44398	David	Jackson	10/7/1991	Bachelor of Rural Science	Science and Technology	SCI100	Science in Practice	4
44398	David	Jackson	10/7/1991	Bachelor of Rural Science	Science and Technology	CHEM110	Chemistry 1	4
44398	David	Jackson	10/7/1991	Bachelor of Rural Science	Environmental and Rural Science	ECOL100	Ecology: Concepts and Applications	4

Here is a comma-separated-values (csv) file of the data presented above for you to review. This can be viewed with spreadsheet or text editing software and it may be easier to analyse the data using the sort and filter functions.

Task:

Your task is to design a relational database schema for the data provided. This database schema will need to be in 3NF. To do this you should first list all the functional dependencies that are implied from data, specify the key attributes and use these to normalise the schema so that it is in 3NF.

Once you have completed your relational schema, you should explain why each of the relations is in 3NF.

Question 3

Consider a database schema with attributes A, B, C, D, and E and functional dependencies:

B → E
E → A
A → D
D → E
Show that the decomposition of this schema into {AB}, {BCD}, and {ADE} is lossless. To do this, you should apply the algorithm (Testing for Nonadditive Join Property) introduced on page 546 of the prescribed text (7th edition). (Labeled algorithm 15.3)

Question 4

Consider the following functional dependencies over the attribute set A,B,C,D,E,F:

A → C
C → DE
A → D
AD → EF
B → AC
E → F
Find the minimal cover, then decompose the universial relation into lossless 3NF. Make sure that you document each step of the algorithm.

Question 5

Consider the following SQL Query:

SELECT fname, lname, pname
FROM WORKS_ON, PROJECT, DEPENDENT, EMPLOYEE
WHERE EMPLOYEE.ssn = DEPENDENT.essn AND EMPLOYEE.ssn = WORKS_ON.essn AND PROJECT.pnumber = WORKS_ON.pno
AND EMPLOYEE.sex = 'M' AND DEPENDENT.sex = 'F';

a) Construct an initial (i.e. Canonical) query-tree representation of this query. Make sure that your query tree is presented neatly using a graphics manipulation application such as xfig, InkScape or draw.io. These applications are available on turing for you to use.

b) Show how your canonical query-tree from part a) can be optimised using The Heuristic Algebraic Optimisation Algorithm (page 730 of the 7th edition text) . Make sure that you show your query tree after applying each step of the algorithm. As in part a), make sure that your diagrams are constructed using a graphics manipulation application such as Xfig, InkScape or draw.io. These applications are available on turing for you to use.

Assignment 2

Aims

Implement a database schema using SQL (in the PostgreSQL) database management system.

Construct a data-driven Java/Python application.

Implement appropriate error checking functionality in a distributed application.

The Scenario
In this assignment you will be constructing a relational database using the PostgreSQL DBMS and implementing a data-driven application using either the Java or Python programming language.

You have been tasked by the owners of MovieDirect, a small retailer, to re-develop their orders and shipments information system. Currently, the system uses three separate spreadsheets to keep track of customers, movies that are currently available and shipments out to the customers. This system is starting to become difficult to use and will prevent MovieDirect from effectively managing its long-term operations in its current state.

Your task is to develop a robust and scalable database solution for the information system that will effectively store MovieDirect‘s information and provide the capabilities for extracting information to improve sales and management of customers.

Exercise 1 - Database Design

The first tasks is to develop a database using the PostgreSQL DBMS, which will contain 3 tables (Customers, Shipments and Movies) linked together using foreign-key relationships. The referential diagram shown below (figure 1.) shows the structure of the database (i.e. how these tables should joined together).

Figure 1. The database schema for MovieDirect.

The SQL definitions for each of the tables will have the following fields:

Customers

customer_id - an integer and primary key for the customers table.
last_name - a character field that allows for up to 50 characters. This field should always have a value.
first_name - a character field that allows for up to 50 characters. This field should always have a value. 
address - a character field that allows for up to 200 characters.
city - a character field that allows for up to 50 characters.
state - character field that allows for up to 3 characters. This field should always contain a value from the followoing list: NSW, VIC, QLD, ACT, TAS, NT, SA, WA.
postcode - a character field that allows for up to 8 characters.
Movie

movie_id - an integer and primary key for the Movie table.
movie_title - a character field that allows for up to 100 characters. This field should always have a value.
director_last_name - a character field that allows for up to 50 characters. This field should always have a value.
director_first_name - a character field that allows for up to 50 characters. This field should always have a value.
genre - a character field that allows for up to 20 characters that should always contain a value from the following list of genres: Action, Adventure, Comedy, Romance, Science Fiction, Documentary, Drama, Horror.
media_type - a character field of up to 20 characters that can contain a value from the following list of mediums: DVD, Blu-Ray.
release_date - a date field.
studio_name - a character field that allows for up to 50 characters.
retail_price - A real field that should always have a positive value.
current_stock - An integer field that should have a value of 0 or more.

Shipments

shipment_id - an integer and primary key for the shipments table.
customer_id - an integer that should always have a value that references the ‘customer_id' field in the "Customer" table.
movie_id - an integer that should always have a value that references the ‘movie_id' field of the "Movie" table.
shipment_date - a date-type field.
Your task is to construct a set of SQL table definitions to create the Customers, Movies and Shipments tables. Your table definitions should be placed in a single SQL file that can be executed to create the whole database. Be aware of the order in which you create your tables - you can't reference a table that doesn't exist.

To implement the constraints outlined in the description, you will need to use foreign key constraints, CHECK operations, NOT NULL constraints and primary keys.

You should create your database using your "apps" database user the you used in practical session 4. This will allow you to connect your java applications up to the database for exercise 2.

Exercise 2 - A Data-driven Java Application

Your final task is to construct a client application (Java or Python) that will allow you insert new movie records into the movies table in the database. (Other functionality will be implemented in a later development phase). Your application should prompt the user to first enter the database name, their username and password. The program should then prompt the user to enter each piece of information for each move then connect to the database and attempt to insert a new record.

21641:~ mwelch8$ java exercise_2_220881088
******************************************************************

Welcome to MovieDirect

******************************************************************

datebase: a4_demo
user: mwelch8_apps
password: 11223344

Please enter the id for the new movie: 1112223
Please enter the title for the new movie: The Imitation Game
Please enter the director's first name: Morten
Please enter the director's last name: Tyldum
Please enter the genre of the movie: Drama
Please enter the media type: Blu-Ray
Please enter the movies's release date: 2015-01-07
Please enter the movies's studio: The Weinstein Company
Please enter the retail price of the Movie: 15.95
Please enter the number of copies in stock: 13

Success! A new entry for The Imitation Game has been entered into the database.

Your application will need to handle errors that may occur. These include:

Missing data/blank items (that can be dealt with on the client side before attempting to insert).

Database constraint violations that are returned to your program via exceptions. For example, if you attempt to enter a record into the database with a duplicate primary key, the database will return an error. You Client application should catch these errors (accessed through the SQLExecptions thrown by the insertRow(...) function) and prompt the user to re-enter the offending data.