First page Back Continue Last page Graphics
Schema Modification Exercise 1
Consider the following database schema:
- teacher(tnr, tname, nb-hours)
- qualification(tnr, coursename, grade)
- program(grade, coursename, nb-hours)
- teaches(tnr, coursename, grade)
- This scheme describes the assignment of teachers to courses and grades in a certain school. Every teacher (with a number and name) works for a number of hours and is qualified to teach certain courses to certain grades. Every grade gets courses for a number of hours a week. This number is never 0. Teachers only teach courses in a grade when they are qualified for it. This scheme is in BCNF (?)
- Modification:
- Every teacher is allowed to teach only one course in this school (but may still be qualified for more courses).
- Every teacher is qualified for teaching certain courses and for teaching in certain grades. He is qualified to teach each course in each grade.
- Modify the database scheme so that the new scheme is again in BCNF.
Notes:
The first new constraint is the fd tnr->coursename in the relation teaches. This relation is no longer in BCNF. Decomposition results in
teaches_1(tnr, coursename) teaches_2(tnr, grade)
Note that the constraint has no influence on the relation qualification.
The second constraint is an mvd. Since the question is to ensure BCNF only we can ignore it.
If we were to use it we would decompose the qualification table into
qualification_1(tnr, coursename) qualification_2(tnr, grade)