Homework SourseStudentID StudentName Activity ActivityFee AmountPaid 100 Jo
StudentID
StudentName
Activity
ActivityFee
AmountPaid
100
Jones
Golf
75.00
75.00
100
Jones
Skiing
200.00
0.00
200
Davis
Skiing
200.00
0.00
200
Davis
Swimming
40.00
40.00
300
Garrett
Skiing
200.00
100.00
300
Garrett
Swimming
40.00
20.00
400
Jones
Golf
75.00
55.00
400
Jones
Swimming
40.00
40.00
1. Using the table above, list all functional dependencies
2. Based on the functional dependencies, transform the above table into in BCNF.
3. State:
a.) Primary keys for the BCNF table(s):
b.) Candidate keys for the BCNF table(s):
c.) Foreign keys for the BCNF table(s):
d.) Composite keys for the BCNF table(s):
4. In the original table above,
a.) if we delete both of the Jones (StudentID = 100 and StudentID = 400), describe what happens. What is the anomaly type?
b.) if we want to ADD a NEW ActivityFee of $30.00, describe what is needed. What is the anomaly type?
c.) if we change the Activity name of Swimming to Diving in only one row, describe what happens. What is the anomaly type?
5. Given the following table STUDENT;
STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Course)
Assume that students pay different dorm costs, depending on the type of room they have. Also, all members of a club pay the same cost.
a). State ATLEAST TWO multivalued dependencies.
| StudentID | StudentName | Activity | ActivityFee | AmountPaid |
| 100 | Jones | Golf | 75.00 | 75.00 |
| 100 | Jones | Skiing | 200.00 | 0.00 |
| 200 | Davis | Skiing | 200.00 | 0.00 |
| 200 | Davis | Swimming | 40.00 | 40.00 |
| 300 | Garrett | Skiing | 200.00 | 100.00 |
| 300 | Garrett | Swimming | 40.00 | 20.00 |
| 400 | Jones | Golf | 75.00 | 55.00 |
| 400 | Jones | Swimming | 40.00 | 40.00 |
Solution
1)
StudentName is the functionally dependent on StudentID as StudentID is unique
so we can find Student name on the basis of it.
StudentID -> StudentName.
3)
a) Primary Key for BCNF table is StudentID
b) Candidate key for the BCNF table is StudentID
c) Foreign Key for the BCNF table is StudentName
d) composite key for the BCNF table is StudentID
