Download Solved Homework II - Database Systems | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
Homework 2
Administrivia
Out: 7th^ 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.
Answer:
(a) Customer(custID, name, address) Order(order#, date, CustomerID) OrderIncludesArtwork(Order#, ArtworkTitle, ArtistName) Artwork(title, ArtistName, year, price) Artist(name, birthplace, age, style) VIPCustomer(CustomerID, salary) CustomerLikesArtist(CustomerID, ArtistName) (b) Keys are underlined in part (a). (c) RegularCustomer(custID, name, address) VIPCustomer(custID, name, address, salary)
Functional Dependency and Normalization
Problem 2. (20 points) Suppose that we have the following relation. A, B, and C are attributes; (a 1 , b1 , c 1 ) is the first tuple and so on.
A B C a 1 b1 c (^1) a 1 b2 c (^1) a 2 b3 c (^1) a 2 b1 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.
Answer:
(a) A→B and BC → A do not hold. (b) If you assume that this relation has only given 4 tuples, then the answers are A→C, AB→C, and B→C. Otherwise, it is not determinable.
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
Problem 5. (15 points) Prove each Armstrong’s axiom using the definition of the functional dependency.
(a) Reflexive rule: If {B 1 , B 2 , …, B (^) m}⊆{A 1 , A 2 , …, An }, then A 1 A 2 … An→ B 1 B 2 … B (^) m. (b) Augmentation rule: If A 1 A 2 … An → B 1 B 2 … B (^) m, then A 1 A 2 … An C 1 C 2 … Ck→ B 1 B 2 … B (^) m C 1 C 2 … Ck , for any set of attributes C 1 , C 2 , …, Ck. (c) Transitive rule: If A 1 A 2 … An → B 1 B 2 … B (^) m and B 1 B 2 … B (^) m → C 1 C 2 … Ck , then A 1 A 2 … An → C 1 C 2 … Ck.
Answer: Note that this is a sample solution, NOT the only solution. [Notation] t (^) X: the value of attribute X of tuple t in a relation R
(a) Assume that {B 1 , B 2 , …, B (^) m}⊆{A 1 , A 2 , …, An } holds and A 1 A 2 … An→ B 1 B 2 … B (^) m does NOT hold. Then, based on the definition of FD, there are two tuples u and v in R, where u (^) A1 =vA1 , …, u (^) An =vAn ,and u (^) i≠ vi (i∈{B 1 , B 2 , …, B (^) m}). Since {B 1 , B 2 , …, B (^) m}⊆{A 1 , A 2 , …, An }, i ∈ {A 1 , A 2 , …, An }. Thus, there is a contradiction. Therefore, the Reflexive rule holds. (b) Assume that A 1 A 2 … A (^) n → B 1 B 2 … B (^) m holds and A 1 A 2 … An C 1 C 2 … Ck→ B 1 B 2 … B (^) m C 1 C 2 … Ck does NOT hold for any set of attributes C 1 , C 2 , …, Ck. Then, based on the definition of FD, there are two tuples u and v in R, where u A1 =vA1 , …, uAn =vAn , u^ C1 =vC1 , …, uCk=v^ Ck, and ui≠^ vi ( i ∈ {B 1 , B 2 , …, B^ m}). [Since uC1 =vC1 , …, uCk=v (^) Ck , i∈{B 1 , B 2 , …, B (^) m} not i∈{B 1 , B 2 , …, B (^) m, C 1 , C 2 , …, Ck }.] Also, since A 1 A 2 … An → B 1 B 2 … B (^) m holds, if u and v agrees on A 1 , A 2 , …, An , then u and v must agree on B 1 , B 2 , …, B (^) m. Thus, there is a contradiction. Therefore, the Augmentation rule holds. (c) Assume that both A 1 A 2 … An → B 1 B 2 … B (^) m and B 1 B 2 … B (^) m → C 1 C 2 … Ck hold. Let u and v be any tuple of R that agree on A 1 , A 2 ,…, An. Since A 1 A 2 … An → B 1 B 2 … B (^) m holds, u and v agree on B1, B2, …, B (^) m. Then, since B 1 B 2 … B (^) m → C 1 C 2 … Ck holds, u and v also agree on C 1 , C 2 , …, Ck. Thus, for any two tuples agreeing on A 1 , A 2 ,…, An , they also agree on C 1 , C 2 , …, Ck. Therefore, A 1 A 2 … An → C 1 C 2 … Ck holds.
