1 / 80

800 likes | 935 Views

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.

Download Presentation
## Relational Algebra and My SQL

**An Image/Link below is provided (as is) to download presentation**
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.
Content is provided to you AS IS for your information and personal use only.
Download presentation by click this link.
While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
During download, if you can't get a presentation, the file might be deleted by the publisher.

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"

More Related