Database Systems: Homework 2 KeyDue 7 October, 2013Team: Key1. (8 points) Consider the ER diagram in Figure 7.22. Assume that an employee maywork in up to two departments or may not be assigned to any department. Assume thateach department must have one and may have up to three phone numbers. Supply(min, max) constraints on this diagram. State clearly any additional assump-tions you make. Under what conditions would the relationship HAS PHONE beredundant in this example?Assume the following additional system requirements:• Each department can have anywhere between 1 and 30 employees.• Each phone is used by one, and only one, department.• Each phone is assigned to at least one, and may be assigned to up to 30 employees.• Each employee is assigned at least one, but no more than 5 phones.The relationship HAS PHONE would be redundant when employees have all the phones of theirdepartment(s) and none of any other department.2. (8 points) Consider the ER schema for the MOVIES database in Figure 7.24. Assumethat MOVIES is a populated database. ACTOR is used as a generic term and includesactresses. Given the constraints shown in the ER schema, respond to the followingstatements with True, False, or Maybe. Assign a response of Maybe to statementsthat, while not explicitly shown to be True, cannot be proven False based on theschema as shown. Briefly justify each answer.1(a) There are no actors in this database that have been in no movies.T—Actor has full participation in Performs In.(b) There are some actors who have acted in more than ten movies.M—The max cardinality on Actor-Performs In-Movie is N , so this is neither required norruled out.(c) A movie can have only a maximum of two lead actors.T—The max cardinality on Movie-Has Lead Role-Actor (reading the relationship back-wards) is 2.(d) Every director has been an actor in some movie.M—This can’t be false because the relationship Also A Director exists, but it can’t be truebecause Director doesn’t have total participation in it.(e) No producer has ever been an actor.M—The same reasoning as the previous question applies here, looking at the Actor Producerrelationship.(f) There are movies with more than a dozen actors.M—The max cardinality on Movie-Is Performed In By-Actor (reading backwards) is M ,so this is possible but not required.(g) Most movies have one director and one producer.M—The min and max cardinality bounds are just that, min/max bounds. They can saynothing about average, typical, or “most” cases. Not that they aren’t important for examiningthe performance of a design!(h) No movie has a director who also acted in that movie.M—Like before, nothing about these constraints either mandates or prohibits this.23. (16 points) A database is being constructed to keep track of the teams and games ofa sports league. A team has a number of players, not all of whom participate in eachgame. It is desired to keep track of the players participating in each game for eachteam, the positions they played in that game, and the result of the game. Design anER schema diagram for this application, stating any assumptions you make. Chooseyour favorite team sport (e.g., baseball, curling, kabbadi, ...). Be sure your design isdescribed in a way understandable by someone not familiar with that sport.Here’s one example solution as provided by the textbook authors...3The following design may be used for a baseball league. Here, we assumed that each gamein the schedule is identified by a unique Game#, and a game is also identified uniquely bythe combination of Date, starting Time, and Field where it is played. The Performanceattribute of PARTICIPATE is used to store information on the individual performance ofeach player in a game. This attribute can be designed to keep the information needed forstatistics, and may be quite complex. One possible design for the Performance attributemay be the following (using the notation of Figure 7.8):Performance( {Hitting(AtBat#, Inning#, HitType, Runs, RunsBattedIn,StolenBases)}, {Pitching(Inning#, Hits, Runs, EarnedRuns, StrikeOuts, Walks,Outs, Balks, WildPitches)}, {Defense(Inning#, {FieldingRecord(Position,PutOuts, Assists, Errors)})} )Here, performance is a composite attribute made up of three multivalued components: Hit-ting, Pitching, and Defense. Hitting has a value for each AtBat of a player, and recordsthe HitType (suitable coded; for example, 1 for single, 2 for double, 3 for triple, 4 for homerun, 0 for walk, −1 for strikeout, −2 for fly out, ...) and other information concerning theAtBat. Pitching has a value for each inning during which the player pitched. Defense hasa value for each inning a player played a fielding position. We can have a less detailed or amore detailed design for the performance of a player in each game, depending on how muchinformation we need to keep in the database. Suitable variations of the ER diagram shownbelow can be used for other
View Full Document
Unlocking...