First page Back Continue Last page Graphics
Schema Modification Exercise 2
Consider the following database schema:
- crewmember(cnr, cname) qualification(cnr, fname, pltype)
- function(fname, pltype, min, max) plane(pltype, nbseats)
- crew(cnr, flnr, fname) flight(flnr, pltype, from, to)
- This scheme describes the assignment of crews to flights. A flight is flown with a certain plane type (pltype, e.g. Boeing 747) that has a number of seats (nbseats). Each plane type requires a number of crew members with specific qualifications. A boeing 747 may require 3 crewmembers with a qualification of pilot for Boeing 747 and between 8 and 15 crewmembers with a qualification of steward(ess) on a Boeing 747. Crew members may have several specific qualifications (fname, pltype pairs). This scheme is in BCNF (?)
- Modification:
- The property “crew members may have several qualifications” is removed.
- Each crew member has only one qualification (e.g. pilot, steward(ess))
- A crew member with a qualification for a specific plane type is also qualified for the same function on all plane types (that have that function).
- Modify the database scheme so that the new scheme is again in BCNF.
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)