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

Annotation of /uni/COMP2400/a1DB.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.2 - (hide annotations)
Mon Aug 9 02:39:13 2004 UTC (18 years, 3 months ago) by apollock
Branch: MAIN
CVS Tags: HEAD
Changes since 1.1: +3 -0 lines
Updated a1DB.sql

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    

  ViewVC Help
Powered by ViewVC 1.1.22