generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
84 lines (66 loc) · 1.84 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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
/* Database schema to keep the structure of entire database. */
CREATE TABLE animals (
id SERIAL PRIMARY KEY,
name text,
date_of_birth date,
escape_attemps INT,
neutered boolean,
weight_kg decimal
);
ALTER TABLE animals ADD species VARCHAR(255);
/*create a table named owners*/
CREATE TABLE owners (
id serial PRIMARY KEY,
full_name varchar(255),
age INT
);
/*create a table named species*/
CREATE TABLE species (
id serial PRIMARY KEY,
name varchar(255)
);
/* modify animals table */
/* remove column species */
ALTER TABLE animals DROP COLUMN species;
ALTER TABLE animals
ADD COLUMN species_id INT;
ALTER TABLE animals
ADD COLUMN owner_id INT;
ALTER TABLE animals
ADD CONSTRAINT fk_species
FOREIGN KEY (species_id)
REFERENCES species(id);
ALTER TABLE animals
ADD CONSTRAINT fk_owner
FOREIGN KEY (owner_id)
REFERENCES owners(id);
/* creating the table named vets */
CREATE TABLE vets (
id SERIAL PRIMARY KEY,
name varchar(100),
age INT,
date_of_graduation DATE
);
/* creating a join table called specializations */
CREATE TABLE specializations (
vet_id INT REFERENCES vets(id),
species_id INT REFERENCES species(id),
PRIMARY KEY (vet_id, species_id)
);
/* creating a join table called visits */
CREATE TABLE visits (
animal_id INT REFERENCES animals(id),
vet_id INT REFERENCES vets(id),
visit_date DATE,
PRIMARY KEY (animal_id, vet_id)
);
ALTER TABLE visits DROP CONSTRAINT visits_pkey;
ALTER TABLE visits ADD PRIMARY KEY (animal_id, vet_id, visit_date);
/* database performance audit */
-- Add an email column to your owners table
ALTER TABLE owners ADD COLUMN email VARCHAR(120);
/* improving execution time strategies */
ANALYZE visits;
CREATE INDEX idx_animal_id ON visits (animal_id);
CREATE INDEX idx_vet_id ON visits (vet_id);
CREATE INDEX idx_email_id ON owners (email);