1) For each of the following relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF relations. Functional dependencies (other than those implied by the primary key) are shown where appropriate.
a. CLASS(CourseNo, SectionNo)
b. CLASS(CourseNo, SectionNo, Room)
c. CLASS(CourseNo, SectionNo, Room, Capacity)
[FD:Room-Capacity]
d. CLASS(CourseNo, SectionNo, CourseName, Room, Capacity) [FD:FD:ourseNo->CourseRoom; CRoom-Capacity]
2) The Relation below stores information about weather observations made in the Albany area. You can assume that there are many observations made each day, that observer names are all unique, and that each observer measures the weather at just one location per day. Convert the relation them into a 3NF model and show the referential relationships. Don't add any additional fields.
(Date, ObserverName, Location, ObserverPhone, ObserverAddress, Time, CloudCover, Temperature, Humidity)
3) The Grade Report Table below shows a relation called Grade Report for a university. For this relation:
Draw a relational model and diagram the functional dependencies in the relation.
In what normal form is this relation in?
Decompose Grade Report into a set of 3NF relations.
Draw a relational model for your 3NF relations and show the referential relationships.
|
StudentID
|
StudentName
|
CampusAddress
|
Major
|
CourseID
|
CourseTitle
|
Instructor Name
|
Instructor Location
|
Grade
|
|
168300458
|
Williams
|
208 Brooks
|
IS
|
IS 350
|
Database Mgt
|
Codd
|
B104
|
A
|
|
168300458
|
Williams
|
208 Brooks
|
IS
|
IS 465
|
Systems Analysis
|
Parsons
|
B317
|
B
|
|
543291073
|
Baker
|
104 Phillips
|
Acctg
|
IS 350
|
Database Mgt
|
Codd
|
B104
|
C
|
|
543291073
|
Baker
|
104 Phillips
|
Acctg
|
Acct 201
|
Fund Acctg
|
Miller
|
H310
|
B
|
|
543291073
|
Baker
|
104 Phillips
|
Acctg
|
Mkgt 300
|
Into Mktg
|
Bennett
|
|
|