generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
64 lines (58 loc) · 1.44 KB
/
schema.sql
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
/* Database schema to keep the structure of entire database. */
CREATE TABLE OWNERS (
id INT GENERATED ALWAYS AS IDENTITY,
full_name VARCHAR,
age INT,
PRIMARY KEY (id)
);
CREATE TABLE SPECIES (
id INT GENERATED ALWAYS AS IDENTITY,
name VARCHAR,
PRIMARY KEY (id)
);
CREATE TABLE ANIMALS (
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR,
date_of_birth DATE,
escape_attempts INT,
neutered BOOLEAN,
weight_kg DECIMAL,
species_id INT,
owner_id INT,
PRIMARY KEY (id),
CONSTRAINT species_key
FOREIGN KEY (species_id)
REFERENCES species(id),
CONSTRAINT owner_key
FOREIGN KEY (owner_id)
REFERENCES owners(id)
);
CREATE TABLE VETS (
id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
name VARCHAR,
age INT,
date_of_graduation DATE,
PRIMARY KEY (id)
);
CREATE TABLE SPECIALIZATION (
species_id INT,
vets_id INT,
CONSTRAINT species_key
FOREIGN KEY (species_id)
REFERENCES species(id),
CONSTRAINT vets_key
FOREIGN KEY (vets_id)
REFERENCES vets(id)
ON DELETE CASCADE
);
CREATE TABLE VISITS (
animal_id INT,
vets_id INT,
date_of_visit DATE,
CONSTRAINT animals_key
FOREIGN KEY (animal_id)
REFERENCES animals(id),
CONSTRAINT vets_key
FOREIGN KEY (vets_id)
REFERENCES vets(id)
);