-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
54 lines (44 loc) · 1.18 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
/* Database schema to keep the structure of entire database. */
CREATE TABLE animals (
id INT,
name VARCHAR,
date_of_birth DATE,
escape_attempts INT,
neutered BOOLEAN,
weight_kg DECIMAL
);
ALTER TABLE animals
ADD species VARCHAR;
CREATE TABLE owners (
id INT generated by default as identity,
full_name VARCHAR,
age INT,
PRIMARY KEY(id)
);
CREATE TABLE species (
id INT generated by default as identity,
name VARCHAR,
PRIMARY KEY(id)
);
ALTER TABLE animals ADD COLUMN id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
ALTER TABLE animals DROP COLUMN species;
ALTER TABLE animals ADD COLUMN species_id INTEGER REFERENCES species(id);
ALTER TABLE animals ADD COLUMN owner_id INTEGER REFERENCES owners(id);
CREATE TABLE vets (
id INT GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR,
age INT,
date_of_graduation DATE,
PRIMARY KEY(id)
);
CREATE TABLE specializations (
vet_id INT REFERENCES vets(id),
species_id INT REFERENCES species(id),
PRIMARY KEY(vet_id, species_id)
);
CREATE TABLE visits (
animal_id INT REFERENCES animals(id),
vet_id INT REFERENCES vets(id),
date_of_visit DATE,
PRIMARY KEY (animal_id, vet_id, date_of_visit)
);