CPSC 343 Database Theory and Practice Fall 2017

Practice Problems - ER-to-Relational Mapping and Normalization

  1. Map the ER schema below into a relational schema. (Click for a larger version.) Be sure to specify all primary key, referential integrity (foreign key), and NOT NULL constraints.

    (solution)

    1. Map the ER schema below (click for a larger version) into a relational schema using each of the following approaches:

      1. multiple relations - superclass and subclasses
      2. multiple relations - subclasses only
      3. single relation - single type attribute
      4. single relation - multiple type attributes

      If an approach isn't applicable to this situation, explain why.

    2. Compare the results. Which approach do you think is the best for this situation? Explain.

    (solution)

  2. Map the ER schema below into a relational schema. (Click for a larger version.) Be sure to specify all primary key, referential integrity (foreign key), and NOT NULL constraints.

    (solution)

  3. Consider the following relation for published books:

    BOOK(title,author,booktype,listprice,author_affiliation,publisher)
    

    The functional dependencies are:

    1. What normal form is this relation in? Explain your answer.

    2. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

    (solution)

  4. A particular hard disk drive has a serial number, a model number, and a capacity; it is made by a manufacturer and released in a particular batch; and is sold by a retailer.

    DISK_DRIVE(serialnum,manufacturer,model,batch,capacity,retailer)
    
    1. Write each of the following dependencies as a functional dependency in the form X → Y, where X and Y are sets of attributes of DISK_DRIVE. Hint: X → Y says that there can only be one set of values for the attributes in Y for a particular set of values for the attributes in X.

      1. The manufacturer and serial number uniquely identifies the drive.
      2. A model number is registered by a manufacturer and therefore can't be used by another manufacturer.
      3. All disk drives in a particular batch from a particular manufacturer are the same model.
      4. All disk drives of a particular model have exactly the same capacity.
    2. In light of the functional dependencies from (a), what normal form is this relation in? Explain your answer.

    3. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

    (solution)


Valid HTML 4.01!