8.9 练习
1. 将表 8-13 中的数据规范化为第三范式。
表 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为主键:
Patients
SSN |
Patient |
555-55-5555 |
Sam Spade |
333-33-3333 |
Sally Nally |
222-22-2222 |
Peter Piper |
123-45-6789 |
Nicki Doohichey |
然后建立一张医院信息表Hospitals,并以医院编号HospitalID为主键:
Hospitals
HospitalID |
Hospital |
1 |
Mayo Clinic |
2 |
Mustard Clinic |
然后建立一张医生信息Physicians表,并以医生编号PhysicianID为主键:
Physicians
PhysicianID |
Physician |
HospitalID |
1 |
Albert Schweitzer |
1 |
2 |
Mo Betta |
2 |
3 |
Sheeze Sheila |
2 |
最后建立一个疗疹记录表,它以整个字段组为主键:
Treatments
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 |