Database normalization 3NF Entities in caps where the orginal entities; lower case entities where added moving toward 3NF.
I would like for you to check over the below entities and check the Foreign Keys based on the cardinalities relationships. Also, I need to make sure, if the relationship between two entities is a many-to-many, then I need one more table in the 3NF. Add some tables that you think are needed for normalization. Im unsure if the entities are in 3NF or still need adjusted more for 3NF. Entities: PATIENT -(doc_id, dept_id), pat_id, pat_name, pat_dob, pat_sex. Patient Address -(FK pat_id), pat_address_key, , street, city, zip code, phone. BED - (FK admission_id, pat_id, doc_id), bed_id, room_id, room_type, bed_cost,. One to one relationship; one patient to one bed EMPLOYEE - (FK dept_id), emp_id, emp_name, emp_dob, emp_sex. One to many relationship. One employee working in many departments. Employee Address - (FK emp_id), emp_address, emp_city, emp_state, emp_zip, emp_phone). One to one relationship; employee can have only one home address. DEPARTMENT - (FK doc_id), dept_id, dept_name, treatment_names. Many to many relationship; many doctors to many departments. TREATMENT - (FK pat_id, doc_id, presc_id, med_id, op_id, test_id), treatment_id, treatment_name, date. Many to many relationship; many patients receive many treatments. Prescription - (FK treatment_id, pat_id, doc_id), presc_id, presc_name, date, presc_scost. Many to many relationship; many patients obtain many prescriptions. Medicine - (FK presc_id, pat_id, doc_id), med_id, med_name, date, med_cost. Many to many relationship; many patients receive many medications. Operation - (FK treatment_id, pat_id, doc_id), op_id, op_name, op_date, op_time. Many to many relationship; many patients receive many operations. TEST -(FK pat_id, doc_id, treatment_id), test_id, test_name, date, cost. Many to many relationship; many patients receive many test.