CS W186 Introduction to Database Systems
Spring 2020 Josh Hug, Michael Ball DIS 10
1 ER Diagrams
We want to store sports teams and their players in our database. Draw an ER diagram correspond- ing to data given below:
• Every Team in our database will have a unique team_name and a stadium where they play their games.
• Each Coach has a name.
• Each Player will have a player_id, name and their average score.
• Our database will contain who Plays_For which team and also the “position” that the player plays in. We also need to store who Captains a team, and who Coaches a team.
• Every Team needs players, and needs exactly one captain.
• Each Player can be on at most one team, but may currently be a free agent and not on any team.
• Each team needs coaches and may have many.
• A Coach is uniquely identified by which team they coach.
CS W186, Spring 2020, DIS 10 1
2
Functional Dependencies
1. When there’s a lot of symbols floating around, it’s best to keep track of the “type” of the various symbols and expressions. Consider a set of functional dependencies F = {X -> Y, Y -> Z}. For each of the following symbols or expressions, indicate whether it is (a) an attribute, (b) a set of attributes, (c), a set of sets of attributes, (d) a functional dependency, (e) a set of functional dependencies, or (f) none of the above.
(a) X
(b) XY
(c) X->Y (d) F
(e) F+
(f) X+
(g) Armstrong’s reflexivity axiom
2. Consider a relation R(x, y, z) and the list of functional dependencies X -> Y, XY -> YZ, and Y -> X where X = {x}, Y = {y}, and Z = {z}. For each of the following relations, indicate which functional dependencies it might satisfy.
3. Consider the set F = {A -> B, AB -> AC, BC -> BD, DA -> C} of functional dependencies. Compute the following attribute closures.
(a) A+
(b) B+, C+, D+
(c) AB+, AC+, AD+ (d) BC+
(e) BD+
(f) CD+ (g) BCD+
CS W186, Spring 2020, DIS 10 2
3
4. Consider again the set F of functional dependencies from Question 3. Indicate whether the following sets of attributes are candidate keys, superkeys (but not candidate keys), or neither.
(a) A
(b) B, C, D
(c) AB, AC, AD (d) BC
(e) BD
(f) CD (g) BCD
Normal Forms
1. Decompose R = ABCDEFG into BCNF, given the functional dependency set: F = AB CD, C EF, G A, G F, CE F.
2. Does the above decomposition preserve dependencies? Why/why not?
CS W186, Spring 2020, DIS 10 3