Polish the final project

3 files changed, 79 insertions, 26 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index 769bac7..ab83a40 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -99,8 +99,9 @@ It's possible that implementation inheritance may need to be modeled in this cas
 * 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.
+I prefer to favour composition over inheritance. If the 
+[Liskov substitution principle](https://en.wikipedia.org/wiki/Liskov_substitution_principle) is
+satisfied for any relationship, then I think utilizing implementation inheritance is acceptable.
 
 **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?**
 
@@ -117,10 +118,22 @@ 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)
+  <!--![EER](./module-1.png)-->
+  ![ERD](./erd.png)
+
+I have chosen to model the different types of staff as subclasses of a `Staff` class.
+Each of the different types of Staff can have different skills modelled as a text field
+in the `qualifications` attribute on the `Staff` relation.
+
+Every entity has a primary key called `id`.
+Foreign keys are modeled using a convention of `<relation name>_id`.
 
 **Are there any universal data models that can be reused as a starting point for modeling RVH’s data requirements?**
 
+The [Oracle Healthcare Data Model](https://docs.oracle.com/cd/E18811_01/doc/doc.112/e18026/logical_physical_hdm.htm#CACBAAAH)
+is a large model that covers many different aspects of health care. For the purpose of this
+project this data model includes more functionality than what is necessary for this project.
+
 ## Project Module 2
 
 Use the relational schema of the EER diagram you developed in Module 1 to answer the following questions.
@@ -135,13 +148,13 @@ At the moment there is no identified need to de-normalize the database.
 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
+organized, which makes it easier for scheduling personnel 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)
+  <!--![relational schema](./module-2.png)-->
+  <!--![ERD](./erd.png)-->
 
 ```sql
 CREATE TABLE accounts (
@@ -354,18 +367,18 @@ ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFER
 
 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.
+We could also look at partitioning the data by care centre, so that there would be a separate "staff" 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.
+of the program working with this data. This may also lead to some unnecessary duplication for scenarios like staff who work in
+multiple 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.
+I chose to denormalize the data by flattening the supertype/subtype hierachy into a single table (staff). This is more commonly known
+as Single Table Inheritance. A 'type' column is 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?**
 
 * Add logic in the application layer to validate the input data.
-* Add a NOT NULL check constraint on the column.
+* Add a NOT NULL check constraint on the column in the database.
 
 **Consider the following query against the RVH database.**
 
@@ -381,7 +394,7 @@ 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')
+WHERE occurred_at > current_date - interval '14 days'
 GROUP BY "treatments"."id", DATE(treatments.occurred_at), staff.id
 ORDER BY "treatments"."id" DESC LIMIT $2  [["type", "Physician"],
 ```
@@ -619,24 +632,58 @@ SET D=3
 WHERE D=4;
 ```
 
-This statement is not allowed. Please use:
+The above statement is not allowed. An attempt to update
+the nid for a specific Nurse could be attempted with the following:
 
 ```sql
 UPDATE Nurses SET nid = 3 WHERE nid = 4;
 ```
 
+This will cause an error due to another Nurse already having the `nid` of 3.
+It would yield an error similar to:
+
+```sql
+ERROR:  23505: duplicate key value violates unique constraint "nurses_pkey"
+DETAIL:  Key (nid)=(3) already exists.
+SCHEMA NAME:  public
+TABLE NAME:  nurses
+CONSTRAINT NAME:  nurses_pkey
+LOCATION:  _bt_check_unique, nbtinsert.c:427
+```
+
 Q5.
 ```sql
 DELETE FROM NURSE_SUMMARY
 WHERE C > 4;
 ```
 
-This statement is not allowed. Please use:
+The above statement is not allowed. Instead the following can be used.
 
 ```sql
 DELETE FROM Nurses WHERE nid IN (SELECT D FROM NURSE_SUMMARY WHERE C > 4);
+DELETE 0
+
+SELECT * FROM Nurses;
+
+ nid |     name     | care_centre_id | certificate_type |  telephone   | salary
+-----+--------------+----------------+------------------+--------------+--------
+   1 | Sharp        |              1 | birth            | 111-222-3333 | 100000
+   2 | Barton       |              1 | RN               | 111-222-3333 | 100000
+   3 | Nightingale  |              1 | birth            | 111-222-3333 | 100000
+   4 | Seacole      |              1 | RN               | 111-222-3333 |  50000
+   5 | Dix          |              1 | birth            | 111-222-3333 | 100000
+   6 | Mahoney      |              1 | birth            | 111-222-3333 |  50000
+   7 | Wald         |              1 | birth            | 111-222-3333 | 100000
+   8 | Sanger       |              1 | RN               | 111-222-3333 | 100000
+   9 | Breckinridge |              1 | birth            | 111-222-3333 |  50000
+  10 | Teresa       |              1 | birth            | 111-222-3333 |      0
+  11 | Bertschinger |              1 | RN               | 111-222-3333 |     50
+(11 rows)
 ```
 
+This statement has no affect because in the current data set there are zero
+`NURSE_SUMMARY` results with a count of greater than 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).**
 
 ```text
@@ -651,6 +698,10 @@ ___________ 	___________ 	___________ 	___________
 . . . 	. . . 	. . . 	. . .
 ```
 
+The following [Ruby](https://www.ruby-lang.org/en/) program
+will request a patient id when run and then
+query the necessary information from a [PostgreSQL](https://www.postgresql.org/)
+database to print a report to the terminal.
 
 ```ruby
 #!/usr/bin/env ruby
@@ -663,12 +714,9 @@ gemfile do
   gem 'pg'
 end
 
-connection = PG.connect(
-  dbname: ENV.fetch('PGDBNAME', 'postgres'),
-  host: ENV.fetch('PGHOST', Pathname.pwd.join('tmp/sockets'))
-)
+connection = PG.connect(dbname: ENV.fetch('PGDBNAME', 'postgres'), host: ENV.fetch('PGHOST', Pathname.pwd.join('tmp/sockets')))
 
-print "Patient Number: "
+print "\nPatient Number: "
 sql = <<~SQL
 SELECT p.name AS p_name, c.name as c_name, n.name as n_name
 FROM Patients p
@@ -679,12 +727,12 @@ SQL
 patient_id = gets.to_i
 connection.exec_params(sql, [patient_id]) do |result|
   result.each do |row|
-    puts "Patient Name: #{row['p_name']}"
+    puts "\nPatient Name: #{row['p_name']}"
     puts "Care Centre Name: #{row['c_name']}"
     puts "Name of Nurse-in-Charge: #{row['n_name']}"
   end
 end
-puts "Treatment ID | Treatment Name | Physician ID | Date"
+puts "\nTreatment ID | Treatment Name | Physician ID | Date"
 connection.exec_params("SELECT * FROM Treatments WHERE patient_id = $1;", [patient_id]) do |treatments|
   treatments.each do |x|
     puts [x['tid'].ljust(12), x['treatment_name'].ljust(14), x['physician_id'].ljust(12), x['date']].join(' | ')
@@ -692,12 +740,17 @@ connection.exec_params("SELECT * FROM Treatments WHERE patient_id = $1;", [patie
 end
 ```
 
+The program can be run like this:
+
 ```bash
 モ ruby program.rb
+
 Patient Number: 1
+
 Patient Name: moe sislack
 Care Centre Name: Intensive Care Unit
 Name of Nurse-in-Charge: Sharp
+
 Treatment ID | Treatment Name | Physician ID | Date
 1            | Surgery        | 1            | 2020-03-01 00:00:00
 ```
diff --git a/assignments/final/erd.png b/assignments/final/erd.png
index 7ff9c14..13a72a0 100644
--- a/assignments/final/erd.png
+++ b/assignments/final/erd.png
diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index 131a6b9..e6d6a92 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -311,8 +311,8 @@ GROUP BY c.cid;
 
 SELECT * FROM NURSE_SUMMARY;
 
-SELECT D, C FROM NURSE_SUMMARY WHERE TOTAL_S > 100000;
+-- SELECT D, C FROM NURSE_SUMMARY WHERE TOTAL_S > 100000;
 
-SELECT D, AVERAGE_S
-FROM NURSE_SUMMARY
-WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);
+-- SELECT D, AVERAGE_S
+-- FROM NURSE_SUMMARY
+-- WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);