Assignment
Questions 1 - 4 refer to a proposedLong-Term Care Hospital Comparedatabase model in response to a request to design a database to store hospital evaluation data. You will use the database modeland an Excel workbook to complete the tasks described in each question. The database modelincludes a description of the data, a relational model and a set of business rules.The accompanying Excel workbook contains the data to populate the proposed database.
Examples of the types of information contained in the proposed database:
1. The Rate of complications for hip/knee replacement patients at SOUTHEAST ALABAMA MEDICAL CENTER is the same as the national rate.
2. The estimate of the average rate of complications for hip/knee replacement patients at SOUTHEAST ALABAMA MEDICAL CENTER is 3.8 and the 95% confidence interval of the average rate of complications is (2.6, 5.5).
3. 331 cases of hip/knee replacement procedures were evaluated at SOUTHEAST ALABAMA MEDICAL CENTER during the period 4/1/2012 - 3/31/2015.
Description of the Long-Term Care Hospital Comparedata and data model:
The model represents a proposed database in response to a request to create a Hospital database to storeevaluation data. The hospitals are evaluated on one or more medical procedures called measures. The database userwill be able to obtain the evaluation results of a hospital and compare it to the national average.
Some of the business rules are
• A hospital is uniquely identified by a provider id.
• There are different types of hospitals.
• There are different types of hospital ownership.
• A hospital is evaluated on one or more measures. A hospital and measure combination is unique.
• The severity of the complications resulting from procedures is evaluated on a scale of 1 - 10.
Table 1: Tables, primary keys and foreign keys shown in the relational diagram
|
Description of table
|
Table Name
|
Primary key
|
Foreign keys
|
|
Hospital information
|
HOSP_LIST
|
PRVDR_ID
|
HOSP_TYPE_CD, HOSP_OWNRSHP_CD
|
|
Hospital type description
|
HOSP_TYPE_LIST
|
HOSP_TYPE_CD
|
|
|
Ownership type description
|
HOSP_OWNRSHP_LIST
|
HOSP_OWNRSHP_CD
|
|
|
Measures (Procedures) description
|
HOSP_MEASURE_LIST
|
MEASURE_ID
|
|
|
National Averages
|
HOSP_NATNL_CMPR
|
PRVDR_ID, MEASURE_ID
|
PRVDR_ID, MEASURE_ID
|
Figure 1:Long-Term Care Hospital Compare database model
1. Use any technique to write or generate the Oracle DDL commands necessary to create thetables, primary keys and referential integrity constraintsfor the tables and attributes listed in Table 1 and Figure 1.
It is not required to name non-key attributes the same the name shown on the relational model.
The instructor will execute the SQL commands to verify that they execute correctly.
To test your tables, primary keys and referential integrity constraints, write the Oracle DML commands to test the primary key constraints and the referential integrity constraints. Test the primary key constraint for each table and then test the referential integrity constraint for each table that contains a foreign key. The instructor will execute the SQL commands to verify that they reasonably test the constraints of the database. Use the INSERT statement. Use the DROP statement to as necessary to expedite testing.
• Submit a copy of the Oracle DDL as a separate file with the nameDDL_Midterm.SQLto the assignment folder for the midterm.
• Show evidence that you successfully created the tables - show screenshots.
• Submit a copy of the Oracle SQLtest commandswith INSERT and DROP commands as a separate file with the name Test_Constraints_Midterm.SQLto the assignment folder for the midterm.
• For each test, describe the test, the test data and the expected result of the test.
• Show evidence that you successfully tested the constraints of your database- show screenshots.
2. Populate the tables you created in question 1 with the data set provided.
• Describe the process you followed to populate the database. Support your description with screenshots or SQL for the process you describe.
• Show evidence that you successfully populated the tables in Oracle - in the space below show screenshots of the result set of a SELECTandCOUNT statements.
3.
3.a Write the following queries:
Determine the number of hospitals by type of hospital in each state. Include the state name and the hospital type in the result set.
• Show evidence that you successfully executed the query - show screenshots below of the result of the queries.
3.b Add an index to your database thatwould benefit the query in part 3.a
--- Do not create an index on a primary key
• Explain why the index is beneficial.
• Show evidence that you successfully created the index - show screenshots below that show the index in Oracle SQL Developer.
• Submit one file that contains the SQL commands for the queries a
Place Question 3 answers and documentation here.
4. Write a query whose result set contains the names of the hospitals where Deaths among Patients with Serious Treatable Complications after Surgery is "No Different than the Nation Rate" and Mortality national comparisonis "Above the national average".
• Show evidence that you wrote and successfully executed the query.
https://www.dropbox.com/s/tld7b6g4gtqrgz9/HQI-HOS-Comp.rar?dl=0