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

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 (Gillenson, 2011).

  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)

    Central Hospital entity-relationship diagram

    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