CS3431 - Phase 2 - SQL Design and Queries  - Solved

Building a SQL Application 

 

        

 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.  (See “Deliverables” section below). 

Part 1 (25 Points): 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: 

 

o   All tables with descriptive names and fields. 

o   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 (60 Points—5 points each): 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 patient’s 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 (15 Points): 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: 

a.    10 Patients 

b.    10 Rooms, at least 3 of these rooms have 2 or more services 

c.    3 Equipment types 

d.    3 Equipment units of each type 

e.    At least 5 patients have 2 or more admissions (visits) 

f.     15 regular employees, 4 division managers, and 2 general managers 

i. 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. 


 

If the file’s syntax was not correct (and the file did not run), you will lose 20 points in addition to any other deductions.  

 

Above each SQL query, also write the description of the query in text enclosed between /* */. In this case, SQL will treat this text as a comment and will execute normally. For example: 

 

       /* This query is to select the emp name given its id */        Select name From emp Where Id = 10; 

   

Important Notes: 

-       The .sql file you create is just a regular text file. It does not have any special format. Put the file in the right directory so SQL can reach it. You may write a full path in the command, e.g.,  “@<full-path>”  

 

-       Check this link about adding comments within the SQL commands (be careful by putting space after “/*” and before “*/”, otherwise some commands may execute 

multiple times, and give you strange errors. That is “/*    …     */“ 

https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#SQ LRF51086 

 

2)    Any comments or assumptions that you have, you can write them to a separate .pdf file. 

 

3)    Put the two files in a single zip file that will be submitted as described below.