2. alg1.pptx - Chapter 8 The Relational Algebra and Calculus Queries Student S SNAME 1000 John 2000 Kate 3000 Tony 1 2 3 4 5 6 7 Grade Course AGE S C

# 2. alg1.pptx - Chapter 8 The Relational Algebra and...

• 46

This preview shows page 1 - 11 out of 46 pages.

Chapter 8 The Relational Algebra and Calculus

Subscribe to view the full document.

Queries 2 1. Find John’s age 2. Find students taking courses 3. Find students not taking courses 4. Find students taking DB course 5. Find students taking all courses 6. Find students taking all courses Kate takes 7. Find students taking two courses Student Grade Course S# SNAME AGE S# C# MARK C# CNAME LOC 1000 John 25 1000 CS300 90 CS300 OS ME300 2000 Kate 24 1000 CS305 85 CS305 DB UC231 3000 Tony 20 2000 CS300 85 MT230 AL TB300
Relational Languages Relational Algebra (ALG) E.F. Codd, Communications of the ACM , 13(6):377–387, 1970 Tuple Relational Calculus (TRC) E.F. Codd, Communications of the ACM, 13(6):377–387, 1970 Domain Relational Calculus (DRC) Michel Lacroix and Alain Pirotte, VLDB 1977:370-378 3

Subscribe to view the full document.

Brief History of Origins of Algebra Algebra is from Arabic word al-jabr It is the study of mathematical symbols and the rules for manipulating these symbols. Use symbols to represent queries (operations) on relations Operations should be simple but can be combined to represent complex queries Use minimal number of operations but can represent all possible queries. 4 Student Grade Course S# SNAME AGE S# C# MARK C# CNAME LOC 1000 John 25 1000 CS300 90 CS300 OS ME300 2000 Kate 24 1000 CS305 85 CS305 DB UC231 3000 Tony 20 2000 CS300 85 MT230 AL TB300
Relational Algebra The basic set of operations for the relational model Used to specify basic retrieval requests (or queries ) Each operation takes one or two relations generates one new relation This property makes the algebra closed A sequence of relational algebra operations forms a relational algebra expression The result of a relational algebra expression is also a relation that represents the result of a database query (or retrieval request) 5

Subscribe to view the full document.

Kinds of Relational Algebra Unary Relational Operations SELECT ( σ ) PROJECT ( π ) RENAME ( ) Relational Algebra Operations From Set Theory UNION ( ), INTERSECTION ( ), DIFFERENCE or MINUS ( ) CARTESIAN PRODUCT ( ) Binary Relational Operations JOIN (several variations of JOIN exist) ( ) DIVIDEBY ( / ) Additional Relational Operations OUTER JOINS, OUTER UNION AGGREGATE FUNCTIONS (These compute summary of information: for example, SUM, COUNT, AVG, MIN, MAX) 6
Student S# SNAME AGE 1000 John 25 2000 Kate 24 3000 Tony 20 7 Unary Operations Choose tuples (rows) Choose attributes (columns) Which operation? Which operation? Select ( σ) Project ( π )

Subscribe to view the full document.

Unary Operation: SELECT ( σ ) The SELECT operation selects a subset of the tuples from a relation based on a condition . select <condition> (R) or <condition> (R) <condition> is a Boolean (conditional) expression specified on the attributes of relation R select CNAME = ‘DB’ (Course); 8 Course C# CNAME LOC CS300 OS ME300 CS305 DB UC231 MT230 AL TB300 C# CNAME LOC CS305 DB UC231
Unary Operations: SELECT ( σ ) Find students whose age is greater than 20: select age > 20 (Student) or Age > 20 (Student) 9 Student S# SNAME AGE 1000 John 25 2000 Kate 24 3000 Tony 20 S# SNAME AGE 1000 John 25 2000 Kate 24

Subscribe to view the full document.

Unary Operations: SELECT ( σ ) Select students whose age is greater than 20 and
• Spring '10
• Neel
• Relational model, Kate

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern

Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes