From f070e5007a2a7d9ffc5c28fb3455e6608c12ca02 Mon Sep 17 00:00:00 2001 From: mo khan Date: Sun, 8 Mar 2020 15:55:21 -0600 Subject: Work on final project --- assignments/final/README.md | 102 +++++++++++++++++++++----------- assignments/final/module-1.png | Bin 44718 -> 29615 bytes assignments/final/module-2.png | Bin 51044 -> 36223 bytes assignments/final/run.sh | 4 ++ assignments/final/run.sql | 128 +++++++++++++++++++++++++++++++++++++++++ 5 files changed, 200 insertions(+), 34 deletions(-) create mode 100644 assignments/final/run.sh create mode 100644 assignments/final/run.sql diff --git a/assignments/final/README.md b/assignments/final/README.md index 776bd8b..064d7f5 100644 --- a/assignments/final/README.md +++ b/assignments/final/README.md @@ -142,82 +142,116 @@ care that they need. ![relational schema](./module-2.png) -**Besides the 3NF relations, what additional types of information are required to create a physical database design? (3 marks)** +**Besides the 3NF relations, what additional types of information are required to create a physical database design?** + +* Usage patterns +* Regional policies related to location of storage. .e.g EU data regulations. +* 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)** +> Horizontal partitioning is similar to creating supertype/subtype relationship because different types of the entity are involved in different relationships. + +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. +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. + +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)** +* Add logic in the application layer to validate the input data. +* Add a NOT NULL check constraint on the column. + **Consider the following query against the RVH database.** -For each treatment performed in the past two weeks, list the physicians performing the treatment (grouped by treatment), and the number of times this physician performed that particular treatment, on that particular day. Order the list by treatment ID, and by reverse chronological order for each treatment ID. +For each treatment performed in the past two weeks, +list the physicians performing the treatment (grouped by treatment), +and the number of times this physician performed that particular treatment, +on that particular day. +Order the list by treatment ID, and by reverse chronological order for each treatment ID. -Create secondary key indexes to optimize the performance of this query. State any assumptions. (5 marks) +```sql +``` + +Create secondary key indexes to optimize the performance of this query. State any assumptions. ## Project Module 3 Consider the following relations: - Patients(pid, name, address, telephone, care_centre_id) - Care_centres(cid, name, location, nurse_charge_id) - Treatments(tid, patient_id, physician_id, treatment_name, date) - Nurses(nid, name, care_centre_id, certificate_type, telephone, salary) - Physicians(phid, name, pager_number, specialization, salary). +```text +Patients(pid, name, address, telephone, care_centre_id) +Care_centres(cid, name, location, nurse_charge_id) +Treatments(tid, patient_id, physician_id, treatment_name, date) +Nurses(nid, name, care_centre_id, certificate_type, telephone, salary) +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. (6 marks) - 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) +**Create the tables that correspond to these relations in your Oracle home database.** +**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)** +```sql CREATE VIEW NURSE_SUMMARY (D, C, TOTAL_S, AVERAGE_S) - AS SELECT cid, COUNT (*), SUM (salary), AVG (salary) - FROM Care_centres, Nurses - WHERE nurse_charge_id = nid and certificate_type like ‘RN’ - GROUP BY cid; +``` - State which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. (5 marks) +State which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. (5 marks) -Q1. SELECT * - -FROM NURSE_SUMMARY; - -Q2. SELECT D, C +Q1 +```sql +SELECT * FROM NURSE_SUMMARY; +``` +Q2. +```sql +SELECT D, C FROM NURSE_SUMMARY - WHERE TOTAL_S > 100000; +``` -Q3. SELECT D, AVERAGE_S - +Q3. +```sql +SELECT D, AVERAGE_S FROM NURSE_SUMMARY - WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4); +``` -Q4. UPDATE NURSE_SUMMARY - +Q4. +```sql +UPDATE NURSE_SUMMARY SET D=3 - WHERE D=4; +``` -Q5. DELETE FROM NURSE_SUMMARY - +Q5. +```sql +DELETE 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). (10 marks)** +```text Patient Number: _______________ Patient Name: ______________________ - Care Centre Name: ___________________ - Name of Nurse-in-Charge: ___________________ + Treatment ID Treatment Name Physician ID Date ___________ ___________ ___________ ___________ . . . . . . . . . . . . +``` diff --git a/assignments/final/module-1.png b/assignments/final/module-1.png index 53795cd..79eb22e 100644 Binary files a/assignments/final/module-1.png and b/assignments/final/module-1.png differ diff --git a/assignments/final/module-2.png b/assignments/final/module-2.png index 3b54d39..739ff93 100644 Binary files a/assignments/final/module-2.png and b/assignments/final/module-2.png differ diff --git a/assignments/final/run.sh b/assignments/final/run.sh new file mode 100644 index 0000000..f32b6ab --- /dev/null +++ b/assignments/final/run.sh @@ -0,0 +1,4 @@ +#!/bin/sh + +dir="$(dirname $0)" +./bin/console -f "${dir}/run.sql" diff --git a/assignments/final/run.sql b/assignments/final/run.sql new file mode 100644 index 0000000..e6817c6 --- /dev/null +++ b/assignments/final/run.sql @@ -0,0 +1,128 @@ +\echo '--- Question 4 ---' +\set ON_ERROR_STOP on + +DROP TABLE IF EXISTS beds; +DROP TABLE IF EXISTS care_centres; +DROP TABLE IF EXISTS consumptions; +DROP TABLE IF EXISTS employees; +DROP TABLE IF EXISTS items; +DROP TABLE IF EXISTS laboratories; +DROP TABLE IF EXISTS nurses; +DROP TABLE IF EXISTS patients; +DROP TABLE IF EXISTS people; +DROP TABLE IF EXISTS physicians; +DROP TABLE IF EXISTS staff; +DROP TABLE IF EXISTS technicians; +DROP TABLE IF EXISTS timesheets; +DROP TABLE IF EXISTS treatments; +DROP TABLE IF EXISTS visits; + +CREATE TABLE timesheets( + id bigint primary key, + employee_id bigint, + care_centre_id bigint, + hours integer, + week integer +); + +CREATE TABLE consumptions( + id bigint primary key, + physican_id bigint, + patient_id bigint, + item_id bigint, + consumed_at timestamp, + quantity integer, + total_cost decimal +); + +CREATE TABLE employees( + id bigint primary key, + person_id bigint, + "type" varchar(255), + hired_at timestamp +); + +CREATE TABLE items( + id bigint primary key, + number integer, + description text, + unit_cost decimal +); + +CREATE TABLE beds( + id bigint primary key, + care_centre_id bigint, + bed_number integer, + room_number integer +); + +CREATE TABLE laboratories( + id bigint primary key, + location text +); + +CREATE TABLE nurses( + id bigint primary key, + employee_id bigint, + certificate text +); + +CREATE TABLE patients( + id bigint primary key, + person_id bigint, + physician_id bigint, + referring_physician_id bigint, + contacted_at timestamp +); + +CREATE TABLE people( + id bigint primary key, + name varchar(255), + address varchar(255), + birth_date timestamp, + phone_number varchar(255) +); + +CREATE TABLE care_centres( + id bigint primary key, + name varchar(255), + location text, + nurse_in_charge_id bigint +); + +CREATE TABLE physicians( + id bigint primary key, + employee_id bigint, + specialty text, + pager_number varchar(255) +); + +CREATE TABLE staff( + id bigint primary key, + employee_id bigint, + job_class varchar(255) +); + +CREATE TABLE technicians( + id bigint primary key, + employee_id bigint, + skill varchar(255) +); + +CREATE TABLE treatments( + id bigint primary key, + physician_id bigint, + patient_id bigint, + number integer, + name varchar(255), + occurred_at timestamp, + results text +); + +CREATE TABLE visits( + id bigint primary key, + patient_id bigint, + physician_id bigint, + comments text, + scheduled_at timestamp +); -- cgit v1.2.3