RelationalAlgebra-DepartmentofComputerScience.pptVIP

Lecture 4 Relational Algebra Relational AlgebraCh. 7.4 – 7.6 John Ortiz Relational Query Languages Query languages: allow manipulation and retrieval of data from a database. Relational QLs are simple powerful. Strong formal foundation based on logic. Allows for much optimization. Query languages != programming languages! Not intended for complex calculations. Support easy, efficient access to large data sets. Formal Relational Query Languages Two mathematical query languages form the basis for “real” languages (e.g., SQL), and for implementation: Relational algebra: more operational, very useful for understanding meanings of queries and for representing execution plans. Relational calculus: lets users describe what they want, rather than how to compute it. Preliminaries A query is applied to relation instances, and the result of a query is also a relation instance. Schemas of input result relations are fixed (determined by relations query language constructs). A query is specified against schemas (regardless of instances). Attributes may be referenced either by names or by positions (two notation systems). Relational Algebra Basic Operations: Selection (?): choose a subset of rows. Projection (?): choose a subset of columns. Cross Product (?): Combine two tables. Union (?): unique tuples from either table. Set difference (?): tuples in R1 not in R2. Renaming (?): change names of tables columns Additional Operations (for convenience): Intersection, joins (very useful), division, outer joins, aggregate functions, etc. Selection Format: ?selection-condition(R). Choose tuples that satisfy the selection condition. Result has identical schema as the input. ?Major = ‘CS’ (Students) Projection Format: ?attribute-list(R). Retain only those columns in the attribute-list. Result must eliminate duplicates. ?Major(Students) Cross Product Format: R1 ? R2. Each row of R1 is paired with each row of R2. Result schema consists of all