Relational AlgebraIntroductionIntroduction – cont’dSelectionExample 1:Example 2:Example 3Selection - PropertiesProjectionProjection - ExampleSlide 11Slide 12SET OperationsUnion CompatibilityUnion Compatibility - ExamplesUnion, Intersection, DifferenceExamplesSlide 18Cartesian ProductSlide 20QuestionA Sample DB: student-course-enrollment01/13/191Relational AlgebraRelational AlgebraEugene ShengComputer Science DepartmentNIU01/13/192Introduction Introduction 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 (⋈)01/13/193Introduction – cont’dIntroduction – 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.01/13/194Selection 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.01/13/195Example 1:SNO SNAME AGE STATES1 MIKE 21 ILS2 STEVE 20 LAS3 MARY 18 CAS4 MING 19 NYS5 OLGA 21 NYS:state=‘IL’(S)01/13/196Example 2:CNO CNAME CREDIT DEPTC1 Database 3 CSC2 Statistics 3 MATHC3 Tennis 1 SPORTSC4 Violin 4 MUSICC5 Golf 2 SPORTSC6 Piano 5 MUSICC:CREDIT 3(C)01/13/197Example 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)01/13/198Selection - PropertiesSelection - 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.01/13/199ProjectionProjectionDenoted 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.8Duplication is removed from the result.01/13/1910Projection - ExampleProjection - ExampleExample 1: STATE (S)SNO SNAME AGE STATES1 MIKE 21 ILS2 STEVE 20 LAS3 MARY 18 CAS4 MING 19 NYS5 OLGA 21 NYSTATEILLACANY01/13/1911Projection - ExampleProjection - ExampleExample 2: CNAME, DEPT(C)CNO CNAME CREDIT DEPTC1 Database 3 CSC2 Statistics 3 MATHC3 Tennis 1 SPORTSC4 Violin 4 MUSICC5 Golf 2 SPORTSC6 Piano 5 MUSICCNAME DEPTDatabase CSStatistics MATHTennis SPORTSViolin MUSICGolf SPORTSPiano MUSIC01/13/1912Projection - ExampleProjection - ExampleExample 3: S#(STATE=‘NY'(S))SNO SNAME AGE STATES1 MIKE 21 ILS2 STEVE 20 LAS3 MARY 18 CAS4 MING 19 NYS5 OLGA 21 NYSNOS4S501/13/1913SET OperationsSET OperationsUNION: R1 R2INTERSECTION: R1 R2DIFFERENCE: R1 - R2CARTESIAN PRODUCT: R1 R201/13/1914Union CompatibilityUnion CompatibilityFor operators , , -, the operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) must have the same number of attributes, and the domains of the corresponding attributes must be compatible; that is, dom(Ai)=dom(Bi) for i=1,2,...,n.The resulting relation for , , or - has the same attribute names as the first operand relation R1 (by convention).01/13/1915Union Compatibility - ExamplesUnion Compatibility - ExamplesAre S(SNO, SNAME, AGE, STATE) and C(CNO, CNAME, CREDIT, DEPT) union compatible?Are S(S#, SNAME, AGE, STATE) and C(CNO, CNAME, CREDIT_HOURS, DEPT_NAME) union compatible?01/13/1916Union, Intersection, DifferenceUnion, Intersection, DifferenceT= R U S : A tuple t is in relation T if and only if t is in relation R or t is in relation ST = R S: A tuple t is in relation T if and only if t is in both relations R and ST= R - S :A tuple t is in relation T if and only if t is in R but not in S01/13/1917ExamplesExamplesA1 A21 Red3 White4 greenB1 B23 White2 BlueR S01/13/1918ExamplesExamplesA1 A21 Red3 White4 Green2 BlueA1 A23 WhiteR SR SS - RB1 B22 BlueA1 A21 Red4 GreenR - S01/13/1919Cartesian ProductCartesian ProductR(A1, A2, ..., Am) and S(B1, B2, ... , Bn)T(A1, A2, ... , Am, B1, B2, ..., Bn) = R(A1, A2, ..., Am) X S(B1, B2, ..., Bn)A tuple t is in T if and only if t[A1,A2, ... ,Am] is in R and t[B1, B2, ..., Bn] is in S.- If R has N1 tuples and S has N2 tuples, then T will have N1*N2 tuples.01/13/1920Cartesian ProductCartesian ProductA Ba1 2a2 4RSRx SC D E4 d1 e13 d2 e15 d3 e2A B C D Ea1 2 4 d1 e1a1 2 3 d2 e1a1 2 5 d3 e2a2 4 4 d1 e1a2 4 3 d2 e1a2 4 5 d3 e201/13/1921QuestionQuestionCompute S x CCompute S x E01/13/1922A Sample DB: A Sample DB: student-course-enrollmentstudent-course-enrollmentSNO SNAME AGE STATES1 MIKE 21 ILS2 STEVE 20 LAS3 MARY 18 CAS4 MING 19 NYS5 OLGA 21 NYCNO CNAME CREDIT DEPTC1 Database 3 CSC2 Statistics 3 MATHC3 Tennis 1 SPORTSC4 Violin 4 MUSICC5 Golf 2 SPORTSC6 Piano 5 MUSICSNO 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
View Full Document
Unlocking...