SQL Design and Queries

In this phase you are required to convert the model you created for a Hospital System to a working relational model using the Oracle system.

Instructions to access Oracle (the pdf posted in Canvas System)

Important Note

Make sure to store the statements you write, e.g., CREATE TABLE statements, or SELECT queries in a file because you may need to re-use them multiple times.

Part 1 : Create the tables

Given the ER diagram and the initial relational model that you created in Phase 1, you are required to do the following:

• Build the tables in Oracle that reflect the given requirements in Phase 1. When converting from the ERD to the relational tables, you must follow the rules given in class to create a good design.

• DO NOT use the create database command; instead use the create table command and make sure that your resulting work includes:

• All tables with descriptive names and fields.
• For the fields, choose the appropriate data types o Add the appropriate constraints, which must include (you may add more):

▪ Primary Keys and Unique constraints

▪ Foreign Key constraints

▪ Not Null constraints

▪ Domain constraints

Note: It is up to each team, to either keep using your own ER design from Phase 1, or you may use the design given in the posted solution of Phase 1. Both are accepted as long as your design is correct and captures all the requirements. Therefore, the solution to the queries (Part 2 below) may differ based on your design.

Part 2 : SQL Queries

Write SQL queries against the tables you created above to answer the following queries:

Q1: Report the id, specialty, gender and school of graduation for doctors that have graduated from WPI (WPI).

Q2: For a given division manager (say, ID = 10), report all regular employees that are supervised by this manager. Display the employees ID, names, and salary.

Q3: For each patient, report the sum of amounts paid by the insurance company for that patient, i.e., report the patients SSN, and the sum of insurance payments over all visits. Note: If you keep the insurance coverage as a percentage, then compute this percentage before getting the sum.

Q4: Report the number of visits done for each patient, i.e., for each patient, report the patient SSN, first and last names, and the count of visits done by this patient.

Q5: Report the room number that has an equipment unit with serial number A01-02X.

Q6: Report the employee who has access to the largest number of rooms. We need the employee ID, and the number of rooms they can access.

Note: If there are several employees with the same maximum number, then report all of these employees.

Q7: Report the number of regular employees, division managers, and general managers in the hospital. The output should look like:

Type Count

Regular employees 10

Division managers 5

General managers 2

Q8: For patients who have a scheduled future visit (which is part of their most recent visit), report that patients SSN, first name, and last name, and the visit date. Do not report patients who do not have a scheduled visit.

Q9: Report all equipment types that have less than two technicians that can maintain them.

Q10: Report the date of the coming future visit for patient with SSN = 111-22-3333. Note: This date should exist in the last (most recent) visit of that patient.

Q11: For patient with SSN = 111-22-3333, report the doctors (only ID) who have examined this patient more than 2 times.

Q12: Report the equipment types (only the ID) for which the hospital has purchased equipment (units) in both 2010 and 2011. Do not report duplication.

Part 3 : Populate the Tables Insert records in the tables such that:

1. All the constraints that you defined in Part 1 will be obeyed 2. Each of the above queries must return some records in their results 3. Insert information for at least:

1. 10 Patients
2. 10 Rooms, at least 3 of these rooms have 2 or more services
3. 3 Equipment types
4. 3 Equipment units of each type
5. At least 5 patients have 2 or more admissions (visits)
6. 15 regular employees, 4 division managers, and 2 general managers
7. Within the regular employees ensure that 5 are doctors and 5 are equipment technicians

Test your queries against the data that you will insert and make sure they return the expected results.