10 1 08 Instructor Amol Deshpande amol cs umd edu Data Models Conceptual representa8on of the data Data Retrieval How to ask ques8ons of the database How to answer those ques8ons Data Storage How where to store data how to access it Data Integrity Manage crashes concurrency Manage seman8c inconsistencies 1 10 1 08 SQL Rela8onal Algebra Formal Seman8cs of SQL More SQL Movie tle year length inColor studioName producerC StarsIn movieTitle movieYear starName MovieStar name address gender birthdate MovieExec name address cert netWorth Studio name address presC 2 10 1 08 Data De ni8on Language Create table insert into values etc 3 10 1 08 Types of queries constructs Many di erent types of predicates Aggregates Set opera8ons Nested subqueries Finding max tuple Key observa8on A tuple has the maximum value if its value equals the maximum value Ranking Key observa8on A tuple is ranked 5th or lower by score i the number of tuples with score larger than it is less than 5 Set comparisons Key Do a join to get an appropriate table Use the constructs for single table queries You will get used to doing all at once Di erent types of joins Inner joins natural joins Only tuples that have matches Outer joins Le right outer join Full outer join Semi join an8 join theta join not SQL opera8ons Important we will discuss them later 4 10 1 08 Using FROM clause with WHERE clause select 8tle year me name as producerName from movies m movieexec me where m producerC me cert inner join select 8tle year me name as producerName from movies m inner join movieexec me on m producerC me cert natural join Finds the common acributes and does equality join Useless on the movies database common acributes named di erently select from olympics natural join countries Consider the query select 8tle year producerC count starName from movies starsIn where 8tle starsIn movieTitle and year starsIn movieYear group by 8tle year producerC You expect A list of movies with the number of stars in them But What about movies with no stars We would like to get movie year producerC 0 for them 5 10 1 08 Le outer join select 8tle year producerC count starName from movies le outer join starsIn on 8tle starsIn movieTitle and year starsIn movieYear group by 8tle year producerC All tuples from movies that have no matches in starsIn are included with NULLs So if a tuple m1 1990 has no match in starsIn we get m1 1990 NULL in the result The count starName works correctly then Note count would not work correctly Extensions Right outer join and Full outer join Set comparisons in SQL why some The subquery may not return something that is ordinal e g it may return strings all seems it would be useful to simplify some queries that involve nding maximums among groups Won t have to create temporary tables E g 6 10 1 08 Find the producer with max average length movie We used create table temp as select name avg length as aveL from movieExec me movies m where me cert m producer group by name select name from temp where aveL select max aveL from temp Alterna8ve select name from movieExec me movie m where me cert m producer group by name having avg length all select avg length from movie m group by producer Note No need to do the join here Grouping by producer is identical to grouping by name Find movie 8tles that appear more than once Op8on 1 Use group by having clause select 8tle from movies group by 8tle having count year 1 Op8on 2 Use set opera8ons select 8tle from movies old where year any select year from movie where 8tle old 8tle 7 10 1 08 Find actors actresses 3 degrees away from H Ford An actor is one degree away from H Ford if they starred in a movie together First create a costars table create table costars as select dis8nct s1 starName as star1 s2 starname as star2 from starsIn s1 starsIn s2 where s1 movieTitle s2 movieTitle and s1 movieYear s2 movieYear Join mul8ple of these together appropriately select c3 star2 from costars c1 costars c2 costars c3 where c1 star1 H Ford and c1 star2 c2 star1 and c2 star2 c3 star1 Di erent answers depending on whether H Ford H Ford exists in this table it does in the above table 8 10 1 08 SQL Rela8onal Algebra Formal Seman8cs of SQL More SQL Procedural language Six basic operators select project union set di erence Cartesian product rename The operators take one or more rela8ons as inputs and give a new rela8on as a result 9 10 1 08 Relation r r A B C D 7 1 7 5 7 12 3 23 10 A B C D 1 A B D 5 23 10 SQL Equivalent select from r where A B and D 5 Unfortunate naming confusion Relation r r A D A D 7 7 7 5 7 7 3 12 3 3 10 23 10 10 A B C D 1 A D SQL Equivalent select distinct A D from r 10 10 1 08 Relation r s A B A B 1 2 1 r s A B 1 1 2 1 1 3 A B 2 3 s r Must be compatible schemas r s SQL Equivalent select from r union except intersect select from s What about intersection Can be derived r s r r s This is one case where duplicates are removed Relation r s A B C D E 1 10 10 20 10 a a b b 2 r s r s A B C D E 1 1 1 1 2 2 2 2 10 10 20 10 10 10 20 10 a a b b a a b b SQL Equivalent select distinct from r s Does not remove duplicates 11 10 1 08 Allows us to name and therefore to refer to the results of rela8onal algebra expressions Allows us to refer to a rela8on by more than one name Example x E returns the expression E under the name X If a rela8onal algebra expression E has arity n then x A1 A2 An E returns the result of expression E under the name X and with the acributes renamed to A1 A2 An Those are the basic opera8ons What about SQL Joins Compose mul8ple operators together A C r x s Addi8onal Opera8ons Set intersec8on Natural join Division Assignment 12 10 1 08 Set intersec8on r s r r s SQL Equivalent intersect Assignment A convenient way to right complex RA expressions Essen8ally for crea8ng temporary rela8ons temp1 R S r SQL Equivalent create table as Natural join A Cartesian product with equality condi8on on common acributes Example if r has schema R A B C D and if s has schema S E B D Common acributes B and D Then r s r A r B r C r D s E r B s B r D s D r x s SQL Equivalent select …
View Full Document
Unlocking...