uni/COMP2400/a1DB.sql

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