generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
116 lines (97 loc) · 2.5 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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
/* Database schema to keep the structure of entire database. */
/* Create table animals */
CREATE TABLE ANIMALS(
id int GENERATED BY DEFAULT AS IDENTITY,
name varchar(100) NOT NULL,
date_of_birth date,
escape_attempts int NOT NULL,
neutered boolean NOT NULL,
weight_kg decimal NOT NULL
);
/* Alter the animals table */
ALTER TABLE
animals
ADD
COLUMN species varchar(100);
/* Create table owners */
CREATE TABLE owners (
id SERIAL PRIMARY KEY,
full_name varchar(100) NOT NULL,
age int NOT NULL
);
/* Create table species */
CREATE TABLE species (
id SERIAL PRIMARY KEY,
name varchar(100) NOT NULL
);
/* Alter table animals */
/* Open transaction */
BEGIN;
/* Remove column species */
ALTER TABLE
animals DROP COLUMN species;
/* Add column species_id which is a foreign key referencing species table*/
ALTER TABLE
animals
ADD
COLUMN species_id int,
ADD
CONSTRAINT fk_animals_species FOREIGN KEY (species_id) REFERENCES species (id);
/* Add column owner_id which is a foreign key referencing the owners table */
ALTER TABLE
animals
ADD
COLUMN owner_id int,
ADD
CONSTRAINT fk_animals_owners FOREIGN KEY(owner_id) REFERENCES owners(id);
/* Commit transaction */
COMMIT;
/* PROJECT 3 */
/* Create table vets */
CREATE TABLE vets (
id SERIAL PRIMARY KEY,
name varchar(100) NOT NULL,
age int NOT NULL,
date_of_graduation date NOT NULL
);
/* Create join table specializations */
CREATE TABLE specializations (
vet_id int REFERENCES vets(id),
specie_id int REFERENCES species(id),
PRIMARY KEY(vet_id, specie_id)
);
/* Alter table animals, set id as primary key */
ALTER TABLE
animals
ADD
PRIMARY KEY (id);
/* Create join table visits */
CREATE TABLE visits (
id SERIAL PRIMARY KEY,
animals_id INTEGER REFERENCES animals(id),
vets_id INTEGER REFERENCES vets(id),
date_of_visit date
);
/* PROJECT 5 */
/* Add new column email to the table owner */
ALTER TABLE
owners
ADD
COLUMN email VARCHAR(120);
/* Rename animals_id to animal_id into the table visits */
ALTER TABLE
visits RENAME COLUMN animals_id TO animal_id;
/* Rename vets_id to vet_id into the table visits */
ALTER TABLE
visits RENAME COLUMN vets_id TO vet_id;
/* Remove NOT NULL constrain in the column age from the table owner */
ALTER TABLE
owners
ALTER COLUMN
age DROP NOT NULL;
/* CREATE INDEX animals_id_asc */
CREATE INDEX animals_id_asc ON visits(animal_id ASC);
/* Drop index animals_id_asc */
DROP INDEX animals_id_asc;
/* CREATE INDEX emails on the owners table */
CREATE INDEX emails ON owners(email ASC);