<p> </p>
8.9 练习
1. 将表 8-13 中的数据规范化为第三范式。
Patient | SSN | Physician | Hospital | Treatment | AdminDate | ReleaseDate |
Sam Spade | 555-55-5555 |
Albert Schweitzer |
Mayo Clinic |
Lobotomy | 10/01/2005 | 11/07/2005 |
Sally Nally | 333-33-3333 |
Albert Schweitzer |
NULL |
Cortizone Injection |
10/10/2005 | 10/10/2005 |
Peter Piper | 222-22-2222 | Mo Betta |
Mustard Clinic |
Pickle Extraction |
11/07/2005 | 11/07/2005 |
Nicki Doohichey |
123-45-6789 |
Sheeze Sheila |
Musard Clinic |
Cortizone Injection |
11/07/2005 | 11/07/2005 |
先建立一个病人的社会安全账号信息的表Patients,并以社会安全账号SSN为主键:
SSN | Patient |
555-55-5555 | Sam Spade |
333-33-3333 | Sally Nally |
222-22-2222 | Peter Piper |
123-45-6789 | Nicki Doohichey |
然后建立一张医院信息表Hospitals,并以医院编号HospitalID为主键:
HospitalID | Hospital |
1 | Mayo Clinic |
2 | Mustard Clinic |
然后建立一张医生信息Physicians表,并以医生编号PhysicianID为主键:
PhysicianID | Physician | HospitalID |
1 | Albert Schweitzer | 1 |
2 | Mo Betta | 2 |
3 | Sheeze Sheila | 2 |
最后建立一个疗疹记录表,它以整个字段组为主键:
PatientID | PhysicianID | Treatment | AdminDate | ReleaseDate |
1 | 1 | Lobotomy | 10/01/2005 | 11/07/2005 |
2 | 1 | Cortizone Injection | 10/10/2005 | 10/10/2005 |
3 | 2 | Pickle Extraction | 11/07/2005 | 11/07/2005 |
4 | 3 | Cortizone Injection | 11/07/2005 | 11/07/2005 |