HealthOne Medical Database:

You are a small database consulting company specializing in developing databases for the medical industry. You have just been awarded the contract to develop a data model for a database application system for a mid-size health insurance company to keep track of health claims including patient information, provider(doctor) information, information about patient visits to their doctor as well as prescription drugs prescribed to patients.

Information such as patient name, address, phone, email etc. are needed as well as who each patients primary care doctor is, their insurance ID number and insurance company name. We also want information on each doctor such as their specialty and what hospitals they are affiliated with as well as their phone, address etc. Regarding the hospitals themselves we will need to know where they are located and how to contact them.

The prescriptions given to each patient by a health-care provider also need to be tracked in this particular database at this time to determine claim eligibility including some basic information on the drug being prescribed to make sure there are no conflicts with a patients other prescriptions. We need to know each drugs name, purpose/use and possible side effects.

Eventually, the database will be used to track trends and for some extrapolative modeling based on the accumulated data. The database will be accessible in English only right now, although plans include making it available in multiple languages eventually.

1.Populate your tables with sample data and illustrate the three types of anomalies that can exist.

2. Ensure that each entity has been normalized to the 1NF. Give appropriate reasons for your answer.

3. Ensure that each entity has been normalized to 2NF. Give appropriate reasons for your answer.

4. Ensure that each entity has been normalized to 3NF. Give appropriate reasons for your answer.

Some patients in the patient entity may be part of the same family and be covered by the same insurance we would like to designate a field in the patient entity showing who is the insurance holder for each patient this field would be the patient ID number of the person holding the insurance for the family.

5. Modify the ERD to include a recursive relationship on the Patient entity showing the insurance owners role.

Handle Date sme send Doctor make Treat Affiliated with Patent Patient patientiam address phone Number email Insurance ID Insurance Company Doctor Doctor DoctorName address PhoneNumber Specialization Hospital Hospital HospitalName location Contact Number Gives For Prescription Prescription Date Prescribed Brugs Drugi DrugName Use Side Effect Handle Date sme send Doctor make Treat Affiliated with Patent Patient patientiam address phone Number email Insurance ID Insurance Company Doctor Doctor DoctorName address PhoneNumber Specialization Hospital Hospital HospitalName location Contact Number Gives For Prescription Prescription Date Prescribed Brugs Drugi DrugName Use Side Effect