Each design problem will be a Crowsfoot logical model created in ER Studio. Show all attributes. Please label each model and turn in either hard copies or pdfs.
1. (20 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 herclients. 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.
2. (20 pts) Create a model in ER Studio to hold data about a senior golf league. They always play on Saturday mornings at Alvord Desert Country Club. Alvord Desert 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.
3. (20 pts) Exercise 7, pp 144-145
United Helpers is a nonprofit organization that provides aid to people after natural disasters. Based on the following description of operations, create the appropriate fully labeled Crow’s Foot ERD.Volunteer carry out the tasks of the organization. The name, address, and telephone number are tracked for each volunteer. Each volunteer may be assigned to several tasks, and some tasks require many volunteers. A volunteer might be in the system without having been assigned a task yet. It is possible to have tasks that no one has been assigned. When a volunteer is assigned to a task, the system should track the start time and end time of that assignment.Each task as a task code, task description, task type, and task status. For example, there may be a task with task code “101” a description of “answer the telephone” a type of “recurring”, and a status of “ongoing.” Another task might have a code of “102” a description of “prepare 5,000 packages of basic medical supplies,” a type of “packing and a status of “open”.For all tasks of type “packing,” there is a packing list that specifies the contents of the packages. There are many packing lists to product different packages, such as basic medical packages, child-care packages, and food packages. Each packing list has an ID number, a packing list name, and a packing list description, which describes the items that should make up the package. Every packing type is associated with only one packing list. A packing list my not be associated with any task, or it may be associated with many tasks. Tasks that are not packing tasks are not associated with any packing list.Packing tasks result in the creation of packages. Each individual package of supplies produced by the organization is tracked, and each package is assigned an ID number. The date the package was created and it’s total weight are recorded… A given package is associated with only one task. Some tasks (such as “answer the phones”) will not produce any packages, while other tasks (such as “prepare 5,000 packages of basic medical supplies”) will be associated with many packages.The packing list describes the ideal contents of each package, but it is not always possible to include the ideal number of each item. Therefore, the actual items included in each package should be tracked. A package can contain many different items, and a given item can be used in many different packages.Each item that the organization provides has an item ID number, item description, item value, and item quantity on hand stored in the system. Along with tracking the actual items that are placed in each package, the quantity of each item placed in the package must be tracked as well. For example, a packing list may state that basic medical packages should include 100 bandages, 4 bottles of iodine, and 4 bottles of hydrogen peroxide. However, because of the limited supply of items, a given package may include only 10 bandages, 1 bottle of iodine, and no hydrogen peroxide. The fact that the package includes bandages and iodine needs to be recorded along with the quantity of each item included. It is possible for the organization to have item that have not been included in any package yet, but every package will contain at least one item.

4. (20 pts) Exercise 8, pp 145
Using the Crow’s Foot notation, create an ERD that can be implemented for a medical clinic, using the following business rules: A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one
patient.  Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as “unscheduled.”  If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment.  With each visit, the patient’s records are updated to provide a medical history. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients.  Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.  If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.