Logical Database Design

This assignment is ungraded and should not be submitted. The questions review key concepts from the reading for this module.

Expected duration

1.5–2 hours

Deadline

None (no explicit submission is required)

Points

None

Help Policy

Authorized Resources

Any

Notes

You may jointly work on this assignment with classmates

Assignment

Answer the following questions from Fundamentals of Database Management Systems (solutions):

1. Explain the following terms (Question 7.7):

   • Functional dependency

     A functional dependency indicates that the value of an attribute is dependent on the value of another attribute (or group of attributes).

   • Determinant

     The determinant is the attribute on which another attribute is functionally dependent.

2. What characterizes tables in first normal form? Why is such data problematic? (Question 7.9)

   First normal form (1NF) is characterized by every attribute of every record having a single value. In addition, 1NF requires identifying the primary key of each table.

3. What is a partial functional dependency? What does the term “fully functionally dependent” mean? (Question 7.10)

   A partial functional dependency exists when a non-key attribute depends on only part of the (primary) key. The term fully functionally dependent means that a non-key attribute depends on the entire (primary) key.

4. What is the definition of data in second normal form? (Question 7.12)

   Second normal form (2NF) is characterized by every non-key attribute being fully functionally dependent on the primary key.

5. What is a transitive dependency? (Question 7.13)

   A transative dependency exists when a non-key attribute is functionally dependent on another non-key attribute.

6. What is the definition of data in third normal form? (Question 7.15)

   Third normal form (3NF) is characterized by every attribute being fully functionally dependent on the primary key and by the absence of transitive dependencies.

7. What are the characteristics of data in third normal form? (Question 7.16)

   There are no partial functional dependencies and no transitive dependencies. Unless there is an exception condition (e.g., a functional dependency where the determinant is not a superset of a candidate key), then the database is free of data redundancy.

8. Convert the Central Hospital entity-relationship diagram that follows into a well-structured relational database. (Exercise 7.2)

   

   Note: Foreign keys are not indicated in the following relations.

   Insurance Company

   Company Name,* Telephone, City, State, President

   Claim

   Claim Number,* Company Name, Patient Number, Date, Diagnosis, Amount

   Patient

   Patient Number,* Patient Name, Age, Address, City, State

   Operation

   Patient Number,* Doctor Number*, Operation Name,* Date,* Start Time, End Time, Operating Room Number

   Operation Type

   Operation Name,* Duration, Cost

   Doctor

   Doctor Number,* Doctor Name, Office Number, Telephone, Department Number, Backup (Doctor Number)

   Degree

   Doctor Number,* Degree Type,* Major, University, Year

   Department

   Department Number,* Office Number, Telephone, Administrator

   Nurse

   Nurse Number,* Nurse Name, Certification, Year Hired, Department Number, Supervisor (Nurse Number)

9. Video Centers of Europe, Ltd., is a chain of movie DVD rental stores. It must maintain data on the DVDs it has for rent, the movies recorded on the DVDs, its customers, and the actual rental. Each DVD for rent has a unique serial number. Movie titles and customer numbers are also unique identifiers. Assume that each movie has exactly one “star.” Note the difference in the year that the movie was originally filmed and the date that a DVD—an actual disk—was manufactured. Some of the attributes and functional dependencies in this environment are as follows:
   • Attributes
     • DVD Number
     • Manufacture Date
     • Movie Title
     • Star
     • Year Filmed
     • Length [in minutes]
     • Customer Number
     • Customer Name
     • Customer Address
     • Rental Date
     • Return Date
     • Fee Paid
   • Functional Dependencies
     • DVD Number \(\rightarrow\) Movie Title
     • DVD Number \(\rightarrow\) Manufacture Date
     • Movie Title \(\rightarrow\) Star
     • Movie Title \(\rightarrow\) Length
     • Movie Title \(\rightarrow\) Year Filmed
     • Customer Number \(\rightarrow\) Customer Name
     • Customer Number \(\rightarrow\) Customer Address
     • DVD Number, Customer Number, Rental Date \(\rightarrow\) Return Date, Fee Paid

   For each of the following tables, first write the table’s current normal form (as 1NF, 2NF, or 3NF). Then, take those tables that are currently in 1NF or 2NF and reconstruct them as well structured 3 NF tables. Primary key attributes are indicated by an asterisk. Do not assume any functional dependencies other than those shown. (Exercise 7.3)

   1. Movie Title,* Star, Length, Year Filmed

      3NF

   2. DVD Number,* Customer Number,* Rental Date,* Customer Name, Return Date, Fee Paid

      1NF – e.g., Customer Name is not fully functionally dependent on the primary key

      Customer

      Customer Number,* Customer Name

      Rental

      DVD Number,* Customer Number,* Rental Date,* Return Date, Fee Paid

   3. DVD Number,* Manufacture Date, Movie Title, Star

      2NF – e.g., Star is transitively dependent on Movie Title

      DVD

      DVD Number,* Manufacture Date, Movie Title

      Movie

      Movie Title,* Star

   4. Movie Title,* Customer Number,* Star, Length, Customer Name, Customer Address

      1NF – e.g., Star is not fully functionally dependent on the primary key

      Movie

      Movie Title,* Star, Length

      Customer

      Customer Number,* Customer Name, Customer Address

      Rental

      Movie Title,* Customer Number*

      Note: Omitting this last table effectively drops the relationship between movies and customers, which would be a loss of data that exists in the original tables.

   5. DVD Number,* Customer Number,* Rental Date,* Return Date, Fee Paid

      3NF