RELATIONAL ALGEBRASlide 2Database SchemeSAMPLE SCHEMAS AND INSTANCESSlide 5What is Relational Algebra?Slide 7Query languagesSEMANTICS OF THE SAMPLE RELATIONSSelection and ProjectionSlide 12Slide 13IntroductionIntroduction – cont’dSelectionExample 1:Example 2:Example 3Slide 20Selection - PropertiesProjectionProjection - ExampleSlide 24Slide 25SET OperationsUnion CompatibilityUnion Compatibility - ExamplesSlide 29UNION, SET DIFFERENCE & SET INTERSECTEXAMPLESlide 32Slide 33Slide 34Slide 35Union, Intersection, DifferenceSet-IntersectionExamplesSlide 39RENAME OPERATORRELATIONAL ALGEBRA INTRODUCTIONSELECT OPERATORSlide 43Slide 44Slide 45Slide 46Slide 47PROJECT OPERATORSlide 49Slide 50Slide 51Slide 52Slide 53CARTESIAN PRODUCTCARTESIAN PRODUCT (Cont…)Slide 56Slide 57Slide 58Slide 59Slide 60Slide 61Slide 62Slide 63Slide 64EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOINEXAMPLE JOINSlide 67Slide 68EQUALITY JOIN, NATURAL JOIN, JOIN, SEMI-JOIN (Cont…)RELATIONAL ALGEBRAProf. Sin-Min LEEDepartment of Computer Science•Relation schema–Named relation defined by a set of attribute and domain name pairs.•Relational database schema–Set of relation schemas, each with a distinct name.•Each tuple is distinct; there are no duplicate tuples.•Order of attributes has no significance.•Order of tuples has no significance, theoretically.•Relation name is distinct from all other relation names in relational schema.•Each cell of relation contains exactly one atomic (single) value.•Each attribute has a distinct name.•Values of an attribute are all from the same domain.•Each tuple is distinct; there are no duplicate tuples.•Order of attributes has no significance.•Order of tuples has no significance, theoretically.Database Scheme A relational database scheme, or schema, corresponds to a set of table definitions.Eg: product(p_id, name, category, description) supply(p_id, s_id, qnty_per_month) supplier(s_id, name, address, ph#)* remember the difference between a DB instance and a DB scheme.SAMPLE SCHEMAS AND INSTANCESThe Schemas:Sailors(sid: integer, sname: string, rating: integer, age: real)Boats(bid: integer, bname: string, color: string)Reserves(sid: integer, bid: integer, day: date) The Instances:What is Relational Algebra?Relational algebra is a procedural query language.It consists of the select, project, union, set difference, Cartesian product, and rename operations.Set intersection, division, natural join, and assignment combine the fundamental operations.SQL is based on relational algebra•Relational algebra and relational calculus are formal languages associated with the relational model.•Both are equivalent to one anotherIt is an abstract language. We use it to express the set of operations that any relational query language must perform.Two types of operations: 1.set-theoretic operations: tables are essentially sets of rows 2.native relational operations: focus on the structure of the rows Query languages are specialized languages for asking questions,or queries,that involve the data in database.What are the query languages ?Query languagesprocedural vs. non-proceduralcommercial languages have some of bothwe will study:relational algebra (which is procedural, i.e. tells you how to process a query)relational calculus (which is non-procedural i.e. tells what you want)SEMANTICS OF THE SAMPLE RELATIONSSailors: Entity set; lists the relevant properties of sailors.BoatsBoats: Entity set; lists the relevant properties of boats.Reserves: Relationship set: links sailors and boats by describing the boat number and date for which a sailor made a reservation.Example of the declarative sentences for which rows stand:Row 1: “Sailor ’22’ reserved boat number ‘101’ on 10/10/98”.Selection and ProjectionSelection Operator: σrating>8 (S2)Retrieves from the current instance of relation named S2 those rows where the value of the attribute ‘rating’ is greater than 8.Applying the above selection operator to the sample instance of S2 shown in figure 4.2 yields the relational instance on figure 4.4 as shown below:πconditionProjection Operator πsname,rating(S2)Retrieves from the current instance of the relation named S2 those columns whose names are ‘sname’ and ‘rating’.Applying the above operator to the sample instance of S2 shown in figure 4.2 yields the relational instance on figure 4.5 as shown below:N. B.: Note that the projection operator can produce duplicate rows in the resulting instance.- Projection Operator (cont’d)Similarly πage(S2) yields the following relational instance Note here the elimination of duplicatesSQL would yieldFor πage (S2):age35.055.035.035.0Introduction one of the two formal query languages of the relational modelcollection of operators for manipulating relationsOperators: two types of operators Set Operators: Union(),Intersection(), Difference(-), Cartesian Product (x)New Operators: Select (), Project (), Join (⋈)Introduction – cont’dA Relational Algebra Expression: a sequence of relational algebra operators and operands (relations), formed according to a set of rules.The result of evaluating a relational algebra expression is a relation.Selection Denoted by c(R)Selects the tuples (rows) from a relation R that satisfy a certain selection condition c.It is a unary operatorThe resulting relation has the same attributes as those in R.Example 1:SNO SNAME AGE STATES1 MIKE 21 ILS2 STEVE 20 LAS3 MARY 18 CAS4 MING 19 NYS5 OLGA 21 NYS:state=‘IL’(S)Example 2:CNOCNAMECREDITDEPTC1Database3 CSC2Statistics3 MATHC3 Tennis 1 SPORTSC4 Violin 4 MUSICC5 Golf 2 SPORTSC6 Piano 5 MUSICC:CREDIT 3(C)Example 3SNO CNO GradeS1 C1 90S1 C2 80S1 C3 75S1 C4 70S1 C5 100S1 C6 60S2 C1 90S2 C2 80S3 C2 90S4 C2 80S4 C4 85S4 C5 100E:SNO=‘S1’and CNO=‘C1’(E)Selection - PropertiesSelection Operator is commutative C1(C2 (R)) = C2(C1 (R)) The Selection is an unary operator, it cannot be used to select tuples from more than one relations.ProjectionDenoted by L(R), where L is list of attribute names and R is a relation name or some other relational algebra expression.The resulting relation has only those attributes of R specified in L.The projection is also an unary operation.PDuplicate rows are not permitted in relational algebra. Duplication is removed from the
View Full Document
Unlocking...