Question

Understanding Databases: Exercises

Type your answers into this Word document. Save when completed and submit as a file upload.

You are a manager of an HIM department and you want to track how many charts each of your employees scanned and indexed. This is the data you found.

On Monday, John did 10 charts, Sue did 25 charts, and Maria did 20 charts.

On Tuesday, John did 12 charts, Sue did 22 charts, and Maria did 20 charts.

On Wednesday, John did 11 charts, Sue did 24 charts, and Maria did 19 charts.

On Thursday, John did 12 charts, Sue did 26 charts, and Maria did 21 charts.

On Friday, John did 10 charts, Sue did 25 charts, and Maria did 20 charts.

1. Arrange the above data in a flat file database, in column delimited data form. A table has been started for you. (10 points)

2. Arrange the above data in a flat file database, in comma-separated value (CSV) form. (10 points)

3. Of the following four choices, what would most likely be the Primary Key in the following tables? (3 points)

              Choices: Account ID, Patient ID, Provider ID, Diagnosis Code

1. Patient Table:
2. Visit Table:
3. Physician Table:

4. Give an example when the same Key could be a Primary Key in one table and a Foreign Key in another table. (2 points)

5.            Give the type of relationship cardinality for each scenario: (5 points)

              Choices: One to one, One to Many, or Many to Many

1. A clinic has bill types for Outpatient, Inpatient, and Laboratory. A patient could have multiple bills on multiple dates for multiple visits:

2. Mary Johnson's Medical Record Number is 3458236:

3. Mary Johnson has diagnoses of high blood pressure, hypercholesterolemia, and migraines:

4. ICD10 Code O24.011 is Preexisting Diabetes Mellitus, Type 1, In pregnancy, first trimester:

5. Mary has received three different bills over the past year from the clinic, and has made three payment on one of the bills, two payments on the second bill, and one payment on the third bill:

Answer 1

#. Primary key in the following tables are :-

No. OF CHARTS DONE Name Mos The 1 2 lo 1a. Ttn Sue 95 a4 26 25 /9 21

Patient Table - patient ID

Visit table - account ID

Physician table - provider ID

Provider ID will be primary key in the physician table and it will be foreign key in patient table .

#. A) is one to one because patient will get separate bills for everyday visit and the type of services he got .

B) One to one ,because every patient has individual ID and record number

C) One to many ,because Mary Johnson has multiple diseases like hypertension, hypercholesterolemia,and migraine

D) One to one , because every disease and it's sub parts has its own individual ICD-10 code .

E) Many to many, because she had paid multiple times for the overall three bills