Exercise

Points

$1$

$10$

$2$

$90(6*15)$

Total

$100$

$$

$$

Exercise $1$

$1.$ What is the purpose of relational algebra? Why is it useful?

$2.$ What is$/$are the link$/$s between relational algebra and SQL$?$

$$

Exercise $2$

Here is the relational database schema we consider.

$$

Participants$($SiteID$($INT$),$ LastName$($CHAR$),$ FirstName$($CHAR$),$ PatientID$($INT$),$ Status$($CHAR$),$ Gender$($CHAR$),$ Age$($INT$),$ Language$($CHAR$),$ Disease $($CHAR$),$ Ethnicity$($CHAR$))$

$$

AlertPIN$($PatientID$($INT$),$ EndUserID$($INT$),$ AlertName$($CHAR$))$

$$

Events$($EndUserID$($INT$),$ StartDate$($DATE$),$ EndDate$($DATE$),$ EventName$($CHAR$))$

$$

Messages$($EndUserID$($INT$),$ ResponseDate$($DATE$),$ Question$($VARCHAR$),$ Response$($VARCHAR$))$

$$

Readings$($EndUserID$($INT$),$ ReadingID$($INT$),$ ReadingType$($CHAR$),$ ReadingDate$($DATE$))$

$$

Note:

$$ Primary keys are underlined.

$$ Datatypes are provided for your guidance.

$$ Status$$is either Active or Not Active. Patients are either actively involved in the program or not at all involved.

$$ Language is English, French, Hindi, Spanish, Mandarin

$$ Ethnicity is Asian, Black, Native American, White

$$ EndUserID $($different than the PatientID$)$ is generated for a participant in case of an alert event

$$ ResponseDate is the date recorded when a patient is questioned by a doctor$/$ nurse

$$ ReadingID generated for every EndUserID for different ReadingType like blood pressure, pulse rate, weight, etc.

$$

Write the following queries in relational algebra, not SQL:

$$

$1.$ PatientIDs who$$s readings were recorded for blood pressure and oxygen level.

$$

$2.$ Names of the black participants who suffer from coronary disease or Alzheimer$$s$.$

$$

$3.$ Ids of the patients who have at least $3$ readings recorded on $21$st$$February $2021(\text{'}02-21-2021\text{'}).$

$$

$4.$ Status of the participants for whom an alert was generated and who$$s readings were recorded for weight and sugar.

$$

$5.$ PatientIDs of the participants who are active but have no readings recorded.

$$

$6.$ SiteID with at least $500$ non$-$active participants. CS$623$ HW $4$

Exercise $1$

$1.$ What is the purpose of relational algebra? Why is it useful?

$2.$ What is$/$are the link$/$s between relational algebra and SQL$?$

Exercise $2$

Here is the relational database schema we consider.

Participants$($SiteID $($INT$),$ LastName $($CHAR$),$ FirstName $($CHAR$),$ PatientID $($INT$),$ Status $($CHAR$),$ Gender $($CHAR$),$ Age $($INT$),$ Language $($CHAR$),$ Disease $($CHAR$),$ Ethnicity $($CHAR$))$

AlertPIN$($PatientID $($INT$),$ EndUserID $($INT$),$ AlertName $($CHAR$))$

$\backslash (\backslash$because $\backslash$quad $\backslash )$ Events$($EndUserID $($INT$),$ StartDate $($DATE$),$ EndDate $($DATE$),$ EventName $($CHAR$))$

Messages$($EndUserID $($INT$),$ ResponseDate $($DATE$),$ Question $($VARCHAR$),$ Response $($VARCHAR$))$

Readings$($EndUserID $($INT$),$ ReadingID $($INT$),$ ReadingType $($CHAR$),$ ReadingDate $($DATE$))$

Note:

$-$ Primarykeys are underlined.

$-$ Datatypes are provided for your guidance.

$-$ Status is either Active or Not Active. Patients are either actively involved in the program or not at all involved.

$-$ Language is English, French, Hindi, Spanish, Mandarin

$-$ Ethnicity is Asian, Black, Native American, White

$-$ EndUserID $($different than the PatientID$)$ is generated for a participant in case of an alert event

$-$ ResponseDate is the date recorded when a patient is questioned by a doctor$/$ nurse

$-$ ReadingID generated for every EndUserID for different ReadingType like blood pressure, pulse rate, weight, etc.

Write the following queries in relational algebra, not SQL:

$1.$ PatientIDs who's readings were recorded for blood pressure and oxygen level.

$2.$ Names of the black participants who suffer from coronary disease or Alzheimer's.

$3.$ Ids of the patients who have at least $3$ readings recorded on $\backslash (21^\{34\}\backslash )$ February $2021(02-21-2021).$

$4.$ Status of the participants for whom an alert was generated and who's readings were recorded for weight and sugar.

CS$623$ HW $4$

$5.$ PatientIDs of the participants who are active but have no readings recorded.

$6.$ SiteID with at least $500$ non$-$active participants.