Q1) Here is the relational scheme in 1NF, find any partial dependencies and eliminate them then recreate it in 2NF Form Q2) Once you have completed the 2NF normalisation, normalise to 3NF, however do not over normalise where it is not necessary | ROOM (ROOMID, ROOM_STATUS, NO_OF_BEDS, FLOOR_NO) PATIENT_ROOM (P ROOMID, PATIENT_ID) DOCTOR_ROOM (D ROOMID, D_EMPLOYEE_ID) DEPARTMENTS (DEPARTMENT ID, LOCATION, DEPARTMENT_TYPE, FACILITIES, EMPLOYEE ID, D_EMPLOYEE_ID, N_EMPLOYEE_ID) EMPLOYEE (EMPLOYEE ID, FNAME, LNAME, EMPLOYEE_TYPE, DEPARTMENT_TYPE, ADDRESS, SALARY, QUALIFICATIONS ID, DEGREE, DIPLOMA, EXPERIENCE) DOCTOR (D_EMPLOYEE_ID, DOCTOR_TYPE, DEPARTMENT_ID) NURSE (N EMPLOYEE ID, NURSE_TYPE) COMPOUNDERS (C EMPLOYEE ID) OTHER_EMPLOYEES (O EMPLOYEE ID, OTHER_ID) DOCTOR_VISITATION (D EMPLOYEE ID, VISITATION ID) NURSE_VISITATION (N EMPLOYEE ID, VISITATION ID VISITATION (VISITATION ID, IN_DATE, OUT_DATE, PATIENT_ID, TREATMENT, TREATMENT_ADVICE, TREATMENT_GIVEN, CHARGES, REFERRED_STAFF_ID, ROOM_NUMBER, ROOM_TYPE, D_EMPLOYEE_ID, N_EMPLOYEE_ID) OUT-PATIENT_VISITATION (O VISITATION ID, TREATMENT_STATUS, DIAGNOSIS, MEDICINE_GIVEN) INPATIENT_VISITATION (VISITATION ID, OPERATION_TYPE, TREATMENT_ADVICE, OPERATING_DOCTOR, OPERATING DOCTOR, PATIENT_CONDITION, DATE_OF_OPERATION, PATIENT_ROOM_ID) PATIENT_RECORD (PAYMENT MODE, INSURANCE, PAYMENT_AMOUNT) PATIENT (PATIENT ID, ADDRESS, NAME, AGE, GENDER, PHONE NO. PAYMENT MODE) Q1) Here is the relational scheme in 1NF, find any partial dependencies and eliminate them then recreate it in 2NF Form Q2) Once you have completed the 2NF normalisation, normalise to 3NF, however do not over normalise where it is not necessary | ROOM (ROOMID, ROOM_STATUS, NO_OF_BEDS, FLOOR_NO) PATIENT_ROOM (P ROOMID, PATIENT_ID) DOCTOR_ROOM (D ROOMID, D_EMPLOYEE_ID) DEPARTMENTS (DEPARTMENT ID, LOCATION, DEPARTMENT_TYPE, FACILITIES, EMPLOYEE ID, D_EMPLOYEE_ID, N_EMPLOYEE_ID) EMPLOYEE (EMPLOYEE ID, FNAME, LNAME, EMPLOYEE_TYPE, DEPARTMENT_TYPE, ADDRESS, SALARY, QUALIFICATIONS ID, DEGREE, DIPLOMA, EXPERIENCE) DOCTOR (D_EMPLOYEE_ID, DOCTOR_TYPE, DEPARTMENT_ID) NURSE (N EMPLOYEE ID, NURSE_TYPE) COMPOUNDERS (C EMPLOYEE ID) OTHER_EMPLOYEES (O EMPLOYEE ID, OTHER_ID) DOCTOR_VISITATION (D EMPLOYEE ID, VISITATION ID) NURSE_VISITATION (N EMPLOYEE ID, VISITATION ID VISITATION (VISITATION ID, IN_DATE, OUT_DATE, PATIENT_ID, TREATMENT, TREATMENT_ADVICE, TREATMENT_GIVEN, CHARGES, REFERRED_STAFF_ID, ROOM_NUMBER, ROOM_TYPE, D_EMPLOYEE_ID, N_EMPLOYEE_ID) OUT-PATIENT_VISITATION (O VISITATION ID, TREATMENT_STATUS, DIAGNOSIS, MEDICINE_GIVEN) INPATIENT_VISITATION (VISITATION ID, OPERATION_TYPE, TREATMENT_ADVICE, OPERATING_DOCTOR, OPERATING DOCTOR, PATIENT_CONDITION, DATE_OF_OPERATION, PATIENT_ROOM_ID) PATIENT_RECORD (PAYMENT MODE, INSURANCE, PAYMENT_AMOUNT) PATIENT (PATIENT ID, ADDRESS, NAME, AGE, GENDER, PHONE NO. PAYMENT MODE)
