Lecture 12: Further relational algebra, further SQL

1. Lecture 12:Further relational algebra, further SQL www.cl.cam.ac.uk/Teaching/current/Databases/

2. Today’s lecture • Where does SQL differ from relational model? • What are some other features of SQL? • How can we extend the relational algebra to match more closely SQL?

3. Duplicate rows • Consider our relation instances from lecture 6, Reserves, Sailors and Boats • Consider SELECT rating,age FROM Sailors; • We get a relation that doesn’t satisfy our definition of a relation! • RECALL: We have the keyword DISTINCT to remove duplicates

4. Multiset semantics • A relation in SQL is really a multiset or bag, rather than a set as in the relational model • A multiset has no order (unlike a list), but allows duplicates • E.g. {1,2,1,3} is a bag • select, project and join work for bags as well as sets • Just work on a tuple-by-tuple basis

5. Bag operations • Bag union: • Sum the number of times that an element appears in the two bags, e.g. • {1,2,1}{1,2,3} = {1,1,1,2,2,3} • Bag intersection: • Take the minimum of the number of occurrences in each bag, e.g. • {1,2,1}{1,2,3,3} = {1,2} • Bag difference: • Proper-subtract the number of occurrences in the two bags, e.g. • {1,2,1}-{1,2,3,3} = {1}

6. Laws for bags • Note that whilst some of the familiar (set-theoretic) laws continue to hold, some of them do not • Example: R(ST) = (RS)(RT) ??

7. Extended relational algebra Add features needed for SQL • Bag semantics • Duplicate elimination operator,  • Sorting operator,  • Grouping and aggregation operator,  • Outerjoin operators, oV, Vo, oVo

8. Duplicate-elimination operator • (R) = relation R with any duplicated tuples removed • R= (R)= • This is used to model the DISTINCT feature of SQL

9. Sorting • L1,… Ln(R) returns a list of tuples of R, ordered according to the attributes L1, …, Ln • Note:  does not return a relation • R= B(R)= [(5,2),(1,3),(3,4)] • ORDER BY in SQL, e.g. SELECT * FROM Sailors WHERE rating>7 ORDER BY age, sname;

10. Extended projection • SQL allows us to use arithmetic operators SELECT age*5 FROM Sailors; • We extend the projection operator to allow the columns in the projection to be functions of one or more columns in the argument relation, e.g. • R= A+B,A,A(R)=

11. Arithmetic • Arithmetic (and other expressions) can not be used at the top level • i.e. 2+2 is not a valid SQL query • How would you get SQL to compute 2+2?

12. Aggregation • SQL provides us with operations to summarise a column in some way, e.g. SELECT COUNT(rating) FROM Sailors; SELECT COUNT(DISTINCT rating) FROM Sailors; SELECT COUNT(*) FROM Sailors WHERE rating>7; • We also have SUM, AVG, MIN and MAX

13. Grouping • These aggregation operators have been applied to all qualifying tuples. Sometimes we want to apply them to each of several groups of tuples, e.g. • For each rating, find the average age of the sailors • For each rating, find the age of the youngest sailor

14. GROUP BY in SQL SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list; • The target-list contains • List of column names • Aggregate terms • NOTE: The variables in target-list must be contained in grouping-list

15. GROUP BY cont. For each rating, find the average age of the sailors SELECT rating,AVG(age) FROM Sailors GROUP BY rating; For each rating find the age of the youngest sailor SELECT rating,MIN(age) FROM Sailors GROUP BY rating;

16. Grouping and aggregation • L(R) where L is a list of elements that are either • Individual column names (“Grouping attributes”), or • Of the form (A), where  is an aggregation operator (MIN, SUM, …) and A is the column it is applied to • For example, rating,AVG(age)(Sailors)

17. Semantics • Group R according to the grouping attributes • Within each group, compute (A) • Result is the relation consisting of one tuple for each group. The components of that tuple are the values associated with each element of L for that group

18. Example • Let R= • Compute beer,AVG(price)(R)

19. Example cont. • Group according to the grouping attribute, beer: • Compute average of price within groups:

20. NULL values • Sometimes field values are unknown (e.g. rating not known yet), or inapplicable (e.g. no spouse name) • SQL provides a special value, NULL, for both these situations • This complicates several issues • Special operators needed to check for NULL • Is NULL>8? Is (NULL OR TRUE)=TRUE? • We need a three-valued logic • Need to carefully re-define semantics

21. NULL values • Consider INSERT INTO Sailors (sid,sname) VALUES (101,”Julia”); SELECT * FROM Sailors; SELECT rating FROM Sailors; SELECT sname FROM Sailors WHERE rating>0;

22. Entity integrity constraint • An entity integrity constraint states that no primary key value can be NULL

23. Outer join • Note that with the usual join, a tuple that doesn’t ‘join’ with any from the other relation is removed from the resulting relation • Instead, we can ‘pad out’ the columns with NULLs • This operator is called an full outer join, written oVo

24. Example of full outer join • Let R= Let S= • Then RVS = • But RoVoS =

25. Outer joins in SQL • SQL/92 has three variants: • LEFT OUTER JOIN (algebra: oV) • RIGHT OUTER JOIN (algebra: Vo) • FULL OUTER JOIN (algebra: oVo) • For example: SELECT * FROM Reserves r LEFT OUTER JOIN Sailors sON r.sid=s.sid;

26. Views • A view is a query with a name that can be used in further SELECT statements, e.g. CREATE VIEW ExpertSailors(sid,sname,age) AS SELECT sid,sname,age FROM Sailors WHERE rating>9; • Note that ExpertSailors is not a stored relation • (WARNING: mysql does not support views )

27. Querying views • So an example query SELECT sname FROM ExpertSailors WHERE age>27; • is translated by the system to the following: SELECT sname FROM Sailors WHERE rating>9 AND age>27;

28. Summary You should now understand: • Multi-set semantics • Conditions • Aggregation • GROUP BY • NULLs, entity ICs and outer joins • Views • Extensions to the core relational algebra