Add final program to final project report

1 files changed, 57 insertions, 6 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index 2b9450a..769bac7 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -348,7 +348,7 @@ ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFER
 * 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)**
+**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?**
 
 > Horizontal partitioning is similar to creating supertype/subtype relationship because different types of the entity are involved in different relationships.
 
@@ -362,7 +362,7 @@ 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)**
+**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.
@@ -456,13 +456,13 @@ CREATE TABLE Treatments(
 );
 ```
 
-**If not automatically created by the DBMS, create indexes corresponding to the primary and foreign keys. (6 marks)**
+**If not automatically created by the DBMS, create indexes corresponding to the primary and foreign keys.**
 
 ```sql
 ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);
 ```
 
-**Populate these tables with some sample data, and write SQL queries that show the content of each table after entering the data. (6 marks)**
+**Populate these tables with some sample data, and write SQL queries that show the content of each table after entering the data.**
 
 ```sql
 INSERT INTO Care_centres VALUES(1, 'Intensive Care Unit', 'MT-M');
@@ -540,7 +540,7 @@ SELECT * FROM Treatments;
 ```
 
 
-**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)**
+**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.**
 
 ```sql
 CREATE VIEW NURSE_SUMMARY (D, C, TOTAL_S, AVERAGE_S)
@@ -637,7 +637,7 @@ This statement is not allowed. Please use:
 DELETE FROM Nurses WHERE nid IN (SELECT D 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)**
+**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
 Patient Number: _______________
@@ -650,3 +650,54 @@ Treatment ID 	Treatment Name  	Physician ID  	Date
 ___________ 	___________ 	___________ 	___________
 . . . 	. . . 	. . . 	. . .
 ```
+
+
+```ruby
+#!/usr/bin/env ruby
+
+require 'bundler/inline'
+
+gemfile do
+  source 'https://rubygems.org'
+
+  gem 'pg'
+end
+
+connection = PG.connect(
+  dbname: ENV.fetch('PGDBNAME', 'postgres'),
+  host: ENV.fetch('PGHOST', Pathname.pwd.join('tmp/sockets'))
+)
+
+print "Patient Number: "
+sql = <<~SQL
+SELECT p.name AS p_name, c.name as c_name, n.name as n_name
+FROM Patients p
+INNER JOIN Care_centres c ON c.cid = p.care_centre_id
+INNER JOIN Nurses n ON n.nid = c.nurse_charge_id
+WHERE pid = $1;
+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 "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"
+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(' | ')
+  end
+end
+```
+
+```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
+```