Description
Description: 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) 2 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). 3 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. 4 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. 5 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. Grading: The maximum grade is 100 Points. No late submissions. Deliverables: Each team should deliver two files as follows: 1) Text file (.sql) that contains all SQL commands from Part1, Part3, and Part2 above (Put the insert statements before the queries). The file must be executable from SQL using command: SQL > @ The file must run correctly, which means: a) Dropping all tables first using DROP TABLE commands. b) Creating all tables in the right order of foreign keys-Primary keys. c) Inserting data in all tables in the right order of foreign keys-Primary keys. d) Finally, executing queries. 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: 6 /* 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., “@” – 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. Submission (Each team gives one submission): ● Submit electronically by the due date via canvas.wpi.edu website. Make sure your two files (the text file .sql, and your report .pdf) are zipped, and you upload one file. ● Each team submits one copy (from either of the team members). ● No hard copy submissions ● The team group id and members’ names must be written inside the report file.