Create tables

2 files changed, 66 insertions, 4 deletions

diff --git a/assignments/final/README.md b/assignments/final/README.md
index dbbbc75..4304839 100644
--- a/assignments/final/README.md
+++ b/assignments/final/README.md
@@ -199,6 +199,60 @@ 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)**
@@ -217,28 +271,24 @@ 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
diff --git a/assignments/final/run.sql b/assignments/final/run.sql
index ef0e132..e7c6459 100644
--- a/assignments/final/run.sql
+++ b/assignments/final/run.sql
@@ -226,3 +226,15 @@ CREATE TABLE Treatments(
 );
 
 ALTER TABLE Nurses ADD CONSTRAINT care_centres_fk FOREIGN KEY (care_centre_id) REFERENCES Care_centres (cid);
+
+INSERT INTO Care_centres VALUES(1, 'A', 'Earth');
+INSERT INTO Nurses VALUES (1, 'Sharp', 1, 'birth', '111-222-3333', 100000);
+
+INSERT INTO Physicians VALUES (1, 'dr. mo', '555-555-5555', 'love', 10000000);
+INSERT INTO Physicians VALUES (2, 'dr. allison', '555-555-0001', 'head aches', 10000000);
+INSERT INTO Physicians VALUES (3, 'dr. didi', '555-555-0002', 'foot pain', 10000000);
+INSERT INTO Physicians VALUES (4, 'dr. didi', '555-555-0003', 'back pain', 10000000);
+
+INSERT INTO Patients VALUES (1, 'moe sislack', '123 street nw', '777-777-7777', 1);
+INSERT INTO Patients VALUES (2, 'mose allison', '234 street nw', '888-888-8888', 1);
+INSERT INTO Patients VALUES (3, 'didi kong', '345 street nw', '999-999-9999', 1);