uni/COMP2400/a1DB.sql

-- A V Peterson   26-07-2004
-- Database for Assignment 1


-- Person(*PersonId, Surname, FirstName, OtherNames, BirthDate, DeathDate,
--         Sex, CountrOfBirth)

CREATE TABLE Person
(
  PersonId VARCHAR(6),
  Surname  VARCHAR(20) NOT NULL,
  FirstName VARCHAR(10) NOT NULL,
  OtherNames VARCHAR(20),
  BirthDate DATE,
  DeathDate DATE,
  Sex CHARACTER(1),
  CountryOfBirth VARCHAR(30),
  CONSTRAINT Person_pk PRIMARY KEY(PersonId)
)
/


-- Parents(*PersonId, MotherId, FatherId)

CREATE TABLE Parents
(
  PersonId VARCHAR(6),
  MotherId VARCHAR(6),
  FatherId VARCHAR(6),
  CONSTRAINT Parents_pk PRIMARY KEY (PersonId),
  CONSTRAINT Parents_Mother_fk
     FOREIGN KEY (MotherId) REFERENCES Person(PersonId),
  CONSTRAINT Parents_Father_fk
     FOREIGN KEY (FatherId) REFERENCES Person(PersonId)
)
/


-- Marriage (*WifeId, *HusbandId, *MarriageDate, 
--            TerminationDate, TerminationMode)

CREATE TABLE Marriage
(
  WifeId VARCHAR(6),
  HusbandId VARCHAR(6),
  MarriageDate DATE,
  TerminationDate DATE,
  TerminationMode CHARACTER(1),
  CONSTRAINT Marriage_pk PRIMARY KEY(WifeId, HusbandId, MarriageDate),
  CONSTRAINT Marriage_Wife_fk
     FOREIGN KEY (WifeId) REFERENCES Person(PersonId),
  CONSTRAINT Marriage_Husband_fk
     FOREIGN KEY (HusbandId) REFERENCES Person(PersonId)
)
/

1	apollock	1.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