Determine the number of hospitals by type of hospital

Assignment Help Database Management System

Reference no: EM131683551

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

Reference no: EM131683551

Questions Cloud

What is the firms corporate cost of capital : What is each alternatives IRR - If the cost of capital for both methods is 9 percent, which method should be chosen and What are worst-case and best case IRRs

Steps-take to research current storage technologies : Alternative to Tape Storage Your company still uses tape storage to back up important files, but your manager has asked you to begin researching alternatives.

Define comprise sediments that are very small in size : Based on the characteristics of the sediments, such as size, mass, and solubility, the sediments are classified into dissolved

Member of the american arbitration association : Maria, a client of yours in Buffalo, New York, calls you up one day and says, "As you know, I am a contractor who specializes in repair of problems.

Determine the number of hospitals by type of hospital : Determine the number of hospitals by type of hospital in each state. Include the state name and the hospital type in the result set.

What steps can you take to retrieve the files : Files Not Synchronizing You have saved files on the cloud from your home computer, but the files are not appearing on the computer in your office.

Marsha challenges this regulation on the grounds : Marsha challenges this regulation on the grounds that it discriminates against women, because most women are not able to lift as much weight as men.

Drag the appropriate labels to their respective targets : Materials associated with each type of mass movement, Drag the appropriate labels to their respective targets

Define-technology in the automotive industry technology : Technology in the Automotive Industry Technology is used in the automotive industry to increase speed and efficiency.

User Account

All Pages