Data Modeling for the TRU Sleep Clinic (2 %)

Use the following information/knowledge to prepare data requirements for the TRU Sleep Clinic.

TRU sleep clinic runs a special study on blood pressure (BP) and sleep disorders (specifically obstructive sleep apnea). The following data has to be kept for each patient: patient ID, gender, DOB, date/time of assessment, weight, height, BMI (calculated from weight and height), blood pressure: systolic and diastolic BP, antihypertensive medication (yes/no). The data for the weight, height, BP, and AH medication are recorded once a month. The weight and height should be stored using the International System of Units (SI standard/ metric).

1)Create a Logical Data Model (use Relational Model) for the above study: list the relations with attributes, PKs, and FKs. For each attribute provide a short description (meaning), domain, standard for units/representations. Patient ID is a unique integer number assigned by the Clinic Coordinator. Gender should allow for undeclared values (represented by U) and unknown (missing values) as NULL.

List the relations with indicated PKs (underlined) and FKs (in italics).

2)Using your Logical Data Model, create a Physical Data Model. Prepare a DDL script to create the table(s) with the appropriate constraints (entity, referential integrity, and domain). The BMI column should be defined as a virtual column (value generated automatically based on other columns). Calculate the Body Mass Index (BMI) of the patients at the time of the study. Use only as many significant digits as is justified. BMI = weight(kg)/height(m)²

Print the DDL script.

3)Using your table(s) created in 2 perform the following tasks:

a)Write SQL DML statements to add 3 patients. For each patient add data for 3-4 clinical assessments.

b)Write an SQL query to list the patients and their age at the time of each assessment. List the patient ID, age, and assessment date.

c)Write an SQL query to list all patients BMI characteristics: min, max, median, mean, and standard deviation.

Print the SQL statements for a, SQL queries and results for b and c.