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

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

View Full Document Right Arrow Icon
INFS614 1 Lecture 4
Background image of page 1

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

View Full DocumentRight Arrow Icon
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
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
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 .
Background image of page 4
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
Background image of page 5

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

View Full DocumentRight Arrow Icon
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.
Background image of page 6
INFS614 7 Algebra Operations (1st Three) π σ X Projection Selection Cross Product
Background image of page 7

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

View Full DocumentRight Arrow Icon
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?)
Background image of page 8
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
Background image of page 9

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

View Full DocumentRight Arrow Icon