generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
77 lines (65 loc) · 1.6 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
/* Database schema to keep the structure of entire database. */
CREATE TABLE animals (
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(100),
date_of_birth DATE,
escape_attempts INTEGER,
neutered BOOLEAN,
weight_kg DECIMAL
);
/*Created new tables*/
CREATE TABLE species (
id INTEGER GENERATED ALWAYS AS IDENTITY,
name VARCHAR(50),
PRIMARY KEY (id)
);
CREATE TABLE owners (
id INTEGER GENERATED ALWAYS AS IDENTITY,
full_name VARCHAR(100),
age INTEGER,
PRIMARY KEY (id)
);
/* Transaction for refatoring animal table */
BEGIN WORK;
/* Add new columns to animals table*/
ALTER TABLE
animals
ADD
species_id INTEGER;
ALTER TABLE
animals
ADD
owner_id INTEGER;
SAVEPOINT SP1;
/* Add FOREING KEY to animals table */
ALTER TABLE
animals
ADD
CONSTRAINT fk_species FOREIGN KEY (species_id) REFERENCES species (id);
ALTER TABLE
animals
ADD
CONSTRAINT fk_owners FOREIGN KEY (owner_id) REFERENCES owners (id);
COMMIT;
/* MILESTONE--4 */
/* SQL Relationships Adding new tables*/
CREATE TABLE vets(
id INTEGER GENERATED ALWAYS AS IDENTITY,
name VARCHAR(255),
age INTEGER,
data_of_graduation DATE,
PRIMARY KEY (id)
);
CREATE TABLE specializations(
vet_id INTEGER,
species_id INTEGER,
CONSTRAINT vet_key FOREIGN KEY (vet_id) REFERENCES vets(id) ON DELETE CASCADE,
CONSTRAINT species_key FOREIGN KEY (species_id REFERENCES species(id)
);
CREATE TABLE visits(
animal_id INTEGER,
vet_id INTEGER,
visit_date DATE,
CONSTRAINT animal_key FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
CONSTRAINT vet_key FOREIGN KEY (vet_id) REFERENCES vets(id) ON DELETE CASCADE
);