Database Systems Lecture #6

1. Database SystemsLecture #6 Yan Pan School of Software, SYSU 2011

2. Agenda • Basic SQL • RA…

3. Recap: You are here • First part of course is done: conceptual foundations • You now know: • E/R Model • Relational Model • Relational Algebra (a little) • You now know how to: • Capture part of world as an E/R model • Convert E/R models to relational models • Convert relational models to good (normal) forms • Next: • Create, update, query tables with R.A/SQL • Write SQL/DB-connected applications

4. 3-minute Normalization Review • Q: What’s required for BCNF? • Q: How do we fix a non-BCNF relation? • Q: If AsBs violates BCNF, what do we do? • Q: Can BCNF decomposition ever be lossy? • Q: How do we combine two relations? • Q: Can BCNF decomp. lose FDs? • Q: Why would you ever use 3NF?

5. Next topic: SQL • Standard language for querying and manipulating data Structured Query Language • Many standards: ANSI SQL, SQL92/SQL2, SQL3/SQL99 • Originally: Structured English Query Language (SEQUEL) • Vendors support various subsets/extensions • We’ll do Oracle/MySQL/generic • “No one ever got fired for buying Oracle.” • Basic form (many more bells and whistles in addition): SELECT attributes FROM relations (possibly multiple, joined) WHERE conditions (selections)

6. Data Types in SQL • Characters: • CHAR(20) -- fixed length • VARCHAR(40) -- variable length • Numbers: • BIGINT, INT, SMALLINT, TINYINT • REAL, FLOAT -- differ in precision • MONEY • Times and dates: • DATE • DATETIME -- SQL Server

7. Table name “Tables” Attribute names Product Tuples or rows

8. Simple SQL Query Product SELECT *FROM ProductWHERE category='Gadgets' “selection”

9. Simple SQL Query Product SELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100 “selection” and “projection”

10. A Notation for SQL Queries Input Relation Product(PName, Price, Category, Manfacturer) SELECT Name, Price, ManufacturerFROM ProductWHERE Price > 100 (PName, Price, Manfacturer) Output Relation

11. The WHERE clause • Contains a boolean expression • Teach literal is a test: x = y, x < y, x <= y, etc. • For numbers, they have the usual meanings • For CHARs/VARCHARs: lexicographic ordering • Expected conversion between CHAR and VARCHAR • For dates and times, what you expect Guifeng Zheng, DBMS, SS/SYSU

12. Complex RA Expressions • Schema: Movies (Title, year, length, inColor, studioName, Prdcr#) • Q: How long was Star Wars (1977)? • Strategy: find the row with Star Wars; then project the length field

13. Combining operations • Query: Which Fox movies were >= 100 minutes long?

14. Next (parallel) topic: relational algebra • Projection • Selection • Cartesian Product • Joins: natural joins, theta joins • Set operations: union, intersection, difference • Combining operations to form queries • Dependent and independent operations

15. What is relational algebra? • An algebra for relations • “High-school” algebra: an algebra for numbers • Algebra = formalism for constructing expressions • Operations • Operands: Variables, Constants, expressions • Expressions: • Vars & constants • Operators applied to expressions • They evaluate to values

16. Why do we care about relational algebra? • The exprs are the form that questions about the data take • The relations these exprs cash out to are the answers to our questions • RA ~ more succinct rep. of many SQL queries • DBMS parse SQL into something like RA • First proofs of concept for RDBMS/RA: • System R at IBM • Ingress at Berkeley • “Modern” implementation of RA: SQL • Both state of the art, mid-70s

17. Relation operators • Basic operators: • Selection: s • Projection: P • Cartesian Product:  • Other set-theoretic ops: • Union:  • Intersection: • Difference: - • Additional operators: • Joins (natural, equijoin, theta join, semijoin) • Renaming: r • Grouping…

18. Selection op • Selects all tuples satisfying a condition • Notation: sc(R) • Examples • ssalary > 100000(Employee) • sname = “Smith”(Employee) • The condition c can have • comparison ops:=, <, , >,, <> • boolean ops: and, or

19. Theater N’hood Title Sunshine Village Annie Hall Sunshine Village Bad Edu. Theater N’hood Title Film Forum Village Masc. Fem. Sunshine Village Annie Hall Sunshine Village Bad Edu. Selection example • Select the movies at Angelica: • sTheater=“Sunshine”(Showings)

20. Projection op • Keep only certain columns • Projection: op we used for decomposition • Eliminates other columns, then removes duplicates • Notation: PA1,…,An(R)

21. Cartesian product op • Cross product again • “Cartesian Product” • Each tuple in R1 combines w/each tuple in R2 • Algebraic notation: R1 R2 • Not actual SQL! • If R1, R2 fields overlap, include both and disambiguate: R1.A, R2.A • Q: Where does the name come from? • Q: If R1 has n1 rows and R2 has n2, how large is R1 x R2?

22. Cartesian product example Hillary-addresses Hillary-jobs Hillary-addresses x Hillary-jobs

23. Join op • Corresponds to SQL query doing cross & equality test • Specifically: R1 R2 = Pevery att once(sshared atts =(R1 R2)) • I.e., first compute the cross product R1 x R2 • Next, select the rows in which shared fields agree • Finally, project onto the union of R1 and R2’s fields (remove duplicates)

24. Natural join example Addresses Jobs Addresses Jobs

25. Natural Join • R S • R S= ? • Unpaired tuples called dangling

26. Natural Join • Given the schemas R(A, B, C, D), S(A, C, E), what is the schema of R S ? • Given R(A, B, C), S(D, E), what is R S? • Given R(A, B), S(A, B), what is R S?

27. Join on arbitrary test U V “Theta-join” U V A<D

28. Rename op • Changes the schema, not the instance • Notation: rB1,…,Bn(R) • r is spelled “rho”, pronounced “row” • Example: • Employee(ssn,name) • rE2(social, name)(Employee) • Or just: rE(Employee)

29. RA  SQL • SQL SELECT RA Projection P • SQL WHERE  RA Selection s • SQL FROM  RA Join/cross • Comma-separated list… • SQL renaming  RA rho r • More ops later • Keep RA in the back of your mind…

30. Next: Joins in SQL • Connect two or more tables: Product Company What is the connection between them?

31. Joinbetween Productand Company Joins in SQL Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all products under $200 manufactured in Japan;return their names and prices. SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200

32. Joins in SQL Product Company SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200

33. Joins in SQL Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets'

34. Joins in SQL Product Company SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets' What is the problem? What’s thesolution?

35. Joins Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(name, phone, city) Find names of Seattleites who bought Gadgets, and the names of the stores they bought such product from. SELECT DISTINCT name, storeFROM Person, Purchase, ProductWHEREpersname=buyer AND product = pname AND city='Seattle' AND category='Gadgets'

36. SQL Query Semantics Parallel assignment – all tuples Doesn’t impose any order SELECT a1, a2, …, ak FROM R1 AS x1, R2 AS x2, …, Rn AS xn WHERE Conditions Answer = {} for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer  {(a1,…,ak)} return Answer

37. SQL Query Semantics Nested loops: SELECT a1, a2, …, ak FROM R1 AS x1, R2 AS x2, …, Rn AS xn WHERE Conditions Answer = {} for x1 in R1 do for x2 in R2 do ….. for xn in Rn do if Conditions then Answer = Answer  {(a1,…,ak)} return Answer

38. Multiple join syntaxes • Old-style syntax simply lists tables separated by commas • New-style makes the join explicit: • Functionally equivalent to old-style, but perhaps more elegant • Introduced in Oracle 8i, MySQL 3.x/4.x • Older versions / other DBMSs may not support this SELECT * FROM A,B WHERE …; SELECT * FROM A JOIN B ON … WHERE …;

39. New-style join types • Cross joins (simplest): • FROM A CROSS JOIN B • Inner joins (regular joins): • FROM A [INNER] JOIN B ON … • Natural join: • FROM A NATURAL JOIN B; • Joins on common fields and merges • Outer joins (later) • No dangling rows

40. CROSS JOIN e.g. MovieStar MovieExec

41. CROSS JOIN e.g. SELECT * FROM MovieStar CROSS JOIN MovieExec

42. JOIN … ON e.g SELECT * FROM MovieStar JOIN MovieExec ON MovieStar.Name <> MovieExec.Name

43. NATURAL JOIN • MovieStar(name, address, gender, birthdate) • MovieExec(name, address, networth) • Natural Join syntax: • FROM MovieStar NATURAL JOIN MovieExec • Results: list of movie stars who are also execs: • (Name, address, gender, birthdate, networth)

44. NATURAL JOIN e.g. MovieStar MovieExec SELECT * FROM MovieStar NATURAL JOIN MovieExec

45. Another complex example • People(ssn, name, street, city, state, state) • Q: Who lives on George’s street? • A: First, generate pairs of (renamed) people: • rp1(People) x rp2(People) • Then pick out pairs with George: • sp1.name='George'(rp1(People) x rp2(People)) • And refine to rows with George and someone else: • sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People)) • Finally, project out the names: • Pp2.name(sp1.name='George‘ AND p1.name<>p2.name(rp1(People) x rp2(People))

46. Live examples • Q: produce a list of employees and their bosses • What if no boss? Or no subordinate? • Joins on emp, emp man: • Comma-based • Inner • Natural • Cross • Outer – left, right, full

47. More live examples • Inner joins require an ON clause • Like a where clause • Arbitrary boolean expression • If always true (1=1), reduces to cross join • New compar op: BETWEEN • a between 5 and 10 a >= 5 and a <= 10 • Q: produce a list of employees with their salary grades • emp, salgrade

48. Review • Examples from sqlzoo.net SELECTL FROM R1, …, Rn WHERE C PL(sC(R1 x … Rn)