First page Back Continue Last page Graphics

Schema Modification Exercise 2


Notes:

The description of the database does not contain any fds or mvds.
Hence the key dependencies (given through the underlining) are the only fds.
Note that “a crew menber may have several specific qualifications” is not a constraint. It just reassures that there is no fd cnr->fname, cnr->pltype or cnr->fname,pltype.
The new constraint “Each crew member has only one qualification” is cnr->fname. Because of this the relations crew and qualification are no longer in BCNF. We can use the fd for decomposition.
The second new constraint is the mvd fname->->cnr or fname->->pltype in the relation qualification. The relation qualification is thus no longer in 4NF. Because the requirement is only to guarantee BCNF we do not have to use this constraint, but we can if we wish.
BCNF decomposition (2 steps) yields:
crewmember(cnr, cname)
qualification_1(cnr, fname) qualification_2(cnr, pltype)
function(fname, pltype, min, max)
plane(pltype, nbseats)
crew_1(cnr, fname) crew_2(cnr, flnr)
flight(flnr, pltype, from, to)