CIT 381 Database Systems
Fall 2007

Each design problem will be an IDEF1X logical model created in ER Studio. Show all attributes. Please label each model and turn in either hard copies or pdfs.

1. (20 pts) Create a model in ER Studio for a small bank chain. Your entities will be branches, customers, checks, and safety deposit boxes (you'll want an ACCOUNT entity too). Add appropriate attributes.
   
   Business Rule 1: The check number identifies it within an account, but another account may issue a check with the same number.
   
   Business Rule 2: Accounts are not connected to any particular branch.
   
   Business Rule 3: Safety deposit boxes are located at a single branch, and are associated with a single account. Not all accounts have a safety deposit box.
   
   Business Rule 4: Accounts are associated with a single customer, but a customer may have several accounts.
   
   
2. (25 pts) Eugenia, owner of Eugenia's Parties, wants a database to keep track of information for her business. She wants to keep a list of all the clients who do business with her. She wants to track the name, address, and phone number of each client. For each party, she needs to know the date and the cost of the party. Eugenia has a list of potential locations for parties that she can recommend to her clients. She would like the database to store the name, address, phone number, cost, and name of manager at each location she uses. When a party is set up, she would like to know which location was used for that party - a party occurs at only one location. In addition to setting up the location, Eugenia also sets up the entertainment. She wants to maintain a list of bands and DJs that can be used. For each band or DJ, she needs a name, address, phone number, and normal fee. Each party uses only one band or DJ. Finallly, Eugenia has a group of employees who work parties. She need to know which employees have worked at which parties, and for how long they worked at a given party. An employee can work many parties, and a party typically needs several empoyees. For each employee, she needs to store their ssn, name, address, phone number, and hourly pay rate.
   
   
3. (25 pts) Create a model in ER Studio to hold data about a senior golf league. They always play on Saturday mornings at Squid Acres Country Club. Squid Acres has 18 holes but the players usually only play nine holes each round. Some do the first nine holes, some do the second nine holes, and every once in a while they do all 18 holes. It is also possible to play a custom set of 9 holes: for example, holes 1-4, 7, 8, and 13-15. Thus, a round may consist of either 9 holes or 18 holes and is specific to each player. We aren't concerned with who the playing partners are. Other info on a round includes first name, last name, and the day they played it. For each hole, we want to store the yardage, the par value, and the number of sand traps. We also want to know, for each round, the score the player got on each hole. Make sure your design doesn't lead to a database full of NULLS. Watch for redundancy and derived attributes.
   
   Business Rule 1: A player can only play one round per day.
   
   Business Rule 2: Each round has a unique round number and consists of only one player.