DOC PREVIEW
SJSU CMPE 226 - Relational Algebra and SQL

This preview shows page 1-2-3-20-21-40-41-42 out of 42 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 42 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Database DesignPowerPoint PresentationSlide 3Two Different Query Languages for Relational Database (1)Two Different Query Languages for Relational Database (2)Relational Algebra OperatorsIntersectionUnionDifferenceProductProjectSelectRenameNatural JoinWhich Operators can be applied?How to read set of pointsRelation algebra operators (1)Relation algebra operators (2)Relation algebra operators (3)Relation algebra operators (4)Relation algebra operators (5)Relational Algebra Queries (1)Relational Algebra Queries (2)Relational Algebra Queries (3)Relational Algebra Queries (4)Relational Algebra Queries (5)Relational Algebra Queries (6)SQL (1)SQL (2)SQL (3)SQL: ExampleAS – keyword used to rename relations (1)AS – keyword used to rename relations (2)AS – keyword used to rename relations (3)Another ExampleAnother way of connecting SQL expressions is using the IN keyword.SQL with AggregationExampleGROUP BYGROUP BY: ExampleSolution for Exercise # 3.1Discussion Questions2003SJSU -- CmpE L7-S1 RA & SQLDatabase Design Dr. M.E. Fayad, ProfessorComputer Engineering Department, Room #283I College of EngineeringSan José State UniversityOne Washington SquareSan José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad2003SJSU – CmpE --- M.E. Fayad L7-S2 RA & SQL2Lesson 07:Relational Algebra and SQL2003SJSU – CmpE --- M.E. Fayad L7-S3 RA & SQL Lesson ObjectivesObjectives3 Understand Two Different Query Languages: Relational Algebra and SQL Learn how to deal with:  Relational Algebra Operators Relational Algebra Queries The Benefits of Query CompositionIllustrated Examples Explore SQL with examples2003SJSU – CmpE --- M.E. Fayad L7-S4 RA & SQLRelational Algebra Has a small set of well-defined operators that can be composed to form query expressions. It is a procedural language, because the sequence of operators and the operators themselves can be evaluated using well-defined procedures Relational Algebra is a useful theoretical language that serves to define more complex languages.4Two Different Query Languages for Relational Database (1)2003SJSU – CmpE --- M.E. Fayad L7-S5 RA & SQLStructured Query Language (SQL) It is a practical language that allows a high level expression of queries. A user of SQL does not need to think procedurally about queries But can rely on the meaning of higher-level keywords provided by SQL. However, most SQL queries can be translated to relational algebra queries to understand the precise meaning of a SQL query.5Two Different Query Languages for Relational Database (2)2003SJSU – CmpE --- M.E. Fayad L7-S6 RA & SQLRelational Algebra is a query language composed of a number of operators.Each take relations as arguments and return a single relation as result.6Relational Algebra Operators2003SJSU – CmpE --- M.E. Fayad L7-S7 RA & SQLThe intersection of two relations A and B, denoted A  BResults: the set of points belongs to both A and BThe intersection operator can be applied only to operands that have the same set and order of attributes.7Intersection2003SJSU – CmpE --- M.E. Fayad L7-S8 RA & SQLThe union of two relations A and B, denoted A  BResults: the set of points belongs to A or B or both.The union operator can be applied only to operands that have the same set and order of attributes.8Union2003SJSU – CmpE --- M.E. Fayad L7-S9 RA & SQLThe difference of two relations, denoted A \ BResults: the set of points belongs to A but do not belongs to B.The difference operator can be applied only to operands that have the same set and order of attributes.9Difference2003SJSU – CmpE --- M.E. Fayad L7-S10 RA & SQLThe product operator applied to an n-dimensional relation A and an m-dimensional relation B, denoted A x BResults: a relation that contains all (n + m) dimensional points whose first n components belong to A and last m components belong to B.The product operator can be applied only to operands that have no common attributes.10Product2003SJSU – CmpE --- M.E. Fayad L7-S11 RA & SQLThe project is used to reorder the columns of a relation or to eliminate some columns of a relation.The project operator from a relation A is denoted πL A, where L is a list of [l1, ….., lk] that specifies an ordering of a subset of the attributes of A.The project operator creates a new relation that contains in its ith column the column of A corresponding to attribute li.11Project2003SJSU – CmpE --- M.E. Fayad L7-S12 RA & SQLThe select operator is used to select a relation A those points that satisfy a certain logical formula F.The select operator has the form σF A. The logical formula F is a constraint formula.Constraint formula – Chapter 2 12Select2003SJSU – CmpE --- M.E. Fayad L7-S13 RA & SQLThe rename operator ρB(X1/Y1, …., Xn/Yn)A for any n ≥ 1, changes the name of relation A to B and changes the attribute Xi to Yi.If we only want to change the name of the relation, then the form ρBA can be used.13Rename2003SJSU – CmpE --- M.E. Fayad L7-S14 RA & SQLThe natural join operator applied to an n-dimensional relation A and an m-dimensional relation B that have k attributes in common is denoted A B.The natural join operator returns a relation that contains all (n + m – k)-dimensional points whose projection onto attributes of A belong to A and whose projection onto the attributes of B belong to B.14Natural Join2003SJSU – CmpE --- M.E. Fayad L7-S15 RA & SQL15Which Operators can be applied?A and BTwo sets of points: A(x, y) and B (x, y) are 2-dimensionalOperators that can be applied:IntersectionUnionDifference2003SJSU – CmpE --- M.E. Fayad L7-S16 RA & SQLExample: Let the following relations describe point sets:•A(x, y), B(x, y), J(x, y) 2D points in the plane•H(x, y, z), I(x, y, z) 3D points in space•F(z) line on the z-axis•K(y,z) 2D points in (y, z) plane16How to read set of points2003SJSU – CmpE --- M.E. Fayad L7-S17 RA & SQL5Relation algebra operators (1)A  B = CA  B = D A \ B = EA and B2003SJSU – CmpE --- M.E. Fayad L7-S18 RA & SQLRelation algebra operators (2)A  FLet F(z) be a relation that contains all points


View Full Document

SJSU CMPE 226 - Relational Algebra and SQL

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download Relational Algebra and SQL
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Relational Algebra and SQL and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Relational Algebra and SQL 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?