Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Health Data Management and Analytics Midterm Exam

Problem 1 – ER Diagram and Concepts of Relational Database (25 pts)

1.1 Complete the ER diagram. (20)

Medical devices get registered by the device company so that if recovered from loss, medical devices can be returned to owners. Add entity sets and relationships to the ER diagram started below for the medical device safety programs, which are offered by the same company unit that does registration. Business rule: the owner is also the user of the medical device.

You need to add three different events to the database. Leave the current relationship between owner and medical device as is shown in the box below.

First is the registration itself. A registration has one medical device. A medical device can be re-registered when sold to a new owner.

Next is the medical device inspection program. When registered, each device’s  operational functionality, battery life, software updates, and overall safety are inspected. Owners are encouraged to have medical devices inspected at least once a year. There is no need to record the inspector.

Last is the medical device safety class. Safety classes are held on weekends. Medical device owners are encouraged to attend the class more than once. There is no need to record the instructor of a class.

Include cardinalities (such as 1 and m) in the ER diagram as shown in the diagram below.

1.2 Write down the table schema for all tables associated with the ER diagram. (5)

Problem 2 – Database Design (10 points)

2.1 What is the diagram below called?  (2)

A._____________________________________________________________________

2.2 Describe at least 2 characteristics of a database are specific to the third normal form. (4)

2.3 When implementing a data warehouse, what data is crucial to keep that we learned in class? (4)

Instructions for Problems 3 and 5–SQL Queries

This page has instructions. The next two pages have the actual problems 3 to 5.

See Exhibit 1—Complaint Database of the Supplemental Materials.

Employ one of the user views or single tables (for tables that have no views) as defined in the supplemental materials for input to each query. Do not use joins when you can use views.

Write SQL commands as in the following sample syntax.  To save time, do not write user view or table names as part of data element names unless the same data element name appears in two or more tables. So instead of qryPerformanceView.Date write Date but write tblTheater.Name instead of Name if Name appears in more than one table.

Example:

SELECT

  tblTheater.Name,

  Date,

  Title

FROM

  qryPerformanceView

WHERE

  Company = ‘ABC’

ORDER BY

  Date;

Problem 3 – SQL (20 points)

Provide a list of all complaints in 2010 against the provider with provider name, “Sam Kelly”. Include Date; concatenated last name, first name of patient (for example, Miller, Mary); and patient phone. Include aliases where needed.

Problem 4 – SQL (20 points)

Provide the frequency (count) of the complaint type, “Excessive testing or treatment,” by provider name in 2010. Include aliases where needed.

Problem 5– SQL (25 points)

For this problem, assume tblComplaint has 5 rows of data as follows:

5.1 Alter the table to add a column named Status, which has a varchar (225) data type, and can be null. (5) Then create a view qryComplaintStatus using ComplaintID and Status from tblComplaint and LastName from tblComplainant by inner joining two tables. (10)

5.2 Continuing with the above table that you altered and entered new values. Now update the table so that row(s) where HealthCareProviderID is 42742 and the date of complaint is after September 2010 is updated to a Status of “Under Investigation”. (10)