/[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.2 - (show annotations)
Mon Aug 9 02:39:13 2004 UTC (18 years ago) by apollock
Branch: MAIN
CVS Tags: HEAD
Changes since 1.1: +3 -0 lines
Updated a1DB.sql

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

  ViewVC Help
Powered by ViewVC 1.1.22