COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems
20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12]
Question 12 (6 marks)
Consider the following simplified schema for a database representing employees and departments in a
medium-sized business:
create table Employees (
eno integer,
ename text,
payRate float,
dno integer,
…
— employee ID
— employee’s name
— hourly rate of pay
— department where they work
primary key (eno),
foreign key (dno) references Departments(dno)
);
create table TimeSheet ( — keep data for only one week
eno integer, — employee ID
day varchar(3) check (day in (‘mon’,’tue’,’wed’,’thu’,’fri’,’sat’ started time, — starting time of work period
ended time, — ending time of work period
primary key (eno,day,start),
foreign key (eno) references Employees(eno)
);
create table Department (
);
dno integer,
dname text,
eno integer,
…
— department ID
— department name
— department manager
primary key (dno),
foreign key (eno) references Employees(eno)
The above SQL schema could be represented as the following abstract schema (with some renaming):
,’sun’)),
Empl(eno,ename,payRate,dno…)
Time(eno,day,started,ended)
Dept(dno,dname,eno,…)
Using the abstract schema and the standard relational agebra operators (selection (Sel), projection (Proj), join (Join), union (Union), intersection (Intersect), difference (-), division (/)) along with the boolean operators from SQL where clauses, write relational algebra expressions to solve the queries below. You can express your relational algebra as a sequence of assignments to relational algebra variables representing intermediate results.
A sample of the syntax for relational algebra follows:
Note that you do not need to use an explicit Rename operator (although you can if you want to). You can accomplish renaming by writing the new names of all attributes on the left-hand side of the equals. If you do not use renaming, we assume that the attributes have the same names as the attributes in the tables on the right- hand side of the equals.
Assume that tables are R(x,z), S(y,w)
Tmp1(x,z) = Sel[x > 5](R)
Tmp2(x,z,y,w) = Tmp1 Join[x = y] S
Res = Proj[x,z](Tmp2)
Exercises:
a. Which employees earn more than $20 per hour (give their employee id and name).
b. Who are the department managers (give just their name).
c. Which employees worked on every week day during the last week (give just their name). You may assume that there will be at least one employee working on each week day.
Instructions:
Type your answers to this question into a file called q12.txt Submit via: submit q12.txt
End of Question