Tutorial 2

Functional dependencies and BCNF

This Tutorial uses the same domain as Tutorial 1.

The things that are to be recorded are (I repeat the information here in case you don't have the exercises from the first week):

• Course names
• Teacher names
• Teacher titles (optional, e.g. Professor)
• Class room names
• Number of students taking a course
• Day and time of classes

Classes in a particular course are given at the same day and time each week, possibly more than once each week. Each course is given by one teacher. A teacher can hold several courses, but will only hold classes in the same class room. More than one teacher could have classes in the same class room (though of course not at the same time).

The full relation could thus be given as:

Classes(courseName, teacherName, teacherTitle, roomName, #students, weekday, time, #seats)

• Argue for sensible FDs and keys given the domain description above.
• Decompose the full Classes relation so that it fulfils BCNF.

Car rental

Here's another domain that you can practise on to do the same thing. A small car rental business wants a database to store information about customers, cars and rentals. The full relation is

RentACar(custNr, custName, custAddress, carType, regNr, rentStart, rentFinish)

• Argue for sensible FDs and keys.
• Decompose the full relation so that it fulfils BCNF.

Third Normal Form (3NF)

Suppose we have relation R(A,B,C,D,E,F) with functional dependencies AB → C, CD → E, E → B, B → F, E → D, E → F.

State which FDs listed above violate third normal form (3NF).

Decompose relation R to 3NF.

Multivalued Dependecies and 4NF

Let's extend the domain to encompass students. A student is identified by his or her name (assumed to be unique). A student is affiliated with one of the four student houses; Gryffindor, Hufflepuff, Ravenclaw and Slytherin. We also want to store the year that the student started (mini-quiz: Why is this better than to store which year the student is on now, e.g. a 4th-year student?).

Students read courses (we don't care about those who have finished courses, or the grades). Each student can read several courses at the same time, and of course several students can be enrolled in the same course.

Furthermore we add information about course literature. For each course we want to store the books (could be more than one) and their authors.

Classes(courseName, teacherName, teacherTitle, roomName, weekday, time, studentName, studentHouse, studentYear, book, author)

• Argue for sensible FDs, MVDs and keys.
• Decompose the relation so that it fulfils BCNF. Why is this not good enough?
• Decompose the relation so that it fulfils 4NF.

Book exercises

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 3.2.1
• 3.2.2
• 3.2.6
• 3.3.1
• 3.6.1
• 3.6.2
• 3.6.3

If you have the first edition of the textbook, do exercises:

• 3.5.1 (similar to 3.2.1 in 2/E)
• 3.5.2 (similar to 3.2.2 in 2/E)
• 3.5.4 (similar to 3.2.6 in 2/E; new(a)=old(c); new(b)=old(a); new(c)=old(b))
• 3.6.1 (similar to 3.3.1 in 2/E)
• 3.7.1 (similar to 3.6.1 in 2/E - columns have been renamed)
• 3.7.2 (same as 3.6.3 in 2/E)
• 3.7.3 (similar to 3.6.2 in 2/E)