Relational algebra tuple). More formally, R × S is defined as follows: tain To ob- tributes of each tuple) to be retrieved. This specifies the specific subset of columns (at- . the cardinalities of its factors, i.e., | R × S | = | R | × | S |. The cardinality of the Cartesian product is the product of a set of 2-tuples, each containing an n -tuple and an m - names tuples (whereas basic set theory would have prescribed with a set of m -tuples yields a set of “flattened” ( n + m )- ation. That is, the Cartesian product of a set of n -tuples considered to be “shallow” for the purposes of the oper- In addition, the Cartesian product is defined differently involved must have disjoint headers—that is, they must For the Cartesian product to be defined, the two relations union-compatible. the and intersection can be defined in terms of set difference, written attributes for each unique entry in addressBook. contactPhoneNumber and contactName the only sult of that projection would be a relation which contains The re- be phone might projection the book, address an from numbers the two relations involved in set intersection must also be 1 volved must be union-compatible —that is, the two rela- into what is called first normal form (1NF). Typically a ularly query languages for such databases, chief among formed in the where statement in SQL, which is where (keeping only some columns). Set operations are per- Relational algebra received little attention outside of (keeping only some rows of a table) and the projection bra can be performed which are the set operations (such and tables), the main operations of the relational alge- Once the data is normalized and in sets of data (entities known as a record. mary key uniquely identifies an instance of an entity, also tionships between data entities (with its elements). A pri- logical data model documents and standardizes the rela- relational model of data in 1970. Codd proposed such ized. By doing this the data is organized or normalized The main application of relational algebra is providing an algebra as a basis for database query languages. (See constraints to these operators. Cartesian product from set theory, but adds additional IBM, is a family of algebra with a well-founded semantics used for modelling the data stored in relational databases, and defining queries on it. difference . To organize the data, first the redundant data and repeat- selection , the projection , the Cartesian product (also called Five primitive operators of Codd’s algebra are the ing groups of data are removed, which we call normal- one set of data is related to another set of data. Not to be confused with Relation algebra. tions must have the same set of attributes. Because set Relational algebra , first described by E.F. Codd while at not have a common attribute name. from the one in set theory in the sense that tuples are R × S = {( r 1 , r 2 , ..., rn , s 1 , s 2 , ..., sm ) | ( r 1 , r 2 , as union, intersection, and cartesian product), selection ..., rn ) ∈ R , ( s 1 , s 2 , ..., sm ) ∈ S } 1.2 Projection (π) a theoretical foundation for relational databases, partic- Main article: Projection (relational algebra) which is SQL. A projection is a unary operation written as π a 1 ,...,a n ( R ) 1 Introduction where a 1 , . . . , a n is a set of attribute names. The result of such projection is defined as the set that is obtained when all tuples in R are restricted to the set { a 1 , . . . , a n } pure mathematics until the publication of E.F. Codd's section Implementations.) π contactPhoneNumber contactName, ( addressBook ) . the cross product or cross join ), the set union , and the set 1.1 Set operators 1.3 Selection ( σ ) The relational algebra uses set union, set difference, and Main article: Selection (relational algebra) A generalized selection is a unary operation written as σ ϕ ( R ) where φ is a propositional formula that consists of For set union and set difference, the two relations in-
2 foreign key holds between attributes with the same name. straint is omitted, and R and S have no common attributes, must have at least one common attribute, but if this con- fined as follows: More formally the semantics of the natural join are de- these columns before we take the natural join. Such a join Dept . manager to Employee . Name then we have to rename If this is not the case such as in the foreign key from with their departments. Note that this works because the uct. ural join of Employee and Dept combines all employees Employee . DeptName to Dept . DeptName and then the nat- 2 particular, natural join allows the combination of rela- ances must always be substituted by the same value. In that variable stands for the same thing and both appear- each of two predicates that are connected by AND, then AND. Note carefully that if the same variable appears in then the natural join becomes exactly the Cartesian prod- The natural join can be simulated with Codd’s primitives The natural join is arguably one of the most important a table with all the possible options. When using a con- eral form of join operator, which is the θ-join (or theta- shared attributes then it is convenient to have a more gen- the combination condition is not simply the equality of If we want to combine tuples from two relations where ternatively (Price) itself. then the condition may be specified as Price=Price or al- dition where the attributes are equal, for example Price, spend more money for the boat than for the car. The θ- neither in R nor in S . In a first step we can now rename wants to buy a car and a boat, but she does not want to and boats and their respective prices. Suppose a customer Consider tables Car and Boat which list models of cars tributes: Finally we take a projection to get rid of the renamed at- that are to be joined: Then we take the Cartesian product and select the tuples the common attribute names in S : operators since it is the relational counterpart of logical in the above example a foreign key probably holds from join). The θ-join is a binary operator that is written as To obtain a listing of all friends or business associates rename the attribute of a relation or the relation itself. To rename the 'isFriend' attribute to 'isBusinessContact' be used. In Codd’s 1970 paper, selection is called restriction. [1] Contact is true. unique record where isFriend is true or where isBusiness- the result is identical to R except that the b attribute in all “Natural join” redirects here. For the SQL implementa- in an address book, the selection might be written as sult would be a relation containing every attribute of every tuples is renamed to an a attribute. This is simply used to and their natural join: their join as shown above, projected on all but the com- For example, the composition of Employee and Dept is JOINS AND JOIN-LIKE OPERATORS For an example consider the tables Employee and Dept and S that are equal on their common attribute names. atoms as allowed in the normal selection and the logical natural join is the set of all combinations of tuples in R operators ∧ (and), ∨ (or) and ¬ (negation). This selection selects all those tuples in R for which φ holds. is sometimes also referred to as an equijoin (see θ -join). σ true = isFriend ∨ true = isBusinessContact ( addressBook ) . The re- ◁ S = { t ∪ s | t ∈ R ∧ s ∈ S ∧ Fun ( t ∪ s ) } R ▷ where Fun is a predicate that is true for a relation r if and 1.4 Rename ( ρ ) only if r is a function. It is usually required that R and S Main article: Rename (relational algebra) A rename is a unary operation written as ρ a / b ( R ) where as follows. Assume that c 1 ,..., cm are the attribute names common to R and S , r 1 ,..., rn are the attribute names unique to R and s 1 ,..., sk are the attribute unique to S . Furthermore assume that the attribute names x 1 ,..., xm are in a relation, ρ isFriend / isBusinessContact ( addressBook ) might 2 Joins and join-like operators T = ρ x 1 / c 1 ,...,x m / c m ( S ) = ρ x 1 / c 1 ( ρ x 2 / c 2 ( . . . ρ x m / c m ( S ) . . . )) 2.1 Natural join (⋈) tion, see Natural join (SQL). P = σ c 1 = x 1 ,...,c m = x m ( R × T ) = σ c 1 = x 1 ( σ c 2 = x 2 ( . . . σ c m = x m ( R × T ) . . . )) Natural join ( ▷ ◁ ) is a binary operator that is written as ( R ▷ ◁ S ) where R and S are relations. [2] The result of the U = π r 1 ,...,r n ,c 1 ,...,c m ,s 1 ,...,s k ( P ) This can also be used to define composition of relations. 2.2 θ -join and equijoin mon attribute DeptName . In category theory, the join is precisely the fiber product. join ( ▷ ◁ θ) on the relation CarPrice ≥ BoatPrice produces tions that are associated by a foreign key. For example,
Recommend
More recommend
