 |
|
 |
| |
Database Design
eXercises #5
Sub-Typing
- Referring to the following medical records:
| PatientId |
Name |
Sex |
Phone |
Prostate Status |
Pregnancies |
| 101 |
Adams A |
M |
2052061 |
OK |
- |
| 102 |
Blossom F |
F |
3652999 |
- |
5 |
| 103 |
Jones E |
F |
? |
- |
0 |
| 104 |
King P |
M |
? |
benign enlargement |
- |
| 105 |
Smith J |
M |
205764 |
? |
- |
If '?' indicates missing or incomplete data and '-' indicates data
never to be recorded, then complete the conceptual schema of this
universe, complete with constraints and subtyping.
- The following results were harvested from a survey of media usage:
| Person |
Age
(yrs) |
Television
(h/week) |
Newspaper
(h/week) |
Favourite Channel |
Favourite Paper |
Preferred News Provider |
| 5001 |
41 |
0 |
10 |
- |
Courier Mail |
- |
| 5002 |
60 |
0 |
25 |
- |
Courier Mail |
- |
| 5003 |
16 |
20 |
2 |
9 |
Courier Mail |
- |
| 5004 |
18 |
20 |
5 |
2 |
Weekend Australian |
TV |
| 5005 |
13 |
25 |
0 |
7 |
- |
- |
| 5006 |
17 |
14 |
4 |
9 |
Weekend Australian |
- |
| 5007 |
50 |
8 |
10 |
2 |
Telegraph |
Papers |
| 5008 |
33 |
0 |
0 |
- |
- |
- |
| 5009 |
13 |
50 |
0 |
10 |
- |
- |
If '-' indicates data deliberately omitted, then complete the conceptual
schema of this universe, complete with constraints and subtyping.
- A hospital maintains an information system about its employees and
patients. The tables that follow are extracts of employee and patient
records.
| emp# |
name |
job |
office |
pager# |
initials |
| e10 |
Adams A |
admin |
G17 |
- |
- |
| e20 |
Watson M |
doc |
302 |
5333 |
MW1 |
| e30 |
Jones E |
pharm |
- |
- |
EJ |
| e40 |
Kent C |
admin |
G17 |
- |
- |
| e50 |
Kildare J |
doc |
315 |
5400 |
JK |
| e60 |
Brown C |
pharm |
- |
- |
CB |
| e70 |
Collins T |
pharm |
- |
- |
TC |
| e80 |
Watson M |
doc |
315 |
5511 |
MW2 |
Employees are identified by their employee number, each employee has
exactly one job (either doctor, pharmasist, administrator). A pager
is a portable electronic communications device when beeps when a number
is rung. Some employees are assigned unique initials, which may be
used as a second identifier. The '-' means inapplicable.
Patients are identified by their patient number, and are either inpatients
(who occupy a bed) and outpatients (who go home to bed) but not both.
Some patients are placed in wards. Patients may be allergic to certain
drugs, Prescriptions are identified by their script number. Each dispensed
perscription is prescribed by a doctor, dispensed by a pharmacist
and issued to a patient. Each prescription specifies exactly one drug.
Initials are used on prescriptions.
| |
|
|
|
prescriptions dispensed to patients |
| patient# |
type |
ward |
allergies |
script# |
prescriber |
dispenser |
drug |
| p511 |
in |
5b |
asprin
doxepin |
7001 |
MW1 |
EJ |
warfarin |
| p632 |
out |
- |
|
7132
7250 |
JK
MW1 |
CB
EJ |
asprin
paracetamol |
| p760 |
in |
4C |
warfarin |
8055 |
JK |
EJ |
asprin |
| p874 |
in |
5B |
|
|
|
|
|
Using these output reports as guides, construct Conceptual schema
diagrams from this information, complete with constraints and subtyping.
- more to come
|
|
|
 |
|
 |
|
