Data and Databases Coursework

DataAndDatabasesCoursework.sql

/** 
  * Bournemouth University Level 4 Data and Databases coursework 2021
  * An SQL database for a care home for tracking residents, their care, and the carers.
  * Designed for Oracle SQL
  * 
  * Copyright (c) 2021 JoeBlakeB (Joe Baker), All Rights Reserved.
**/

-- CREATE SCRIPTS

CREATE TABLE carer (
    carer_id    NUMBER(4,0) PRIMARY KEY,
    forename    VARCHAR(32) NOT NULL, 
    surname     VARCHAR(32) NOT NULL
);

CREATE TABLE training (
    training_id   NUMBER(4,0) PRIMARY KEY,
    training_desc VARCHAR(64) NOT NULL
);

CREATE TABLE carer_training_link (
    carer_id      NUMBER(4,0),
    training_id   NUMBER(4,0),
    training_date DATE,
    CONSTRAINT fk_ctl_carer_id   FOREIGN KEY (carer_id)   REFERENCES carer(carer_id),
    CONSTRAINT fk_ctl_traning_id FOREIGN KEY (training_id) REFERENCES training(training_id)
);

CREATE TABLE resident (
    resident_id NUMBER(4,0) PRIMARY KEY,
    forename    VARCHAR(32) NOT NULL, 
    surname     VARCHAR(32) NOT NULL,
    room_number NUMBER(4,0),
    dob         DATE
);

CREATE TABLE care_need (
    care_need_id  NUMBER(4,0) PRIMARY KEY,
    care_desc     VARCHAR(64) NOT NULL
);

CREATE TABLE care (
    resident_id   NUMBER(4,0),
    carer_id      NUMBER(4,0),
    care_need_id  NUMBER(4,0),
    date_provided DATE,
    CONSTRAINT fk_c_resident_id  FOREIGN KEY (resident_id)  REFERENCES resident(resident_id),
    CONSTRAINT fk_c_carer_id     FOREIGN KEY (carer_id)     REFERENCES carer(carer_id),
    CONSTRAINT fk_c_care_need_id FOREIGN KEY (care_need_id) REFERENCES care_need(care_need_id)
);

COMMIT;

-- INSERT SCRIPTS

-- Insert carer information
INSERT INTO carer VALUES(1,  'TJ',      'Hess');
INSERT INTO carer VALUES(2,  'Jenna',   'Begay');
INSERT INTO carer VALUES(3,  'Chase',   'Hunter');
INSERT INTO carer VALUES(4,  'Flynn',   'Moore');
INSERT INTO carer VALUES(5,  'Carl',    'Hendricks');

-- Insert training information and link them to carers
INSERT INTO training VALUES(1, 'how to properly give patients medication');
Insert INTO carer_training_link VALUES (1, 1, '07-APR-2021');
Insert INTO carer_training_link VALUES (2, 1, '09-JUN-2021');
INSERT INTO training VALUES(2, 'food hygiene and food safety training');
Insert INTO carer_training_link VALUES (1, 2, '12-MAY-2021');
Insert INTO carer_training_link VALUES (2, 2, '22-APR-2021');
Insert INTO carer_training_link VALUES (3, 2, '30-AUG-2021');
INSERT INTO training VALUES(3, 'health and safety awareness training');
Insert INTO carer_training_link VALUES (1, 3, '22-JUN-2021');
Insert INTO carer_training_link VALUES (2, 3, '22-JUN-2021');
Insert INTO carer_training_link VALUES (3, 3, '04-AUG-2021');
Insert INTO carer_training_link VALUES (4, 3, '04-AUG-2021');
Insert INTO carer_training_link VALUES (5, 3, '05-AUG-2021');
INSERT INTO training VALUES(4, 'first aid training');
Insert INTO carer_training_link VALUES (3, 4, '10-AUG-2021');
Insert INTO carer_training_link VALUES (4, 4, '11-AUG-2021');
Insert INTO carer_training_link VALUES (5, 4, '17-AUG-2021');

-- Insert resident information
INSERT INTO resident VALUES(1,  'Shaine',   'Thomas',       43, '28-OCT-1933');
INSERT INTO resident VALUES(2,  'Oren',     'Buck',         39, '14-SEP-1935');
INSERT INTO resident VALUES(3,  'Gary',     'Adkins',       12, '28-DEC-1949');
INSERT INTO resident VALUES(4,  'Hannah',   'Case',         80, '07-NOV-1936');
INSERT INTO resident VALUES(5,  'Tamekah',  'Leonard',      16, '09-MAR-1935');
INSERT INTO resident VALUES(6,  'Big',      'Chungus',      62, '27-JUL-1940');

-- Insert care need information
INSERT INTO care_need VALUES(1, 'dental hygiene assistance');
INSERT INTO care_need VALUES(2, 'assistance with dressing');
INSERT INTO care_need VALUES(3, 'assistance with washing');
INSERT INTO care_need VALUES(4, 'assistance with using the bathroom');
INSERT INTO care_need VALUES(5, 'assistance with taking medication');

-- Insert care given information
INSERT INTO care VALUES(1, 1, 5, TO_DATE('2021-06-21 16:20', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(3, 1, 5, TO_DATE('2021-06-21 16:30', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(2, 3, 2, TO_DATE('2021-06-25 10:55', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(3, 2, 1, TO_DATE('2021-07-03 12:00', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(4, 1, 3, TO_DATE('2021-07-17 15:35', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(5, 4, 1, TO_DATE('2021-08-12 11:45', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(3, 3, 2, TO_DATE('2021-08-27 13:20', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(2, 5, 4, TO_DATE('2021-09-03 20:15', 'YYYY-MM-DD HH24:MI'));
INSERT INTO care VALUES(3, 1, 5, TO_DATE('2021-09-26 12:30', 'YYYY-MM-DD HH24:MI'));

COMMIT;

-- SELECT SCRIPTS

-- A list of all the residents with their name, room number and date of birth.
/**
 * Query 1: A list of all the residents in the system with their name, their room 
 * number and their date of birth. IDs are not required. 
 * 1 point for select statement + 1 point for the projection.  
 * Total around 2. 
**/
SELECT  forename, surname, room_number, dob
FROM    resident;

-- A list of all the care a specific carer completed.
/**
 * Query 2: A list of all the care a specific carer completed. It is required that the 
 * names of the carer and the care need description are shown with the date it was 
 * provided. IDs do not need to be shown, and the residents it was provided to is not 
 * needed. 
 * 1 point for the correct number of tables (3) + 1 point for correct restriction 
 * (one carer) + 1 point for projection (no IDs) + 4 points for joins (2 joins).  
 * Total around 7. 
**/
SELECT  carer.forename AS carer_forename,
        carer.surname AS carer_surname,
        care_need.care_desc,
        care.date_provided
FROM    carer
JOIN    care      USING (carer_id) 
JOIN    care_need USING (care_need_id)
WHERE   carer.forename = 'TJ' AND carer.surname = 'Hess';

-- A list of all carers and the number of the training sessions they attended in a specific month.
/**
 * Query 3: A list of all carers and the number (counted) of the training 
 * sessions (entries in CARE TRAINING LINK) they attended in a specific month.
 * Show the name of the carers, but not their ID. 
 * 1 point for the correct number of tables (3)
 * 1 point for projection (no IDs) + 
 * 2 points for joins (1 join) +
 * 3 points for correct use of aggregation + 
 * 4 points for correct restriction (date restriction).  
 * Total around 11. 
**/
SELECT  forename, surname,
        COUNT(training_date) AS training_attended
FROM    carer
JOIN    carer_training_link USING (carer_id)
WHERE   TRUNC(training_date, 'mm') = '01-AUG-2021'
GROUP BY carer.forename, carer.surname;

-- The resident who received the most care, and which care needs they received.
/**
 * Query 4: The resident who received the most care, and which care needs they 
 * received. 
 * 1 point for the correct number of base tables (3 - there may be nested 
 * queries) + 4 points for projection + 6 points for joins (3 joins - plus 1 
 * potential nested query) + 3 points for correct use of aggregation + 5 points 
 * for restriction. 
 * Total around 19. 
**/
SELECT  forename,
        surname,
        care_desc
FROM    (
            SELECT  COUNT(date_provided) AS care_count,
                    resident_id
            FROM    care
            GROUP BY resident_id
            ORDER BY care_count DESC
            FETCH FIRST 1 ROWS ONLY
        )
JOIN    care      USING (resident_id)
JOIN    resident  USING (resident_id)
JOIN    care_need USING (care_need_id);
 
-- DROP SCRIPTS

DROP TABLE carer_training_link PURGE;
DROP TABLE training PURGE;
DROP TABLE care PURGE;
DROP TABLE carer PURGE;
DROP TABLE care_need PURGE;
DROP TABLE resident PURGE;

COMMIT;