Fundamental of databaseChapter 5Chapter FiveRelational Algebra and Calculus4.1 Relational AlgebraRelational Algebrais a procedural query language that consists of a set of operations that takeone or two relations as input and produce a new relation as a result. The algebra operationsenable a user to retrieve specific request on a relational model. The operations that produce anew relation can be further manipulated using operations of the relation algebra.The sequence of the relational algebra that produces new relation forms arelational algebraexpression, whose result will also be a relation that represents the result of a database query (orretrieval request).The core relational algebra that has traditionally been thought of as the relational algebra consistsof the Fundamental operations that can be grouped into two based on the number of relationoperands of the operator. These are:Unary OperatorsSelection(σ)Selects a subset of rows from a relation.Projection (π)Select a subset of columns from a relation.Renaming:assigning intermediate name for a single operationBinary OperatorsCross-Product ( x )Allows us to combine two relations.Set-Difference ( - )Tuples in relation1, but not in relation2.Union (∪)Tuples in relation1 or in relation2.Additional OperationsIntersection (∩)Tuples in relation1 and in relation2JoinTuples joined from two relations based on a conditionAssignmentDivisionSample table used to illustrate different kinds of relational operations. The relation containsinformation about employees, IT skills they have and the school where they attend each skill.1
Fundamental of databaseChapter 5SelectionSelects subset of tuples/rows in a relation that satisfyselection condition.Selection operation is a unary operator (it is applied to a single relation)The Selection operation is applied to each tuple individuallyThe degree of the resulting relation is the same as the original relation but thecardinality (no. of tuples) is less than or equal to the original relation.The Selection operator is commutative.Set of conditions can be combined using Boolean operations (∧(AND),∨(OR), and~(NOT))No duplicates in result!Allows comparisons using =, =,<,≤,>,≥in the selection predicate.Resultrelation can be theinputfor another relational algebra operation! (Operatorcomposition.)It is a filter that keeps only those tuples that satisfy a qualifying condition (thosesatisfying the condition are selected while others are discarded.)Notation:σ<Selection Condition><Relation Name>Example: Find all Employees with skill type of Database.σ<SkillType =”Database”>(Employee)This query will extract every tuple from a relation called Employee with all the attributes wherethe SkillType attribute with a value of “Database”.The resulting relation will be the following.