relational algebra cs420. relation a relation is a two dimensional table that rows contain data...
TRANSCRIPT
![Page 1: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/1.jpg)
Relational AlgebraCS420
![Page 2: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/2.jpg)
RelationA relation is a two dimensional table that
Rows contain data about an entityColumns contains data about attributes of the entityCells of the table hold a single valueAll entries in a column are of the same kindEach column has a unique nameThe order of the columns is unimportant The order of the rows is unimportant No two rows may be identical
![Page 3: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/3.jpg)
Example of Relations
![Page 4: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/4.jpg)
Relational AlgebraDefines operations that work on relations to achieve the desired resultsThe exercise allows people to better understand the database and SQLIt is closed, meaning the operands are relations and so is the result It is an example of DML (Data Manipulation Language)
![Page 5: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/5.jpg)
The Seven Relational Operations
UnionDifferenceIntersectionProductSelectionProjectionJoin
![Page 6: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/6.jpg)
UnionThe union of two relations A and B is formed by adding the rows from one relation to those of a second relation to produce a third relation with duplications removedIt is denoted as A+B.
![Page 7: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/7.jpg)
Union CompatibleTwo relations are union compatible if
The two relations have the same number of columnsThe corresponding columns have the same domain
![Page 8: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/8.jpg)
Difference The Difference of two relations A and B contains all the rows in A but not in B.A and B must be union compatibleIt is denoted as A – BThe difference of a-b on the right is cA – B B - A
(c)
![Page 9: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/9.jpg)
IntersectionThe Intersection of two relations A and B contains all the rows in both A and B.A and B must be union compatibleIt is denoted as A Intersection BThe Intersection of A-B on the right is c
(c)
![Page 10: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/10.jpg)
ProductThe Product of two relations A and B contains the concatenation of every row in A with every row in B. It is denoted as A X BIf there are 100 2K rows in A and 5000 5K rows in B, the product of A and B is a relation of 100X5000 rows of (2K+5K) recordsA and B do not need to be union compatible
![Page 11: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/11.jpg)
Product Example
A: STUDENT
B: ENROLLMENTC: PRODUCT of A and B
![Page 12: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/12.jpg)
ProjectionThe Project operation is applied on a single relation.It selects specified attributes from a relation with duplications removedThe example on the right is a projection on the STUDENT relation’s major and GradeLevel attributes, represented as STUDENT[Major, GradeLevel]Notice the removal of duplicated rows as the result of projection
A: STUDENT
![Page 13: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/13.jpg)
SelectionThe Selection operation is applied on a single relation.It selects specified rows from a relation based on the given conditionThe examples on the right are the results of (a) STUDENT where major = ‘MATH’(b) STUDENT where GradeLevel = ‘JR”
A: STUDENT
![Page 14: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/14.jpg)
JoinThe Join operation is a Product followed by a SelectionIt is this concept of JOIN that connect rows in two different relations together There are two types of joins: equijoin and natural join
![Page 15: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/15.jpg)
Join Examples
A: STUDENT B: ENROLLMENT
Equijoin
Natural Join
STUDENT Join ENROLLMENT on SID = Student-Number
![Page 16: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/16.jpg)
Examples Of Relational Algebra
The relations on the right will be used in the examples
![Page 17: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/17.jpg)
Examples Of Relational Algebra
1. What are the names used by students?2. What are the student numbers of all students
enrolled in a class?3. What are the student numbers of all students not
enrolled in a class?4. What are the student numbers of all students
enrolled in ‘BD445’?5. What are the student name of all students enrolled in
‘BD445’?6. What are the names and meeting times of ‘PARKS
classes7. What are the names of the students who have a
class at the same time as ‘BAKERs’
![Page 18: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/18.jpg)
Example (1)What are the names used by students
This is an example of Projection, just about the simplest operation
STUDENT[Name]
![Page 19: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/19.jpg)
Example (2)What are the student numbers of all
students enrolled in a class?
This is another example of Projection
ENROLLMENT[StudentNumber]
![Page 20: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/20.jpg)
Example (3)What are the student numbers of all
students not enrolled in a class?
This needs a little think …We know all the student IDs and IDs of all
students taking a class, right?The Difference is the answer
STUDENT[SID] - ENROLLMENT[StudentNumber]
![Page 21: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/21.jpg)
Example (4)What are the student numbers of all students
enrolled in ‘BD445’?
This is an example of Selection because only a few records satisfies the condition
(ENROLLMENT where ClassName=‘BD445’ ) [StudentNumber]
![Page 22: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/22.jpg)
Example (5)What are the student names of all students enrolled
in ‘BD445’?
The student names are in STUDENT relationThe information about who is taking which class is in
the ENROLLMENT relationHow to put the two together?
JOIN
STUDENT Join ENROLLMENT on SID=StudentNumber where ClassName = ‘BD445’[STUDENT.NAME]
![Page 23: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/23.jpg)
Example (6)What are the names and meeting times of ‘PARKS’
classesAgain, JOIN, JOIN, and more JOINTwo possible approaches
1. Select as much as one can at the earliest possible time (STUDENT where name = ‘PARKS’ Join ENROLLMENT on SID =
StudentNumber ) Join CLASS on ClassName = CLASS.NAME [CLASS.NAME, Time]
2. Join all then selectSTUDENT Join ENROLLMENT on SID = StudentNumber Join CLASS
on ClassName = CLASS.NAME where STUDENT.name = ‘PARKS’ [CLASS.NAME, Time]
Which is a better solution?First one! Why?
![Page 24: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/24.jpg)
Example (7)What are the names of the students
BAKER’s classmates.Translate: List the names of all students
who take at least one class with a student who’s name is BAKER.
This is a hard one. First, there are two BAKERs. Second, how
do we express this “taking the same class?”
![Page 25: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/25.jpg)
Example (7 - 2)Let’s try this one first: List the student numbers of the students taking a class with the student whose number is 200.
ENROLLEMENT where studentNumber = 200 JOIN ENROLLMENT as E2 on ENROLLMENT.ClassName = E2.ClassName [E2.StudentNumber]
![Page 26: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/26.jpg)
Example (7-3)
ENROLLMENT ENROLLMENT as E2
![Page 27: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/27.jpg)
Example (7-4)
JONESRYE
S
E
E2
S2
![Page 28: Relational Algebra CS420. Relation A relation is a two dimensional table that Rows contain data about an entity Columns contains data about attributes](https://reader035.vdocuments.us/reader035/viewer/2022081519/56649f125503460f94c24db2/html5/thumbnails/28.jpg)
Example (7-Answer)What are the names of the students BAKER’s
classmates S Where S.NAME = ‘BAKER’ Join E on S.SID=E.StudentNumber Join E as E2 on E.ClassName = E2.ClassName Join S as S2 on E2.StudentNumber = S2.SID Where S2.SID Not = S.SID [S2.Name]