Relational Algebra Lecture 7

1. Relational AlgebraLecture 7 Presented By: Miss N. Nembhard

3. Relation Algebra Relational Algebra is : • the formal description of how a relational database operates • the mathematics which underpin SQL operations. Operators in relational algebra are not necessarily the same as SQL operators, even if they have the same name.

4. Terminology • Relation - a set of tuples. • Tuple - a collection of attributes which describe some real world entity. • Attribute - a real world role played by a named domain. • Domain - a set of atomic values. • Set - a mathematical definition for a collection of objects which contains no duplicates.

5. Operators - Retrieval There are two groups of operations: • Mathematical set theory based relations: UNION, INTERSECTION, DIFFERENCE, and CARTESIAN PRODUCT. • Special database operations: SELECT (not the same as SQL SELECT), PROJECT, and JOIN.

6. Query Languages • A language in which a user requests information from the database. • Usually on a higher level than that of standard programming languages.

7. Query Languages • Can be categorized as either procedural or non-procedural language. • Procedural - user instructs the system to a sequence of operations on the database to compute the desired result. • Non-procedural - the user describes the desired information without giving the specific procedure for obtaining the information.

8. Relational Algebra • Relational Algebra is a “pure” language because it is procedural whereas the tuple relational calculus and domain relational are non-procedural. • These languages lack the syntax of commercial languages, but illustrate the fundamental techniques for extracting data from a database.

9. Relational Algebra • A procedural query language. That consists of a set of operations that take one or two relations as input and produce a new relation as their result. • The fundamental operations in the relational algebra are: • select, project, union, set difference, Cartesian product, and rename.

10. Relational Algebra • There are several other operations : • set intersection • natural join • division • assignment

11. Fundamental Operations • The select, project, and rename operations are called unaryoperations, because they operate on one relation. • The other three operations (union, set difference, Cartesian product) operate on pairs of relations and are, therefore, called binary, operations.

12. Select Operation • Select operation selects tuples that satisfy a given predicate. • Use lower case Greek letter sigma () to denote selection. The predicate appears as a subscript to . The argument relation is in the parenthesis after the sign.

13. Select Operation SELECT is used to obtain a subset of the tuples of a relation that satisfy a select condition. For example, find all employees born after 1st Jan 1950: SELECT dob > ’01/JAN/1950’ (employee)

14. Project Operation • A unary operation that returns its argument relation, with certain attributes left out. • Since the relation is a set duplicate rows are eliminated. • Projection is denoted by the uppercase Greek letter pi ().

15. Project Operation The PROJECT operation is used to select a subset of the attributes of a relation by specifying the names of the required attributes. For example, to get a list of all employees surnames and employee numbers: PROJECT surname,empno (employee)

16. SELECT and PROJECT SELECT and PROJECT can be combined together. For example, to get a list of employee numbers for employees in department number 1:

17. Cartesian Product • The Cartesian product operation, denoted by a cross (x). • Allows us to combine information from two relations. • We write the Cartesian product of relations r1 and r2 as r1 x r2.

18. Cartesian Product The Cartesian Product is also an operator which works on two sets. It is sometimes called the CROSS PRODUCT or CROSS JOIN. It combines the tuples of one relation with all the tuples of the other relation.

19. Cartesian Product