OCL4 Oracle 10g: SQL & PL/SQL Session #3

1. OCL4 Oracle 10g:SQL & PL/SQLSession #3 Matthew P. Johnson CISDD, CUNY June, 2005 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

2. Agenda • Review • Lab 2 • SQL • Lab 3 • SQL • Lab 4 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

3. name buys Person Product price name ssn Relational Model: plus FD’s Normalization: Eliminates anomalies High-level design strategy Conceptual Model: Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

4. then they must also agree on the attributes B1, B2, …, Bm Functional dependencies • Definition: • Notation: • Read: Ai functionally determines Bj If two tuples agree on the attributes A1, A2, …, An A1, A2, …, An B1, B2, …, Bm Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

5. Typical Examples of FDs • Product • name  price, manufacturer • Person • ssn  name, age • father’s/husband’s-name  last-name • zipcode  state • phone  state (notwithstanding inter-state area codes) • Company • name  stockprice, president • symbol  name • name  symbol Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

6. Example of anomalies • Redundancy: name, maddress • Update anomaly: Bill moves • Delete anom.: Bill doesn’t pay bills, lose phones  lose Bill! • Insert anom: can’t insert someone without a (non-null) phone • Underlying cause: SSN-phone is many-many • Effect: partial dependency ssn  name, maddress, • Whereas key = {ssn,phone} SSN Name, Mailing-address SSN Phone Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

7. Most important: BCNF A simple condition for removing anomalies from relations: A relation R is in BCNF if: If As  Bs is a non-trivial dependency in R , then As is a superkey for R I.e.: The left side must always contain a key I.e: If a set of attributes determines other attributes, it must determine all the attributes • Codd: Ted Codd, IBM researcher, inventor of relational model, 1970 • Boyce: Ray Boyce, IBM researcher, helped develop SQL in the 1970s Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

8. Boyce-Codd Normal Form • Name/phone example is not BCNF: • {ssn,phone} is key • FD: ssn  name,mailing-address holds • Violates BCNF: ssn is not a superkey • Its decomposition is BCNF • Only superkeys  anything else Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

9. Lab 2 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

10. Spooling • Review lab 1 • SQL Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

11. Joins in SQL • Connect two or more tables: Product Company What is the connection between them? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

12. 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

13. Joins in SQL Product Company SELECT PName, PriceFROM Product, CompanyWHEREManufacturer=CName AND Country='Japan' AND Price <= 200 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

14. 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' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

15. Joins in SQL Product Company SELECT CountryFROM Product, CompanyWHEREManufacturer=CName AND Category='Gadgets' What is the problem? What’s thesolution? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

16. 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' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

17. Disambiguating Attributes • Sometimes two relations have the same attr:Person(pname, address, worksfor)Company(cname, address) Whichaddress ? SELECT DISTINCT pname, addressFROM Person, CompanyWHEREworksfor = cname SELECT DISTINCT Person.pname, Company.addressFROM Person, CompanyWHEREPerson.worksfor = Company.cname Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

18. Tuple Variables Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city) Find all stores that sold at least one product that the store‘BestBuy’ also sold: SELECT DISTINCTx.store FROM Purchase AS x, Purchase AS y WHEREx.product = y.product AND y.store = 'BestBuy' Answer: (store) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

19. Tuple Variables • Tuple variables introduced automatically: • Product (name, price, category, manufacturer) • Becomes: • Doesn’t work when Product occurs more than once • In that case the user needs to define variables explicitly SELECTname FROMProduct WHEREprice > 100 SELECTProduct.name FROMProduct AS Product WHEREProduct.price > 100 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

20. Details: Disambiguation in SQL • Every selected field must be unambiguous • For R(A,B), • Select A from R, R •  Select R1.A from R R1, R R2 • Consider: • Why? • * is shorthand for all fields, each must be unambiguous •  Select * from R R1, R R2 SQL> Select * from R, R; Select * from R, R * ERROR at line 1: ORA-00918: column ambiguously defined Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

21. Details: Disambiguation in Oracle SQL • Can rename fields by • Select name as n … • Select name n … • But not by • Select name=n… • Can rename relations only by • … from tab t1, tab t2 • Lesson: if you get errors, remove all =s, ASs Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

22. SQL Query Semantics SELECTa1, a2, …, ak FROMR1 AS x1, R2 AS x2, …, Rn AS xn WHEREConditions 1. Nested loops: 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 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

23. SQL Query Semantics SELECTa1, a2, …, ak FROMR1 AS x1, R2 AS x2, …, Rn AS xn WHEREConditions 2. Parallel assignment Doesn’t impose any order! Answer = {} for all assignments x1 in R1, …, xn in Rn do if Conditions then Answer = Answer  {(a1,…,ak)} return Answer Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

24. SQL e.g. • Acc(name,ssn,balance) • Q: Who has the largest balance? • Conceptually: Pname(Acc) - Pa2.name(sa2.bal < Acc.bal(Acc x ra2(Acc))) • In SQL? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

25. New topic: Subqueries • Powerful feature of SQL: one clause can contain other SQL queries • Anywhere where a value or relation is allowed • Several ways: • Selection  single constant (scalar) in SELECT • Selection  single constant (scalar) in WHERE • Selection  relation in WHERE • Selection  relation in FROM Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

26. Subquery motivation • Consider standard multi-table example: • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • What did Christo buy? • As usual, need to AND on equality identifying ssn’s row and buyerssn’s row SELECT Purchase.prodnameFROM Purchase, PersonWHERE buyerssn = ssn AND name = 'Christo' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

27. Subquery motivation • Purchase(prodname, buyerssn, etc.) • Person(name, ssn, etc.) • What did Conrad buy? • Natural intuition: • Go find Conrad’s ssn • Then find purchases SELECT ssnFROM PersonWHERE name = 'Christo' SELECT Purchase.prodnameFROM PurchaseWHERE buyerssn = Christo’s-ssn Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

28. Subqueries • Subquery: copy in Conrad’s selection for his ssn: • The subquery returns one value, so the = is valid • If it returns more (or fewer), we get a run-time error SELECT Purchase.prodname FROM Purchase WHERE buyerssn = (SELECT ssn FROM Person WHERE name = 'Christo') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

29. Operators on subqueries • Several new operators applied to (unary) selections: • IN R • EXISTS R • UNIQUE R • s > ALL R • s > ANY R • x IN R • > is just an example op • Each expression can be negated with NOT Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

30. Subqueries with IN • Product(name,maker), Person(name,ssn), Purchase(buyerssn,product) • Q: Find companies Martha bought products from • Strategy: • Find Martha’s ssn • Find products listed with that ssn as buyer • Find company names of those products SELECT DISTINCT Product.maker FROM Product WHERE Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase.buyerssn = (SELECT ssn FROM Person WHERE name = 'Martha')) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

31. Subqueries returning relations • Equivalent to: SELECT DISTINCT Product.maker FROM Product, Purchase, People WHERE Product.name = Purchase.product AND Purchase.buyerssn = ssn AND name = 'Martha' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

32. FROM subqueries • Motivation for another way: • suppose we’re given Martha’s purchases • Then could just cross with Products to get product makers •  Substitute (named) subquery for Martha’s purchases SELECT Product.maker FROM Product, (SELECT Purchase.product FROM Purchase WHERE Purchase.buyerssn = (SELECT ssn FROM Person WHERE name = 'Martha')) Marthas WHERE Product.name = Marthas.product Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

33. ALL op Employees(name, job, divid, salary) Find which employees are paid more than all the programmers SELECT name FROM Employees WHERE salary > ALL (SELECT salary FROM Employees WHERE job='programmer') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

34. ANY/SOME op Employees(name, job, divid, salary) Find which employees are paid more than at least one vice president SELECT name FROM Employees WHERE salary > ANY (SELECT salary FROM Employees WHERE job='VP') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

35. ANY/SOME op Employees(name, job, divid, salary) Find which employees are paid more than at least one vice president SELECT name FROM Employees WHERE salary > SOME (SELECT salary FROM Employees WHERE job='VP') Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

36. Existential/Universal Conditions Employees(name, job, divid, salary) Division(name, id, head) Find all divisions with an employee whose salary is > 100000 SELECT DISTINCT Division.name FROM Employees, Division WHERE salary > 100000 AND divid=id Existential: easy! Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

37. Existential/Universal Conditions Employees(name, job, divid, salary) Division(name, id, head) Find all divisions in which everyone makes > 100000 Universal: hard! Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

38. Existential/universal with IN 1. Find the other divisions: in which someone makes <= 100000 SELECT name FROM Division WHERE id IN (SELECT divid FROM Employees WHERE salary <= 100000 2. Select the divisions we didn’t find SELECT name FROM Division WHERE id NOT IN (SELECT divid FROM Employees WHERE salary <= 100000 Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

39. Acc(name,bal,type…) • Q: Who has the largest balance? • Can we do this with subqueries? Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

40. Correlated Queries • Last time: Acc(name,bal,type,…) • Q: Find holder of largest account SELECT name FROM Acc WHERE bal >= ALL (SELECT bal FROM Acc) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

41. Correlated Queries • So far, subquery executed once; • result used for higher query • More complicated: correlated queries • “[T]he subquery… [is] evaluated many times, once for each assignment of a value to some term in the subquery that comes from a tuple variable outside the subquery” (Ullman, p286). • Q: What does this mean? • A: That subqueries refer to vars from outer queries Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

42. Correlated Queries • Last time: Acc(name,bal,type,…) • Q2: Find holder of largest account of each type SELECT name, type FROM Acc WHERE bal >= ALL (SELECT bal FROM Acc WHERE type=type) correlation Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

43. Correlated Queries • Last time: Acc(name,bal,type,…) • Q2: Find holder of largest account of each type • Note: • scope of variables • this can still be expressed as single SFW SELECT name, type FROM Acc a1 WHERE bal >= ALL (SELECT bal FROM Acc WHERE type=a1.type) correlation Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

44. EXCEPT and INTERSECT SELECT R.A, R.B FROM RWHEREEXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B) (SELECT R.A, R.B FROM R)INTERSECT (SELECT S.A, S.B FROM S) SELECT R.A, R.B FROM RWHERENOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B) (SELECT R.A, R.B FROM R)EXCEPT (SELECT S.A, S.B FROM S) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

45. More on Set-Comparison Operators • We’ve already seen IN R, NOT IN R. • Can also use EXISTS R, NOT EXISTS R • Also available: op ANY R, op ALL R • Find sailors whose rating is greater than that of some sailor called Alberto: SELECT R.SID FROM Reserves R WHERE R.rating> ANY(SELECT R2.rating FROM Reserves R2 WHERE R2.sname=‘Alberto’) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

46. Extended e.g. • Scenario: • Purchase(pid, seller-ssn, buyer-ssn, etc.) • Person(ssn, name, etc.) • Product(pid, name, etc.) • Q: Who (give names) bought gizmos from Dick? • Where to start? • Purchase uses pid, ssn, so must get them… Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

47. Last time: Complex RA Expressions • Scenario: • Purchase(pid, seller-ssn, buyer-ssn, etc.) • Person(ssn, name, etc.) • Product(pid, name, etc.) • Q: Who (give names) bought gizmos from Dick? • Where to start? • Purchase uses pid, ssn, so must get them… Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

48. seller-ssn=ssn pid=pid buyer-ssn=Person.ssn Complex RA Expressions Pname Person Purchase Person Product Pssn Ppid sname='Dick' sname='Gizmo' Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

49. Translation to SQL • We’re converting the tree on the last slide into SQL • The result of the query should be the names indicated above • One step at a time, we’ll make the query more complete, until we’ve translated the English-language description to an actual SQL query • We’ll also simplify the query when possible (the names of the people who bought gadgets from Dick) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005

50. Translation to SQL • Blue type = actual SQL • Black italics = description of subquery • Note: the subquery above consists of purchase records, except with the info describing the buyers attached • In the results, the column header for name will be 'buyer' SELECT DISTINCT name buyer FROM (the info, along with buyer names, for purchases of gadgets sold by Dick) Matthew P. Johnson, OCL4, CISDD CUNY, Sept 2005