/[cvs.andrew.net.au]/uni/COMP2400/a1DB.sql
ViewVC logotype

Contents of /uni/COMP2400/a1DB.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1 - (show annotations)
Sat Aug 7 11:07:01 2004 UTC (18 years, 2 months ago) by apollock
Branch: MAIN
Blah

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

  ViewVC Help
Powered by ViewVC 1.1.22