D ata Modeling for the TRU Sleep Clinic (2 %) Use the following information/know

Question

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)2

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.

Explanation / Answer

As per the given details, th Logical Data Model includes two main tables to store the assessment details.

Patient

Assessment

Sample Queries to create tables and insert data.

CREATE TABLE Patient (patient_ID INT, patient_Name VARCHAR (75) NOT NULL, gender VARCHAR (2), DOB DATE NOT NULL, PRIMARY KEY (patient_ID));

CREATE TABLE Assessment (AutoID INT AUTO_INCREMENT, patient_ID INT, Assessmentdatetime DATETIME, Weight DOUBLE NOT NULL,Height DOUBLE NOT NULL,BMI DOUBLE AS (Weight/( Height* Height)),BP VARCHAR(15), antihypertensive VARCHAR(2), PRIMARY KEY (AutoID), FOREIGN KEY(patient_ID) REFERENCES Patient(patient_ID));

INSERT INTO Patient VALUES(1001,'John Marc','M','09/08/1980');

INSERT INTO Assessment(patient_ID, Assessmentdatetime, Weight, Height,BP, antihypertensive) VALUES(1001,'10-08-2017 11:15:45',72,1.7,'systolic','Y');

SQL query to list the patients and their age at the time of each assessment

select p.patient_Name, DATEDIFF(p.DOB,a.Assessmentdatetime) / 365.25 as age from Assessment a LEFT OUTER JOIN Patient p ON a.patient_ID =p.patient_ID

Column Name Data Type Description PK patient_ID INT unique integer number assigned by coordinator. patient_Name VARCHAR(75) Patient Name - NOT NULL gender VARCHAR(2) Gender of patient which can take values:F/M/U/NULL DOB DATE Patient's Date of birth - NOT NULL

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.