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	-- A V Peterson 26-07-2004
2	-- Updated 05-08-2004: added FOREIGN KEY constraint on Parents(PersonId)
3	-- 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	CONSTRAINT Parents_Person_fk
33	FOREIGN KEY (PersonId) REFERENCES Person(PersonId),
34	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