MASY1-GC3500: Spring 2022: Final Exam Due: 4/27/2022 at 8:55 pm Here are the requirements for the Final Exam (20 points) Guidelines: Use proper naming conventions (eg: all objects should be MB_FE_* where MB is your initials) All screenshots must clearly show your connection name in SQL Developer (eg: MASY_IB48) Do not use SQL reserved words or have invalid names (quotes, spaces etc) Make sure all data are committed and all constraints are enabled Grading will be very strict so double-check all work before submitting Must be submitted before 8:55 pm – LATE NOT ACCEPTED!!! A) Create a stage table based on my stage table (1 pt.) [select * from masy_ib48. masy3500_final_exam_view] Deliverable: SQL/DDL for stage table B) Create a normalized database to support this data (4 pt.) Deliverable: Screenshot of Data Model and DDL (hint: 5 tables) Must use proper naming conventions and appropriate data types C) Moving data from your stage to your normalized tables (5 pts) Write SQL INSERTs to migrate data from your stage to your normalized tables. Use SEQUENCE to generate primary keys Must include MINUS to avoid duplicate inserts. DO NOT USE correlated sub queries to look up foreign keys. Use joins instead with your stage query. HINT (This is the correct structure for your inserts) insert into tableToInsert (cols…) select cols… from (select cols… from yourStage minus select cols… from tableToInsert ) Deliverable: Screenshot for each table insert as follow, i. Showing the INSERT statement with the query result ii. SQL to show the data after insert (select * from the table) D) Create 5 SQL views using your normalized tables (5 pts) SQL Joins Query all normalized tables to replicate the stage data Top-N query First Name and Last name of top 3 Doctor’s in terms number of treatments they provided. Single Row Sub Query Find ICD_CODE for which maximum number of treatments provided. Multi Row Sub Query
List doctor’s first name and last name for
those doctors who have provided treatment in last three months.
Group By: Find doctor id and patient id for those patients who have received treatment by same doctor more than once . Deliverable: SQL for each view and screenshot for each result E) Create appropriate index(s) that helps performance of the following query (5 pts) SELECT patient_id, dob, gender FROM patient WHERE gender=’M’ and to_char(dob,’YYYY’)=’2010’; Steps: i) Run this query (replace table name with one that you have created), and show execution plan ii) Create appropriate indexes, and show index creation screenshots iii) Run this query again and show the execution plan iv) Explain briefly in few sentences about your observation of change in execution plan Deliverable: Screenshots of steps first 3 steps, plus the explanation Deliverable as listed above Everything must be attached as one document (word, pdf or ppt) This one document must be very clear of what you are showing (Organize properly)
