Database DesignSlide 2Slide 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 Questions© 2003-2006 M.E. FayadSJSU -- CmpEDatabase 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/~fayad© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S2 RA & SQL2Lesson 06:Relational Algebra and SQL© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-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 CompositionIllustrated Examples Explore SQL with examples© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S4 RA & SQLRelational 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)© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S5 RA & SQLStructured 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)© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S6 RA & SQLRelational 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 Operators© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S7 RA & SQLThe intersection of two relations A and B, denoted A BResults: the set of points belongs to both A and BThe intersection operator can be applied only to operands that have the same set and order of attributes.7Intersection© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S8 RA & SQLThe union of two relations A and B, denoted A BResults: 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.8Union© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S9 RA & SQLThe difference of two relations, denoted A \ BResults: 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.9Difference© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S10 RA & SQLThe product operator applied to an n-dimensional relation A and an m-dimensional relation B, denoted A x BResults: 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.10Product© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S11 RA & SQLThe 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.11Project© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S12 RA & SQLThe 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 12Select© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S13 RA & SQLThe 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.13Rename© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S14 RA & SQLThe 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 Join© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-S15 RA & SQL15Which Operators can be applied?A and BTwo sets of points: A(x, y) and B (x, y) are 2-dimensionalOperators that can be applied:IntersectionUnionDifference© 2003-2006 M.E. FayadSJSU – CmpE M.E. Fayad L6-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 points© 2003-2006 M.E. FayadSJSU – CmpE
View Full Document