BOOK: \" Database systems : a practical to design, implementation, and managemen
ID: 3591024 • Letter: B
Question
BOOK: "Database systems: a practical to design, implementation, and management", 6th EDITION.
Case Study 2
for Exercises 6.32 - 6.40, use the Projects schema defined in the Exercises at the end of Chapter 5.
QUESTIONS:
6.32 List all employees from BRICS countries in alphabetical order of surname.
6.33 List all details of employees born between1980-90
6.34 List all managers who are females in alphabetical order of surname, and then first name.
6.35 Remove all projects that are managed by the planning department.
6.36 Assume the planning department is going to be merged with the IT department. Update employee records that reflect the proposed change.
6.37 Using the UNION command, list all projects that are managed by the IT and the HR department.
6.38 Produce a report of the total hours worked by each female employee, arranged by department number and alphabetically by employee surname within each department.
6.39 Remove all projects from the database which had no employees worked.
6.40 List the total number of employees in each department for those departments with more than 10 employees. Create an appropriate heading for the columns of the results table.
THIS IS THE SCHEMA (PLEASE FOCUS ON JUST THE SCHEMA) THE QUESTIONS ABOVE ONLY SHOULD BE ANSWERED 6.32-6.40
B2.guestNo= B1.guestNo B2.dateFrom*B 1.dateFrom} Provide the equivalent domain relational calculus and relational algebra expressions for each of caculus expressions given in Exercise 5.10 2 Generate the relational algebra tuple relational calculus, and idomain relational calculus expressi ing queries (a) List all hotels (b) List all single rooms with a price below f(20 per night. (c) List the names and cities of all guests (d) List the price and type of all rooms atthe Grosvenor Hotel (e) List all guests currently staying at the Grosvenor Hotel. (0 List the details of all rooms at the Grosvenor Hotel. including the name of the guest staying i room is occupied. g) List the guest details (guestNo, guestName, and guestAddress) of all guests staying at the Gro .13 Using relational algebra, produce a report of all employees from the IT and planning departments after 1990. The following tables form part of a database held in an RDBMS: Employee (empNo. fName, IName, address, DOB, sex, position, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (proiNo, projName, deptid WorksOn (empNo. proiNo, dateWotked, hoursWorked) where Employee contai ns employee details and empNo is the key Department contains department detail's and deptNo is the key mgrEmpNo ide employee who is the manager of the department There is only or each de contains details of the projects in each department and the key is p two departments can run the same project) contains details of the hours worked by employees on each project projNo/dateWorked form the key Project and worksOn : Formulate the following queries in relational algebra tuple relational calculus and domain relationsl 5.14 List all employees 5 15 List all the details of employees who are female and bom after 1990 5:16 List all employees who are not managers and are paid more than $150oExplanation / Answer
6.32 List all employees from BRICS countries in alphabetical order of surname.
select * from Employee where counry IN ('China','Russia','India','Brazil','South Africa') order by surname;
6.33 List all details of employees born between1980-90
select * from Employee where year(DOB) BETWEEN 1980 AND 1990;
6.34 List all managers who are females in alphabetical order of surname, and then first name.
select e.lName,e.fName
from Employee e, Department d
where e.empNo = d.mgrEmpNo
and LOWER(e.sex) = 'female'
order by e.lName ,e.fName;
6.35 Remove all projects that are managed by the planning department.
DELETE P
FROM Projects as P INNER JOIN Department as D
ON P. deptNo = D.deptNo
where D.deptName like '%planning%';
6.36 Assume the planning department is going to be merged with the IT department. Update employee records that reflect the proposed change.
UPDATE E
SET E.deptNo = D.deptNo
FROM Employee as E INNER JOIN department as D
ON D.deptNo = E .deptNo
WHERE E.deptName like 'planning' and D.deptName line '%IT%';
6.37 Using the UNION command, list all projects that are managed by the IT and the HR department.
SELECT * FROM Projects
UNION
SELECT * FROM Department
where deptName IN ('IT' , 'HR');
6.38 Produce a report of the total hours worked by each female employee, arranged by department number and alphabetically by employee surname within each department.
select E.lName, E.fName, E.empNo , W.hoursWorked
From Employee E , WorksOn W
where E.empNo = W.empNo
group By E.deptNo
Order By E.lName;
6.39 Remove all projects from the database which had no employees worked.
DELETE Projects
FROM Projects as P INNER JOIN Employee as E
ON P.deptNo = E.deptNo
where (select count(*) from Employee where Employee.deptNo = P.deptNo) = 0;
6.40 List the total number of employees in each department for those departments with more than 10 employees. Create an appropriate heading for the columns of the results table.
select count(*) as TotalEmployee , D.deptNo
From Department D , Employee e
WHERE D.deptNo = E.deptNo
and (select count(*) from Employee where Employee.deptNo = P.deptNo) > 10
group by D.deptNo;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.