Database Systems  Assignment 2 SOLUTION

Each design problem will be a ER diagram created using MySQL Workbench. Turn in a single pdf file, uploaded to blackboard.
READ: 4.1
1. (10 pts) Using the partly completed diagram from class (here) finish exercise 25 from chapter 3 of the text. A copy of the appropriate pages had been handed out in class.
2. (15 pts) Modify the previous diagram according to the requirements of exercise 27 of chapter 3 of the text.
3. (15 pts) Draw an ER diagram for the following problem. An auction web site has items for sale that are provided by sellers. Each item has an opening price, a description, and an ending time. Customers submit bids. The highest, earliest bid submitted before the ending time is the winning bid and the item is sold to the bidder (note: this possibly will not affect the design - but the information needed to find the times and amounts of bids needs to be stored). The auction company wants to be able to analyze the sales behavior of its customers and sellers and so must keep track of all bids and sales. (Hint: the entities look like they'll be customers, sellers, items, and bids.)
4. (15 pts) Draw an ER diagram for the following problem. 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 employees. For each employee, she needs to store their ssn, name, address, phone number, and hourly pay rate

5. (15 pts) Problem 2 of chapter 4 of the text (p 143 in 10th, p 152 in 11th ed)