44tBhAWX

1DROP DATABASE IF EXISTS DBee;
2CREATE DATABASE DBee;
3USE DBee;
4
5CREATE TABLE Apiary (
6  Apiary_ID INTEGER not null,
7  Address VARCHAR(100) not null,
8  Number_Of_Hives INTEGER not null,
9  Last_Inspection_Date DATE not null,
10  PRIMARY KEY (Apiary_ID)
11);
12
13CREATE TABLE Queen (
14  Q_Name VARCHAR(50) not null,
15  Years_Active INTEGER not null,
16  Origin VARCHAR(50) not null,
17  PRIMARY KEY (Q_Name)
18);
19
20CREATE TABLE Forage (
21  Forage_ID INTEGER not null,
22  Apiary_ID INTEGER not null,
23  Primary_Flora VARCHAR(50) not null,
24  Radius INTEGER not null,
25  FOREIGN KEY (Apiary_ID) REFERENCES Apiary(Apiary_ID),
26  PRIMARY KEY (Forage_ID, Apiary_ID),
27  CONSTRAINT Radius_Check CHECK (Radius > 0)
28);
29
30CREATE TABLE Beekeepers (
31  Employee_ID INTEGER not null,
32  Apiary_ID INTEGER not null,
33  First_Name VARCHAR(50) not null,
34  Surname VARCHAR(50) not null,
35  PPPSN INTEGER not null,
36  FOREIGN KEY (Apiary_ID) REFERENCES Apiary(Apiary_ID),
37  PRIMARY KEY (Employee_ID, Apiary_ID),
38  CONSTRAINT PPSN_Check CHECK (PPSN < 1000000000)
39);
40
41CREATE TABLE Hive (
42  Queen VARCHAR(50) not null,
43  Apiary_ID INTEGER not null,
44  Forage_ID INTEGER not null,
45  Quarantine BIT not null,
46  FOREIGN KEY (Queen) REFERENCES Queen(Q_Name),
47  FOREIGN KEY (Apiary_ID) REFERENCES Beekeepers(Apiary_ID),
48  FOREIGN KEY (Forage_ID) REFERENCES Forage(Forage_ID),
49  PRIMARY KEY (Queen)
50);
51
52CREATE TABLE Honey (
53    Queen_Name VARCHAR(50) not null,
54    Average_Quantity INTEGER not null,
55    Water_Quantity INTEGER not null,
56    FOREIGN KEY (Queen_Name) REFERENCES Queen(Q_Name),
57    PRIMARY KEY (Queen_Name),
58    CONSTRAINT Water_Quantity_Check CHECK (Water_Quantity >= 0
59      AND Water_Quantity <= 100)
60);
61
62INSERT INTO Apiary VALUES (1, '123 Willowbrook lane', 10, '2018/10/12');
63INSERT INTO Apiary VALUES (2, '45 Justicefield road', 5, '2018/07/04');
64INSERT INTO Apiary VALUES (3, '20 Linseed road', 5, '2018/03/10');
65INSERT INTO Apiary VALUES (4, '37 Smithfield road', 5, '2017/11/28');
66INSERT INTO Apiary VALUES (5, '09 Marketlane Avenue', 10, '2018/09/13');
67
68INSERT INTO Queen VALUES ('Elizabeth', 1, 'External breeder');
69INSERT INTO Queen VALUES ('Victoria', 2, 'Internal breeding');
70INSERT INTO Queen VALUES ('Maeve', 1, 'Internal breeding');
71INSERT INTO Queen VALUES ('Mathilde', 4, 'Internal breeding');
72INSERT INTO Queen VALUES ('Olivia', 2, 'External breeder');
73INSERT INTO Queen VALUES ('Anna', 2, 'Internal breeder');
74INSERT INTO Queen VALUES ('Mary', 3, 'Internal breeding');
75INSERT INTO Queen VALUES ('Jordan', 2, 'Internal breeding');
76INSERT INTO Queen VALUES ('Sheba', 1, 'External breeder');
77INSERT INTO Queen VALUES ('Isabella', 3, 'External breeder');
78
79INSERT INTO Beekeepers VALUES (1, 1, 'James', 'Kelleher', 123456789);
80INSERT INTO Beekeepers VALUES (1, 2, 'Cyrus', 'Morgan', 573957263);
81INSERT INTO Beekeepers VALUES (1, 3, 'Lucas', 'Ghost', 286184084);
82INSERT INTO Beekeepers VALUES (1, 4, 'Ollie', 'Voltaire', 983667367);
83INSERT INTO Beekeepers VALUES (1, 5, 'Dawn', 'Vega', 456837898);
84INSERT INTO Beekeepers VALUES (2, 1, 'Sarah', 'Stafford', 123456789);
85INSERT INTO Beekeepers VALUES (2, 2, 'Trisha', 'Montejo', 573957263);
86INSERT INTO Beekeepers VALUES (2, 3, 'Ann', 'Honer', 286184084);
87INSERT INTO Beekeepers VALUES (2, 4, 'Ebin', 'Benny', 983667367);
88INSERT INTO Beekeepers VALUES (2, 5, 'David', 'Kilroy', 456837898);
89
90INSERT INTO Forage VALUES (1, 1, 'Lavender', 5);
91INSERT INTO Forage VALUES (2, 1, 'Crocus', 5);
92INSERT INTO Forage VALUES (1, 2, 'Hyacinth', 2);
93INSERT INTO Forage VALUES (1, 3, 'Borage', 4);
94INSERT INTO Forage VALUES (1, 4, 'Foxglove', 6);
95INSERT INTO Forage VALUES (1, 5, 'Cosmos', 5);
96INSERT INTO Forage VALUES (2, 5, 'Snapdragon', 7);
97
98INSERT INTO Hive VALUES ('Elizabeth', 1, 1, 0);
99INSERT INTO Hive VALUES ('Victoria', 1, 2, 0);
100INSERT INTO Hive VALUES ('Maeve', 2, 1, 0);
101INSERT INTO Hive VALUES ('Mathilde', 2, 1, 0);
102INSERT INTO Hive VALUES ('Olivia', 3, 1, 0);
103INSERT INTO Hive VALUES ('Anna', 3, 1, 0);
104INSERT INTO Hive VALUES ('Mary', 4, 1, 0);
105INSERT INTO Hive VALUES ('Jordan', 4, 1, 0);
106INSERT INTO Hive VALUES ('Sheba', 5, 1, 1);
107INSERT INTO Hive VALUES ('Isabella', 5, 2, 1);
108
109INSERT INTO Honey VALUES ('Elizabeth', 20, 7);
110INSERT INTO Honey VALUES ('Maeve', 18, 4);
111INSERT INTO Honey VALUES ('Olivia', 17, 5);
112INSERT INTO Honey VALUES ('Mary', 19, 6);
113INSERT INTO Honey VALUES ('Sheba', 20, 7);
114
115DELIMITER |
116CREATE TRIGGER Del_Hive AFTER DELETE ON Hive
117FOR EACH ROW BEGIN
118IF Apiary.Apiary_ID = OLD.Apiary_ID THEN
119UPDATE Apiary SET Number_Of_Hives = Number_Of_Hives - 1;
120END IF;
121END;
122|
123DELIMITER ;
124
125DELIMITER |
126CREATE TRIGGER Add_Hive BEFORE INSERT ON Hive
127FOR EACH ROW BEGIN
128IF Apiary.Apiary_ID = NEW.Apiary_ID THEN
129UPDATE Apiary SET Number_Of_Hives = Number_Of_Hives + 1;
130END IF;
131END;
132|
133DELIMITER ;
134
135CREATE VIEW Hive_View AS
136SELECT Beekeepers.*
137  FROM Beekeepers LEFT JOIN Hive ON Beekeepers.Apiary_ID = Hive.Apiary_ID;