| 4. |
(a) For each of the following
relations, indicate the normal form in which the relation has been written. Functional
dependencies (other than those implied by the primary key) are shown where appropriate. |
[4] |
|
|
(i) CLASS (COURSE NO, SECTION NO) |
|
|
|
3NF |
|
|
[1 mark] |
|
|
|
(ii) CLASS (COURSE NO, SECTION NO,
ROOM) |
|
|
|
3NF |
|
|
[1 mark] |
|
|
|
(iii) CLASS (COURSE NO, SECTION NO,
ROOM, CAPACITY) |
|
|
|
ROOM ® CAPACITY |
|
|
|
2NF |
|
|
[1 mark] |
|
|
|
(iv) CLASS (COURSE NO, SECTION NO, COURSE
NAME, ROOM, CAPACITY) |
|
|
|
ROOM ® CAPACITY, COURSE NO ® COURSE NAME |
|
|
|
1NF |
|
|
[1 mark] |
|
|
|
|
|
(b) For those relations in (a)
which are not already in 3NF, carry out the normalization to 3NF. |
[4] |
|
|
CLASS (COURSE NO,
SECTION NO, ROOM) |
|
|
|
ROOM (ROOM,
CAPACITY) |
|
|
[2
marks] |
|
|
|
COURSE (COURSE NO,
COURSE NAME) |
|
|
|
CLASS (COURSE NO,
SECTION NO, ROOM) |
|
|
|
ROOM (ROOM,
CAPACITY) |
|
|
[2
marks] |
|
|
|
|
|
(c) The following form shows a
user view of the class list for Jubilee College. Convert this user view to a set of 3NF
relations. |
[12] |
|
Jubilee College
Class list
Summer term 1997 |
Course no: DB212
Course tittle: Database Management Systems
Instructor name: Codd
Instructor location: W/303 |
| Student No |
Student Name |
Major |
Grade |
| 22314 |
Bright |
IS |
A |
| 12785 |
Collins |
CS |
B |
| 64984 |
Leung |
IS |
A |
| ... |
|
|
|
|
|
|
You may make the following
assumptions: |
|
|
|
(i) Each instructor has a unique location |
|
|
|
(ii) Each student has a unique major |
|
|
|
(iii) Each course has a unique title |
|
|
You should include an explanation
of how to get from 1NF to 2NF, and from 2NF to 3NF. |
|
|
 |
|
|
1 mark for
UNF, 2 marks for 1NF, 3 marks for 2NF, 4 for 3NF. 1 mark for explanation of how to get
from 1NF to 2NF, 1 mark for explanation of how to get from 2NF to 3NF |
|
|
[12
marks] |
|