COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12]
Question 11 (6 marks)
Consider the following spreadsheet used by a lecturer to manage marks in her courses:
A
B
C
D
E
F
G
3312345
John Smith
3978
COMP3123
ass1
10
8
3312345
John Smith
3978
COMP3123
ass2
20
14
3312345
John Smith
3978
COMP3321
proj
35
30
3312345
John Smith
3978
COMP3345
lab1
5
4
3234567
Cynthia Wang
3648
COMP3123
ass1
10
9
3234567
Cynthia Wang
3648
COMP3321
proj
35
34
3234567
Cynthia Wang
3648
COMP3345
lab1
5
3
3234567
Cynthia Wang
3648
COMP3345
lab2
5
5
3255555
John Smith
3645
COMP3345
lab1
5
1
3255555
John Smith
3645
COMP3345
ass1
8
4
…
…
…
…
…
…
…
The columns in the spreadsheet represent the following:
A
Student ID number
B
Student’s name
C
Degree being studied by the student
D
Course code
E
Assessment item within the course
F
Maximum possible mark for this item
G
Student’s mark for this item
You may assume that students only ever enrol in one degree.
Exercises:
a. Based on your understanding of the problem, aided by the values in the spreadsheet, identify the functional dependencies in the above table.
b. Using the BCNF decomposition algorithm, convert ABCDEFG into a BCNF schema.
At each step, you must show the relevant functional dependencies and the primary key for each table.
c. 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 q11.txt Submit via: submit q11.txt
End of Question