DIS 622-001
Business Data Systems Analysis
and Design
Assignment 2
Due:
1. Map the given ER diagram to a relational schema, using the techniques and format discussed in class. If any relation is not in BCNF, decompose that relation into BCNF and revise the relational schema. Save your answer as Task1.
2. Comment on the limitations of using a relational schema for data modeling. Save your answer as Task2.
3. Convert the Mountain View Hospital Staffing Report given below to a set of BCNF relations. Be sure to follow and document the steps of the normalization process discussed in class. Assume the following:
(a) The hospital has several departments, e.g., surgery, intensive care, emergency, etc.
(b) A staff can assign to work at several departments in one month.
(c) Each department has a number of supervisors.
(d) Each supervisor belongs only to one department.
(e) A staff assigned to work at a particular department will be designated a specific supervisor for that month.
Save your answer as Task3.
|
MOUNTAIN VIEW HOSPITAL
STAFFING REPORT For the month of February, 2005 |
||||
|
Staff_ID |
Staff Name |
Department |
Department_Phone |
Supervisor |
|
S111 |
Rob Scott |
Surgery |
7-1111 |
Smith |
|
S111 |
Rob Scott |
Recovery |
7-2222 |
Jones |
|
W222 |
Sue Store |
Surgery |
7-1111 |
Rivers |
|
W222 |
Sue Store |
Intensive Care |
7-3333 |
Johnson |
|
W222 |
Sue Store |
Recovery |
7-2222 |
Jones |
|
… |
… |
… |
… |
… |
4. Merge the relations resulted from tasks (1) and (3) above into a single set of BCNF relations. Save your answer as Task4.
NOTE: Save all your work
electronically in a folder called ####HW2 where #### is the last four digits of
your student number. Create a zip
version of your ###HW2 folder and submit your ####HW2.zip through blackboard.