Exercise 8

Given the following relation schemas, write the RA for questions 8.1 and 8.2

STUDENT={StudentID, FName, LName, Address, DoB}

ENROL={StudentID, CourseNo, Semester, EnrolDate}

8.1.Which students have only enrolled in Semester 1 and CourseNo MATHS200?

8.2.Which students have only enrolled in ENG1002?

Exercise 9

Consider the following database of a flight booking system

CUSTOMER = {CustomerID, CustomerName, Phone}

PK: {CustomerID}

EMPLOYEE = {EmployeeID, EmployeeName}

PK: {EmployeeID}

FLIGHT = {FlightNo, Date, Origin, Destination}

PK: {FlightNo, Date}

BOOKING = {CustomerID, EmployeeID, FlightNo, Date, Price}

PK: {CustomerID, EmployeeID, FlightNo, Date}

FK: [CustomerID] CUSTOMER[CustomerID], [EmployeeID]

EMPLOYEE[EmployeeID], [FlightNo, Date] FLIGHT[FlightNo, Date]

9.1 Draw the query tree for the RA given below

BOOKING.CustomerID,Origin,Price( BOOKING.FlightNo=FLIGHT.FlightNo

(BOOKING.CustomerID=CUSTOMER.CustomerID (BOOKING FLIGHT CUSTOMER)))

Exercise 10

Optimize the relational algebra given below

BOOKING.CustomerID,Origin,Price( BOOKING.FlightNo=FLIGHT.FlightNo (BOOKING.CustomerID=CUSTOMER.CustomerID

(BOOKING FLIGHT CUSTOMER)))