Add ddl for module 3

1 files changed, 210 insertions, 158 deletions

diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index 2d568eb..ef0e132 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -1,176 +1,228 @@
 \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 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
+-- );
+
+-- 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
+-- );
+
+-- 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
+-- );
+
+-- CREATE TABLE laboratories(
+  -- id bigint primary key,
+  -- location text
+-- );
+
+-- CREATE TABLE staff(
+  -- id bigint primary key,
+  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
+  -- job_class varchar(255)
+-- );
+
+-- CREATE TABLE technicians(
+  -- id bigint primary key,
+  -- employee_id bigint references employees(id) NOT NULL UNIQUE,
+  -- skill varchar(255)
+-- );
+
+-- 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,
+  -- name varchar(255),
+  -- occurred_at timestamp NOT NULL,
+  -- results text
+-- );
+
+-- 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
+-- );
+
+-- -- 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;
 
-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;
+ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);