Using Crows Foot notation and Visio, draw an Entity Relationship Diagram (ERD) that captures the business rules given below. For each problem, you will build upon the ERD from the previous problem. Make sure that no lines cross each other in any of your ERDs. Note: Make sure that your ERD shows the primary key (PK) for each entity set and each relationship is captured using foreign keys (FKs). If you have any many-to-many relationships, make sure you break them down into 1-to-many relationships, so that you can capture them using FKs.

Problem #1: A clinic needs to record information about patients and doctors. Doctors are assigned unique IDs upon hiring. New patients are assigned unique IDs on their initial visit. The following additional information needs to be recorded about patients: first name, last name, date of birth, phone number and address.The following information needs to be recorded about doctors: first name, last name, office number, phone number, specialization.

Problem #2: Every patient has one primary doctor and that information needs to be recorded. A doctor can be the primary doctor for many patients.

Problem #3: A patient can make many appointments with any doctor in the clinic. A doctor can accept appointments with many patients. Each appointment is made with only one doctor and one patient. For each appointment we need to record the date and time of the appointment and whether the appointment was made over the phone, online or in person (that is, in the clinic).

Problem #4: The clinic needs to maintain information about medical diagnosis. Each diagnosis is identified by a code and has a name.

Problem #5: Each appointment may or may not be kept, meaning that each appointment may or may not yield a visit with the doctor specified in the appointment. For simplicity, lets assume that each visit yields one diagnosis. (5pts) In addition, we need to record a treatment plan for each visit.

Problem #6: Each patient visit will generate one or more bills depending on what services were provided during that visit. For example, if the visit involved 3 services (a doctor exam, a CBC blood test and a flu shot) there will be 3 bills generated. Each bill corresponds to only one visit.

Problem #7: The clinic needs to store information about the all services provided. Each service has a unique code, a name and a cost. Every service is billed separately (so, every bill corresponds to only one service). One service however can appear on multiple bills (for all the patients that received that service).

Problem #8: Some patients have insurance. Each patient can be covered by at most one insurance policy. Each insurance policy has unique ID, plan name, provider and type (HMO or PPO).An insurance policy can cover multiple patients (for example, an entire family).

Problem #9: For patients that have an insurance policy, each bill may be the basis for a claim submitted to an insurance company, in which case the insurance may partially or totally cover a bill. Each insurance policy can cover multiple payments but each insurance payment corresponds to only one bill. The amount and the date for each insurance payment must be recorded.

Problem #10: A bill that is not fully covered by the insurance, must be paid by the patient. A patient can make multiple payments. Each patient payment can cover part of a bill (meaning a patient can pay a bill in multiple installments) or multiple bills. The amount and the date for each patient payment must be recorded.

EXTRA CREDIT: Make sure no lines cross each other in your final ERD.