Relational Databases: Query Languages and Relational Algebra, Study notes of Algebra

Download Relational Databases: Query Languages and Relational Algebra and more Study notes Algebra in PDF only on Docsity!

RELATIONAL ALGEBRA

CS121: Relational Databases

Fall 2018 – Lecture 2

Query Languages

¨ A query language specifies how to access the data in

the database

¨ Different kinds of query languages:

¤ Declarative languages specify what data to retrieve, but not

how to retrieve it

¤ Procedural languages specify what to retrieve, as well as

the process for retrieving it

¨ Query languages often include updating and deleting

data as well

¨ Also called data manipulation language (DML)

2

“Why is this useful?”

¨ SQL is only loosely based on relational algebra

¨ SQL is much more on the “declarative” end of the

spectrum

¨ Many relational databases use relational algebra

operations for representing execution plans

¤ Simple, clean, effective abstraction for representing

how results will be generated

¤ Relatively easy to manipulate for query optimization

4

Fundamental Relational Algebra Operations ¨ Six fundamental operations: σ select operation Π project operation ∪ set-union operation

• set-difference operation × Cartesian product operation ρ rename operation ¨ Each operation takes one or two relations as input ¨ Produces another relation as output ¨ Important details: ¤ What tuples are included in the result relation? ¤ Any constraints on input schemas? What is schema of result? 5

Select Examples

Using the account relation:

“Retrieve all tuples for accounts

in the Los Angeles branch.”

σ branch_name =“Los Angeles”( account )

“Retrieve all tuples for accounts

in the Los Angeles branch,

with a balance under $300.”

σ branch_name =“Los Angeles” ∧ balance <300( account )

acct_id branch_name balance A- 301 A- 307 A- 318 A- 319 A- 322 New York Seattle Los Angeles New York Los Angeles

account acct_id branch_name balance A- 318 A- 322 Los Angeles Los Angeles

acct_id branch_name balance A- 322 Los Angeles 275 7

Project Operation

¨ Written as: Π

a,b,…

(r)

¨ Result relation contains only specified attributes of r

¤ Specified attributes must actually be in schema of r

¤ Result’s schema only contains the specified attributes

¤ Domains are same as source attributes’ domains

¨ Important note:

¤ Result relation may have fewer rows than input relation!

¤ Why?

n Relations are sets of tuples, not multisets

8

Composing Operations

¨ Input can also be an expression that evaluates to a

relation, instead of just a relation

acct_id

balance ≥ 300

( account ))

¤ Selects the account IDs of all accounts with a balance of

$300 or more

¤ Input relation’s schema is:

Account_schema = ( acct_id , branch_name , balance )

¤ Final result relation’s schema?

n Just one attribute: ( acct_id )

¨ Distinguish between base and derived relations

¤ account is a base relation

¤ σ balance ≥ 300 ( account ) is a derived relation

10

Set-Union Operation

¨ Written as: r ∪ s

¨ Result contains all tuples from r and s

¤ Each tuple is unique, even if it’s in both r and s

¨ Constraints on schemas for r and s?

¨ r and s must have compatible schemas:

¤ r and s must have same arity

n (same number of attributes)

¤ For each attribute i in r and s , r [i] must have the same

domain as s [i]

¤ (Our examples also generally have same attribute names,

but not required! Arity and domains are what matter.)

11

Set-Union Example (2)

¨ Find names of all customers that have either a

bank account or a loan at the bank

acct_id branch_name balance A- 301 A- 307 A- 318 A- 319 A- 322 New York Seattle Los Angeles New York Los Angeles

account loan_id branch_name amount L- 421 L- 445 L- 437 L- 419 San Francisco Los Angeles Las Vegas Seattle

loan cust_name acct_id Johnson Smith Reynolds Lewis Reynolds

A- 318

A- 322

A- 319

A- 307

A- 301

depositor cust_name loan_id Anderson Jackson Lewis Smith

L- 437

L- 419

L- 421

L- 445

borrower 13

Set-Union Example (3)

¨ Find names of all customers that have either a bank

account or a loan at the bank

¤ Easy to find the customers

with an account:

Π cust_name ( depositor )

¤ Also easy to find customers

with a loan:

Π cust_name ( borrower )

¨ Result is set-union of these expressions:

Π cust_name ( depositor ) ∪ Π cust_name ( borrower )

¤ Note that inputs have 8 tuples,

but result has 6 tuples.

cust_name Johnson Smith Reynolds Lewis cust_name Anderson Jackson Lewis Smith cust_name Johnson Smith Reynolds Lewis Anderson Jackson Π cust_name ( depositor ) Π cust_name ( borrower ) 14

Set-Difference Example

“Find all customers that have an account but not a loan.”

acct_id branch_name balance A- 301 A- 307 A- 318 A- 319 A- 322 New York Seattle Los Angeles New York Los Angeles

account loan_id branch_name amount L- 421 L- 445 L- 437 L- 419 San Francisco Los Angeles Las Vegas Seattle

loan cust_name acct_id Johnson Smith Reynolds Lewis Reynolds

A- 318

A- 322

A- 319

A- 307

A- 301

depositor cust_name loan_id Anderson Jackson Lewis Smith

L- 437

L- 419

L- 421

L- 445

borrower 16

Set-Difference Example (2)

¨ Again, each component is easy

¤ All customers that have an account:

cust_name

( depositor )

¤ All customers that have a loan:

Π cust_name ( borrower )

¨ Result is set-difference of these expressions

cust_name

( depositor ) – Π

cust_name

( borrower )

cust_name Johnson Smith Reynolds Lewis cust_name Anderson Jackson Lewis Smith cust_name Johnson Reynolds Π cust_name ( depositor ) Π cust_name ( borrower ) 17

Cartesian Product Operation (2)

¨ Result of r × s

¤ Contains every tuple in r , combined with every tuple in s

¤ If r contains N

r

tuples, and s contains N

s

tuples, result

contains Nr × Ns tuples

¨ Allows two relations to be compared and/or

combined

¤ If we want to correlate tuples in relation r with tuples in

relation s …

¤ Compute r × s , then select out desired results with an

appropriate predicate

19

Cartesian Product Example

¨ Compute result of borrower × loan

¨ Result will contain 4 × 4 = 16 tuples

loan_id branch_name amount L- 421 L- 445 L- 437 L- 419 San Francisco Los Angeles Las Vegas Seattle

loan cust_name loan_id Anderson Jackson Lewis Smith

L- 437

L- 419

L- 421

L- 445

borrower 20