Relational Algebra Handout project p formal definition: p A(r)={t[A]|te r} description: The project operation is a unary operation that returns its argument relation with certain attributes left out. Relational Algebra example: p branch-name (loan) SQL example: select branch-namefrom loanselect s formal definition: s F(r)={t|te r and t satisfies F}description: The select operation is a unary operation that returns its argument relation with certain tuples left out. Relational Algebra example: s branch-name ="Perryville"(loan) SQL example: select *from loanwhere branch-name= "Perryville"natural join | ´ | formal definition: r| ´ | s={t|($ ue r)($ ve s)(t[R]=u and t[S]=v)} description: The natural join is a binary operation that allows us to combine certain selections and a Cartesian product into one operation. Alternate expression: r| ´ | s=p rÇ s(s F(r´ s)) where F is a selection condition that indicates the tuple values under the common attributes of r and s are equal. Relational Algebra example: p customer-name, loan-numbr (borrower| ´ | loan) SQL example: select distinct customer-name,borrower.loan-numberfrom borrower,loanwhere borrower.number = loan.loan-numberrename r formal definition: r s(r) description: The rename operation is a unary operation that returns its argument with a different name. Normally the results of a relational-algebra expression do not have a name that we can use to refer to them. Relational Algebra example: p account.balance (s account.balance > d.balance(account´ r d(account))) SQL example: select distinct account.balancefrom account, account as dwhere account.balance > d.balanceintersection Ç formal definition: rÇ s={t|te r and te s}description: The intersection operation is a binary operation that returns a relation that contains all tuples that are contained in both argument relations. Alternate expression: rÇ s=r-(r-s)Relational Algebra example: loanÇ borrower SQL example: (select distinct customer-namefrom depositor)intersect (select distinctcustomer-name from borrower) union È formal definition: rÈ s={t|te r or te s}description: The union operation is a binary operation that returns a relation that contains all tuples that are contained in each of the argument relations. Relational Algebra example: loanÇ borrower SQL example: (select distinct *from depositor)intersect (select distinct* from borrower) difference - formal definition: r-s={t|te r and not(te s)} description: The difference operation is a binary operation that returns a relation that contains all those tuples in the first relation that are not present in the second relation. Relational Algebra example: loan - borrower SQL example: (select *from depositor) except all (select *from borrower)Cartesian product ´ formal definition: r´ s={t1.t2|te r and te s}where t1.t2 is the concatenation of tuples t1 and t2 to form a large tuple description: The Cartesian product is a binary operator that takes as input two relations and produces a elation on the schema that is the concatenation of the relation schemes of the inputs. The tuples in the Cartesian product are constructed by concatenating each tuple in the first input relation with each tuple in the second input relation. Relational Algebra example: borrower x loan SQL example: select *from branch, loandivision ¸ formal definition: r¸ s={t|(" ue s)(tue r)} description: The division operator takes as input two relations, called the dividend relation (r on schema R) and the divisor relation (s on schema S) such that all the attributes in S also appear in R and s is not empty. The output of the division operation is a relation on schema R with all the attributes common with S discarded. A tuple in t is put in the output of the operation if for all tuples us in s, the tuple tu is in r, where tu is a tuple constructed from tu and u by combining the individual values in these tuples in the proper order to from a tuple in r. Alternate expression: r¸ s= p R-S(r)-p R-S((p R-S(r)´ s)-r)Relational Algebra example: branch ¸ loan SQL example: No SQL equivalent assignment ¬ formal definition: r¬s description: The assignment operation is a unary operation that returns its argument with a new name. The argument can be the result of a relational-algebra expression. Relational Algebra example: new-branch ß branch SQL example: No SQL equivalent What is the selection operation?Select operation chooses the subset of tuples from the relation that satisfies the given condition mentioned in the syntax of selection. The selection operation is also known as horizontal partitioning since it partitions the table or relation horizontally. Notation: σ c(R)
Which is a unary operation in database?Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category. Numeric value is positive. Numeric value is negative. Returns the ones complement of the number.
What is selection in database?In relational terminology, selection is defined as taking the horizontal subset of rows of a single table that satisfies a particular condition. This kind of SELECT statement returns some of the rows and all the columns in a table.
Which of the following is a selection operation?Explanation: The fundamental operations are select, project, union, set difference, Cartesian product, and rename. 3. Which of the following is used to denote the selection operation in relational algebra? Explanation: The select operation selects tuples that satisfy a given predicate.
|