351/751 Database Systems Lab 05: THE RELATIONAL ALGEBRA
Databases 351/751 Lab 5 in Week 7 2023 1
15 marks in total = 1 .5% of the final grade including attendance, see below
THE RELATIONAL ALGEBRA
- Specify the following queries on the COMPANY relational database schema and its data state shown below in the form of relational algebra expressions (i.e. using relational algebra operations, such as ‘ σ’, ‘π’, ‘ρ’, set operations, ‘X’, ‘ ⋈’, ‘*’, ‘ℑ’ of aggregate functions, etc.). And show the result of each query as it would apply to the following sample database state.
(a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the ' ProductX' project. [2 marks]
(b) For each project, list the project name and the total hours per week (by all employees) spent on that project. [2 marks]
(c) For each department, retrieve the department name, and the average salary of employees working in that department. [2 mark s]
(d) List the last names of department managers who have no dependents. [3 mark s]
FUNCTIONAL DEPENDENCIES AND NORMALIZATION
- Consider the following relations for an order -processing application database at ABC, Inc.
ORDER ( Order_no , Odate, Cust _no, Total_amount) ORDER -ITEM ( Order_no , Item_no , Qty_ordered, Total_price, Discount)
Assume that each item has a different discount. The Total_price refers to one item, Odate is the date on which the order was placed, and the Total_amount is the amount of the order. If we apply a natural join on the relations Order -Item and Order in this d atabase, what does the resulting relation schema look like? What will be its key? Show the FDs in this resulting relation. Is it in 2NF? Is it in 3NF? Why or why not? (State any assumptions you make.) [3 mark s]
THIS LAB HAS AN ATTENDANCE COMPONENT OF [3 MARKS ]
