Only solutions for parts 5,6,7 and 8 are required.

Read the following Scenario and answer all the questions pertaining to this Scenario in Section A that follows.

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.

SECTION A

Based on the business scenario stated above complete the following:

1. Build a list of business needs, rules and assumptions based on your scenario, research, and objectives.

2. Develop a list of potential entities including their attributes, the attributes optionality as well as a possible primary key for each entity. Write the relational schemas.

While creating your entities you should have been thinking about what relationships the entities would have with each other. Create the relationships between your entities including the relationships optionality and cardinality.

3. Create an Entity Relationship Diagram (ERD) using UML notation that meets these needs and objectives. Any many-to-many relationships will need to be resolved.

4. Explain in detail how you arrived at the cardinalities in your ERD.

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

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

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

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