CSE444 Introduction to Database Systems Fall 1998Homework 2 – Due Monday, Nov. 21. Convert the E/R diagram below to a relational database schema.Author BookPublisherDistributorWritesssn nameisbntitlenameaddrPublishesDistributesnameaddrstate2. For the following two schemas and sets of functional dependencies,a.) What are all the completely nontrivial FDs that follow from the given dependencies?b.) What are all the keys of the relation?c.) What are all the superkeys for the relation that are not keys?i.) R(A, B, C, D) with FDs A -> B, A -> C, D -> A.ii.) S(A, B, C, D) with FDs AB -> C, AC -> D, A -> BC, B -> C.3. Consider two sets of FDs for a relation T(A, B, C, D, E):i.) A-> B, AB -> C, D -> AC, D -> Eii.) A -> BC, D -> AEAre they equivalent? (Can you derive the same set of completely nontrivial FDs from each?)4. For the following schemas and FDs,a.) Indicate all the BCNF violations. Do not forget to consider dependencies that are not in the given set, but follow from them. However, it is not necessary to give violations that have more than one attribute on the right side.b.) Decompose the relations, as necessary, into collections of relations that are in BCNF.i.) R(A, B, C, D, E) with FDs E -> CD, BCD -> E.ii.) S(A, B, C, D) with FDs A -> B, BC -> D, D -> A.iii.) T(A, B, C, D) with FDs AB -> D, BD -> C, CD -> A, AC -> B.iv.) U(A, B, C, D, E) with FDs AB -> E, CD -> E, A-> D, D-> A.5. Consider a database with the following relations:Player (name, team, hometown)Team (name, captain, city)Game (number, team, player, minutes, touchdowns)MVP (game-number, team, player)[Assume each game has exactly 2 MVPs, one from each team.] Write the following 2 queries in relational algebra (do it in parts, using and naming intermediate relations):i.) What are the names of players who played in games in which their team captain did not play?ii.) What are the names of captains who have scored more touchdowns than any other player from the same hometown? Write the following 5 queries in SQL:iii.) From which hometown did the highest-scoring player of game # 4 come from?iv.) What was the average number of minutes played in the first 7 games?v.) How many touchdowns did the highest-scoring MVP of each game score?vi.) What are the names of co-MVPs who are from the same hometown?vii.) For any captains that were MVPs, what are their names, what hometown are they from and how many minutes did they play in those games in which they were
View Full Document
Unlocking...