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	-- 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