Database_system_management/HW3.sql at main · summer2788/Database_system_management · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
CREATE TABLE AnimalSpecies (
    AID         INTEGER,
    Name        VARCHAR(64),
    Nutrition   CHAR(1),
    Temperature FLOAT,
    Habitat     VARCHAR(150),
    minFlock    INTEGER,
    maxFlock    INTEGER,
    spaceRequirements   FLOAT);
ALTER TABLE AnimalSpecies ADD CONSTRAINT PK_AnimalSpecies
    PRIMARY KEY(AID);
CREATE TABLE Habitat (
    HID         INTEGER,
    Name        VARCHAR(64),
    Habitat     VARCHAR(64),
    Size        FLOAT,
    Temperature FLOAT           );
ALTER TABLE Habitat ADD CONSTRAINT PK_Habitat
    PRIMARY KEY(HID);
CREATE TABLE Specimen (
    EID       INTEGER,
    AID       INTEGER,
    HID       INTEGER,
    Name      VARCHAR(64),
    BirthDate   DATE,
    Gender    CHAR(1),
    Weight    FLOAT,
    Height    FLOAT            );
ALTER TABLE Specimen ADD CONSTRAINT PK_Specimen
    PRIMARY KEY(EID);
ALTER TABLE Specimen ADD CONSTRAINT FK_Specimen1
    FOREIGN KEY(AID) REFERENCES AnimalSpecies(AID);
ALTER TABLE Specimen ADD CONSTRAINT FK_Specimen2
    FOREIGN KEY(HID) REFERENCES Habitat(HID);
CREATE TABLE Ancestry (
    EID     INTEGER,
    Parent  INTEGER      );
ALTER TABLE Ancestry ADD CONSTRAINT PK_Ancestry
    PRIMARY KEY(EID, Parent);
ALTER TABLE ancestry ADD CONSTRAINT FK_Ancestry1
    FOREIGN KEY(EID) REFERENCES Specimen(EID);
ALTER TABLE Ancestry ADD CONSTRAINT FK_Ancestry2
    FOREIGN KEY(Parent) REFERENCES Specimen(EID);


SET datestyle TO SQL, DMY;


INSERT INTO Habitat VALUES(101, 'Woods', 'temperate', 160.0, 35.0);
INSERT INTO Habitat VALUES(102, 'Swamp', 'damp', 150.0, 30.0);
INSERT INTO Habitat VALUES(103, 'Woods II', 'temperate', 170.0, 35.0);
INSERT INTO Habitat VALUES(104, 'Woods III', 'temperate', 400.0, 35.0);

INSERT INTO AnimalSpecies VALUES(1, 'Tiger', 'S', 35.5, 'tropical, temperate', 1, 3, 20.0);
INSERT INTO Specimen VALUES(1001, 1, 101, 'Tigger', '05/04/2014', 'M', 30.0, 100.0);
INSERT INTO Specimen VALUES(1002, 1, 101, 'Kit-Kat', '17/11/2012', 'F', 30.0, 100.0);
INSERT INTO Specimen VALUES(1003, 1, 101, 'Khan', '02/02/2010', 'M', 30.0, 100.0);
INSERT INTO Specimen VALUES(1004, 1, 101, 'Nova', '01/03/2014', 'F', 30.0, 100.0);
INSERT INTO Specimen VALUES(1005, 1, 101, 'Young one', '01/03/2016', 'F', 30.0, 100.0);