Chapter 6 Formal Relational Query Languages n Relational

Chapter 6: Formal Relational Query Languages n Relational Algebra basics

ER for Banking Enterprise

Schema Diagram for the Banking Enterprise

Query Languages n Categories of languages n n n “Pure” languages: n n procedural non-procedural Relational Algebra Tuple Relational Calculus Domain Relational Calculus Declarative languages: n SQL

Relational Algebra n n Procedural language Six basic operators n Select Projection Union set difference – Cartesian product x n Rename n n n The operators take one or more relations as inputs and give a new relation as a result.

Select Operation – Example • Relation r • A=B ^ D > 5 (r) A B C D 1 7 5 7 12 3 23 10 A B C D 1 7 23 10

Select Operation n Notation: p(r) p is called the selection predicate Defined as: p(r) = {t | t r and p(t)} Where p is a formula in propositional calculus consisting of terms connected by : (and), (or), (not) Each term is one of: <attribute>op <attribute> or <constant> where op is one of: =, , >, . <.

Example of selection n n branch-name=“Perryridge”(account) Selection gives a horizontal subset of a relation n a subset of all the tuples (rows) of a relation account n branch-name=“Perryridge”(account) ? ?

Project Operation – Example n Relation r: n A, C (r) A B C 10 1 20 1 30 1 40 2 A C 1 1 1 2 2 =

Project Operation n Notation: n A 1, A 2, …, Ak (r) where A 1, A 2 are attribute names and r is a relation name. The result is defined as the relation of k columns obtained by erasing the columns that are not listed Duplicate rows removed from result, since relations are sets n

Example of Projection n n To eliminate the branch-name attribute of account-number, balance (account) Projection gives a vertical subset of a relation n a subset of all the columns of a relation account-number, balance (account) ?

Union Operation – Example n Relations r, s: A B 1 2 2 3 1 s r r s: A B 1 2 1 3

Union Operation n Notation: r s Defined as: r s = {t | t r or t s} n For r s to be valid. n 1. 2. r, s must have the same arity (same number of attributes) The attribute domains must be compatible (e. g. , 2 nd column of r deals with the same type of values as does the 2 nd column of s)

Example of Union n Find all customers with either an account or a loan customer-name (depositor) customer-name (borrower) depositor customer-name (depositor) ? borrower customer-name (borrower) ? ?

Set Difference Operation – Example n Relations r, s: A B 1 2 2 3 1 s r r – s: A B 1 1

Set Difference Operation n Notation r – s Defined as: r – s = {t | t r and t s} Set differences must be taken between compatible relations. n n r and s must have the same arity attribute domains of r and s must be compatible

Example of Set Difference n Find all customers with either an account or a loan customer-name (depositor) customer-name (borrower) depositor customer-name (depositor) ? - borrower customer-name (borrower) ? ?

Cartesian-Product Operation-Example Relations r, s: A B C D E 1 2 10 10 20 10 a a b b r s r x s: A B C D E 1 1 2 2 10 10 20 10 a a b b

Cartesian-Product Operation n n Notation r x s Defined as: r x s = {t q | t r and q s} Assume that attributes of r(R) and s(S) are disjoint. (That is, R S = ). If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

the borrower relation

the loan relation

Result of borrower |X| loan

Cartesian-Product Operation n Cartesian-Product itself is usually not so useful It is often used as a “pre-processing” Other operators such as selection and projection will follow

Composition of Operations n n Can build expressions using multiple operations Example: A=C(r x s) rxs A=C(r x s) A B C D E 1 1 2 2 10 10 20 10 a a b b A B C D E 1 2 2 10 20 a a b

Rename Operation n Allows us to refer to a relation by more than one name. n Example: x (E) returns the expression E under the name X n If a relational-algebra expression E has arity n, then n x (A 1, A 2, …, An) (E) returns the result of expression E under the name X, and with the attributes renamed to A 1, A 2, …. , An.

Rename Operation n Example: downtown-account(account-number, branch-name, balance) ( branch-name=“Downtown”(account) account ?

Banking Example branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-only) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number)

Example Queries n Find all loans of over $1200 amount > 1200 (loan) Find the loan number for each loan of an amount greater than $1200 n loan-number ( amount > 1200 (loan))

Example Queries n Find the names of all customers who have a loan, an account, or both, from the bank customer-name (borrower) customer-name (depositor) n Find the names of all customers who have a loan and an account at bank. customer-name (borrower) customer-name (depositor)

Example Queries n Find the names of all customers who have a loan at the Perryridge branch. customer-name ( branch-name=“Perryridge” ( borrower. loan-number = loan-number(borrower x loan))) n Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank. customer-name ( branch-name = “Perryridge” ( borrower. loan-number = loan-number(borrower customer-name(depositor) x loan))) –

Example Queries n Find the names of all customers who have a loan at the Perryridge branch. Query 1 customer-name( branch-name = “Perryridge” ( borrower. loan-number = loan-number(borrower x loan))) Query 2 customer-name( loan-number = borrower. loan-number( ( branch-name = “Perryridge”(loan)) x borrower))

Example Queries Find the largest account balance n Rename account relation as d n The query is: balance(account) - account. balance ( account. balance < d. balance (account x d (account)))

branch-name assets

account-number branch-name balance

depositor customer-name account-number

customer-name customer-street customer-city

borrower customer-name loan-number

loan-number branch-name amount

customer

branch

loan

account

borrower

depositor