Lecture 9 Database Usage and Construction Relational Algebra

Lecture 9 Database Usage (and Construction) Relational Algebra: Summary

Relational Algebra I • Select – σ<selection condition> (R) • Project – π<attribute list> (R) • Rename – ρ<new schema> (R) • Union –RUS

Relational Algebra II • Intersection –R∩S • Difference –R-S • Cross Product –Rx. S • Join – R ⋈ <join condition> S

Relational Algebra III • Natural Join –R⋈S • Division –R÷S • Grouping – γX, G(R)

Selection & Projection name branc h salary Andersso n 3 20000 Johnsson 3 25000 Larsson 3 32000 Persson 2 28000 Svensson 2 35000 name branc h salary name salary Andersso n 20000 Johnsson 25000 Larsson 32000 Persson 28000 Svensson 35000 Andersso n 3 20000 Johnsson 3 25000 Larsson 3 32000 Persson 2 28000 Svensson 2 35000 Selection σname=‘Andersson’ (R) name branc h salary Andersso n 3 20000 “Horizontal” decompositions Projection πname, salary (R) “Vertical” decompositions

Outer Join Operators R 1 R 2 name branch salary Andersson 3 3 20000 Johnsson 3 4 25000 Larsson 1 R 1 ⋈ LEFT R 2 name branc h salary Andersso n 3 20000 Johnsson 3 20000 Larsson 1 Null branc h salary Andersso n 3 20000 Johnsson 2 20000 Null 4 25000 name R 1 ⋈ RIGHT R 2 R 1 ⋈ FULL R 2 name branc h salary Andersso n 3 20000 Johnsson 2 20000 Larsson 1 Null 4 25000

Q 1 BOOKS (Doc. Id, Title, Publisher, Year) Write a relational algebra expression that lists the year and title of each book.

Q 2 STUDENTS (St. Id, St. Name, Major, Age) Write a relational algebra expression that lists all information about students whose major is CS.

Q 3 BOOKS (Doc. Id, Title, Publisher, Year) STUDENTS (St. Id, St. Name, Major, Age) Write a relational algebra expression that lists all students with the books they can borrow.

Q 3. 5 BOOKS (Doc. Id, Title, Publisher, Year) STUDENTS (St. Id, St. Name, Major, Age) BORROWS (Doc. Id, St. Id, Date) Write a relational algebra expression that lists each student with the books s/he has borrowed.

Q 4 BOOKS (Doc. Id, Title, Publisher, Year) Write a relational algebra expression that lists all books published by Mc. Graw-Hill before 1990

Q 5 AUTHORS (AName, Address) Write a relational algebra expression that lists the name of those authors who are living in Davis.

Q 6 AUTHORS (AName, Address) Write a relational algebra expression that renames “Aname” attribute in the relation AUTHORS to “Name”

Q 7 STUDENTS (St. Id, St. Name, Major, Age) Write a relational algebra expression that lists the name of students who are older than 30 and who are not studying CS

Q 8 Wards (number, num. Beds) Patients (pid, name, year, gender) Patient. In. Ward (pid, ward) Tests (pid, test. Time, temperature, heart. Rate) Write a relational algebra expression that finds the temperature and heart rate measured in each test carried out on patients born before 1950. a) σtemperature, heart. Rate(πyear<=1950(Patients) ⋈ Tests) b) πtemperature, heart. Rate(σyear<=1950(Patients) ⋈ Tests) c) πtemperature, heart. Rate(σyear<1950(Patients) ⋈ Tests) d) σtemperature, heart. Rate(πyear<1950(Patients) ⋈ Tests)

Q 9 Departments (dept. Name, location) Employees (emp. Id, name) Works. In (employee, dept, location, percentage) Write a relational algebra expression that finds the employee name and department name of employees who are assigned to work more than 50% in a department in Stockholm. a) σemp. Id, dept. Name(Employees ⋈ emp. Id=employee (σpercentage>50 AND location= Stockholm (Works. In))) b) πemp. Id, dept. Name(Employees ⋈ (πpercentage>50 AND location= Stockholm (Works. In))) c) πemp. Id, dept. Name(Employees ⋈ (σpercentage>50 AND location= Stockholm (Works. In))) d) πemp. Id, dept. Name(Employees ⋈ emp. Id=employee (σpercentage>50 AND location= Stockholm (Works. In)))

Q 11 Exams (course, exam. Date, exam. Time) Students (student. Id, name) registered. For (student, course, exam. Date) Write a relational algebra expression that finds the names of students who have registered for the exam in course ’TDA 357’ on ’ 2010 -12 -18’. a) πname(Students ⋈ (σcourse=‘TDA 357’ AND exam. Data=‘ 2010 -12 -18’ (registered. For))) b) πname(Students ⋈ student. Id=student (σcourse=‘TDA 357’ AND exam. Data=‘ 2010 -12 -18’ (registered. For))) c) πname(registered. For ⋈ student. Id=student (σcourse=‘TDA 357’ AND exam. Data=‘ 2010 -12 -18’ (Students))) d) πname(Students ⋈ student. Id=student (σcourse=‘TDA 357’ OR exam. Data=‘ 2010 -12 -18’ (registered. For)))

Exams (course, exam. Date, exam. Time) Students (student. Id, name) registered. For (student, course, exam. Date) Write a relational algebra expression that finds the average number of students who have registered for the exams in each course (for example, if there have been three exams in course ’TDA 357’ and 100 students registered for the exam on the first occasion, 150 students registered for the second occasion and 80 students registered for the third occasion, then the average number of students registering for an exam in course ’TDA 357’ would be 110). Q 12 The result should contain the course code and the average number of students registered for exams in that course, and the results should be sorted by course code. a) τcourse (γAVG(nr. St) → avg. St (γcourse, exam. Date, COUNT(student) → nr. St (registered. For))) b) τcourse (γcourse, AVG(nr. St) → avg. St (γcourse, COUNT(student) → nr. St (registered. For))) c) τcourse (γcourse, AVG(nr. St) → avg. St (γcourse, exam. Date, COUNT(student) → Students (registered. For))) d) τcourse (γcourse, AVG(nr. St) → avg. St (γcourse, exam. Date, COUNT(student) → nr. St (registered. For)))

Q 13 Employees (emp. Id, name, year, salary, entitlement, branch) Parental. Leave (employee, start. Day, start. Year, end. Day, end. Year) Write a relational algebra expression that finds the names of employees who had a period of parental leave that started in 2007. a) πname (Employees ⋈ emp. Id=employee (πstart. Year=2007 (Parental. Leave))) b) σname (Employees ⋈ emp. Id=employee (σstart. Year>=2007 (Parental. Leave))) c) πname (Employees ⋈ emp. Id=employee (σstart. Year=2007 (Parental. Leave))) d) πname (Employees ⋈ emp. Id=employee (σstart. Year>=2007 (Parental. Leave)))

Q 14 Employees (emp. Id, name, year, salary, entitlement, branch) Parental. Leave (employee, start. Day, start. Year, end. Day, end. Year) Write a relational algebra expression that finds the employee(s) in each branch who have the highest salary in their branch. The result should contain the employee’s name, the branch and the salary, and the result should be sorted by branch name. a) τbranch (πname, branch, salary (σsalary=max. Sal (Employees ⋈ (γbranch, MAX(salary)→max. Sal(Employees)))) b) τbranch (πname, salary (σsalary=max. Sal (Employees ⋈ (γbranch, MAX(salary)→max. Sal(Employees))))) c) τbranch (πname, branch, salary (σ (Employees ⋈ (γbranch, MAX(salary)→max. Sal(Employees))))) d) τbranch (πname, branch, salary (σsalary=max. Sal (γbranch, MAX(salary)→max. Sal(Employees))))

Q 15 Doctors (person_number, health_centre) Appointments (appointment_id, patient, doctor, day, month, year) doctor → Doctors. person number Write a relational algebra expression that computes then names of the health centres, sorted in alphabetical order, where the patient with identification number ‘ 6006064444’ had appointments in year 2000 or more recently. a) τhealth_centre (πhealth_centre(Doctors ⋈ person_number=doctor (σpatient=“ 6006064444” AND year ≥ 2000(Appointments)))) b) τhealth_centre (πhealth_centre(Appointments ⋈ person_number=doctor (σpatient=“ 6006064444” AND year ≥ 2000(Doctors)))) c) τhealth_centre (πhealth_centre(Doctors ⋈ (πpatient=“ 6006064444” AND year ≥ 2000(Appointments)))) d) τhealth_centre (πhealth_centre(Doctors ⋈ person_number=doctor (σpatient=“ 6006064444” AND year ≥ 2000(Appointments))))

Q 16 Doctors (person_number, health_centre) Appointments (appointment_id, patient, doctor, day, month, year) doctor → Doctors. person number Write a relational algebra expression that computes, for each health centre, the total number of appointments at that health centre in each month of 2007. a) πhealth_centre, month, count(*) → num. Apps (Doctors ⋈ person_number=doctor (σyear=2007(Appointments))) b) σhealth_centre, month, count(*) → num. Apps (Doctors ⋈ person_number=doctor (σyear=2007(Appointments))) c) τhealth_centre, month, count(*) → num. Apps (Doctors ⋈ person_number=doctor (σyear=2007(Appointments))) d) γhealth_centre, month, count(*) → num. Apps (Doctors ⋈ person_number=doctor (σyear=2007(Appointments)))

Employees name branc h salary Andersso n 3 20000 Johnsson 3 25000 Larsson 3 32000 Persson 2 28000 Svensson 2 35000 Branches number city 1 Stockholm 2 Paris 3 London 4 Berlin 5 Rome Q 17 Write two different relational algebra expressions that evaluate to: name city Larsson London Svensson Paris 1) one of these relational algebra expressions must use the Cartesian product operator. 2) one of these relational algebra expressions must not use the Cartesian product operator.

Employees name branc h salary Andersso n 3 20000 Johnsson 3 25000 Larsson 3 32000 Persson 2 28000 Svensson 2 35000 Branches number city 1 Stockholm 2 Paris 3 London 4 Berlin 5 Rome Q 18 Write a relational algebra expression that computes the average salary at each branch, and sorts the results in increasing order of the average salary.

Q 19 Offices (city, supplement) Departments (city, dname, department. Head) Employees (emp. Id, name, salary, dept, city) Write a relational algebra expression that finds the employee identifier, name and total monthly salary of all employees (recall that the total monthly salary for an employee can be calculated by adding the city supplement to the employee’s basic monthly salary). The results should be sorted by employee name. a) τname (πemp. Id, name, supplement (Employees ⋈ Offices)) b) τname (πemp. Id, name, salary (Employees ⋈ Offices)) c) τname (πemp. Id, name, salary+supplement (Employees ⋈ Offices)) d) τname (πemp. Id, salary+supplement (Employees ⋈ Offices))

Q 20 Offices (city, supplement) Departments (city, dname, department. Head) Employees (emp. Id, name, salary, dept, city) Write a relational algebra expression that finds the names of cities where there is a sales department and, for each of these departments, the average basic salary of the employees in that department. a) γcity, AVG(salary)→avg. Salary (σdept=”sales” (Employees)) b) γAVG(salary)→avg. Salary (σdept=”sales” (Employees)) c) γcity (σdept=”sales” (Employees)) d) γcity, AVG(salary)→avg. Salary (σ(Employees))

Q 21 Translate the following relational algebra expression to an SQL query: π First. depature. Time, Second. arrival. Time ((σFirst (Flights)) � First. destination. Airport = Second. Departure. Airport (σSecond(Flights))) SELECT first. departure. Time, second. arrival. Time a) FROM flights AS first JOIN flights AS second ON first. destination. Airport = second. departure. Airport; SELECT first. departure. Time, second. arrival. Time b) FROM flights AS first, flights AS second WHERE first. destination. Airport = second. departure. Airport; SELECT departure. Time, arrival. Time c) FROM flights JOIN flights ON destination. Airport = departure. Airport;

Q 22 Words (string, lemma, class, description) Write a relational algebra query that returns those strings whose class is ambiguous, i. e. , can have two or more different values. An example is la cker, which is both the present tense of the verb la cka (“leak”) and the singular real form of the adjective la cker (“delicious”). a) πA. string σA. string=B. string AND A. class<B. class (σAWords × σBWords) b) πA. string σA. string=B. string AND A. class<B. class (ρAWords × ρBWords) c) πstring σstring=string AND class<class (Words × Words) d) σA. string πA. string=B. string AND A. class<B. class (ρAWords × ρBWords)

Q 23 BOOKS (Doc. Id, Title, Publisher, Year) Write a relational algebra expression that finds the title of the oldest book.