Relational Algebra (Korth, 2010)

Question 1: Relational algebra (korth, 2010)
a. List the name and specialty of all instructors.
πInstructorName, Specialty(INSTRUCTOR)
b. List the class name and description of all classes suitable for ‘Over 60s’.
πClassName, Description(σ SuitableFor>=60(CLASS))
c. List the names of instructors who run sessions in the classes ‘Aqua Deep’, ‘Aqua Fit’, or
both.
π InstructorName(σ ClassName=‘Aqua Deep’∨ ClassName= ‘Aqua Fit’(CLASS) ✶SESSION
✶INSTRUCTOR)
d. List the names of all members who participated in a class held in Studio 5 on 19/9/2018,
and the name of the class they participated in.
πMemberName ∪ πClassName(σVenueName=’Studio 5’ ∧ DayAndTime= ’19/9/2018’(VENUE) ✶SESSION
✶PARTICIPANT ✶MEMBER)
e. List the names of members who participated in any class with a venue capacity greater
than 30.
π MemberName(σ Capacity>30(VENUE) ✶SESSION✶PARTICIPANT✶MEMBER)
f. List the details of all sessions running on 19/9/2018, and the names of the members who
have signed up for each of them (if any).
σ DayAndTime= ’19/9/2018’(SESSION) ∪ πMemberName(σ DayAndTime= ’19/9/2018’ (SESSION)
✶PARTICIPANT✶MEMBER)
g. List the names of any instructors who run sessions in both Studio 1 and Studio 2.
π MemberName(σVenueName=’Studio 1’ ∧ VenueName=’Studio 2’(SESSION) ✶PARTICIPANT
✶MEMBER)
h. List the names of members who have either participated in classes for the Over 60s,
classes held in the Warm Water Pool, or both.
π MemberName(σ SuitableFor>60(CLASS) ✶SESSION✶PARTICIPANT✶MEMBER) ∪ π
MemberName(σVenueName=’Warm Water Pool’ (SESSION)✶PARTICIPANT✶MEMBER)
i. List the names of the members who have NOT participated in a ‘Zumba’ class.
π MemberName(σ ClassName!=‘Zumba’(SESSION)✶PARTICIPANT✶MEMBER)
j. List the names of members who have participated in all of the different classes offered.
π MemberName (SESSION)✶PARTICIPANT✶MEMBER
Question 2: SQL Select queries (korth, 2010)

a. SELECT f.FamilyName, f.Suburb from dtoohey.PATIENT f where f.State='SA';
b. SELECT f.FamilyName, f.Suburb from dtoohey.PATIENT f where f.State='SA'or
f.State='NSW' ORDER BY f.FamilyName;

c. SELECT p.GivenName, p.Suburb from dtoohey.PATIENT p, dtoohey.DOCTOR d,
dtoohey.ACCOUNT a where f.State='WA' and (d.name='Dr Brian' or d.name='Dr
Barbara') and p.PatientID=a.PatientID and d.ProviderName=a.ProvierName;
d. SELECT p.GivenName, p.Suburb from dtoohey.PATIENT p, dtoohey.DOCTOR d,
dtoohey.ACCOUNT a where d.name='Dr Brian' and d.name!='Dr Ima' and
p.PatientID=a.PatientID and d.ProviderNo=a.ProviderNo;

e. select d.name, p.suburb from dtoohey.patient p, dtoohey.doctor d where d.providerNo
IN(select a.providerNo from dtoohey.account a, dtoohey.doctor d where
d.providerNo=a.providerNo);