Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Exercise 1. You have been given a form which shows the details of tests done by different patients during their visits to the hospital. In
Exercise 1. You have been given a form which shows the details of tests done by different patients during their visits to the hospital. In fact, when a patient goes to the hospital, he/she asks to be examined by a doctor of a department. This doctor could ask him/her to do some tests and/or send him/her to another doctor of another department. Then, the other doctor could also ask him/her to do other tests. A doctor belongs to only one department. The Amount here is the remaining amount that the patient must pay for the test, after subtracting the amount paid by the security social. The hospital would like to set up a relational database corresponding to this form. a. Normalize the database into INF including only one table. Give the content of the table as well as the list of functional dependencies for the table. b. Normalize the database into 2NF. Give the content of each table after as well as the list of functional dependencies for each table after normalizing. c. Normalize the database into 3NF. Give the content of each table after as well as the list of functional dependencies for each table after normalizing. PID: Patient's ID P_Name: Patient's Name D_ID: Doctor's ID D Name: Doctor's Name DeptID: Department's ID DeptName: Department's Name Visit Time P_ID P_Name Phone DID D_Name DeptName TestName Price Quantity Amount ID Depti D 101 Testi D T01 1 298 0987654534 1002 Maternity 15 3 40 2019/12/23 09:10:00 Mary Walls Alan Smith TO2 20 1 18 1 298 0987654534 1001 Jill Young 102 Gastroenterology TO3 30 1 30 2019/12/23 09:10:00 2019/12/23 10:30:00 Mary Walls Bob Jackson 2 300 0896755888 1001 Jill Young 102 Gastroenterology TO3 30 1 30 Diabetes Blood Test 2D ultrasonography Anorectal function testing Anorectal function testing 24-hour pH monitoring Diabetes Blood Test Capsule endoscopy 24-hour pH monitoring T04 45 2 90 3 3008 0675389799 1003 102 T01 15 Gastroenterology 2 2019/12/23 10:30:00 30 Peter Smith Mary Weber TOS 1 150 45 120 37 4 298 0987654534 1003 102 Gastroenterology T04 1 2019/12/27 14:30:00 Mary Walls Mary Weber Exercise 1. You have been given a form which shows the details of tests done by different patients during their visits to the hospital. In fact, when a patient goes to the hospital, he/she asks to be examined by a doctor of a department. This doctor could ask him/her to do some tests and/or send him/her to another doctor of another department. Then, the other doctor could also ask him/her to do other tests. A doctor belongs to only one department. The Amount here is the remaining amount that the patient must pay for the test, after subtracting the amount paid by the security social. The hospital would like to set up a relational database corresponding to this form. a. Normalize the database into INF including only one table. Give the content of the table as well as the list of functional dependencies for the table. b. Normalize the database into 2NF. Give the content of each table after as well as the list of functional dependencies for each table after normalizing. c. Normalize the database into 3NF. Give the content of each table after as well as the list of functional dependencies for each table after normalizing. PID: Patient's ID P_Name: Patient's Name D_ID: Doctor's ID D Name: Doctor's Name DeptID: Department's ID DeptName: Department's Name Visit Time P_ID P_Name Phone DID D_Name DeptName TestName Price Quantity Amount ID Depti D 101 Testi D T01 1 298 0987654534 1002 Maternity 15 3 40 2019/12/23 09:10:00 Mary Walls Alan Smith TO2 20 1 18 1 298 0987654534 1001 Jill Young 102 Gastroenterology TO3 30 1 30 2019/12/23 09:10:00 2019/12/23 10:30:00 Mary Walls Bob Jackson 2 300 0896755888 1001 Jill Young 102 Gastroenterology TO3 30 1 30 Diabetes Blood Test 2D ultrasonography Anorectal function testing Anorectal function testing 24-hour pH monitoring Diabetes Blood Test Capsule endoscopy 24-hour pH monitoring T04 45 2 90 3 3008 0675389799 1003 102 T01 15 Gastroenterology 2 2019/12/23 10:30:00 30 Peter Smith Mary Weber TOS 1 150 45 120 37 4 298 0987654534 1003 102 Gastroenterology T04 1 2019/12/27 14:30:00 Mary Walls Mary Weber
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started