Homework 3
Practice the concepts of chapter 3.
This homework is worth 40 points.
Goals: When you finish this homework you should be able to:
- Determine dependencies in a set of data.
- Normalize a given set of data.
Assignment
- (20 points) A hospital keeps the following written drug record for patients admitted to the hospital:
Patient Form
Name Bob Smith Patient Number 100-111
Room G 104
| Drug Number | Name | Dosage | Units/Day | Start Date | Finish Date |
|---|
| P100 | Morphine | 10MG/ML | 50 | 3-Feb-10 | 5-Feb-10 |
| A500 | Tetracyclene | 0.5MG/ML | 10 | 3-Feb-10 | 8-Feb-10 |
| P100 | Morphine | 10MG/ML | 10 | 6-Feb-10 | 8-Feb-10 | |
Please note:
- There are multiple patients admitted to the hospital, but each patient will have only one record.
- Drug Numbers, Names and Dosages are Standard
Do the following:
- Build a single representative table for this data.
- Identify the dependencies in your data.
- State any assumptions made in the previous step.
- Give an example of an insertion, deletion and modification anomalies.
- State what you will insert/delete/modify in your example.
- Explain how the anomalies will occur
- Describe and illustrate the process of normalizing the data from UNF through BCNF.
- Provide your final schema.
This problem is a modified version of one in Connolly.
- (20 points) Do all parts of problem C, page 136.
Submission
Your answers must be typed. Print out this report and turn it in by class time on the due date.