Reference no: EM132378904

Case Study 1

1. Create a data model.

2. Create an ER Diagram.

3. Convert the relation to BCNF, for this you have to show the normalization starting from 1 NF

4. Create tables, add constraints (PK, FK)

5. Create Views based on these tables to show:

a. Details of staff members working in wards

b. Patients being treated by Physicians

c. Supply Items that have reached below threshold (for this assume quantity < 10) and display results accordingly

d. Diagnosis and Prescription of patient

e. Ward's requirement of Medical/Surgical Items (Hint: Find the patient in the ward and join with prescription)

Case Study 2

1. Create data model

2. Create ER diagram

3. Convert the relation to BCNF, for this you have to show the normalization starting from 1 NF

4. Create tables, add constraints (PK, FK)

5. Create Views based on these tables to show:

Detail Views

a. Consultant serving clients region-wise

b. Classifications of contracts client-wise

Summary Views (Hint: Use group by having)

c. Group consultants in region by expertise

d. All clients having contracts with more than one classification

e. Consultants serving multiple clients in one region

Information related to above question is enclosed below:

Attachment:- CaseStudy_GroupProject.rar