generated from microverseinc/curriculum-template-databases
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
66 lines (45 loc) · 1.45 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
/* Database schema to keep the structure of entire database. */
CREATE TABLE animals (
id BIGSERIAL PRIMARY KEY NOT NULL,
name VARCHAR(300) NOT NULL,
date_of_birth DATE NOT NULL,
escape_attempts INTEGER NOT NULL,
neutered BOOLEAN NOT NULL,
weight_kg DECIMAL NOT NULL
);
ALTER TABLE animals ADD COLUMN species VARCHAR(100);
-- creating owners table
CREATE TABLE owners (
id SERIAL PRIMARY KEY NOT NULL,
full_name VARCHAR(300);
age INTEGER
);
-- creating species table
CREATE TABLE species(
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(100)
);
-- removing column species from table animals
ALTER TABLE animals DROP COLUMN species;
-- adding column species_id which is a foreign key referencing species table
ALTER TABLE animals ADD COLUMN species_id FOREIGN KEY REFERENCES species(id);
-- Add column owner_id which is a foreign key referencing owners table
ALTER TABLE animals ADD COLUMN owneer_id BIGINT REFERENCES owners(id);
-- Creating a table named vets
CREATE TABLE vets (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(100),
age INTEGER,
date_of_graduation DATE
);
-- creating table named specializations
CREATE TABLE specializations (
vet_id INTEGER REFERENCES vets (id),
species_id INTEGER REFERENCES species (id)
);
-- creating a table named visits
CREATE TABLE visits (
animal_id INTEGER REFERENCES animals (id),
vet_id INTEGER REFERENCES vets (id),
date_of_visit DATE
);