Relational Algebra and My SQL

1. Lecture 5 CS157B Relational Algebra and My SQL Prof. Sin Min Lee Deparment of Computer Science San Jose State University

2. Functional Dependency A, B  R(….) , we say FD: AB if • For all t1, t2 element of R if t1[A] = t2[A] => t1[B] = t2[B] Definition: A R(….) is a key if FD: A R Definition: A R(….) is a candidate key if FD: AR and not proper subset of A is a key.

3. 4. Find all the candidate keys for the following table ( Mid1 Study Guide) • R(A B C D) • 1 2 3 4 • 2 2 3 5 • 3 2 5 1 • 1 2 5 6 • S (A B C D) • 1 2 3 4 • 2 2 3 5 • 3 2 5 1 • 1 2 5 6

4. Queries: • Q1: AR (…) A is not a key • Q2: BR (…) B is not a key • Q3: CR (…) C is not a key • Q4: DR (…) Yes-D is a key • Q5: ABR (…) AB together cannot form the key • Q6: ACR (…) Yes- AC together cannot form the key • Q7: BCR (…) BC together cannot form the key

5. Relational Algebra • Basic operations: • Selection ( ) Selects a subset of rows from relation. • Projection ( ) Deletes unwanted columns from relation. • Cross-product( ) Allows us to combine two relations. • Set-difference ( ) Tuples in reln. 1, but not in reln. 2. • Union( ) Tuples in reln. 1 and in reln. 2. • Additional operations: • Intersection, join, division, renaming: Not essential, but (very!) useful. • Since each operation returns a relation, operationscan be composed! (Algebra is “closed”.)

6. Projection • Deletes attributes that are not in projection list. • Schema of result contains exactly the fields in the projection list, with the same names that they had in the (only) input relation. • Projection operator has to eliminate duplicates! (Why??, what are the consequences?) • Note: real systems typically don’t do duplicate elimination unless the user explicitly asks for it.

7. Selection • Selects rows that satisfy selection condition. • Schema of result identical to schema of (only) input relation. • Result relation can be the input for another relational algebra operation! (Operatorcomposition.)

8. Union, Intersection, Set-Difference • All of these operations take two input relations, which must be union-compatible: • Same number of fields. • `Corresponding’ fields have the same type. • What is the schema of result?

9. Cross-Product • Each row of S1 is paired with each row of R1. • Result schema has one field per field of S1 and R1, with field names `inherited’ if possible. • Conflict: Both S1 and R1 have a field called sid. • Renaming operator:

10. Joins • Condition Join: • Result schema same as that of cross-product. • Fewer tuples than cross-product. Filters tuples not satisfying the join condition. • Sometimes called a theta-join.

11. Joins • Equi-Join: A special case of condition join where the condition c contains only equalities. • Result schema similar to cross-product, but only one copy of fields for which equality is specified. • Natural Join: Equijoin on all common fields.

12. Division • Not supported as a primitive operator, but useful for expressing queries like: Find sailors who have reserved allboats. • Precondition: in A/B, the attributes in B must be included in the schema for A. Also, the result has attributes A-B. • SALES(supId, prodId); • PRODUCTS(prodId); • Relations SALES and PRODUCTS must be built using projections. • SALES/PRODUCTS: the ids of the suppliers supplying ALL products.

13. Examples of Division A/B B1 B2 B3 A/B1 A/B2 A/B3 A

14. Expressing A/B Using Basic Operators • Division is not essential op; just a useful shorthand. • (Also true of joins, but joins are so common that systems implement joins specially. Division is NOT implemented in SQL). • Idea: For SALES/PRODUCTS, compute all products such that there exists at least one supplier not supplying it. • x value is disqualified if by attaching y value from B, we obtain an xy tuple that is not in A. The answer is sid(Sales) - A

17. EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN • Equality join connects tuples from two relations that match on certain attributes. The specified joining columns are kept in the resulting relation. • ∏name(бdname=‘toy’(Emp Dept))) • Natural join connects tuples from two relations that match on the specified common attributes • ∏name(бdname=‘toy’(Emp Dept))) • How is an equality join between Emp and Dept using dno different than a natural join between Emp and Dept using dno? • Equality join: SS#, name, age, salary, Emp.dno, Dept.dno, … • Natural join: SS#, name, age, salary, dno, dname, … • Join is similar to equality join using different comparison operators • A S op = {=, ≠, ≤, ≥, <, >} att op att (dno) (dno)

18. EXAMPLE JOIN • Equality Join, (Emp Dept))) Dept EMP (dno)

19. EXAMPLE JOIN • Natural Join, (Emp Dept))) Dept EMP (dno)

20. EXAMPLE JOIN • Join, (Emp ρx(Emp)))) Dept EMP Salary > 5 * salary

21. EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN (Cont…) • Example: retrieve the name of employees who earn more than Joe: • ∏name(Emp (sal>x.sal)бname=‘Joe’(ρ x(Emp))) • Semi-Join selects the columns of one relation that joins with another. It is equivalent to a join followed by a projection: • Emp (dno)Dept ≡∏SS#, name, age, salary, dno(Emp Dept)

24. JOIN OPERATORS Condition Joins: - Defined as a cross-product followed by a selection: R ⋈cS = σc(R  S) (⋈ is called the bow-tie) where c is the condition. - Example: Given the sample relational instances S1 and R1 The condition join S ⋈S1.sid<R1.sid R1 yields

25. JOIN OPERATORS Condition Joins: - Defined as a cross-product followed by a selection: R ⋈cS = σc(R  S) (⋈ is called the bow-tie) where c is the condition. - Example: Given the sample relational instances S1 and R1 The condition join S ⋈S1.sid<R1.sid R1 yields

29. Equijoin: Special case of the condition join where the join condition consists solely of equalities between two fields in R and S connected by the logical AND operator (∧). Example: Given the two sample relational instances S1 and R1 The operator S1 R.sid=Ssid R1 yields

30. SQL • SQL (Structured Query Language) is the standard language for commercial DBMSs • SEQUEL (Structured English QUEry Language)was originally defined by IBM for System R • standardization of SQL began in the 80s • current standard is SQL-99 • SQL is more than a query language it includes a DDL, DML and administration commands • SQL is an example of a transform-oriented language. • A language designed to use relations to transform inputs into required outputs.

31. 2 Basic structure of an SQL query

32. 2 The Situation:Student Particulars • fieldtypewidthcontents • id numeric 4 student id number • name character 10 name • dob date 8 date of birth • sex character 1 sex: M / F • class character 2 class • hcode character 1 house code: R, Y, B, G • dcode character 3 district code • remission logical 1 fee remission • mtest numeric 2 Math test score

33. I General Structure SELECT ...... FROM ...... WHERE ...... SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition

34. I General Structure SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition • The query will select rows from the source tablename and output the result in table form. • Expressions expr1, expr2 can be : • (1) a column, or • (2) an expression of functions and fields. • And col1, col2 are their corresponding column names in the output table.

35. 3.2 SQL SELECT a1, ..., an FROM R1, R2, …, Rm WHERE Con1, …,Conk This means: πa1, ..., an( σ Con1( … (σ Conk ( R1× R2 × … ×Rm))…))

36. Example: Find the SSN and tax for each person. SELECT SSN, Tax FROM Taxrecord, Taxtable WHERE wages + interest + capital_gain = income

37. AS – keyword used to rename relations Two SQL expressions can be combined by: INTERSECT UNION MINUS– set difference

38. Example: Find the names of the streets that intersect. SELECT S.NAME, T.NAME FROM Streets AS S, Streets AS T WHERE S.X = T.X and S.Y = T.Y

39. Example: Assume we have the relations: Broadcast ( Radio, X , Y ) Town ( Name, X, Y ) Find the parts of Lincoln, NE that can be reached by at least one Radio station. (SELECT X, Y FROM Town WHERE Name = “Lincoln”) INTERSECT (SELECT X, Y FROM Broadcast)

40. Example: Find the SSN and tax for each person. πSSN,Taxσwages+interest+capital_gain = income Taxrecord × Taxtable Example: Find the area of Lincoln reached by a radio station. ( πX,Y ( σName=“Lincoln” Town ) )  ( πX,Y Broadcast )

41. Another way of connecting SQL expressions is using the IN keyword. SELECT …….. FROM …….. WHERE a IN ( SELECT b FROM ….. WHERE ….. )

42. SQL with aggregation – SELECT aggregate_function FROM ……. WHERE …… aggregate_function– Max (c1a1 + ……..+ cnan) where ai are attributes Min (c1a1 + ……..+ cnan) and ci are constants Sum(a) where a is an attribute that is Avg(a) constant in each constraint tuple Count(a)

43. Example: Package(Serial_No, From, Destination, Weight) Postage (Weight , Fee) Find the total postage of all packages sent from Omaha. SELECT Sum(Fee) FROM Package, Postage WHERE Package.Weight = Postage.Weight AND Package.From = “ Omaha “

44. GROUP BY– SELECT a1, …, an, aggregate_function FROM ….. WHERE …… GROUP BY a1, ..., ak • Evaluates basic SQL query • Groups the tuples according to different values of a1,..,ak • Applies the aggregate function to each group separately • {a1, …, ak}  {a1, …, an}

45. Example: Find the total postage sent out from each city. SELECT Package.From, Sum(Postage.Fee) FROM Package, Postage WHERE Package.Weight = Postage.Weight GROUP BY Package.From

46. I General Structure SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition • DISTINCT will eliminate duplication in the output while ALL will keep all duplicated rows. • condition can be : • (1) an inequality, or • (2) a string comparison • using logical operators AND, OR, NOT.

47. I Result General Structure Before using SQL, open the student file: USE student eg. 1List all the student records. SELECT * FROM student

48. I Class Class  1A 1A  1A 1A class="1A"  1A 1A  1B 1B  1B 1B : : General Structure eg. 2List the names and house code of 1A students. SELECT name, hcode, class FROM student ; WHERE class="1A"

49. I Result General Structure eg. 2List the names and house code of 1A students.

50. I Result General Structure eg. 3List the residential district of the Red House members. SELECT DISTINCT dcode FROM student ; WHERE hcode="R"