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?