Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Announcements for a database systems course, including reading assignments, homework assignments, and exam information. Topics covered include sql, join variations, division operator, and aggregate functions. Students are expected to complete exercises and should not cheat on programs.
Typology: Study Guides, Projects, Research
1 / 13
This page cannot be seen from the preview
Don't miss anything!
Reading assignment Chapter 8 – SQL
-^
Written Homework 1 will be officially assigned onTuesday, due Nov 2–
Exercises 5.12, 5.15, 6.17, 6.20, 6.22 (a,c,f only).
Programs: please don’t cheat
-^
Today– Exam recap and solution– Wrap up chapter 6
DIVISION useful for queries such as:– “Retrieve the names of employees who work on all
the projects that ‘John Smith’ works on“
÷
S(X), X must be a subset of Z
tuples t
R^
appear in R with t
R^
[Y] = t, and
with t
R^
[X] = t
s^
for every tuple
ts
in S.
Attributes of R
Attributes of S
Example of DIVISION
-^
Use of the Aggregate Functional operation
ℱ
-^
ℱ
MAX Salary
(EMPLOYEE) retrieves the maximum salary value
from the EMPLOYEE relation
-^
ℱ
MIN Salary
(EMPLOYEE) retrieves the minimum Salary value from
the EMPLOYEE relation
-^
ℱ
SUM Salary
(EMPLOYEE) retrieves the sum of the Salary from the
EMPLOYEE relation
-^
ℱ
COUNT SSN, AVERAGE Salary
(EMPLOYEE) computes the count
(number) of employees and their average salary• Note: count just counts the number of rows, without removing
duplicates
( R,
(EMPLOYEE, {}, MAX Salary)
(EMPLOYEE, Dno, Avg Salary) A relation
Set of attributes in RTo group by beforeapplying theaggregate function
set of (aggr. function, attribute)
Max salary in whole comp.Get avg salary for eachdeparment
Additional Relational Operations (cont.)
-^
The OUTER JOIN Operation– In NATURAL JOIN and EQUIJOIN, tuples without a
matching
(or
related
) tuple are eliminated from the join result
want to keep all the tuples in R, or all those in S, or all those inboth relations in the result of the join, regardless of whether ornot they have matching tuples in the other relation.
Additional Relational Operations (cont.)
The left outer join operation keeps every tuple inthe first or left relation R in R
S; if no
matching tuple is found in S, then the attributesof S in the join result are filled or “padded” withnull values.
-^
A similar operation, right outer join, keeps everytuple in the second or right relation S in theresult of R
A third operation, full outer join, denoted bykeeps all tuples in both the left and the rightrelations when no matching tuples are found,padding them with null values as needed.
