COMP3311 Final Exam 21T3

The University of New South Wales COMP3311 Database Systems Final Exam 21T3

Database Systems

Question 10 (6 marks)

Consider the following spreadsheet containing information about PhD students and their topic and supervisor:

A	B	C	D	E	F
3312345	David Wang	Database Query Optimisation	9300123	Prof Pickle	Databases
3456789	Peter Piper	Simulation of Rat Intelligence	9512345	Prof Zany	AI
3334445	Tammy Tiger	What's Wrong with MongoDB?	9300123	Prof Pickle	Databases
3987654	Sarah Snook	Wifi, Wifi, Wherefore art thou?	9054321	Prof Iso	Networks
3876543	Roger Dodger	After the Singularity	9512345	Prof Zany	AI

The columns in the spreadsheet represent the following:

A	Student ID number
B	Student name
C	Thesis topic
D	Supervisor ID number
E	Supervisor name
F	Supervisor specialty

You may assume that students only ever do one PhD, and don't change topic or supervisor.

Exercises:

1. Based on your understanding of the problem, aided by the values in the spreadsheet, identify the functional dependencies in the above table.

2. Using the BCNF decomposition algorithm, convert ABCDEF into a BCNF schema.
   At each step, you must show the relevant functional dependencies and the primary key for each table.

3. Describe briefly, in english, what each of the tables in the final BCNF schema represents.

Instructions:

• Type your answers to this question into a file called q10.txt
• Submit via:   give cs3311 exam_q10 q10.txt  or  Webcms3