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:
![2095_Database.png](https://secure.tutorsglobe.com/CMSImages/2095_Database.png)
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).
![1570_schema.jpg](https://secure.tutorsglobe.com/CMSImages/1570_schema.jpg)
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.