relational algebra prof. sin-min lee department of computer science
Post on 19-Dec-2015
220 views
TRANSCRIPT
Terminology:table (relation)row (tuple)
formally, a relation is a set of ordered n-tuples.
Domain : set of data values from which an attribute value can be drawn.
Eg. set of department names set of legal salaries set of possible employee birth dates
What are the query languages?
It is an abstract language. We use it to express the set of operations that any relational query language must perform.
Two types of operations: 1.set-theoretic operations: tables are
essentially sets of rows 2.native relational operations: focus on the
structure of the rows Query languages are specialized languages for asking questions,or queries,that involve the data in database.
Database Scheme
A relational database scheme, or schema, corresponds to a set of table definitions.
Eg: product(p_id, name, category, description) supply(p_id, s_id, qnty_per_month) supplier(s_id, name, address, ph#)
* remember the difference between a DB instance and a DB scheme.
Keys
The super key, candidate key and primary key notations presented for ER model apply for relational model also.
Query languages
procedural vs. non-proceduralcommercial languages have some
of bothwe will study:
relational algebra (which is procedural, i.e. tells you how to process a query)
relational calculus (which is non-procedural i.e. tells what you want)
Relational AlgebraFundamental operators select project cartesian product union set difference -
Other operators natural join JOIN (butterfly symbol) set intersection division
A Simple DB
account ac# owner ss# balance 1 bob 123 1000 2 sue 456 2000 3 jane 789 3000
transaction t# ac# type amount outcome date 1 1 W 1500 bounced 5/1/98 2 2 D 1000 ok 5/2/98 3 1 W 100 ok 5/4/98 4 3 D 500 ok 5/7/98 5 2 W 200 ok 5/9/98
account had transaction
Selecteg: balance>=1500 account
result : ac# owner ss# balance 2 sue 456 2000 3 jane 789 3000
Projecteg: π owner, ss# account
result: owner ss# bob 123 sue 456 jane 789
Cartesian product
eg: account transaction this will have 15 rows like the ones shown below:ac# owner ss# balance t# type amount outcome
date1 bob 123 1000 1 W 1500 bounced 5/1/982 sue 456 2000 2 D 1000 ok 5/2/98……………
Composing operationseg: “show all transactions done by account owner
Bob”.
σ account.ano= transaction.ano(( owner=“Bob” account) transaction)
Natural Join- combines σ, π, - very commonly usedNatural Join forms the cross product of its
two arguments, does a selection to enforce equality of columns with the same name and removes duplicate columns.
Eg: “show all transactions done by account owner Bob”
σ owner=“Bob” (account JOIN transaction)
Rename operation
What if you need to access the same relation twice in a query?
eg. person(ss#, name, mother_ss#, father_ss#)
“Find the name of Bob’s mother” needs the “person” table to be accessed twice.
The operation ρ x (r) evaluates to a second logical copy of relation r renamed to x.
Rename operation (contd)
eg:
π mother.name (
(ρ mother (person))
JOIN mother.ss# = person.mother_ss#
( name=“Bob” (person)))
Additional OperationsAdditional Operations are those that can be
expressed in terms of other operations. Set Intersection r s = r-(r-s)eg.: r a b s a b r s = a
b 1 a 1 a 1
a 2 b 2 c 3
d 3 d 3 d
r s
Additional Operations(cntd.) Division
useful for “for all” queriesDefinition: Let r(R) and s(S), where R & S are sets
of attributes, be relations, where S is a subset of R. The relation r s has scheme R-S. The tuples in r s consist of the R-S part of the tuples of r such that some tuple tr in r with the those R-S attribute values matches every tuple in s.
Can also be defined in terms of relational algebra.