Apply instructor feedback

5 files changed, 38 insertions, 34 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index d73221a..2a348de 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -98,6 +98,9 @@ It's possible that implementation inheritance may need to be modeled in this cas
 * 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.
 
+The supertype would allow grouping the common attributes of the subtypes to reduce duplication.
+The subtypes are important because the ensure that logic related to the subtype can be isolated to the subtype.
+
 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](https://en.wikipedia.org/wiki/Liskov_substitution_principle) is
@@ -155,6 +158,7 @@ care that they need.
 
   ![relational schema](./module-2.png)
 
+<!--
 ```sql
 CREATE TABLE accounts (
   id bigint NOT NULL,
@@ -352,6 +356,7 @@ ALTER TABLE treatments ADD CONSTRAINT patients_fk FOREIGN KEY (patient_id) REFER
 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?**
 
@@ -359,6 +364,10 @@ ALTER TABLE volunteers ADD CONSTRAINT accounts_fk FOREIGN KEY (account_id) REFER
 * Regional policies related to location of storage. .e.g EU data regulations.
 * Data retention policies
 * Data segregation policies
+* Backup
+* Security
+* Throughput
+* Maintenance
 
 **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?**
 
diff --git a/assignments/final/module-1.png b/assignments/final/module-1.png
index 44d11ec..9ae000e 100644
--- a/assignments/final/module-1.png
+++ b/assignments/final/module-1.png
diff --git a/assignments/final/module-1.puml b/assignments/final/module-1.puml
index 5b2819b..d2e4718 100644
--- a/assignments/final/module-1.puml
+++ b/assignments/final/module-1.puml
@@ -35,6 +35,13 @@ entity "Consumption" as consumptions {
   total_cost : decimal
 }
 
+entity "HoursWorked" as hours_worked {
+  id : number << generated >>
+  --
+  started_at : datetime
+  ended_at : datetime
+}
+
 entity "Item" as items {
   id : number << generated >>
   --
@@ -53,29 +60,31 @@ entity "Nurse" as nurses {
 }
 
 entity "Patient" as patients {
+  id : number << generated >>
+  --
+  type : varchar
   contacted_at : datetime
 }
 
 entity "Physician" as physicians {
 }
 
+entity "OutPatient" as out_patients {
+}
+
 entity "Qualification" as qualifications {
   id : number << generated >>
   --
-  staff_id : number <<FK>>
-  name : varchar(255)
+  name : varchar
 }
 
-entity "Shift" as shifts {
-  started_at : datetime
-  ended_at : datetime
+entity "Resident" as residents {
 }
 
 entity "Staff" as staff {
   id : number << generated >>
   --
-  type : text
-  type : number
+  type : varchar
   hired_at : datetime
 }
 
@@ -109,20 +118,22 @@ entity "Volunteer" as volunteers {
 
 beds ||--o| patients
 beds }|--|| care_centres
-care_centres ||--|{ shifts
+care_centres ||--|{ hours_worked
 care_centres ||--|| nurses
 consumptions }o--|| items
 consumptions }o--|| patients
 laboratories ||--|{ staff
 patients }o--|| physicians
 qualifications ||--|{ staff
-staff ||--o{ shifts
+staff ||--o{ hours_worked
 treatments }o--|| patients
 treatments }o--|| physicians
 visits }|--|| patients
 
 account ||--|| patients
 account ||--|| staff
+patients <|-- out_patients
+patients <|-- residents
 staff <|-- nurses
 staff <|-- physicians
 staff <|-- technicians
diff --git a/assignments/final/module-2.png b/assignments/final/module-2.png
index d806832..e3f7fa7 100644
--- a/assignments/final/module-2.png
+++ b/assignments/final/module-2.png
diff --git a/assignments/final/module-2.puml b/assignments/final/module-2.puml
index da483e7..98385c5 100644
--- a/assignments/final/module-2.puml
+++ b/assignments/final/module-2.puml
@@ -13,8 +13,6 @@ Table(accounts, "accounts") {
   birth_date
   phone_number
   pager_number
-  created_at
-  updated_at
 }
 
 Table(beds, "beds") {
@@ -23,8 +21,6 @@ Table(beds, "beds") {
   room_number
   foreign_key(care_centre_id)
   foreign_key(patient_id)
-  created_at
-  updated_at
 }
 
 Table(care_centres, "care_centres") {
@@ -32,8 +28,6 @@ Table(care_centres, "care_centres") {
   foreign_key(nurse_id)
   name
   location
-  created_at
-  updated_at
 }
 
 Table(consumptions, "consumptions") {
@@ -43,8 +37,6 @@ Table(consumptions, "consumptions") {
   consumed_at
   quantity
   total_cost
-  created_at
-  updated_at
 }
 
 Table(items, "items") {
@@ -52,16 +44,12 @@ Table(items, "items") {
   sku
   description
   unit_cost
-  created_at
-  updated_at
 }
 
 Table(laboratories, "laboratories") {
   primary_key(id)
   name
   location
-  created_at
-  updated_at
 }
 
 Table(patients, "patients") {
@@ -70,8 +58,6 @@ Table(patients, "patients") {
   foreign_key(physician_id)
   foreign_key(referring_physician_id)
   contacted_at : datetime
-  created_at
-  updated_at
 }
 
 Table(shifts, "shifts") {
@@ -80,8 +66,6 @@ Table(shifts, "shifts") {
   foreign_key(employee_id)
   started_at
   ended_at
-  created_at
-  updated_at
 }
 
 Table(staff, "staff") {
@@ -90,20 +74,22 @@ Table(staff, "staff") {
   foreign_key(laboratory_id)
   type
   hired_at
-  qualifications
-  created_at
-  updated_at
 }
 
 Table(treatments, "treatments") {
   primary_key(id)
   foreign_key(patient_id)
+  foreign_key(physician_id)
   name
   number
   occurred_at
   results
-  created_at
-  updated_at
+}
+
+Table(qualifications, "qualifications") {
+  primary_key(id)
+  foreign_key(staff_id)
+  name
 }
 
 Table(visits, "visits") {
@@ -111,16 +97,12 @@ Table(visits, "visits") {
   foreign_key(patient_id)
   scheduled_at
   comments
-  created_at
-  updated_at
 }
 
 Table(volunteers, "volunteers") {
   primary_key(id)
   foreign_key(account_id)
   skill
-  created_at
-  updated_at
 }
 
 beds --> care_centres : "belongs to"
@@ -134,6 +116,8 @@ patients --> accounts : "belongs to"
 patients --> staff : "belongs to"
 volunteers --> accounts : "belongs to"
 
+qualifications --> staff : "belongs to"
+
 shifts --> care_centres : "belongs to"
 shifts --> staff : "belongs to"