1 |
-- A V Peterson 26-07-2004 |
2 |
-- Database for Assignment 1 |
3 |
|
4 |
|
5 |
-- Person(*PersonId, Surname, FirstName, OtherNames, BirthDate, DeathDate, |
6 |
-- Sex, CountrOfBirth) |
7 |
|
8 |
CREATE TABLE Person |
9 |
( |
10 |
PersonId VARCHAR(6), |
11 |
Surname VARCHAR(20) NOT NULL, |
12 |
FirstName VARCHAR(10) NOT NULL, |
13 |
OtherNames VARCHAR(20), |
14 |
BirthDate DATE, |
15 |
DeathDate DATE, |
16 |
Sex CHARACTER(1), |
17 |
CountryOfBirth VARCHAR(30), |
18 |
CONSTRAINT Person_pk PRIMARY KEY(PersonId) |
19 |
) |
20 |
/ |
21 |
|
22 |
|
23 |
-- Parents(*PersonId, MotherId, FatherId) |
24 |
|
25 |
CREATE TABLE Parents |
26 |
( |
27 |
PersonId VARCHAR(6), |
28 |
MotherId VARCHAR(6), |
29 |
FatherId VARCHAR(6), |
30 |
CONSTRAINT Parents_pk PRIMARY KEY (PersonId), |
31 |
CONSTRAINT Parents_Mother_fk |
32 |
FOREIGN KEY (MotherId) REFERENCES Person(PersonId), |
33 |
CONSTRAINT Parents_Father_fk |
34 |
FOREIGN KEY (FatherId) REFERENCES Person(PersonId) |
35 |
) |
36 |
/ |
37 |
|
38 |
|
39 |
-- Marriage (*WifeId, *HusbandId, *MarriageDate, |
40 |
-- TerminationDate, TerminationMode) |
41 |
|
42 |
CREATE TABLE Marriage |
43 |
( |
44 |
WifeId VARCHAR(6), |
45 |
HusbandId VARCHAR(6), |
46 |
MarriageDate DATE, |
47 |
TerminationDate DATE, |
48 |
TerminationMode CHARACTER(1), |
49 |
CONSTRAINT Marriage_pk PRIMARY KEY(WifeId, HusbandId, MarriageDate), |
50 |
CONSTRAINT Marriage_Wife_fk |
51 |
FOREIGN KEY (WifeId) REFERENCES Person(PersonId), |
52 |
CONSTRAINT Marriage_Husband_fk |
53 |
FOREIGN KEY (HusbandId) REFERENCES Person(PersonId) |
54 |
) |
55 |
/ |
56 |
|