[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #7:    Relational Normal Forms

Reading Assignment

Please don't forget to read the following chapter from the book by Elmasri and Navathe, 3rd Edition (it was already a reading assignment on the last homework):

• Chapter 14, "Functional Dependencies and Normalization for Relational Databases"

Exercise 7 (12 Points)

The following table is used to store information about books and their authors:

• A book can have multiple authors. There is one row for every author of a book. E.g. the data of "Fundamentals of Database Systems" appear in two rows, one with author "Elmasri" and NO=1, and one with author "Navathe" and NO=2. Since authors do not always appear in alphabetical order (e.g. "Silberschatz/Korth/Sudarshan"), "NO" is used to keep track of their sequence.
• One author can write many books.
• It is possible that there are books with the same title but different authors. E.g. I have checked that there are several unrelated books called "Database Management" and several books from different authors called "Database Management Systems".
• It is possible that books have the same author and the same title, but different ISBNs (e.g. a paperback and a hardcover edition).
• The ISBN uniquely identifies a single book. E.g. it is not possible that two books have the same ISBN, but different titles or publishers.
• It is not possible that the same author appears in two different positions in the author list of the same book.

    a)

Please list all FDs which hold for this table. Of course, you do not need to list trivial or implied FDs.

    b)

Please determine a key for this table.

    c)

Is this table in BCNF? Explain your answer. (I.e. if it is not in BCNF, give an example of an FD which violates the BCNF condition. If it is in BCNF, explain for every FD why the condition is not violated.)

    d)

Please transform the table into BCNF (if it should not be in BCNF).

Due Date:

Please submit your solution on next Wednesday (March 22) before the lecture.

Stefan Brass (sbrass@sis.pitt.edu), March 15, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/h7_bcnf.html   [HTML 3.2 Checked]