Add SQL for new schema

2 files changed, 452 insertions, 218 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index 649bf39..2d97283 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -143,6 +143,204 @@ care that they need.
   ![relational schema](./module-2.png)
   ![ERD](./erd.png)
 
+```sql
+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 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 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 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 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 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;
+
+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);
+```
+
 **Besides the 3NF relations, what additional types of information are required to create a physical database design?**
 
 * Usage patterns
diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index e7c6459..0ed6b0f 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -1,240 +1,276 @@
 \echo '--- Module 2 ---'
 \set ON_ERROR_STOP on
 
--- DROP TABLE IF EXISTS beds;
--- DROP TABLE IF EXISTS consumptions;
--- DROP TABLE IF EXISTS items;
--- DROP TABLE IF EXISTS laboratories;
--- DROP TABLE IF EXISTS staff;
--- DROP TABLE IF EXISTS technicians;
--- DROP TABLE IF EXISTS timesheets;
--- DROP TABLE IF EXISTS treatments;
--- DROP TABLE IF EXISTS visits;
-
--- DROP TABLE IF EXISTS patients;
--- DROP TABLE IF EXISTS physicians;
--- DROP TABLE IF EXISTS care_centres;
--- DROP TABLE IF EXISTS nurses;
--- DROP TABLE IF EXISTS employees;
--- DROP TABLE IF EXISTS people;
-
--- CREATE TABLE people(
-  -- id bigint primary key,
-  -- name varchar(255) NOT NULL,
-  -- address varchar(255),
-  -- birth_date timestamp,
-  -- phone_number varchar(255)
--- );
-
--- CREATE TABLE employees(
-  -- id bigint primary key,
-  -- person_id bigint references people(id) NOT NULL UNIQUE,
-  -- hired_at timestamp NOT NULL
--- );
-
--- CREATE TABLE nurses(
-  -- id bigint primary key,
-  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
-  -- certificate text
--- );
-
--- CREATE TABLE care_centres(
-  -- id bigint primary key,
-  -- name varchar(255),
-  -- location text,
-  -- nurse_in_charge_id bigint references nurses(id) NOT NULL
--- );
-
--- CREATE TABLE timesheets(
-  -- id bigint primary key,
-  -- employee_id bigint references employees(id) NOT NULL,
-  -- care_centre_id bigint references care_centres(id) NOT NULL,
-  -- hours integer,
-  -- week integer
--- );
-
--- CREATE TABLE physicians(
-  -- id bigint primary key,
-  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
-  -- specialty text,
-  -- pager_number varchar(255)
--- );
-
--- CREATE TABLE patients(
-  -- id bigint primary key,
-  -- person_id bigint references people(id) NOT NULL UNIQUE,
-  -- physician_id bigint references physicians(id),
-  -- referring_physician_id bigint references physicians(id) NOT NULL,
-  -- contacted_at timestamp
--- );
+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 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 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 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 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 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 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);
 
--- CREATE TABLE items(
-  -- id bigint primary key,
-  -- number integer,
-  -- description text,
-  -- unit_cost decimal
--- );
 
--- CREATE TABLE consumptions(
-  -- id bigint primary key,
-  -- physican_id bigint references physicians(id) NOT NULL,
-  -- patient_id bigint references patients(id) NOT NULL,
-  -- item_id bigint references items(id) NOT NULL,
-  -- consumed_at timestamp,
-  -- quantity integer,
-  -- total_cost decimal
--- );
+\echo '--- Module 3 ---'
 
--- CREATE TABLE beds(
-  -- id bigint primary key,
-  -- care_centre_id bigint references care_centres(id),
-  -- bed_number integer NOT NULL,
-  -- room_number integer NOT NULL
--- );
+-- 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 laboratories(
-  -- id bigint primary key,
-  -- location text
+-- 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 staff(
-  -- id bigint primary key,
-  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
-  -- job_class varchar(255)
+-- CREATE TABLE Care_centres(
+  -- cid bigint primary key,
+  -- name varchar(255),
+  -- location text,
+  -- nurse_charge_id bigint references Nurses(nid)
 -- );
 
--- CREATE TABLE technicians(
-  -- id bigint primary key,
-  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
-  -- skill varchar(255)
+-- 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 treatments(
-  -- id bigint primary key,
-  -- physician_id bigint references physicians(id) NOT NULL,
-  -- patient_id bigint references patients(id) NOT NULL,
-  -- number integer,
+-- CREATE TABLE Physicians(
+  -- phid bigint primary key,
   -- name varchar(255),
-  -- occurred_at timestamp NOT NULL,
-  -- results text
+  -- pager_number varchar(255),
+  -- specialization varchar(255),
+  -- salary decimal
 -- );
 
--- CREATE TABLE visits(
-  -- id bigint primary key,
-  -- patient_id bigint references patients(id) NOT NULL,
-  -- physician_id bigint references physicians(id) NOT NULL,
-  -- comments text,
-  -- scheduled_at timestamp NOT NULL
+-- 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
 -- );
 
--- -- dr mo
--- INSERT INTO people(id, name) VALUES (1, 'dr. mo');
--- INSERT INTO employees(id, person_id, hired_at) VALUES (1, 1, '2004-06-15');
--- INSERT INTO physicians(id, employee_id) VALUES (1, 1);
-
--- -- dr allison
--- INSERT INTO people(id, name) VALUES (2, 'dr. allison');
--- INSERT INTO employees(id, person_id, hired_at) VALUES (2, 2, '2010-01-01');
--- INSERT INTO physicians(id, employee_id) VALUES (2, 2);
-
--- -- dr didi
--- INSERT INTO people(id, name) VALUES (3, 'dr. didi');
--- INSERT INTO employees(id, person_id, hired_at) VALUES (3, 3, '2020-01-01');
--- INSERT INTO physicians(id, employee_id) VALUES (3, 3);
-
--- -- dr nini
--- INSERT INTO people(id, name) VALUES (4, 'dr. nini');
--- INSERT INTO employees(id, person_id, hired_at) VALUES (4, 4, '2020-01-01');
--- INSERT INTO physicians(id, employee_id) VALUES (4, 4);
-
--- -- moe sislack
--- INSERT INTO people(id, name) VALUES (10, 'moe sislack');
--- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (10, 10, 1);
-
--- -- mose allison
--- INSERT INTO people(id, name) VALUES (11, 'mose allison');
--- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (11, 11, 2);
-
--- -- didi kong
--- INSERT INTO people(id, name) VALUES (12, 'didi kong');
--- INSERT INTO patients(id, person_id, referring_physician_id) VALUES (12, 12, 1);
-
-
--- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (1, 1, 10, 'heart surgery', '2020-03-01');
--- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (2, 2, 11, 'knee surgery', '2020-03-02');
--- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (3, 1, 12, 'brain surgery', '2020-03-02');
--- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (4, 1, 10, 'foot surgery', '2020-03-02');
--- INSERT INTO treatments(id, physician_id, patient_id, name, occurred_at) VALUES (5, 1, 10, 'knee surgery', '2020-03-02');
-
--- SELECT pe.name as physician, t.name as treatment, t.occurred_at
--- FROM treatments t
--- INNER JOIN physicians p on p.id = t.physician_id
--- INNER JOIN employees e on e.id = p.employee_id
--- INNER JOIN people pe on pe.id = e.person_id
--- WHERE t.occurred_at BETWEEN CURRENT_DATE - INTERVAL '14 days' AND CURRENT_DATE
--- ;
--- ORDER BY t.id ASC, t.occurred_at DESC;
-
-
-\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;
-
-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 Care_centres(
-  cid bigint primary key,
-  name varchar(255),
-  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 Physicians(
-  phid bigint primary key,
-  name varchar(255),
-  pager_number varchar(255),
-  specialization varchar(255),
-  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
-);
+-- 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, 'A', 'Earth');
+-- 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);