rel-alg

# rel-alg - Lecture 4 INFS614 1 Administration Homework 1b...

This preview shows pages 1–12. Sign up to view the full content.

INFS614 1 Lecture 4

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
INFS614 2 Administration Homework 1b due today – you will get 1a and 1b back together New assignment: – work exercise 4.3 in text (relational algebra only), problems: 1, 3, 4, 7, 8, 10, and 11. – due next week Review tautologies
INFS614 3 Relational Query Languages: Overview of Coming Weeks Natural Language Person Answer Query (SQL) Query Interface Optimizer Stored Data / Disk DBMS Relational Operators SQL Relational Calculus (logic-based) Relational Algebra (set theoretic) reasoning about how to compute the answer reasoning about how to define the answer 2 1 3

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
INFS614 4 Formal Relational Query Languages Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation: Relational Algebra : More operational , very useful for representing execution plans. Relational Calculus : Let users describe what they want, rather than how to compute it. ( Non- operational, declarative .) Note: These languages (and SQL) are not “turing complete” like programming languages, their goal is to express data retrieval queries .
INFS614 5 Algebra Preliminaries Relational algebra includes a set of operators Each operator takes 1 or 2 relation instances as inputs, and returns 1 relation instance as the result result schema determined by the operation Operators can therefore be composed easily. OP result inputs

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
INFS614 6 Example Instances sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96 R1 S1 S2 “Sailors” and “Reserves” relations for our examples.
INFS614 7 Algebra Operations (1st Three) π σ X Projection Selection Cross Product

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
INFS614 8 Projection sname rating yuppy 9 lubber 8 guppy 5 rusty 10 π sname rating S , ( ) 2 age 35.0 55.5 age S ( ) 2 Removes columns (attributes) not in projection list . Schema of result contains exactly the fields in the projection list, with the same names that they had in the (single) input relation. Projection operator has to eliminate duplicates ! (Why??) Note: real systems typically don’t do duplicate elimination unless the user explicitly asks for it. (Why not?)
INFS614 9 Selection = > ) 2 ( 8 S rating σ sid sname rating age 28 yuppy 9 35.0 58 rusty 10 35.0 Selects rows that satisfy the selection condition – a predicate over attribute values No duplicates in result! (Why?) Schema of result identical to schema of (single) input relation. S2 sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document