Question 1: Relational Algebra

Question 1: Relational algebra:
a)
π InstructorName, Specialty (INSTRUCTOR)
b)
π ClassName, Description (σ (SuitableFor=’Over 60s’) CLASS )
c)
π InstructorName (σ (classname=’Aqua Deep’ or classname=’Aqua Fit’ ) SESSION )
d) ClassName, VenueName
π MemberName (σ (DayAndTime=’19/9/2018’ and VenueName=’Studio 5’ ) ) PARTICIPANT⋈MEMBER ⋈
SESSION
e)
π MemberName (σ (Capacity>30) ) PARTICIPANT⋈MEMBER ⋈ SESSION⋈ VENUE
f)
π MemberName (σ (DayAndTime=’19/9/2018’) ) PARTICIPANT⋈MEMBER ⋈ SESSION
g)
π InstructorName (σ (VenueName=’Studio 1’ or VenueName=’Studio 2’ ) SESSION )
h)
π MemberName (σ (VenueName=’Warm Water Pool’ or SuitableFor=’Over 60s’ ) )
PARTICIPANT⋈MEMBER ⋈ SESSION ⋈ CLASS
i)
π MemberName (σ (ClassName<>’Zumba’ )) PARTICIPANT⋈MEMBER ⋈ SESSION
j)
π MemberName PARTICIPANT⋈MEMBER ⋈ SESSION

Question 3:
a) SQL for creating Instructor Table
CREATE TABLE INSTRUCTOR
( InstructorName varchar2(50) not null,
InstructorEmail varchar2(50) not null,
Biography varchar2(200),
Specialty varchar2(50),
CONSTRAINT INSTRUCTOR_pk PRIMARY KEY (InstructorName)
);
b) SQL for Creating TIMETABLE table:
CREATE TABLE TIMETABLE
( SessionID numeric(10) not null,
DayAndTime Date not null,
NumberOfPlaces numeric(10) not null,
ClassName varchar2(50) not null,
VenueName varchar2(200),
InstructorName varchar2(50),
CONSTRAINT TIMETABLE_pk PRIMARY KEY (SessionID),
CONSTRAINT fk_INSTRUCTOR FOREIGN KEY (InstructorName)
REFERENCES INSTRUCTOR(InstructorName)
);

c) Insert Query on instructor Table:
INSERT INTO INSTRUCTOR VALUES ('john peterson','[e-mail]','I am student
and working on Database','Database');
d) Adding constraint on TIMETABLE:
ALTER TABLE TIMETABLE
MODIFY (VenueName varchar2(200) NOT NULL CONSTRAINT VENUE_CONSTRAINT
CHECK (VenueName IN ('Main Group Fitness Studio', 'Indoor Cycle Studio', 'Mind and
Body Studio', '25m Lap Pool', 'Warm Water Pool')));