Create a MS Word document that includes your responses to all questions. Also, submit your working MS Access Database for questions 7-8.
1. Identify appropriate attributes for the following entities in the personal healthcare context. Show your attributes for the three entities using the format in Figure 7.4 of UML for DB Design. (Note: You can also create the entities in Visio using the UML Database Notation template and paste into your submission document.)
The purpose of the data model is to capture all of the data needed for a person to measure and track their health over time.
• Patient is an entity that contains attributes that describe the person that is a patient, e.g, contact information. It will include one instance for every person that is using the database to keep track of their health related data.
• HealthMeasure is an entity that provides the list of health measures that are available from the system. It will include one instance for each thing a patient can measure on their own, e.g., weight, heart rate, blood pressure.
• PatientMeasurements is an entity that keeps track of every time a HealthMeasure is recorded for a patient. It will include many instances for each patient, e.g., one instance for each weight measurements recorded during a year, i.e., 52 instances if a person weighed weekly for one year. It will also include many instances for each instance of the HealthMeasure table, e.g., one instance for every time blood pressure was measured for a patient.
2. Define the primary key for each of your entities/classes. To assign the primary key consider the rules for primary keys from the virtual lecture for assignment 1, most importantly that the attribute(s) must have a unique value for every instance of the entity/class. Indicate the primary keys using a PK notation. Define any foreign keys in your entities.
3. Using UML DataBase Notation in Visio, create a diagram showing any relationships among your entities. (Use Figure 7.5 from UML for DB Design, appendix #1 and appendix #4 as examples of UML diagram notation). Paste this diagram into your submission document.
4. Expand the UML diagram from question #3 by creating entities with attributes and keys (in the same format as question 1) that will allow a patient to keep track of any medications they take. Include at least two additional entities that you define/create to provide this capability. Note any assumptions. Extend the diagram you created in question 3. Paste this new diagram into your submission document.
5. Convert the UML diagram (created in question #4) to a relational model. Note any assumptions. (Use appendix #2, #3 and #4 (below) as the basis of the expectation of the correct answer.)
Relational model format is:
RelationName (Key Attribute underlined, Foreign Key italics, Attribute1, Attribute2, etc.)
6. Compare your relations to the tables presented in Appendix #6. Briefly (a paragraph or so) describe any differences you observe in your submission document.
7. Create the tables from appendix #5 in Access. Insert the instances given in appendix #6 into these tables. Submit your Access Database to show completion of this question and question 8.
8. Using the tables from question #7 write simple queries to show the following:
a. List the description, units, normal low range value, normal high range value, and health measure ID for all measures in the health measures table.
b. What is the average heart rate from the Patient_Measurements table?
c. How many different Patient Measurements (count) were made for patient id 1001?
d. Display the patient last name, first name, health measure description, date of the measurement, patient measurement value, and health measure units for any patients where their measure is greater than the high end of the normal range of the health measure. List these items for the patients alphabetically last name, then first name, then by measurement date.
e. Create a SQL statement named Measure_Units_View that consists of Health_Measure_ID, health measure description, and units from the Health_Measures_tbl. Use the Measure_Units_View query to list the measures that have units = "mmhg".
Attachment:- Data_Base_Access.pdf