DIS 390

Database Management

---

Assignment 2

Due: March 8, 2004 at 4:30pm

1. Map the given ER diagram to a relational schema, using the techniques and format discussed in class. If any relation is not in 4NF, decompose that relation into 4NF and revise the relational schema.

2. The table below contains sample data for a hospital table with the following functional dependencies:

HOSP

DN	PN	Dname	Pname	TN	Treatment	Specialty	Paddress	DateVisit
111	P11	White	Baker, Mary	C1	X-ray	Family	1 Main St.	12/4/03
222	P11	Black	Baker, Mary	A5	EKG	Heart	1 Main St.	12/4/03
111	P11	White	Baker, Mary	B2	Antibiotics	Family	1 Main St.	12/5/03
111	P11	White	Baker, Mary	C1	X-ray	Family	1 Main St.	12/11/03

DN à Dname, Specialty

PN à Pname, Paddress

TN à Treatment

DN + PN + DateVisit à TN

where DN: Doctor Number

PN: Patient Number

Dname: Name of Doctor

Pname: Name of Patient

TN: Treatment Number

Treatment: Description of treatment

Speciality: Specialty of the doctor (exactly one per doctor)

Paddress: Address of Patient

DateVisit: Date of Patient’s visit to the doctor

(a) Identify the primary key of HOSP.

(b) Is HOSP in 2NF? Explain.

(c) Is HOSP in 3NF? Explain.

(d) Convert HOSP into a set of 4NF tables. Be sure to follow and document the steps of the normalization process discussed in class.

3. Merge the relations resulted from tasks (1) and (2) above into a single set of 4NF relations.

Submission Options:

1. Turn in a printed copy of your answer, clearly labeled.
2. Turn in an electronic copy of your answer as a zip file via the digital dropbox.

---