Recap of Feb 11: SQL• “*” is an abbreviation for all attributes in the from list• implicit tuple variables; table names as tuple variables• Set operators: union, intersect, minus, contains, exists, in, not• Insert command and semantics• Delete command and semantics• Update command and semantics• Aggregate functions: avg, min, max, sum, count• group by clause• having clauseSQL: Multiple Group BysExample: using relation emp(ss#, ename, dept, cat, sal)Count the employees and average monthly salary for each employee category in each departmentselect dept, cat, count(*), avg(sal)/12from empgroup by dept, catSQL: Multiple Group BysSelect … from empgroup by catSelect … from empgroup by deptSQL: Multiple Group BySelect … from empgroup by dname, catnote that some dname/cat groups are empty.SQL: Examples on HavingFind the average salary of employees under 30 for each department with more than 10 such employeesselect dname, avg(sal)from empwhere age<30 (employee age under 30)group by dname (group by department)having 10<count(*) (group size > 10)SQL: Examples on HavingFind the average salary of employees under 30 for each department with more than 10 employeesselect e.dname, avg(e.sal)from emp ewhere e.age<30 (employee age under 30)group by e.dname (group by department)having 10<any(select count(ee.ename) (number of employees in group)from emp eewhere ee.dname=e.dname) (… from the same dept as e)(why is this query different from the previous one?)SQL: Examples on HavingFind categories of employees whose average salary exceeds that of programmersselect cat, avg(sal)from empgroup by cathaving avg(sal)> (select avg(sal)from empwhere cat=“programmer”)SQL: Examples on HavingFind all departments with at least two clerksselect dnamefrom empwhere job=“clerk”group by dnamehaving count(*) >= 2SQL: ExamplesFind the names of sailors with the highest ratingselect snamefrom sailorswhere rating = (select max(rating)from sailors)SQL: ExamplesFor each boat, find the number of sailors of rating >7 who have reserved this boatselect bid, bname, count(s.sid)from sailors s, boats b, reserve rwhere s.sid=r.sid and r.bid=b.bid and rating>7group by b.bidSQL: ExamplesFor each red boat, find the number of sailors who have reserved this boatselect bid, bname, count(s.sid)from sailors s, boats b, reserve rwhere s.sid=r.sid and r.bid=b.bid group by b.bidhaving colour=“red”SQL: ExamplesDifference between the last two queries?– First one gave a qualification on the tuples(take all tuples of the multijoindiscard tuples that do not fulfill ratings>7then group them by boat idthen find the cardinality of each group)– Second one gave a qualification for the groups(take all tuples of the multijoingroup them by boat iddiscard groups representing boats that are non-redfind the cardinality of remaining groups)And Now, For SomethingCompletely Different...• The recent SQL material largely covers chapter 4, at least sections 4.1 through 4.6 and some of 4.9.• Earlier we examined Relational Algebra, covering sections 3.1 through 3.3• Now we leave chapter 4 and head back to examine sections 3.6 and 3.7, covering Relational Calculi– based upon predicate calculus– non-procedural query languages (descriptive rather than prescriptive)– we will examine two relational calculi: tuple calculus and domain calculusTuple CalculusQuery: {t | P(t)} P: is a predicate associated with some relation Rt: is a tuple variable ranging over the relation Rt[A]: is the value of attribute A in tuple t– students in CMSC 424• {t | t ? enroll ? t[course#] = CMSC424}– students in CMSC 424 conforming with the CMSC-420 prerequisite• {t | t ? enroll ? ? s ? enroll ? t[course#] = CMSC424 ?s[course#] = CMSC420 ? t[ss#] = s[ss#]}Tuple Calculus• Quantifiers and free variables– ?, ? quantify the variables following them, binding them to some value. (in the previous slide, s was bound by ?)– A tuple variable that is not quantified by ? or ? is called a free variable. (in the previous slide, t was a free variable)• Atoms– R(t) where t is a tuple variable– t[x] ? s[y] where t,s are tuple variables and? ? {?, ?, ?, ?, ?, ?}Tuple Calculus• Formulas– an Atom is a Formula – If P and Q are Formulas, then so are (P), ?P, P?Q, P?Q, and P?Q– If P(t) is a Formula, then so are ?t P(t) and ?t P(t)• Equivalences– ?(P ? Q) ? ?P ? ? Q– ?(P ? Q) ? ?P ? ? Q– ?t P(t) ? ? (?t (? P(t)))– ? t P(t) ? ? (? t (? P(t)))Tuple Calculus• Safety– Math is too powerful; we can easily phrase expressions that describe infinite sets{t | t ? enroll}– These expressions are called unsafe– When we are dealing with finite sets, unsafe expressions happen in expressions that involve negation (?)– We can avoid this problem by using an entirely positive (non-negated) scope as the first operand of any conjunction where we use negation. The first operand establishes the scope and the second one filters the established scope.{t | t ? enroll ? t[course#] ? CMSC-420}Domain Calculus• Another form of relational calculus• Uses domain variables that take values from an attribute’s domain, rather than values representing an entire tuple• Closely related to tuple calculus• Domain Calculus serves as the theoretical basis for the query language QBE, just as the relational algebra we examined earlier forms the basis for SQL• Expressions are of the form:{< x1, x2, x3, ..., xn> | P( x1, x2, x3, ..., xn) }Domain Calculus• Atoms– < x1, x2, x3, ..., xn> ? R – x ? y where x,y are domain variables and? ? {?, ?, ?, ?, ?, ?}– x ? c where c is a constant• Formulas– an atom is a formula – If P and Q are formulas, then so are (P), ?P, P?Q, P?Q, and P?Q– If P(x) is a formula and x is a domain variable, then ?x P(x) and?x P(x) are also formulasDomain Calculus• Queries are of the form:{< x1, x2, x3, ..., xn> | P( x1, x2, x3, ..., xn) }• Examples{<ss#, course#, semester> | Enroll(ss#, course#, semester)}{<x, y, z> | Enroll(x, y, z) ? y = CMSC-424}Reductions of Relational Algebra and Calculi• Relational Algebra (sections 3.2-3.5), Tuple Calculus (section 3.6), and Domain Calculus (section 3.7) can be reduced to each other: they have equivalent expressive power. For every expression in one, we can compute an equivalent expression in the others.Functional Dependencies• Important concept in differentiating good database designs from bad ones• FD is a generalization of the notion of keys• An FD
View Full Document
Unlocking...