Question

University ITS maintains a database about the requests for software that staff members make for their units before each teaching period (e.g. Semester 1, Semester 2). A unit may need several items of software, installed in multiple labs. Labs are housed across the various buildings of the university, with each lab having a unique room number (e.g. Lab 245.3.062 is located in building number 245, name Science & Computing). Each piece of software is requested individually for a lab and is given a request ID so that its progress can be tracked, from initial request, install, testing (both functionality and user acceptance) to final deployment in the lab. Basic information about staff members, buildings, labs, and the software is also kept.

The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).

BUILDING (BuildingNo, BuildingName)

LAB (RoomNo, Capacity, BuildingNo)

STAFF (StaffNo, StaffName, Email, Phone)

SOFTWARE( SoftwareName, Version, MediaLocation)

REQUEST (RequestID, StaffNo, SoftwareID, RoomNo, RequestDate, TeachingPeriod, Progress)

Provide relational algebra queries to find the following information NOTE:  You can use the symbols , , *, etc or the words ‘RESTRICT’, PROJECT’, etc as you prefer.  Use nested brackets or intermediate relations, as you prefer.  You do not need to try to make efficient queries – just correct ones.  Where you use a join, you should always show the join condition.  Use the information provided in the question, not a shortcut – for example, if the question refers to the ‘Law’ building, do not use the Law building number ‘465’ in your criteria.

a. List the name and email of all staff.

b. List the room number and capacity of all labs located in the Science & Computing building with a capacity of over 30.

c. List the names and versions of all the software requested for the labs in Science & Computing in Semester 2.

d. List all the labs (room number) where Microsoft Visio is NOT requested for Semester 2.

e. List the software name, version, room number, building name and staff name of all software requests logged for semester 2.

f. List of emails of all staff who have NOT submitted any software requests for Semester 2.

g. List the names of software requested for labs in the Law building, requested by staff member S2019876, or both.

h. List all the requests (RequestID, StaffNo, RoomNo) for Oracle SQL Developer that have progressed to ‘user acceptance testing’ or ‘deployed’.

i. List the labs whose progress status is listed as ‘deployed’ for ALL software requests to that lab.

j. List all the labs in the Law building, and the names of software requested for each of them (if any).

Answer 1