Some of your old high-school friends have opened a new restaurant, and it has be

Question

Some of your old high-school friends have opened a new restaurant, and it has become so wildly successful that they need a computer to keep track of dinner reservations. Not knowing all that much about databases, they have created a single table to hold reservation information:

RESERVATION (Date, Time, Name, Phone, VIP)

Some customers have only made a single reservation, but many of them have multiple reservations in the table. No two customers have the same name and phone number, but some different customers have either the same name or same phone number. VIP is a Boolean - true value indicates the very best customers, who receive extra special service.

a. What are the possible key(s) and superkeys(s) for this relation? Justify your answer in terms of functional dependencies and closures.

b. Identify any “bad” functional dependencies in the RESERVATION table and use them to decompose it into relations that are BCNF. Are all the functional dependencies preserved? Explain.

Explanation / Answer

a)

The only table in the database is RESERVATION (Date, Time, Name, Phone, VIP)

The possible keys for this relation are:

(Date, Time, Name, Phone) and (Date, Time, Name, Phone, VIP).

The super key for this relation is (Date, Time, Name, Phone, VIP).

The only trivial functional dependency is Name, PhoneàVIP

b)

The table RESERVATION (Date, Time, Name, Phone, VIP) is in third normal form. However, it is not in boyce codd normal form. The trivial functional dependency Name, PhoneàVIP does not satisfy the boyce codd normal form as {Name, Phone} is not a super key.

To convert the RESERVATION (Date, Time, Name, Phone, VIP) into boyce codd normal form, decompose the RESERVATION table into following two tables:

RESERVATION (Date, Time, Name, Phone)

CUSTOMER (Name, Phone, VIP).

In RESERVATION table, { Date, Time, Name, Phone} is the primary key. There are not trivial functional dependency. Therefore, it satisfies boyce codd normal form.

In CUSTOMER table, { Name, Phone} is the primary key and also the super key. The only trivial functional dependency is Name, PhoneàVIP and it satisfies the boyce codd normal form as { Name, Phone} is the super key.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.