I need someone to work on a database design involving normalization for me. Below is the full description of the project.


Green Lake Clinic is located in a remote area of Minnesota. The clinic is using Microsoft excel to keep track of information about patients, appointments, billings, and doctors. Below are four spreadsheets and their column headings used in their current file. As you can see, redundant information is kept in different sheets, which makes updating difficult. Some of the sheets do not seem to have a clear identifier. The attribute names do not seem to follow any naming convention. It seems additional sheets are needed to avoid redundancy.

Doctors (Doctor#, Doctor_name, office phone, patient, appointment time, doctors specialties)

Patient (Patient-SSN, Patient name, address, primary insurance, secondary insurance, appointment time, doctor)

Appointment (Patient name, Doctor_name, appointment date, appointment time, exam room)

Billing (Bill#, Patient, date of service, set of procedures, cost of those procedures, primary insurance companies name, primary insurance address, secondary insurance companies name, secondary insurance address)

You are charged to design a database for the clinic. You may choose to use Microsoft Access later on. But your first step is to come up with a correct relational schema.

After interviewing employees and the manager of the clinic, you discovered the following additional information: (1) the clinic has a set of standard medical procedures and costs. (2) the clinic needs to maintain information about insurance companies (address, phone, deductible and co-pay*). (3) a patient may have several procedures done on the same visit to the clinic. (4) the clinic sends insurance claims on behalf of a patient to his or her insurance company. After any insurance payments, the clinic sends the billing balance to the patient.

(1) Examine the attributes in each table and remove any obvious redundancies. You may need to rename the attributes and add new attributes as appropriate.
(2) Draw dependence diagram for each of the four "tables" after step (1). Clearly label any partial dependencies (P.D.#1, P.D.#2,, ) transitive dependencies (T.D#1, T.D#2,, ) or BC dependencies (B.C#1, B.C.#2,, ) if they exist.
(3) Remove any partial, transitive or BC dependencies to create new tables.
(4) After you have done with step (3), clearly label each of the final tables with "**"

*For simplicity, assume an insurance company's deductible and co-pay are the same for all its insurance policy holders.


After you're done with this project, please e-mail me the solutions and answers using the contact information below. Thank you for your time.

contact info: vaka0602@stcloudstate.edu