Database 1
Database Design

Prof. dr. Paul M.E. De Bra

Dept. of Computer Science

Eindhoven Univ. of Technology

Schema Modification

Traditional database design process:

study the requirements (“description”)

design an entity-relationship model

determine the constraints (like 1..n relationships); note that some constraints cannot be expressed in E-R

translate to a relational schema; normally in BCNF; further decomposition into 4NF may be needed;

Schema modification deals with translating changes to the requirements into changes of the database scheme.

Schema Modification Approaches

Design “from scratch”:

study the changed requirements;

design the E-R schema again, the constraints, the translation to a relational scheme

problem: the new relational scheme may be completely different from the original one

Adapt the design:

undo some “decomposition” because of removed constraints;

use new decomposition to ensure 3NF, BCNF or 4NF after adding the new constraints

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.

Solution:

Use the fd {tnr}  {coursensme} in the “teaches” relation to decompose that. Result:

teacher(tnr, tname, nb-hours)

qualification(tnr, coursename, grade)

program(grade, coursename, nb-hours)

teaches_1(tnr, coursename)	teaches_2(tnr, grade)

We do not need to use the mvd {tnr}  {coursename} in the relation “qualification” to obtain

qualification_1(tnr, coursename) qualification_2(tnr, grade)

because using the mvd results in 4NF and only BCNF was required.

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.

Solution:

First solution:

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)

Question: can you show that the relations crew_1 and
qualification_2 are redundant and may be removed?

In this colstruction we look at multivalued and join dependencies.

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)

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)

In order to show that crew_1 is not needed we can use the constraint that crew members are only allowed to serve in a function for which they are qualified.

In order to show that qualification_2 is not needed we need to apply some common sense (not explicitly given in the description) on the original scheme:

A crew member can only be qualified for a function on a certain plain type if that function exists on that plain type. We also assume that when the function exists in the relation function the “max” number cannot be 0 which would mean that the function in practice does not exist after all. We also need to use the new mvd in order to prove that we can remove qualification_2.

The complete argumentation is given in the answers to the exam of June 30, 2003.

Databases 1, Colstructie 15

Database Design, (vertical) decomposition

Paul De Bra

2003-04-01T22:52:54

Paul De Bra