1 / 23

Relational Algebra

Relational Algebra. The mathematical foundation for SQL Basic operators select project union intersect set difference cross product (i.e. Cartesian product) rename. Select Operation. Notation:  p ( r ) Select only the records satisfying the condition p

yardley-hoover
Download Presentation

Relational Algebra

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Algebra • The mathematical foundation for SQL • Basic operators • select • project • union • intersect • set difference • cross product (i.e. Cartesian product) • rename

  2. Select Operation • Notation: p(r) • Select only the records satisfying the condition p • The condition p is composed of one or more terms connected by :  (and),  (or),  (not)each term is one of the following two forms: <column> op <column> <column> op <constant> where op is one of: =, , >, . <. 

  3. Question 1 A B C D • Relation r         1 5 12 23 7 7 3 10 What is the result of the following operation? C>D  A= B(r)

  4. Project Operation • Notation:A1, A2, …, Ak (r) where A1, A2 are column names and r is a relation name. • The result is defined as a new relation of k columns obtained by erasing the columns that are not listed • Duplicate rows removed from result

  5. Question 2 • Relation r: A B C     10 20 30 40 1 1 1 2 • What is the result of A (r) ?

  6. Union Operation • Notation: r s • Meaning: The set composed of records appearing in r or s or both. • r s is well defined if and only if they are compatible in the following sense: 1. r,s have the same number of columns 2. The corresponding columns have same types (e.g., i-th column of r deals with the same type of values as does the i-th column of s)

  7. Question 3 • Relations r, s: A B A B    1 3 1   1 3 s r What is the result of r  s?

  8. Set Difference Operation • Notation r – s • Meaning: The set composed of records appearing in r but not in s. • r s is well defined if and only if they are compatible in the following sense: 1. r,s have the same number of columns 2. The corresponding columns have same types (e.g., i-th column of r deals with the same type of values as does the i-th column of s)

  9. Question 4 • Relations r, s: A B A B    1 3 1   1 3 s r What is the result of r – s? What is the result of r – (r – s)?

  10. Set-Intersection Operation • Notation: r s • Meaning: The set composed of records appearing both in r or s. • r s is well defined if and only if they are compatible in the following sense: 1. r,s have the same number of columns 2. The corresponding columns have same types (e.g., i-th column of r deals with the same type of values as does the i-th column of s)

  11. Questions 5 • Relation r, s: • What is the result of r  s? • What is the result of r – (r- s)? • What is the result of s – (s- r)? • Are the three expressions above always equivalent to one another no matter what r and s are? A B A B    1 2 1   2 3 r s

  12. Cross-Product Operation • Notation r x s • Defined as: r x s = {t q | t is a record in r and q is a record ins} • In other words, r x s Is the set of all possible pairs of records with one from r and the other one from s to form a pair.

  13. Question 6 A B C D E Relations r, s:   1 2     10 10 20 10 X X Y Y r s What is the result of r xs?

  14. Rename Operation • Allows us to name a resulting relation and its columns. • x (E) returns the relation E under the name X • If a relation E has arity n, then x(A1, A2, …, An)(E) returns the result of expression E under the name X, and with the columns renamed to A1, A2, …., An.

  15. Composing Expressions in Relational Algebra • A basic expression in the relational algebra consists of : • Constants or A table as a relation in the database • With any relational-algebra expressions E1 and E2 ,you can recursively compose more complex relational-algebra expressions as follow: • p (E1), P is a logic condition on columns in E1 • s(E1), S is a list consisting of some of the columns in E1 • E1 x E2 • x(E1), x is the new name for the result of E1 • E1 E2 • E1E2 • E1 - E2

  16. Relation: Students columns sid sname state CA CA OR WA RI Jones Smith Curry Linda Fugitive 01 02 03 04 05 records Students

  17. Relation : Courses courses columns cid cname required CS105 CS440 CS320 CS480 C++ Databases IC Design AI Research Yes No Yes No records

  18. Relation: Enrollment enrollment columns cid sid grade CS105 CS320 CS440 CS105 CS440 CS105 CS320 CS440 CS480 CS320 CS440 CS480 01 01 01 02 02 03 03 03 03 04 04 04 5 5 5 3 4 4 1 3 3 4 4 5 records

  19. Question 7 What are the results of the following relational-algebra expressions with respect to the three relations: courses, enrollment, and students • sid (grade<2 (enrollment)) • cname (required=“Yes” (courses) ) • sid ( enrollment.cid = courses.cid and required=“Yes” ( enrollment x courses ))

  20. Question 8 What are the results of the following relational-algebra expressions with respect to the three relation instances: courses, enrollment, and students • sid (enrollment.grade<2 and enrollment.cid = courses.cid and enrollment.required=“Yes” (enrollment x courses ) ) • sid ( (enrollment.cid = courses.cid (grade<2 (enrollment) x  required=“Yes” (courses) ) )) • sid (  grade<2 (enrollment)x  required=“Yes” (courses) )

  21. Question 9 Assume that a student fails in a course if the grade is less than 2. Identify what expressions (if any) in questions 7 and 8 can provide the following information (given the relation instances: courses, enrollment, and students) : • Thelist of sids of students who fail in some required courses • The list of sids of students who fail in some courses • Thelist of sids of students who take some required courses

  22. Question 10 What are the results of the following relational-algebra expressions with respect to the three relation instances: courses, enrollment, and students •  courses.cname ( enrollment.sid =“04” (enrollment.cid = courses.cid(enrollment x courses))) • cname(courses) –  courses.cname ( enrollment.sid =“04” (enrollment.cid = courses.cid(enrollment x courses))) • cname(required=“Yes” (courses)) –  courses.cname ( enrollment.sid =“04” and courses.required=“Yes” (enrollment.cid = courses.cid(enrollment x courses)))

  23. Question 11 Assume that a student fails in a course if the grade is less than 2. Identify what expressions (if any) in question 10 can provide the following information (given the relation instances: courses, enrollment, and students) : • Thelist of courses taken by the student with the sid ’04’ • Thelist of courses that the student with the sid ’04’ hasn’t taken yet • Thelist of required courses that the student with the sid ’04’ hasn’t taken yet

More Related