generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
106 lines (89 loc) · 2.39 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
/* Database schema to keep the structure of entire database. */
/* Database schema to keep the structure of entire database. */
-- Create a table to store animals.
CREATE TABLE animals(
id INT,
name VARCHAR,
date_of_birth DATE,
escape_attempts INT,
neutered BOOLEAN,
weight_kg DECIMAL
);
-- Add a column species of type string to animals table
ALTER TABLE
animals
ADD
COLUMN species varchar;
-- Create a table to store owners.
CREATE TABLE owners(
id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
full_name VARCHAR NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
);
-- Create a table to store species.
CREATE TABLE species(
id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
name VARCHAR NOT NULL,
PRIMARY KEY (id)
);
-- Edit the animals table to drop the species column.
ALTER TABLE
animals DROP COLUMN species;
-- Add primary key to id column of animals table.
ALTER TABLE
animals
ADD
PRIMARY KEY (id);
-- set id column of animals table as autoincrement primary key.
ALTER TABLE
animals
ALTER COLUMN
id
SET
DEFAULT nextval('animals_pkey');
-- Add columns species_id as foreign key to animals table.
ALTER TABLE
animals
ADD
COLUMN species_id INT REFERENCES species(id);
-- Add columns owner_id as foreign key to animals table.
ALTER TABLE
animals
ADD
COLUMN owner_id INT REFERENCES owners(id);
-- Create the vets to store vets.
CREATE TABLE vets(
id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
name VARCHAR NOT NULL,
age INT NOT NULL,
date_fo_graduation DATE,
PRIMARY KEY (id)
);
-- Create a join table to store species and vets.
CREATE TABLE specialization(
species_id INT REFERENCES species(id),
vet_id INT REFERENCES vets(id)
);
-- Create a join table to store animals and vets.
CREATE TABLE visits(
animal_id INT REFERENCES animals(id),
vet_id INT REFERENCES vets(id),
date_of_visit DATE
);
-- Add new column to owners table.
ALTER TABLE
owners
ADD
COLUMN email VARCHAR(120);
-- Alter owners table set age to null.
ALTER TABLE
owners
ALTER COLUMN
age DROP NOT NULL;
-- Create Index on animal_id column of visits table.
CREATE INDEX animal_id_asc ON visits(animal_id asc);
-- Create Index on vet_id column of visits table.
CREATE INDEX vet_id_asc ON visits(vet_id asc);
-- Create Index owners_email on email column of owners table.
CREATE INDEX owners_email ON owners(email);