MIS 150PartIV 6th Edn IG Exam Questions and Courseworks

(2) List all the details of employees who are female. RA: sex = ‘F’(Employee) TRC: {E | Employee(E)  E.sex = ‘F’} DRC: {empID, fName, lName, address, DOB, sex, position, deptNo | Employee(empID, fName, lName, address, DOB, sex, position, deptNo)  sex = ‘F’} (3) List the names and addresses of all employees who are Managers. RA: fName, lName, address(position = ‘Manager’(Employee)) TRC: {E.fName, E.lName, E.address | Employee(E)  E.position = ‘Manager’} DRC: {fName, lName, address | (empID, DOB, sex, position, deptNo) (Employee(empID, fName, lName, address, DOB, sex, position, deptNo)  position = ‘Manager’} (4) Produce a list of the names and addresses of all employees who work for the ‘IT’ department. RA: lName, address(deptName = ‘IT’(Department) 3 deptNo Employee) TRC: {E.lName, E.address | Employee(E)  (D)(Department(D)  (D.deptNo = E.deptNo )  D.deptName = ‘IT’)} DRC: {lName, address | (empID, fName, DOB, sex, position, deptNo, deptNo1, deptName, mgrEmpID) (Employee(empID, fName, lName, address, DOB, sex, position, deptNo)  Department(deptNo1, deptName, mgrEmpID)  (deptNo = deptNo1)  deptName = ‘IT’)} (5) Produce a list of the names of all employees who work on the ‘SCCS’ project. RA: fName, lName (projName = ‘SCCS’(Project) 3 projNo (WorksOn 3 empID Employee)) TRC: {E.fName, E.lName | Employee(E)  (P) (W) (Project(P)  WorksOn(W)  (E.empID = W.empID )  (W.projNo = P.projNo)  P.projName = ‘SCCS’)} DRC: {fName, lName | (empID, address, DOB, sex, position, deptNo, projNo, projName, deptNo1, empID1, projNo1, hoursWorked) (Employee(empID, fName, lName, address, DOB, sex, position, deptNo)  Project(projNo, projName, deptNo1)  WorksOn(empID1, projNo1, hoursWorked)  (empID = empID1)  (projNo = projNo1)  projName = ‘SCCS’)}Database Systems: Instructor’s Guide – IV 5.5 The following tables form part of a database held in a Relational Database Management System: Employee (empNo, eName, salary, position) Aircraft (aircraftNo, aName, aModel, flyingRange) Flight (flightNo, from, to, flightDistance, departTime, arriveTime) Certified (empNo, aircraftNo) where Employee contains details of all employees (pilots and non-pilots) and empNo is the key. AirCraft contains details of aircraft and aircraftNo is the key. Flight contains details of the flights and flightNo is the key. and Certified contains details of the staff who are certified to fly an aircraft, and empNo/aircraftNo form the key. Formulate the following queries in relational algebra, tuple relational calculus, and domain relational calculus (the answers to these queries in SQL are given in the next section). (1) List all Boeing aircraft. RA: aName = ‘Boeing’(Aircraft) TRC: {A | Aircraft(A)  (A.aName = ‘Boeing’)} DRC: {aircraftNo, aName, aModel, flyingRange | Aircraft(aircraftNo, aName, aModel, flyingRange)  (aName = ‘Boeing’)} (2) List all Boeing 737 aircraft. RA: aName = ‘Boeing’  aModel= ‘737’ (Aircraft) TRC: {A | Aircraft(A)  (A.aName = ‘Boeing’)  (A.aModel = ‘737’)} DRC: {aircraftNo, aName, aModel, flyingRange | Aircraft(aircraftNo, aName, aModel, flyingRange)  (aName = ‘Boeing’)  (aModel = ‘737’)} (3) List the employee numbers of pilots certified for Boeing aircraft. RA: empNo (aName = ‘Boeing’(Aircraft) 3 aircraftNo Certified) TRC: {C.empNo |Certified(C)  (A)(Aircraft(A) (A.aircraftNo=C.aircraftNo)  A.aName = ‘Boeing’)} DRC: {empNo | (aNo)(Certified(empNo, aNo)  (aNo1, aName)Aircraft(aNo1, aName, aT, fR)  (aNo = aNo1)  (aName = ‘Boeing’)}Database Systems: Instructor’s Guide – IV