CS 54100: Database Systems

Assignment 1: Data Modeling

Due beginning of class Friday, 27 January, 2012

Part 1: Standard exercises

1. Create a relational schema corresponding to the ER-diagram of Figure 2.10 of the textbook (Manages and Works_In). This should be a direct correspondence, do not worry about relational normalization. Note that the notation for one to many is different from that used in class; Sections 2.4.1 and 2.4.2 describe their notation.
2. Determine something in the schema of Figure 2.10 that might be appropriate to represent with an ISA relationship. Show how this changes your answer to the preceding question.
3. Write functional dependencies that arise from the ER diagram in figure 2.10 of the textbook (as it appears in the text, don't include your ISA relationship from the preceding question.)
4. Given a schema with relation R=(A,B,C,D) and functional dependencies AB → C, BC → D, CD → A, and AD → B:
   1. List all notrivial FD's that follow from the given FD's.
   2. What are all the keys of R?
   3. What are all the superkeys of R that are not keys?
   You should be able to figure out the answer to the last two from the definition of key and axioms defining functional dependencies, even though we did not show how to do this in class.
5. Prove the following: If FD's A₁ A₂ ... A_m → B₁ B₂ ... B_n and C₁ C₂ ... C_j → D₁ D₂ ... D_k hold, then FD A₁ A₂ ... A_m C₁ C₂ ... C_j → B₁ B₂ ... B_n D₁ D₂ ... D_k holds
6. Given a schema with relation R=(A,B,C,D,E) and functional dependencies AB → C, DE → C, B → D:
   1. Indicate all the violations of BCNF.
   2. Decompose the relations into collections of relations that are in BCNF. Is this a lossless join decomposition?
   3. Indicate all the 3NF violations.
   4. Do a lossless join decomposition of the relations into 3NF.

Part 2: Design Exercise

For this part, you will go through the design process for a relational database. This database is to capture course and student registration information, including:

• Course title, instructor, location, time
• Textbook information
• Students registered, student grade

This is not a complete list - part of the exercise is to figure out what information is necessary and should be included.

List the steps you would take in producing a relational database schema supporting this information. For each step, do the the following:

1. List the information needed to perform the step.
2. Describe how you would obtain that information in a real-world scenario.
3. Making reasonable assumptions about the information, perform the step.

Turning in the assignment

The safest way to turn in the assignment is through Blackboard; this ensures that we know when it is turned in, and protects against loss. However, I realize that some of this is easier to do by hand rather than electronically, so handing in hard copy at the beginning of the class is acceptable as well.

This page last modified .