Reference no: EM132308872

Theory Assignment

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 S#, P# and J# 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:

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:
1. B → E
2. E → A
3. A → D
4. 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:
1. A → C
2. C → DE
3. A → D
4. AD → EF
5. B → AC
6. 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.

Attachment:- Database Management Systems.rar