Answer final question of module 2

7 files changed, 293 insertions, 255 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index bcdf69c..8a0dd0e 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -376,11 +376,26 @@ on that particular day.
 Order the list by treatment ID, and by reverse chronological order for each treatment ID.
 
 ```sql
+SELECT treatments.id, date(treatments.occurred_at), staff.id, count(treatments.id)
+FROM "treatments"
+INNER JOIN "patients" ON "patients"."id" = "treatments"."patient_id"
+INNER JOIN "staff" ON "staff"."id" = "patients"."physician_id" AND "staff"."type" = $1
+INNER JOIN "accounts" ON "accounts"."id" = "staff"."account_id"
+WHERE (occurred_at > '2020-03-14 21:00:39.156746')
+GROUP BY "treatments"."id", DATE(treatments.occurred_at), staff.id
+ORDER BY "treatments"."id" DESC LIMIT $2  [["type", "Physician"],
 ```
 
 Create secondary key indexes to optimize the performance of this query. State any assumptions.
 
+Each of the joins above take advantage of foreign key indexes except for
+the group by on the `treatments.occurred_at` column and the filter to find
+all staff with a type of `Physician`. The query above groups by date so we can
+reduce the size of the index by applying the `DATE` function to the `occurred_at` column.
+
 ```sql
+CREATE INDEX index_occurred_at_on_treatments ON treatments ((occurred_at::DATE));
+CREATE INDEX intex_type_on_staff ON staff (type);
 ```
 
 ## Project Module 3
diff --git a/assignments/final/erd.png b/assignments/final/erd.png
index 5e041f2..7ff9c14 100644
--- a/assignments/final/erd.png
+++ b/assignments/final/erd.png
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;
diff --git a/rvh/app/models/physician.rb b/rvh/app/models/physician.rb
index 58c7fdb..85d6605 100644
--- a/rvh/app/models/physician.rb
+++ b/rvh/app/models/physician.rb
@@ -1,2 +1,4 @@
 class Physician < Staff
+  has_many :patients
+  has_many :treatmeants, through: :patients
 end
diff --git a/rvh/app/models/staff.rb b/rvh/app/models/staff.rb
index 8ac92cd..b462431 100644
--- a/rvh/app/models/staff.rb
+++ b/rvh/app/models/staff.rb
@@ -8,3 +8,19 @@ class Staff < ApplicationRecord
   delegate :phone_number, to: :account
   delegate :pager_number, to: :account
 end
+
+=begin
+
+Find which physicians performed a treatment each day.
+
+treatment | day(treatment.occurred_at) | staff.name | count(treatment_id)
+
+SELECT treatments.id, date(treatments.occurred_at), staff.id, count(treatments.id)
+FROM "treatments"
+INNER JOIN "patients" ON "patients"."id" = "treatments"."patient_id"
+INNER JOIN "staff" ON "staff"."id" = "patients"."physician_id" AND "staff"."type" = $1
+INNER JOIN "accounts" ON "accounts"."id" = "staff"."account_id"
+WHERE (occurred_at > '2020-03-14 21:00:39.156746')
+GROUP BY "treatments"."id", DATE(treatments.occurred_at), staff.id
+ORDER BY "treatments"."id" DESC LIMIT $2  [["type", "Physician"],
+=end
diff --git a/rvh/app/models/treatment.rb b/rvh/app/models/treatment.rb
index 4b64de1..9481aa5 100644
--- a/rvh/app/models/treatment.rb
+++ b/rvh/app/models/treatment.rb
@@ -1,4 +1,4 @@
 class Treatment < ApplicationRecord
-  has_one :physician, through: :patient
   belongs_to :patient
+  has_one :physician, through: :patient
 end
diff --git a/rvh/erd.dot b/rvh/erd.dot
index 22fe494..2d1aac5 100644
--- a/rvh/erd.dot
+++ b/rvh/erd.dot
@@ -41,7 +41,7 @@ m_CareCentre [label = <<table border="0" align="center" cellspacing="0.5" cellpa
 <table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
   <tr><td align="left" width="130" port="location">location <font face="Arial Italic" color="grey60">string</font></td></tr>
   <tr><td align="left" width="130" port="name">name <font face="Arial Italic" color="grey60">string</font></td></tr>
-  <tr><td align="left" width="130" port="nurse_id">nurse_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="nurse_id">nurse_id <font face="Arial Italic" color="grey60">integer (8) ∗</font></td></tr>
 </table>
 >];
 m_Consumption [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
@@ -50,7 +50,6 @@ m_Consumption [label = <<table border="0" align="center" cellspacing="0.5" cellp
 |
 <table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
   <tr><td align="left" width="130" port="consumed_at">consumed_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
-  <tr><td align="left" width="130" port="physician_id">physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
   <tr><td align="left" width="130" port="quantity">quantity <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
   <tr><td align="left" width="130" port="total_cost">total_cost <font face="Arial Italic" color="grey60">decimal ∗</font></td></tr>
 </table>
@@ -84,8 +83,7 @@ m_Patient [label = <<table border="0" align="center" cellspacing="0.5" cellpaddi
 |
 <table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
   <tr><td align="left" width="130" port="contacted_at">contacted_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
-  <tr><td align="left" width="130" port="physician_id">physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
-  <tr><td align="left" width="130" port="referring_physician_id">referring_physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="referring_physician_id">referring_physician_id <font face="Arial Italic" color="grey60">integer (8) ∗</font></td></tr>
 </table>
 >];
 m_Physician [color = "grey60", fontcolor = "grey60", label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
@@ -97,7 +95,7 @@ m_Shift [label = <<table border="0" align="center" cellspacing="0.5" cellpadding
 </table>
 |
 <table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
-  <tr><td align="left" width="130" port="employee_id">employee_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="employee_id">employee_id <font face="Arial Italic" color="grey60">integer (8) ∗</font></td></tr>
   <tr><td align="left" width="130" port="ended_at">ended_at <font face="Arial Italic" color="grey60">datetime</font></td></tr>
   <tr><td align="left" width="130" port="started_at">started_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
 </table>
@@ -144,18 +142,18 @@ m_Volunteer [color = "grey60", fontcolor = "grey60", label = <<table border="0"
   m_Staff -> m_Technician [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
   m_Staff -> m_Volunteer [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
   m_Account -> m_Staff [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "5"];
-  m_Laboratory -> m_Staff [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "5"];
+  m_Laboratory -> m_Staff [arrowsize = "0.6", arrowhead = "odotnormal", arrowtail = "odot", weight = "5"];
   m_Staff -> m_Treatment [arrowsize = "0.6", arrowhead = "odotnormal", arrowtail = "odot", weight = "5"];
+  m_Physician -> m_Patient [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "3"];
   m_CareCentre -> m_Shift [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
   m_Patient -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
   m_Item -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
   m_Physician -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
-  m_Physician -> m_Treatment [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
   m_Patient -> m_Treatment [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
+  m_Treatment -> m_Physician [style = "dotted", arrowsize = "0.6", arrowhead = "odot", arrowtail = "odot", weight = "1", constraint = "false"];
   m_CareCentre -> m_Bed [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
   m_Patient -> m_Bed [arrowsize = "0.6", arrowhead = "odot", arrowtail = "odot", weight = "2"];
   m_Patient -> m_Visit [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
   m_Account -> m_Patient [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
-  m_Physician -> m_Patient [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
   m_Nurse -> m_CareCentre [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
 }