Kqe0aj2Y

1DROP DATABASE IF EXISTS Hunting;
2DROP DATABASE IF NOT EXISTS Hunting;
3USE Hunting;
4
5CREATE TABLE Hunters(
6id INT,
7PRIMARY KEY (id)
8);
9
10CREATE TABLE Animals(
11id INT,
12animalType ENUM ('Dog', 'Bird', 'Fox'),
13weight INT,
14PRIMARY KEY(id)
15);
16
17CREATE TABLE Dogs(
18animalId INT,
19name VARCHAR(250),
20breed VARCHAR(250),
21FOREIGN KEY (animalId) REFERENCES Animals(id)
22);
23
24CREATE TABLE Birds(
25animalId INT,
26wingspan FLOAT,
27FOREIGN KEY (animalId) REFERENCES Animals(id)
28);
29
30CREATE TABLE Foxes(
31animalId INT,
32color VARCHAR(250),
33FOREIGN KEY (animalId) REFERENCES Animals(id)
34);
35
36CREATE TABLE Kills(
37hunterId INT,
38deadAnimalId INT,
39assistingDogId INT,
40FOREIGN KEY (hunterId) REFERENCES Hunters(id),
41FOREIGN KEY (deadAnimalId) REFERENCES Animals(id),
42FOREIGN KEY (assistingDogId) REFERENCES Dogs(animalId),
43PRIMARY KEY (hunterId, deadAnimalId, assistingDogId),
44UNIQUE (deadAnimalId)
45);
46
47CREATE TABLE Owns(
48hunterId INT,
49animalId INT,
50FOREIGN KEY (hunterId) REFERENCES Hunters(id),
51FOREIGN KEY (animalId) REFERENCES Dogs(animalId),
52UNIQUE (animalId)
53);