Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Tasks Step 1 (10 marks) Database normalization Read the following specification of a sample database domain. A hospital would like to create a medical database
Tasks Step 1 (10 marks) Database normalization Read the following specification of a sample database domain. A hospital would like to create a medical database with the health records of its patients. Information recorded in the database supposed to include a medical insurance number that identifies each patient, first name, last name, a list of admission dates to the hospital, list of illnesses cured during each admission, and a list of children. Assume, that more than one illness can be cured during each admission to the hospital. Also assume, that a child is described by the first name and a type of relation to its parent, i.e. either son or daughter. child is the first name of the child, different from fname all are atomic values because there are also relation A database designer created the following relational schema. MRECORD (insurance, fname, Iname, adate, illness, child, relation) (1) Determine what is the highest valid normal form a relational schema MRECORD ? Justify your choice. Note, that to find the highest normal form valid for the relational schema MRECORD you must find all nontrivial functional dependencies valid in the schema, find all minimal keys valid in the schema, and use the definitions of normal forms to provide justification of your choice. (2) Your next task is to decompose the relational schema into the smallest number of relational schemas each one in BCNF and to prove that each schema is in BCNF. To prove, that the relational schemas obtained from the decomposition are in BCNF you must find like before all functional and multivalued dependencies valid in each relational schema, you must find the minimal keys, and then apply the definitions. Please keep in mind that the smallest number of BCNF relational schemas is expected. Deliverables A file solution1.pdf with an identification of the highest valid normal form for a relational schema MRECORD and with a justification your decision. Then, a file solution1.pdf must also contain a list of the relational schemas obtained from the decompositions. Each relational schema must have a list of nontrivial functional and/or multivalued dependencies valid in the schema attached, and a justification why the schema is in BCNF
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