Create Presentation
Download Presentation

Download Presentation
## Relational Algebra and My SQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Lecture 5 CS157B**Relational Algebra and My SQL Prof. Sin Min Lee Deparment of Computer Science San Jose State University**Functional Dependency**A, B R(….) , we say FD: AB 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: AR and not proper subset of A is a key.**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**Queries:**• Q1: AR (…) A is not a key • Q2: BR (…) B is not a key • Q3: CR (…) C is not a key • Q4: DR (…) Yes-D is a key • Q5: ABR (…) AB together cannot form the key • Q6: ACR (…) Yes- AC together cannot form the key • Q7: BCR (…) BC together cannot form the key**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”.)**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.**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.)**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?**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:**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.**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.**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.**Examples of Division A/B**B1 B2 B3 A/B1 A/B2 A/B3 A**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**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)**EXAMPLE JOIN**• Equality Join, (Emp Dept))) Dept EMP (dno)**EXAMPLE JOIN**• Natural Join, (Emp Dept))) Dept EMP (dno)**EXAMPLE JOIN**• Join, (Emp ρx(Emp)))) Dept EMP Salary > 5 * salary**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)**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**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**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**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.**2**Basic structure of an SQL query**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**I**General Structure SELECT ...... FROM ...... WHERE ...... SELECT[ALL / DISTINCT] expr1 [AScol1], expr2 [AScol2] ; FROMtablenameWHEREcondition**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.**3.2 SQL**SELECT a1, ..., an FROM R1, R2, …, Rm WHERE Con1, …,Conk This means: πa1, ..., an( σ Con1( … (σ Conk ( R1× R2 × … ×Rm))…))**Example:**Find the SSN and tax for each person. SELECT SSN, Tax FROM Taxrecord, Taxtable WHERE wages + interest + capital_gain = income**AS – keyword used to rename relations**Two SQL expressions can be combined by: INTERSECT UNION MINUS– set difference**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**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)**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 )**Another way of connecting SQL expressions**is using the IN keyword. SELECT …….. FROM …….. WHERE a IN ( SELECT b FROM ….. WHERE ….. )**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)**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 “**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}**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**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.**I**Result General Structure Before using SQL, open the student file: USE student eg. 1List all the student records. SELECT * FROM student**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"**I**Result General Structure eg. 2List the names and house code of 1A students.**I**Result General Structure eg. 3List the residential district of the Red House members. SELECT DISTINCT dcode FROM student ; WHERE hcode="R"