Download Database Systems Homework 2: ER Diagram to Relational Model and Functional Dependencies and more Assignments Deductive Database Systems in PDF only on Docsity!
Homework 2
Administrivia
Out: 7 th^ February, 2006. Due date: At the beginning of the lecture on 21st^ February, 2006. Please submit a hard copy of your homework. Bring it down to the lecture table (the one with the PC that I use to display the slides).
The hard copy should be as clearly readable as possible. You may be subtracted points for unreadability and ugly presentation.
Off-campus students: You should e-mail your solutions to Yoonkyong Lee in the pdf format or in the Word document. Send the file as attachment with your email by 2 PM UIUC time (CST). Off-campus students in other time zones should note that the deadline is according to CST.
Please note: Late homework will not be accepted, barring exceptional circumstances.
From ER to Relational Model
Problem 1. (30 points) Consider the following ER diagram.
Figure 1 : ER Diagram for Problem 1 (a) Translate the ER diagram into a relational database schema. For translating the isa hierarchy, use the ER style translation (b) Specify the key of each relational table that you created in part (a). (c) For the isa hierarchy in the above ER diagram, translate it into a relational schema using the Object Oriented style translation.
Functional Dependency and Normalization
Problem 2. (20 points) Suppose that we have the following relation. A, B, and C are attributes; (a 1 , b 1 , c 1 ) is the first tuple and so on.
A B C
a 1 b 1 c (^1) a 1 b 2 c (^1) a 2 b 3 c (^1) a 2 b 1 c (^1)
(a) Which of the following functional dependencies are not satisfied by this relation? a. A → B b. BC → A c. B → C (b) Give three non-trivial functional dependencies that are satisfied by this relation.
Problem 3. (15 points) Suppose we have a relation R with four attributes ABCD. For each of the following sets of functional dependencies, assuming those are the only dependencies that hold for R, do the followings: i) State if R is in BCNF. If not, state if R is in 3NF. ii) If it is not in BCNF, decompose it into a collection of BCNF relations that preserve the dependencies.
(a) A→B, B→C, C→D, D→A (b) AB→C, AB→D, C→D (c) ABC→D, CD→A
Problem 4. (20 points) Suppose we have a relation R with five attributes ABCDE. For each of the following instances of R, do the followings: i) State if it violates the
functional dependency AB→C. ii) State if it violates the multivalued dependency
AB→→C. [NOTE: x represents a variable.]
(a) {} (i.e., empty relation) (b) {(1,2,3,4, x ), ( x ,2,8,4,1)} (c) {(1,2,3,4, x ), ( x ,2,8,4,1), (1,2,3,9, x )} (d) {(1,2,3,4, x ), ( x ,2,3,4,1), (1,2,8,4, x )} (e) {(1,2,3,4, x ), ( x ,2,3,4,1), (1,2,8,4, x ), (1,2,8,9, x )}
