Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need some help with normalization.

Here is the data in 0NF :

**HOSPITAL SERVICES (date_hospitalization , id_patient , id_doctor , name , surname , PIN, duration_of_hospitalization , release_date_from_hospital , place_of_birth , date_of_birth , specialization , room_number , id_of_health_fund , id_of_disease , name_of_health_fund , name_of_disease , department_of_hospital , postal_code , city_address , address_of_street_number_entrance_floor_number_apartment)**

I need to use successive decomposition method to normalize into 3NF, and set relationships.

Here are some things, that need to apply:

The hospital services are getting automatized. The automatized service documents the doctors, patients, and hospitalization services, by concrete patients and concrete diseases, which the health insurance covers. (Data about the hospitalization services bills are not documented). Doctors and patients are persons, who have general attributes. The hospitalization for a patient (pick up into the hospital, and start hospitalization services) is being done by the specialist doctor, based on the disease the patient has, and the specialist doctor also selects the room for the patient ,and when to let the patient go from the hospital (The patient can't leave the hospital at his/her own risk).

Here is what I came up so far: () - key * - foreign key

PATIENT (PIN(), name, surname, p_o_b, d_o_b, postal_code, city_address, a_o_s_n_e_f_n_a)
HOSPITALIZATION (id_patient(), PIN*, id_doctor*, room_number, department_of_hospital, date_hospitalization, duration_of_hospitalization, Release_date_from_hospital)
DOCTOR (id_doctor(), name, surname, specialization)
DISEASE (id_of_disease(), id_patient*, id_doctor*, name_of_disease)
HEALTH INSURANCE (id_of_health_fund(), id_patient*, name_of_health_fund)

The problem is i don't really know what this method means... If you feel like, please explain why is my approach bad, and what should I change, to meet the requirements. And how should the relations be...

EDIT:

PATIENT (PIN(), name, surname, p_o_b, d_o_b, postal_code, city_address, a_o_s_n_e_f_n_a)
HOSPITALIZATION (id_patient(), PIN*, id_doctor*, room_number, department_of_hospital, date_hospitalization, duration_of_hospitalization, Release_date_from_hospital)
DOCTOR (id_doctor(), name, surname, specialization)
DISEASE (id_of_disease(), name_of_disease)
HEALTH INSURANCE (id_of_health_fund(), name_of_health_fund)
TREATMENT (id_of_disease(), id_patient*, id_doctor*)
FUND (id_of_health_fund(), id_patient*)
share|improve this question
    
Do not cross-post to multiple sites! stackoverflow.com/questions/21410790/normalizing-data-into-nf –  Phil Jan 29 at 0:31
    
You're on the right track. The next step would be to split your Disease and HealthInsurance tables. There should be a table for diseases (with the name and appropriate codes) and another for diagnoses or treatments, relating Patient, Doctor, Hospital, and Disease. –  Jon of All Trades Jan 29 at 0:39
    
Thank you for your response. And what does this "successive decomposition method" mean? Am I doing it the right way? –  Roland Bicskei Jan 29 at 0:43
    
So, I edited my post. Is it in 3NF now, or should I change something else? –  Roland Bicskei Jan 29 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.