Answer final question of module 2

1 files changed, 253 insertions, 246 deletions

diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index 0ed6b0f..f4a6525 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -1,276 +1,283 @@
 \echo '--- Module 2 ---'
 \set ON_ERROR_STOP on
 
-DROP TABLE IF EXISTS accounts CASCADE;
-DROP TABLE IF EXISTS beds CASCADE;
-DROP TABLE IF EXISTS care_centres CASCADE;
-DROP TABLE IF EXISTS consumptions CASCADE;
-DROP TABLE IF EXISTS items CASCADE;
-DROP TABLE IF EXISTS laboratories CASCADE;
-DROP TABLE IF EXISTS patients CASCADE;
-DROP TABLE IF EXISTS shifts CASCADE;
-DROP TABLE IF EXISTS staff CASCADE;
-DROP TABLE IF EXISTS treatments CASCADE;
-DROP TABLE IF EXISTS visits CASCADE;
-DROP TABLE IF EXISTS volunteers CASCADE;
+-- DROP TABLE IF EXISTS accounts CASCADE;
+-- DROP TABLE IF EXISTS beds CASCADE;
+-- DROP TABLE IF EXISTS care_centres CASCADE;
+-- DROP TABLE IF EXISTS consumptions CASCADE;
+-- DROP TABLE IF EXISTS items CASCADE;
+-- DROP TABLE IF EXISTS laboratories CASCADE;
+-- DROP TABLE IF EXISTS patients CASCADE;
+-- DROP TABLE IF EXISTS shifts CASCADE;
+-- DROP TABLE IF EXISTS staff CASCADE;
+-- DROP TABLE IF EXISTS treatments CASCADE;
+-- DROP TABLE IF EXISTS visits CASCADE;
+-- DROP TABLE IF EXISTS volunteers CASCADE;
 
-CREATE TABLE accounts (
-  id bigint NOT NULL,
-  name varchar NOT NULL,
-  address varchar NOT NULL,
-  birth_date timestamp NOT NULL,
-  phone_number varchar,
-  pager_number varchar,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE accounts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE accounts_id_seq OWNED BY accounts.id;
+-- CREATE TABLE accounts (
+  -- id bigint NOT NULL,
+  -- name varchar NOT NULL,
+  -- address varchar NOT NULL,
+  -- birth_date timestamp NOT NULL,
+  -- phone_number varchar,
+  -- pager_number varchar,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE accounts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE accounts_id_seq OWNED BY accounts.id;
 
-CREATE TABLE beds (
-  id bigint NOT NULL,
-  bed_number varchar,
-  room_number varchar,
-  care_centre_id bigint NOT NULL,
-  patient_id bigint,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE beds_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE beds_id_seq OWNED BY beds.id;
+-- CREATE TABLE beds (
+  -- id bigint NOT NULL,
+  -- bed_number varchar,
+  -- room_number varchar,
+  -- care_centre_id bigint NOT NULL,
+  -- patient_id bigint,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE beds_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE beds_id_seq OWNED BY beds.id;
 
-CREATE TABLE care_centres (
-  id bigint NOT NULL,
-  nurse_id bigint NOT NULL,
-  name varchar,
-  location varchar,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE care_centres_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE care_centres_id_seq OWNED BY care_centres.id;
+-- CREATE TABLE care_centres (
+  -- id bigint NOT NULL,
+  -- nurse_id bigint NOT NULL,
+  -- name varchar,
+  -- location varchar,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE care_centres_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE care_centres_id_seq OWNED BY care_centres.id;
 
-CREATE TABLE consumptions (
-  id bigint NOT NULL,
-  patient_id bigint NOT NULL,
-  item_id bigint NOT NULL,
-  consumed_at timestamp NOT NULL,
-  quantity integer NOT NULL,
-  total_cost numeric NOT NULL,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE consumptions_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE consumptions_id_seq OWNED BY consumptions.id;
+-- CREATE TABLE consumptions (
+  -- id bigint NOT NULL,
+  -- patient_id bigint NOT NULL,
+  -- item_id bigint NOT NULL,
+  -- consumed_at timestamp NOT NULL,
+  -- quantity integer NOT NULL,
+  -- total_cost numeric NOT NULL,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE consumptions_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE consumptions_id_seq OWNED BY consumptions.id;
 
-CREATE TABLE items (
-  id bigint NOT NULL,
-  sku varchar NOT NULL,
-  description text,
-  unit_cost numeric NOT NULL,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE items_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE items_id_seq OWNED BY items.id;
+-- CREATE TABLE items (
+  -- id bigint NOT NULL,
+  -- sku varchar NOT NULL,
+  -- description text,
+  -- unit_cost numeric NOT NULL,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE items_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE items_id_seq OWNED BY items.id;
 
-CREATE TABLE laboratories (
-  id bigint NOT NULL,
-  name varchar,
-  location varchar,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE laboratories_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE laboratories_id_seq OWNED BY laboratories.id;
+-- CREATE TABLE laboratories (
+  -- id bigint NOT NULL,
+  -- name varchar,
+  -- location varchar,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE laboratories_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE laboratories_id_seq OWNED BY laboratories.id;
 
-CREATE TABLE patients (
-  id bigint NOT NULL,
-  account_id bigint NOT NULL,
-  physician_id bigint NOT NULL,
-  referring_physician_id bigint NOT NULL,
-  contacted_at timestamp NOT NULL,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE patients_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE patients_id_seq OWNED BY patients.id;
+-- CREATE TABLE patients (
+  -- id bigint NOT NULL,
+  -- account_id bigint NOT NULL,
+  -- physician_id bigint NOT NULL,
+  -- referring_physician_id bigint NOT NULL,
+  -- contacted_at timestamp NOT NULL,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE patients_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE patients_id_seq OWNED BY patients.id;
 
-CREATE TABLE shifts (
-  id bigint NOT NULL,
-  care_centre_id bigint NOT NULL,
-  employee_id bigint NOT NULL,
-  started_at timestamp NOT NULL,
-  ended_at timestamp without time zone,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE shifts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE shifts_id_seq OWNED BY shifts.id;
+-- CREATE TABLE shifts (
+  -- id bigint NOT NULL,
+  -- care_centre_id bigint NOT NULL,
+  -- employee_id bigint NOT NULL,
+  -- started_at timestamp NOT NULL,
+  -- ended_at timestamp without time zone,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE shifts_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE shifts_id_seq OWNED BY shifts.id;
 
-CREATE TABLE staff (
-  id bigint NOT NULL,
-  account_id bigint NOT NULL,
-  laboratory_id bigint,
-  type varchar,
-  hired_at timestamp without time zone,
-  qualifications text[],
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE staff_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE staff_id_seq OWNED BY staff.id;
+-- CREATE TABLE staff (
+  -- id bigint NOT NULL,
+  -- account_id bigint NOT NULL,
+  -- laboratory_id bigint,
+  -- type varchar,
+  -- hired_at timestamp without time zone,
+  -- qualifications text[],
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE staff_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE staff_id_seq OWNED BY staff.id;
 
-CREATE TABLE treatments (
-  id bigint NOT NULL,
-  patient_id bigint NOT NULL,
-  name varchar NOT NULL,
-  number varchar NOT NULL,
-  occurred_at timestamp NOT NULL,
-  results text,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE treatments_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE treatments_id_seq OWNED BY treatments.id;
+-- CREATE TABLE treatments (
+  -- id bigint NOT NULL,
+  -- patient_id bigint NOT NULL,
+  -- name varchar NOT NULL,
+  -- number varchar NOT NULL,
+  -- occurred_at timestamp NOT NULL,
+  -- results text,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE treatments_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE treatments_id_seq OWNED BY treatments.id;
 
-CREATE TABLE visits (
-  id bigint NOT NULL,
-  patient_id bigint NOT NULL,
-  scheduled_at timestamp NOT NULL,
-  comments text,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE visits_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE visits_id_seq OWNED BY visits.id;
+-- CREATE TABLE visits (
+  -- id bigint NOT NULL,
+  -- patient_id bigint NOT NULL,
+  -- scheduled_at timestamp NOT NULL,
+  -- comments text,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE visits_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE visits_id_seq OWNED BY visits.id;
 
-CREATE TABLE volunteers (
-  id bigint NOT NULL,
-  account_id bigint NOT NULL,
-  skill text NOT NULL,
-  created_at timestamp NOT NULL,
-  updated_at timestamp NOT NULL
-);
-CREATE SEQUENCE volunteers_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-ALTER SEQUENCE volunteers_id_seq OWNED BY volunteers.id;
+-- CREATE TABLE volunteers (
+  -- id bigint NOT NULL,
+  -- account_id bigint NOT NULL,
+  -- skill text NOT NULL,
+  -- created_at timestamp NOT NULL,
+  -- updated_at timestamp NOT NULL
+-- );
+-- CREATE SEQUENCE volunteers_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
+-- ALTER SEQUENCE volunteers_id_seq OWNED BY volunteers.id;
 
-ALTER TABLE accounts ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq'::regclass);
-ALTER TABLE beds ALTER COLUMN id SET DEFAULT nextval('beds_id_seq'::regclass);
-ALTER TABLE care_centres ALTER COLUMN id SET DEFAULT nextval('care_centres_id_seq'::regclass);
-ALTER TABLE consumptions ALTER COLUMN id SET DEFAULT nextval('consumptions_id_seq'::regclass);
-ALTER TABLE items ALTER COLUMN id SET DEFAULT nextval('items_id_seq'::regclass);
-ALTER TABLE laboratories ALTER COLUMN id SET DEFAULT nextval('laboratories_id_seq'::regclass);
-ALTER TABLE patients ALTER COLUMN id SET DEFAULT nextval('patients_id_seq'::regclass);
-ALTER TABLE shifts ALTER COLUMN id SET DEFAULT nextval('shifts_id_seq'::regclass);
-ALTER TABLE staff ALTER COLUMN id SET DEFAULT nextval('staff_id_seq'::regclass);
-ALTER TABLE treatments ALTER COLUMN id SET DEFAULT nextval('treatments_id_seq'::regclass);
-ALTER TABLE visits ALTER COLUMN id SET DEFAULT nextval('visits_id_seq'::regclass);
-ALTER TABLE volunteers ALTER COLUMN id SET DEFAULT nextval('volunteers_id_seq'::regclass);
-ALTER TABLE accounts ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
-ALTER TABLE beds ADD CONSTRAINT beds_pkey PRIMARY KEY (id);
-ALTER TABLE care_centres ADD CONSTRAINT care_centres_pkey PRIMARY KEY (id);
-ALTER TABLE consumptions ADD CONSTRAINT consumptions_pkey PRIMARY KEY (id);
-ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY (id);
-ALTER TABLE laboratories ADD CONSTRAINT laboratories_pkey PRIMARY KEY (id);
-ALTER TABLE patients ADD CONSTRAINT patients_pkey PRIMARY KEY (id);
-ALTER TABLE shifts ADD CONSTRAINT shifts_pkey PRIMARY KEY (id);
-ALTER TABLE staff ADD CONSTRAINT staff_pkey PRIMARY KEY (id);
-ALTER TABLE treatments ADD CONSTRAINT treatments_pkey PRIMARY KEY (id);
-ALTER TABLE visits ADD CONSTRAINT visits_pkey PRIMARY KEY (id);
-ALTER TABLE volunteers ADD CONSTRAINT volunteers_pkey PRIMARY KEY (id);
-CREATE INDEX index_beds_on_care_centre_id ON beds (care_centre_id);
-CREATE INDEX index_beds_on_patient_id ON beds (patient_id);
-CREATE INDEX index_care_centres_on_nurse_id ON care_centres (nurse_id);
-CREATE INDEX index_consumptions_on_item_id ON consumptions (item_id);
-CREATE INDEX index_consumptions_on_patient_id ON consumptions (patient_id);
-CREATE UNIQUE INDEX index_patients_on_account_id ON patients (account_id);
-CREATE INDEX index_patients_on_physician_id ON patients (physician_id);
-CREATE INDEX index_patients_on_referring_physician_id ON patients (referring_physician_id);
-CREATE INDEX index_shifts_on_care_centre_id ON shifts (care_centre_id);
-CREATE INDEX index_shifts_on_employee_id ON shifts (employee_id);
-CREATE UNIQUE INDEX index_staff_on_account_id ON staff (account_id);
-CREATE INDEX index_staff_on_laboratory_id ON staff (laboratory_id);
-CREATE INDEX index_staff_on_type ON staff (type);
-CREATE INDEX index_treatments_on_patient_id ON treatments (patient_id);
-CREATE INDEX index_visits_on_patient_id ON visits (patient_id);
-CREATE UNIQUE INDEX index_volunteers_on_account_id ON volunteers (account_id);
-ALTER TABLE beds ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
-ALTER TABLE beds ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-ALTER TABLE consumptions ADD CONSTRAINT items_fk FOREIGN KEY (item_id) REFERENCES items(id);
-ALTER TABLE consumptions ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-ALTER TABLE patients ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
-ALTER TABLE patients ADD CONSTRAINT staff_fk FOREIGN KEY (physician_id) REFERENCES staff(id);
-ALTER TABLE patients ADD CONSTRAINT referring_staff_fk FOREIGN KEY (referring_physician_id) REFERENCES staff(id);
-ALTER TABLE shifts ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
-ALTER TABLE shifts ADD CONSTRAINT staff_fk FOREIGN KEY (employee_id) REFERENCES staff(id);
-ALTER TABLE staff ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
-ALTER TABLE staff ADD CONSTRAINT laboratories_fk FOREIGN KEY (laboratory_id) REFERENCES laboratories(id);
-ALTER TABLE treatments ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-ALTER TABLE visits ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
-ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
+-- ALTER TABLE accounts ALTER COLUMN id SET DEFAULT nextval('accounts_id_seq'::regclass);
+-- ALTER TABLE beds ALTER COLUMN id SET DEFAULT nextval('beds_id_seq'::regclass);
+-- ALTER TABLE care_centres ALTER COLUMN id SET DEFAULT nextval('care_centres_id_seq'::regclass);
+-- ALTER TABLE consumptions ALTER COLUMN id SET DEFAULT nextval('consumptions_id_seq'::regclass);
+-- ALTER TABLE items ALTER COLUMN id SET DEFAULT nextval('items_id_seq'::regclass);
+-- ALTER TABLE laboratories ALTER COLUMN id SET DEFAULT nextval('laboratories_id_seq'::regclass);
+-- ALTER TABLE patients ALTER COLUMN id SET DEFAULT nextval('patients_id_seq'::regclass);
+-- ALTER TABLE shifts ALTER COLUMN id SET DEFAULT nextval('shifts_id_seq'::regclass);
+-- ALTER TABLE staff ALTER COLUMN id SET DEFAULT nextval('staff_id_seq'::regclass);
+-- ALTER TABLE treatments ALTER COLUMN id SET DEFAULT nextval('treatments_id_seq'::regclass);
+-- ALTER TABLE visits ALTER COLUMN id SET DEFAULT nextval('visits_id_seq'::regclass);
+-- ALTER TABLE volunteers ALTER COLUMN id SET DEFAULT nextval('volunteers_id_seq'::regclass);
+-- ALTER TABLE accounts ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);
+-- ALTER TABLE beds ADD CONSTRAINT beds_pkey PRIMARY KEY (id);
+-- ALTER TABLE care_centres ADD CONSTRAINT care_centres_pkey PRIMARY KEY (id);
+-- ALTER TABLE consumptions ADD CONSTRAINT consumptions_pkey PRIMARY KEY (id);
+-- ALTER TABLE items ADD CONSTRAINT items_pkey PRIMARY KEY (id);
+-- ALTER TABLE laboratories ADD CONSTRAINT laboratories_pkey PRIMARY KEY (id);
+-- ALTER TABLE patients ADD CONSTRAINT patients_pkey PRIMARY KEY (id);
+-- ALTER TABLE shifts ADD CONSTRAINT shifts_pkey PRIMARY KEY (id);
+-- ALTER TABLE staff ADD CONSTRAINT staff_pkey PRIMARY KEY (id);
+-- ALTER TABLE treatments ADD CONSTRAINT treatments_pkey PRIMARY KEY (id);
+-- ALTER TABLE visits ADD CONSTRAINT visits_pkey PRIMARY KEY (id);
+-- ALTER TABLE volunteers ADD CONSTRAINT volunteers_pkey PRIMARY KEY (id);
+-- CREATE INDEX index_beds_on_care_centre_id ON beds (care_centre_id);
+-- CREATE INDEX index_beds_on_patient_id ON beds (patient_id);
+-- CREATE INDEX index_care_centres_on_nurse_id ON care_centres (nurse_id);
+-- CREATE INDEX index_consumptions_on_item_id ON consumptions (item_id);
+-- CREATE INDEX index_consumptions_on_patient_id ON consumptions (patient_id);
+-- CREATE UNIQUE INDEX index_patients_on_account_id ON patients (account_id);
+-- CREATE INDEX index_patients_on_physician_id ON patients (physician_id);
+-- CREATE INDEX index_patients_on_referring_physician_id ON patients (referring_physician_id);
+-- CREATE INDEX index_shifts_on_care_centre_id ON shifts (care_centre_id);
+-- CREATE INDEX index_shifts_on_employee_id ON shifts (employee_id);
+-- CREATE UNIQUE INDEX index_staff_on_account_id ON staff (account_id);
+-- CREATE INDEX index_staff_on_laboratory_id ON staff (laboratory_id);
+-- CREATE INDEX index_staff_on_type ON staff (type);
+-- CREATE INDEX index_treatments_on_patient_id ON treatments (patient_id);
+-- CREATE INDEX index_visits_on_patient_id ON visits (patient_id);
+-- CREATE UNIQUE INDEX index_volunteers_on_account_id ON volunteers (account_id);
+-- ALTER TABLE beds ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
+-- ALTER TABLE beds ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
+-- ALTER TABLE consumptions ADD CONSTRAINT items_fk FOREIGN KEY (item_id) REFERENCES items(id);
+-- ALTER TABLE consumptions ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
+-- ALTER TABLE patients ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
+-- ALTER TABLE patients ADD CONSTRAINT staff_fk FOREIGN KEY (physician_id) REFERENCES staff(id);
+-- ALTER TABLE patients ADD CONSTRAINT referring_staff_fk FOREIGN KEY (referring_physician_id) REFERENCES staff(id);
+-- ALTER TABLE shifts ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES care_centres(id);
+-- ALTER TABLE shifts ADD CONSTRAINT staff_fk FOREIGN KEY (employee_id) REFERENCES staff(id);
+-- ALTER TABLE staff ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
+-- ALTER TABLE staff ADD CONSTRAINT laboratories_fk FOREIGN KEY (laboratory_id) REFERENCES laboratories(id);
+-- ALTER TABLE treatments ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
+-- ALTER TABLE visits ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFERENCES patients(id);
+-- ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(id);
 
 
 \echo '--- Module 3 ---'
 
--- ALTER TABLE IF EXISTS Nurses DROP CONSTRAINT care_centres_fk;
--- DROP TABLE IF EXISTS Treatments;
--- DROP TABLE IF EXISTS Physicians;
--- DROP TABLE IF EXISTS Patients;
--- DROP TABLE IF EXISTS Care_centres;
--- DROP TABLE IF EXISTS Nurses;
+ALTER TABLE IF EXISTS Nurses DROP CONSTRAINT care_centres_fk;
+DROP TABLE IF EXISTS Treatments;
+DROP TABLE IF EXISTS Physicians;
+DROP TABLE IF EXISTS Patients;
+DROP TABLE IF EXISTS Care_centres;
+DROP TABLE IF EXISTS Nurses;
 
--- CREATE TABLE Nurses(
-  -- nid bigint primary key,
-  -- name varchar(255),
-  -- care_centre_id bigint,
-  -- certificate_type varchar(255),
-  -- telephone varchar(255),
-  -- salary decimal
--- );
+CREATE TABLE Nurses(
+  nid bigint primary key,
+  name varchar,
+  care_centre_id bigint,
+  certificate_type varchar,
+  telephone varchar,
+  salary decimal
+);
 
--- CREATE TABLE Care_centres(
-  -- cid bigint primary key,
-  -- name varchar(255),
-  -- location text,
-  -- nurse_charge_id bigint references Nurses(nid)
--- );
+CREATE TABLE Care_centres(
+  cid bigint primary key,
+  name varchar,
+  location text,
+  nurse_charge_id bigint references Nurses(nid)
+);
 
--- CREATE TABLE Patients(
-  -- pid bigint primary key,
-  -- name varchar(255),
-  -- address text,
-  -- telephone varchar(255),
-  -- care_centre_id bigint references Care_centres(cid)
--- );
+CREATE TABLE Patients(
+  pid bigint primary key,
+  name varchar,
+  address text,
+  telephone varchar,
+  care_centre_id bigint references Care_centres(cid)
+);
 
--- CREATE TABLE Physicians(
-  -- phid bigint primary key,
-  -- name varchar(255),
-  -- pager_number varchar(255),
-  -- specialization varchar(255),
-  -- salary decimal
--- );
+CREATE TABLE Physicians(
+  phid bigint primary key,
+  name varchar,
+  pager_number varchar,
+  specialization varchar,
+  salary decimal
+);
 
--- CREATE TABLE Treatments(
-  -- tid bigint primary key,
-  -- patient_id bigint references Patients(pid),
-  -- physician_id bigint references Physicians(phid),
-  -- treatment_name varchar(255),
-  -- "date" timestamp
--- );
+CREATE TABLE Treatments(
+  tid bigint primary key,
+  patient_id bigint references Patients(pid),
+  physician_id bigint references Physicians(phid),
+  treatment_name varchar,
+  "date" timestamp
+);
+
+ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);
 
--- ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);
+INSERT INTO Care_centres VALUES(1, 'Intensive Care Unit', 'MT-M');
+INSERT INTO Nurses VALUES (1, 'Sharp', 1, 'birth', '111-222-3333', 100000);
 
--- INSERT INTO Care_centres VALUES(1, 'A', 'Earth');
--- INSERT INTO Nurses VALUES (1, 'Sharp', 1, 'birth', '111-222-3333', 100000);
+INSERT INTO Physicians VALUES (1, 'dr. mo', '555-555-5555', 'love', 10000000);
+INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 10000000);
+INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 10000000);
+INSERT INTO Physicians VALUES (4, 'dr. didi', '555-555-0003', 'back pain', 10000000);
 
--- INSERT INTO Physicians VALUES (1, 'dr. mo', '555-555-5555', 'love', 10000000);
--- INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 10000000);
--- INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 10000000);
--- INSERT INTO Physicians VALUES (4, 'dr. didi', '555-555-0003', 'back pain', 10000000);
+INSERT INTO Patients VALUES (1, 'moe sislack', '123 street nw', '777-777-7777', 1);
+INSERT INTO Patients VALUES (2, 'mose allison', '234 street nw', '888-888-8888', 1);
+INSERT INTO Patients VALUES (3, 'didi kong', '345 street nw', '999-999-9999', 1);
 
--- INSERT INTO Patients VALUES (1, 'moe sislack', '123 street nw', '777-777-7777', 1);
--- INSERT INTO Patients VALUES (2, 'mose allison', '234 street nw', '888-888-8888', 1);
--- INSERT INTO Patients VALUES (3, 'didi kong', '345 street nw', '999-999-9999', 1);
+INSERT INTO Treatments VALUES(1, 1, 1, 'Surgery', '2020-03-01');
 
+SELECT * FROM Care_centres;
+SELECT * FROM Nurses;
+SELECT * FROM Physicians;
+SELECT * FROM Patients;
+SELECT * FROM Treatments;