summaryrefslogtreecommitdiff
path: root/assignments/final/run.sql
diff options
context:
space:
mode:
Diffstat (limited to 'assignments/final/run.sql')
-rw-r--r--assignments/final/run.sql499
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;
generated by cgit v1.2.3 (git 2.39.1) at 2026-02-02 05:39:27 +0000