Generate Rails ERD

8 files changed, 245 insertions, 73 deletions

diff --git a/Makefile b/Makefile
index 97c6f67..9bee3fa 100644
--- a/Makefile
+++ b/Makefile
@@ -40,4 +40,6 @@ pdf3 :
 	cd assignments/3 && pandoc --metadata title="COMP-378: Assignment 3 - mo khan (3431709)" --from=commonmark --to=html5 -s -o README.pdf README.md
 
 pdffinal :
+	cd rvh && bundle exec erd --notation=bachman --orientation=vertical --filetype=dot --prepend_primary=true --inheritance --title="RVH ERD" && dot -Tpng erd.dot > erd.png
+	mv rvh/erd.png assignments/final/
 	cd assignments/final && pandoc --metadata title="COMP-378: Final Project - mo khan (3431709)" --from=commonmark --to=html5 -s -o README.pdf README.md
diff --git a/assignments/final/README.md b/assignments/final/README.md
index 4304839..649bf39 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -141,6 +141,7 @@ 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)
 
 **Besides the 3NF relations, what additional types of information are required to create a physical database design?**
 
diff --git a/assignments/final/erd.png b/assignments/final/erd.png
new file mode 100644
index 0000000..5e041f2
--- /dev/null
+++ b/assignments/final/erd.png
diff --git a/assignments/final/module-1.png b/assignments/final/module-1.png
index 40f66fe..de765e9 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 cfbabd6..c0d33fc 100644
--- a/assignments/final/module-1.puml
+++ b/assignments/final/module-1.puml
@@ -17,9 +17,9 @@ entity "CareCentre" as care_centres {
   nurse_in_charge : number <<FK>>
 }
 
-entity "Timesheet" as timesheets {
-  hours : number
-  week : number
+entity "Shift" as shifts {
+  started_at : datetime
+  ended_at : datetime
 }
 
 entity "Consumption" as consumptions {
@@ -30,9 +30,10 @@ entity "Consumption" as consumptions {
   total_cost : decimal
 }
 
-entity "Employee" as employees {
+entity "Staff" as staff {
   employee_id : number << generated >>
   --
+  type : text
   type : number
   hired_at : datetime
 }
@@ -73,10 +74,6 @@ entity "Physician" as physicians {
   pager_number : text
 }
 
-entity "Staff" as staff {
-  job_class : text
-}
-
 entity "Technician" as technicians {
   skill : text
 }
@@ -116,20 +113,16 @@ treatments }|--|| patients
 treatments }|--|| physicians
 visits }|--|| patients
 care_centres ||--|| nurses
-care_centres ||--|{ timesheets
-employees ||--o{ timesheets
+care_centres ||--|{ shifts
+staff ||--o{ shifts
 laboratories ||--|{ technicians
 
-account ||--|| employees
-account ||--|| volunteers
+account ||--|| staff
 account ||--|| patients
 
-employees <|-- nurses
-employees <|-- physicians
-employees <|-- staff
-employees <|-- technicians
-' account <|-- employees
-' account <|-- volunteers
-' account <|-- patients
+staff <|-- nurses
+staff <|-- physicians
+staff <|-- technicians
+staff <|-- volunteers
 
 @enduml
diff --git a/assignments/final/module-2.png b/assignments/final/module-2.png
index f5244ba..c33e622 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 e23213a..e987a31 100644
--- a/assignments/final/module-2.puml
+++ b/assignments/final/module-2.puml
@@ -6,121 +6,136 @@ hide methods
 hide stereotypes
 
 ' entities
+Table(accounts, "accounts") {
+  primary_key(id)
+  name
+  address
+  birth_date
+  phone_number
+  pager_number
+  created_at
+  updated_at
+}
+
 Table(beds, "beds") {
   primary_key(id)
   foreign_key(care_centre_id)
+  foreign_key(patient_id)
   bed_number
   room_number
+  created_at
+  updated_at
 }
 
 Table(care_centres, "care_centres") {
   primary_key(id)
+  foreign_key(employee_id)
   name
   location
-  foreign_key(nurse_in_charge_id)
-}
-
-Table(timesheets, "timesheets") {
-  primary_key(id)
-  foreign_key(employee_id)
-  foreign_key(care_centre_id)
-  hours
-  week
+  created_at
+  updated_at
 }
 
 Table(consumptions, "consumptions") {
   primary_key(id)
-  foreign_key(physician_id)
   foreign_key(patient_id)
+  foreign_key(employee_id)
   foreign_key(item_id)
   consumed_at
   quantity
   total_cost
-}
-
-Table(employees, "employees") {
-  primary_key(id)
-  foreign_key(person_id)
-  type
-  hired_at
+  created_at
+  updated_at
 }
 
 Table(items, "items") {
   primary_key(id)
-  number
+  sku
   description
   unit_cost
+  created_at
+  updated_at
 }
 
 Table(laboratories, "laboratories") {
   primary_key(id)
+  name
   location
-}
-
-Table(nurses, "nurses") {
-  primary_key(id)
-  foreign_key(employee_id)
-  certificate : text
+  created_at
+  updated_at
 }
 
 Table(patients, "patients") {
   primary_key(id)
-  foreign_key(person_id)
+  foreign_key(account_id)
   foreign_key(physician_id)
   foreign_key(referring_physician_id)
   contacted_at : datetime
+  created_at
+  updated_at
 }
 
-Table(people, "people") {
-  primary_key(id)
-  name
-  address
-  birth_date
-  phone_number
-}
-
-Table(physicians, "physicians") {
+Table(shifts, "shifts") {
   primary_key(id)
+  foreign_key(care_centre_id)
   foreign_key(employee_id)
-  specialty
-  pager_number
+  started_at
+  ended_at
+  created_at
+  updated_at
 }
 
 Table(staff, "staff") {
   primary_key(id)
-  foreign_key(employee_id)
-  job_class
-}
-
-Table(technicians, "technicians") {
-  primary_key(id)
-  foreign_key(employee_id)
-  skill
+  foreign_key(account_id)
+  foreign_key(laboratory_id)
+  type
+  hired_at
+  qualifications
+  created_at
+  updated_at
 }
 
 Table(treatments, "treatments") {
   primary_key(id)
   foreign_key(physician_id)
   foreign_key(patient_id)
-  number
   name
-  occurred_at : datetime
+  number
+  occurred_at
   results
+  created_at
+  updated_at
 }
 
 Table(visits, "visits") {
   primary_key(id)
   foreign_key(patient_id)
-  foreign_key(physician_id)
+  scheduled_at
   comments
-  scheduled_at : datetime
-}
-
-Table(volunteers, "volunteers") {
-  primary_key(id)
-  foreign_key(person_id)
-  skill
+  created_at
+  updated_at
 }
 
 beds --> care_centres : "belongs to"
+beds --> patients : "belongs to"
+
+consumptions --> patients : "belongs to"
+consumptions --> items : "belongs to"
+consumptions --> staff : "belongs to"
+
+patients --> accounts : "belongs to"
+patients --> staff : "belongs to"
+
+shifts --> care_centres : "belongs to"
+shifts --> staff : "belongs to"
+
+staff --> accounts : "belongs to"
+staff --> laboratories : "belongs to"
+
+treatments --> staff : "belongs to"
+treatments --> patients : "belongs to"
+
+visits --> patients : "belongs to"
+
 @enduml
diff --git a/rvh/erd.dot b/rvh/erd.dot
new file mode 100644
index 0000000..22fe494
--- /dev/null
+++ b/rvh/erd.dot
@@ -0,0 +1,161 @@
+digraph Rvh {
+rankdir = "LR";
+ranksep = "0.5";
+nodesep = "0.4";
+pad = "0.4,0.4";
+margin = "0,0";
+concentrate = "true";
+labelloc = "t";
+fontsize = "13";
+fontname = "Arial Bold";
+splines = "spline";
+node[ shape  =  "Mrecord" , fontsize  =  "10" , fontname  =  "Arial" , margin  =  "0.07,0.05" , penwidth  =  "1.0"];
+edge[ fontname  =  "Arial" , fontsize  =  "7" , dir  =  "both" , arrowsize  =  "0.9" , penwidth  =  "1.0" , labelangle  =  "32" , labeldistance  =  "1.8"];
+rankdir = "LR";
+label = "RVH ERD\n\n";
+m_Account [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Account</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="address">address <font face="Arial Italic" color="grey60">string ∗</font></td></tr>
+  <tr><td align="left" width="130" port="birth_date">birth_date <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+  <tr><td align="left" width="130" port="name">name <font face="Arial Italic" color="grey60">string ∗</font></td></tr>
+  <tr><td align="left" width="130" port="pager_number">pager_number <font face="Arial Italic" color="grey60">string</font></td></tr>
+  <tr><td align="left" width="130" port="phone_number">phone_number <font face="Arial Italic" color="grey60">string</font></td></tr>
+</table>
+>];
+m_Bed [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Bed</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="bed_number">bed_number <font face="Arial Italic" color="grey60">string</font></td></tr>
+  <tr><td align="left" width="130" port="room_number">room_number <font face="Arial Italic" color="grey60">string</font></td></tr>
+</table>
+>];
+m_CareCentre [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">CareCentre</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="location">location <font face="Arial Italic" color="grey60">string</font></td></tr>
+  <tr><td align="left" width="130" port="name">name <font face="Arial Italic" color="grey60">string</font></td></tr>
+  <tr><td align="left" width="130" port="nurse_id">nurse_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+</table>
+>];
+m_Consumption [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Consumption</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="consumed_at">consumed_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+  <tr><td align="left" width="130" port="physician_id">physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="quantity">quantity <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="total_cost">total_cost <font face="Arial Italic" color="grey60">decimal ∗</font></td></tr>
+</table>
+>];
+m_Item [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Item</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="description">description <font face="Arial Italic" color="grey60">text</font></td></tr>
+  <tr><td align="left" width="130" port="sku">sku <font face="Arial Italic" color="grey60">string ∗</font></td></tr>
+  <tr><td align="left" width="130" port="unit_cost">unit_cost <font face="Arial Italic" color="grey60">decimal ∗</font></td></tr>
+</table>
+>];
+m_Laboratory [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Laboratory</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="location">location <font face="Arial Italic" color="grey60">string</font></td></tr>
+  <tr><td align="left" width="130" port="name">name <font face="Arial Italic" color="grey60">string</font></td></tr>
+</table>
+>];
+m_Nurse [color = "grey60", fontcolor = "grey60", label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Nurse</font></td></tr>
+</table>
+>];
+m_Patient [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Patient</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="contacted_at">contacted_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+  <tr><td align="left" width="130" port="physician_id">physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="referring_physician_id">referring_physician_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+</table>
+>];
+m_Physician [color = "grey60", fontcolor = "grey60", label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Physician</font></td></tr>
+</table>
+>];
+m_Shift [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Shift</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="employee_id">employee_id <font face="Arial Italic" color="grey60">integer ∗</font></td></tr>
+  <tr><td align="left" width="130" port="ended_at">ended_at <font face="Arial Italic" color="grey60">datetime</font></td></tr>
+  <tr><td align="left" width="130" port="started_at">started_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+</table>
+>];
+m_Staff [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Staff</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="hired_at">hired_at <font face="Arial Italic" color="grey60">datetime</font></td></tr>
+  <tr><td align="left" width="130" port="qualifications">qualifications <font face="Arial Italic" color="grey60">text</font></td></tr>
+</table>
+>];
+m_Technician [color = "grey60", fontcolor = "grey60", label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Technician</font></td></tr>
+</table>
+>];
+m_Treatment [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Treatment</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="name">name <font face="Arial Italic" color="grey60">string ∗</font></td></tr>
+  <tr><td align="left" width="130" port="number">number <font face="Arial Italic" color="grey60">string ∗</font></td></tr>
+  <tr><td align="left" width="130" port="occurred_at">occurred_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+  <tr><td align="left" width="130" port="results">results <font face="Arial Italic" color="grey60">text</font></td></tr>
+</table>
+>];
+m_Visit [label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Visit</font></td></tr>
+</table>
+|
+<table border="0" align="left" cellspacing="2" cellpadding="0" width="134">
+  <tr><td align="left" width="130" port="comments">comments <font face="Arial Italic" color="grey60">text</font></td></tr>
+  <tr><td align="left" width="130" port="scheduled_at">scheduled_at <font face="Arial Italic" color="grey60">datetime ∗</font></td></tr>
+</table>
+>];
+m_Volunteer [color = "grey60", fontcolor = "grey60", label = <<table border="0" align="center" cellspacing="0.5" cellpadding="0" width="134">
+  <tr><td align="center" valign="bottom" width="130"><font face="Arial Bold" point-size="11">Volunteer</font></td></tr>
+</table>
+>];
+  m_Staff -> m_Nurse [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
+  m_Staff -> m_Physician [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
+  m_Staff -> m_Technician [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
+  m_Staff -> m_Volunteer [color = "grey60", arrowtail = "onormal", arrowhead = "none", arrowsize = "1.2"];
+  m_Account -> m_Staff [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "5"];
+  m_Laboratory -> m_Staff [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "5"];
+  m_Staff -> m_Treatment [arrowsize = "0.6", arrowhead = "odotnormal", arrowtail = "odot", weight = "5"];
+  m_CareCentre -> m_Shift [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Patient -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Item -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
+  m_Physician -> m_Consumption [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Physician -> m_Treatment [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Patient -> m_Treatment [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
+  m_CareCentre -> m_Bed [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
+  m_Patient -> m_Bed [arrowsize = "0.6", arrowhead = "odot", arrowtail = "odot", weight = "2"];
+  m_Patient -> m_Visit [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Account -> m_Patient [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+  m_Physician -> m_Patient [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "2"];
+  m_Nurse -> m_CareCentre [arrowsize = "0.6", arrowhead = "dotnormal", arrowtail = "odot", weight = "1"];
+}