Question

Create Tables - normalized relations

Do the problem below. Create tables using the schema notation only. Identify the primary keys by underlining. Turn in only the schema notation. Do not turn in your work leading to the solution. Create the schema using Word or write clearly legible copies.

Create a set of normalized relations given the bill shown.

Patient bill
Patient #: 12345			Date: 7/20/08
Patient Name: Mary Baker			Date admitted: 7/14/08
Patient Address: 300 Oak Street			Discharge date: 7/17/08
City-State-Zip: Boulder, CO 80638
Cost Center	Cost Name	Date Charged	Item Code	Desc	Charge	Bal Due
100	Room & Board	7/14/08	2000	Semi-prv room	200.00
		7/14/08	2005	Television	5.00
		7/15/08	2000	Semi-prv room	200.00
		7/16/08	2000	semi-prv room	200.00
				Subtotal		605.00
110	Laboratory	7/14/08	1580	Glucose	25.00
		7/15/08	1585	Culture	20.00
				Subtotal		45.00
125	Radiology	7/15/08	3010	X-ray chest	30.00
				Subtotal		30.00
				Balance due		680.00

Answer 1

set of normalized relations

First normal form Patient# Patient Patient City Discharge CostCenter Cost Date name charged ItemCode Desc charge Bal Date Date Admitted Date Address Due state name Zip

Second Normal form Patient# Patient Patient City Date Date Admitted Date Discharge Address state- name Zip CostCenter Cost Date charged name ItemCode Desc charge Bal Due Third normal form In the above table has a second normal form are already in 3NF because there is no transitive dependency exists so the Second normal form table is a third normal form also Primary key are underlined in the above normalized table