Answered step by step
Verified Expert Solution
Link Copied!

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

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Transact SQL Cookbook Help For Database Programmers

Authors: Ales Spetic, Jonathan Gennick

1st Edition

1565927567, 978-1565927568

More Books

Students also viewed these Databases questions

Question

What is loss of bone density and strength as ?

Answered: 1 week ago

Question

The paleolithic age human life, short write up ?

Answered: 1 week ago

Question

10. Are you a. a leader? b. a follower? _______

Answered: 1 week ago