CSE444 HW1

Entity Relationship Diagrams

Due: Beginning of class, Wed. 4/6/99

If the specifications for #1 or #2 are not complete, please write down the assumptions you used when building the ER diagram.

1. A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Try to design an ER schema diagram for this application, stating any assumptions you make. Choose your favorite sport (as long as it's one the TA knows...)

2. A trucking company called TRUCKERS is responsible for picking up shipments for warehouses of a retail chain called MAZE BROTHERS and delivering the shipments to individual retail store locations of MAZE BROTHERS. Currently there are 6 warehouse locations and 45 retail stores of MAZE BROTHERS. A truck may carry several shipments during a single trip, which is identified by a Trip#, and delivers those shipments to multiple stores. Each shipment is identified by a Shipment# and includes data on shipment volume, weight, destination, etc. Trucks have different capacities for both the volumes they can hold and the weights they can carry. The TRUCKERS company currently has 150 trucks, and a truck makes 3 to 4 trips each week. A database, to be used by both TRUCKERS and MAZE BROTHERS, is being designed to keep track of truck usage and deliveries and to help in scheduling trucks to provide timely deliveries to stores. Design an ER diagram for the above application. Make any assumptions you need, but state them clearly.

3. Consider the ER diagram handed out in class (Friday, 4/2) for part of a BANK database. Each Bank can have multiple branches, and each branch can have multiple accounts and loans.

  1. List the (nonweak) entity types in the ER diagram.
  2. Is there a weak entity type? If so, give its name, its partial key, and its identifying relationship.
  3. What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram?
  4. List the names of all relationship types, and spcify the (min, max) constraint on each participation of an entity type in a relationship type. Justify your choices.
  5. Deduce and list concisely the user requirements that led to this ER design.
  6. Suppose that every customer must have at least one account but is restricted to at most two loans at a time, and that a bank branch cannot have more than 1000 loans. How does this show up on the (min, max) constraints?