For each of the examples below indicate the normal form for

Question

Part 1. Identify the Form of Normalization

For each of the examples below, indicate the normal form for each relation.  If the relation is NOT in third normal form, decompose it into 3NF relations. Enter 1NF, 2NF or 3NF in the blank next to each item.

_____1. EMPLOYEE (Emp_ID, F_name, L_name, DOB, email, street, city, state, zip, phone, hire_date)

_____2.  CLASS (Course_No, Section_No, Room

_____3.  CLASS (Course_No, Section_No, Room, Capacity) {Note: Capacity isfuncationally dependent on Room}

_____4.  PART SUPPLIER (Part_No, Description, Vendor_Name,  Address, Unit_Cost)

Part 2: 3NF Normalization

Using the normalization methods described in the second part of Chapter 5, decompose the user view for  the Patient Bill displayed on the next page into a set of relations in 3NF.  The schema is to show PK, FK, and referential integrity.

You are to show:

1. A 2-D Table with no multivalued attributes  (1NF)Hint: Your table should have 4 rows of data in addition to the column headings.
2. A functional dependencies diagram (see page  217 in your text)
3. A set of relations (tables) with no transitive dependencies (3NF)

•  
  • Note:  If there are no transitive dependencies, then 2NF = 3NF

Suggestion: Copy and paste the text from the invoice below into your table and relations.

Mountview Community Hospital
200 Forrest Dr.
Mountview, CO
Statement of Account for:
Patient Name:		Baker, Mary			Patient #:  3249
Patient Address:		200 Oak St.			Date Admitted:  9-15-14
City-State-Zip:		Mountain View,			Date Discharged:  9-18-14
		CO 80608
Item Code			Description			Charge
200			Room semi-pr			1,800.00
205			ECG			75.00
307			X-ray			100.00
413			Lab tests			200.00
			Balance Due			2,225.00