path: root/assignments/final/README.md

## Project Module 1

For a long time, the Royal Victoria Hospital (RVH) worked with an information system that
consisted of a mix of paper-based files and small independent databases developed within
some departments.
The new administration has created an information system (IS) department,
and they hired you as information officer: head of the team in charge of the design and implementation of a new global information system.

The following information was collected by the first team from the IS department,
who conducted interviews with some of the hospital administration and staff to
identify entity types for the hospital.

The hospital depends primarily on four groups of people:

* employees
* physicians
* patients
* volunteers

Of course, some common attributes are shared by all of these groups:

* person_ID (identifier)
* name
* address
* birth date
* phone number

Each group also has at least one unique attribute of its own.
Employees have a date hired, volunteers have a skill,
physicians have a specialty and a pager number,
and patients have a contact date (date of the first contact with the hospital).
Some people may belong to two or more of these groups at a given time (e.g., patient and volunteer).

Patient: A person who is either admitted to the hospital, or is registered in an outpatient program.
One, and only one, physician is responsible for each patient.
Patients are divided into two groups: resident and outpatient.
Each outpatient is scheduled for zero or more visits.
The entity visit has two attributes: date (partial identifier), and comments.
Note that an instance of visit cannot exist without an outpatient owner entity.
Only resident patients are assigned to a bed, and a bed may or may not be assigned to a patient.

Physician: A member of the hospital staff who may admit patients to the hospital,
and who can administer medical treatments.
A given physician can be responsible for zero or more patients at a given time.

A patient must be referred to the hospital by exactly one physician.
A physician can refer any number of patients, or may not refer any patient.

Physicians may perform any number of treatments on behalf of any number of patients,
or may not perform any treatment.
A patient may have treatments performed by any number of physicians.
For each treatment performed on behalf of a given patient by a particular physician,
the hospital records the treatment date, treatment time, and results.

Employee: Any person employed as part of the hospital staff.
Employees are subdivided into three groups: nurse, staff, and technician.
Only nurse has the attribute certificate, which indicates a qualification (RN, LPN, etc.).
Only staff has the attribute job class, and only technician has the attribute skill.

Each nurse is assigned to one (and only one) care centre.
Each technician is assigned to one or more laboratories.

Care centre: A treatment centre within the hospital.
Examples of care centres are maternity, emergency, and cardiology.
Attributes of care centre are name (identifier) and location.
A care centre may have one or more nurses assigned to it.
Also, one of the nurses assigned to each care centre is appointed nurse-in-charge.
A nurse cannot be appointed nurse-in-charge of a care centre unless s/he has an RN certificate.

Each hospital employee is assigned to work in one or more care centre.
Each care centre has at least one employee, and may have any number of employees.
The hospital records the number of hours per week that a given employee works in a particular care centre.
Each physician can be assigned to one or more care centres,
and a care centre can have one or more physicians assigned to it.

Laboratory: A unit in the hospital where clinical tests (i.e., blood, urine, tissue, etc.) are performed to obtain information about the health of a patient.
Attributes of laboratory include name (identifier) and location.
A laboratory must have one or more technicians assigned to it.

Bed: A hospital bed that may be assigned to a resident patient who is admitted to the hospital.
Each bed has a bed number, a room number, and a care centre ID.
There may be no bed assigned to a care centre, or a care centre may have one or more beds assigned to it.

Item: Any medical or surgical item that is used in treating a patient.
Each item has an item number, description, and unit cost.

A patient may optionally consume any number of items.
A given item may be consumed by one or more patients, or may not be consumed.
For each item consumed by a patient, the hospital records the date, time, quantity, and total cost (which can be computed by multiplying quantity by unit cost).

Treatment: Any test or procedure performed by a physician on behalf of a patient.
Each treatment has a treatment ID, which consists of a treatment number and a treatment name.

**Is the ability to model subtype/supertype relationships likely to be important in a hospital environment such as the RVH?**

It's possible that implementation inheritance may need to be modeled in this case due to the following reasons:

* All the people described above share some common attributes which could indicate a need for a subtype/supertype relationship.
* Employees are subdivided into three groups: nurse, staff and technician.

I think that when people model data they tend to jump towards class/subclass relationship too early in the design process.
I prefer to favour composition over inheritance. If the Liskov substitution principle holds
true for any relationship then I think moving towards implementation inheritance is fine.

**Can the business rules paradigm, and the ability to easily define, implement, and maintain business rules, be used as a competitive advantage in a hospital environment such as the RVH?**

Yes. Change is the only constant in software development. Designing software so that it can be
extended, changed and modified allows the needs of the organization to change with the
freedom of knowing that the software can change with it.

**Do there appear to be any weak entities in the description of the data requirements in this project module?**

> A weak entity is a type of entity that exists when some other type exists so it is dependent on an identifying owner.

The `Visit` entity appears to be a weak entity due to the fact that it's existance relies on
requiring an associated outpatient owner entity.

**Draw an EER diagram to accurately represent this set of requirements. State any assumption you had to make in developing the diagram.**

  ![EER](./module-1.png)

**Are there any universal data models that can be reused as a starting point for modeling RVH’s data requirements?**

## Project Module 2

Use the relational schema of the EER diagram you developed in Module 1 to answer the following questions.

**Should the RVH use normalization when designing its database?**

Yes, minimizing duplication will make it easier to keep changes synchronized.
At the moment there is no identified need to de-normalize the database.

**Why are entity integrity and referential integrity constraints of importance to the hospital?**

It's possible that people will need to change their address or phone number. By ensuring that
the proper referential integrity constraints in place we make it easier to update records and
limit the risk of creating orphaned records. This helps to keep out single source of truth
organized, which makes it easier for scheduling personell and ensuring that patients receive the
care that they need.

**Map the EER diagram to a relational schema, and transform the relation into 3NF**

  ![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
* Regional policies related to location of storage. .e.g EU data regulations.
* Data retention policies
* Data segregation policies

**Are there opportunities for horizontal or vertical partitioning of the database? Are there other opportunities to denormalize the relations of this database? If no, explain why? If yes, how might you denormalize the database? (3 marks)**

> Horizontal partitioning is similar to creating supertype/subtype relationship because different types of the entity are involved in different relationships.

This schema has some supertype/subtype relationships so horizontal scaling is possible.

We could also look at partitioning the data by care centre, so that there would be a separate "physicians" table for each care centre.
Performing a vertical partition like this would isolate the data related to each care centre separately but does increase the complexity
of the program working with this data. This may also lead to some unnecessary duplication for scenarios like a physician who works in
different care centres.

We could consider denormalizing the data by flattening the supertype/subtype hierachy into a single table. This is more commonly known
as Single Table Inheritance. A 'type' column can be used as a discriminator to decide what type of subclass and portions of the table to load.

**Suppose the date treatment performed was not entered. What procedures are required to handle the missing data? (3 marks)**

* Add logic in the application layer to validate the input data.
* Add a NOT NULL check constraint on the column.

**Consider the following query against the RVH database.**

For each treatment performed in the past two weeks,
list the physicians performing the treatment (grouped by treatment),
and the number of times this physician performed that particular treatment,
on that particular day.
Order the list by treatment ID, and by reverse chronological order for each treatment ID.

```sql
```

Create secondary key indexes to optimize the performance of this query. State any assumptions.

```sql
```

## Project Module 3

Consider the following relations:

```text
Patients(pid, name, address, telephone, care_centre_id)
Care_centres(cid, name, location, nurse_charge_id)
Treatments(tid, patient_id, physician_id, treatment_name, date)
Nurses(nid, name, care_centre_id, certificate_type, telephone, salary)
Physicians(phid, name, pager_number, specialization, salary).
```

Use Oracle to complete the following tasks.

**Create the tables that correspond to these relations in your Oracle home database.**

```sql
\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);
```

**If not automatically created by the DBMS, create indexes corresponding to the primary and foreign keys. (6 marks)**
**Populate these tables with some sample data, and write SQL queries that show the content of each table after entering the data. (6 marks)**
**For some strategic decisions, the president of the hospital needs summary data about the care centres. For each care centre, s/he needs to know the number of nurses holding an RN certificate, as well as their total and average salaries. Does the following view answer the president’s request? If not, write the correct view that will satisfy the president’s request. (7 marks)**

```sql
CREATE VIEW NURSE_SUMMARY (D, C, TOTAL_S, AVERAGE_S)
AS SELECT cid, COUNT (*), SUM (salary), AVG (salary)
FROM Care_centres, Nurses
WHERE nurse_charge_id = nid and certificate_type like ‘RN’
GROUP BY cid;
```

State which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. (5 marks)

Q1
```sql
SELECT * FROM NURSE_SUMMARY;
```
Q2.
```sql
SELECT D, C
FROM NURSE_SUMMARY
WHERE TOTAL_S > 100000;
```
Q3.
```sql
SELECT D, AVERAGE_S
FROM NURSE_SUMMARY
WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);
```
Q4.
```sql
UPDATE  NURSE_SUMMARY
SET D=3
WHERE D=4;
```
Q5.
```sql
DELETE FROM NURSE_SUMMARY
WHERE C > 4;
```

**Use Oracle Forms, or write a program (using PL/SQL and/or Java), to display the following report for a given patient (the user will only enter the patient number). (10 marks)**

```text
Patient Number: _______________

Patient Name: ______________________
Care Centre Name: ___________________
Name of Nurse-in-Charge: ___________________

Treatment ID 	Treatment Name  	Physician ID  	Date
___________ 	___________ 	___________ 	___________
. . . 	. . . 	. . . 	. . .
```