CMSC424: Database Design

1
CMSC424 Database Design

• Lecture 5

2
Review Relational Algebra

• Relational Algebra Operators
• Select (?)
• Project (?)
• Set Union (U)
• Set Difference (-)
• Cartesian Product (?)
• Rename (?)
• These are called fundamental operations

3
Relational AlgebraRedundant Operators

• 4. Update ( ? )

4
Natural Join
Idea match tuples on common attributes

r
s
5
Division
Relation1
Relation2
Notation
Idea expresses for all queries
Query Find customers who have accounts in all
branches in Brooklyn r1 ? all branches in
Brooklyn r2 ? associate customers with branches
they have accounts in Now what ? Use the
division operator
6
Outer Joins
Motivation
loan
borrower

Join result loses ? any record of Perry ? any
record of Hayes
7
Outer Joins
borrower
loan
1. Left Outer Join ( )

• preserves all tuples in left relation

- NULL
8
Outer Joins
borrower
loan
2. Right Outer Join ( )

• preserves all tuples in right relation

- NULL
9
Outer Joins
borrower
loan
3. Full Outer Join ( )

• preserves all tuples in both relations

- NULL
10
Update
Identifier ? Query
Notation
Common Uses
1. Deletion r ? r s
e.g., account ? account sbnamePerry (account)
(deletes all Perry accounts)
2. Insertion r ? r ? s
e.g., branch ? branch ? (Waltham, Boston, 7M)
(inserts new branch with bname Waltham, bcity
Boston, assets 7M)
3. Update r ? pe1,,en (r)
e.g., account ? pbname,acct_no,bal1.05 (account)
(adds 5 interest to account balances)
11
Extended Relational Algebra

• Generalized projection
• Aggregates

12
Generalized Projection
? e1,,en (Relation)
Notation
e1,,en can include arithmetic expressions not
just attributes
Example
credit
Then
p cname, limit - balance (credit)
13
Generalized Projection
? e1,,en (Relation)
Notation
e1,,en can include arithmetic expressions not
just attributes
Example
credit
Then
p cname, limit - balance as limitbalance (credit)

14
Aggregate Functions and Operations

• Aggregation function takes a collection of values
  and returns a single value as a result.
• avg average value min minimum value max
  maximum value sum sum of values count
  number of values

15
Aggregate Operation Example

• Relation r

A
B
C
? ? ? ?
? ? ? ?
7 7 3 10
sum-C
g sum(c) as sumC (r)
27
16
Aggregate Functions and Operations

• General form
• G1, G2, , Gn g F1( A1), F2( A2),, Fn( An)
  (E)
• E is any relational-algebra expression
• G1, G2 , Gn is a list of attributes on which to
  group (can be empty)
• Each Fi is an aggregate function
• Each Ai is an attribute name

17
Aggregate Operation Example

• Relation account grouped by branch-name

branch-name
account-number
balance
Perryridge Perryridge Brighton Brighton Redwood
A-102 A-201 A-217 A-215 A-222
400 900 750 750 700
branch-name g sum(balance) (account)
branch-name
balance
Perryridge Brighton Redwood
1300 1500 700
18
Other Theoretical Languages

• Relational Calculus
• Non-procedural
• Tuple relational calculus
• Examples
• Safety
• Domain relational calculus

19
Review Query Languages
20
SQL - Introduction

• Standard DML/DDL for relational DBs

• DML Data Manipulation Language (queries,
  updates)
• DDL Data Definition Language (create tables,
  indexes, )

Also includes

• View definition
• Security (Authorization)
• Integrity constraints
• Transactions

History

• Early 70s, IBM system R project (SEQUEL)
• Later, become standard (Structured Query Language)

21
SQL Basic Structure
SELECT A1, .., An FROM r1, .., rm WHERE P
Equivalent to
? A1,A2,,An (sP (r1? ? rn ))
22
A Simple SELECT-FROM-WHERE Query
SELECT bname FROM loan WHERE amt gt 1000

• Similar to

? bname ( ? amt gt 1000 (loan) )
Why preserve duplicates?
But not quite
Can instead write SELECT DISTINCT
bname FROM loan WHERE amt gt 1000 (removes
duplicates from result) We will discuss bag
algebra a bit later
Duplicates are retained (i.e., result not a set)
23
Another SELECT-FROM-WHERE Query
SELECT cname, balance FROM depositor,
account WHERE depositor.acct_no account.acct_no

• Similar to

Note
Returns
Can also write SELECT d.cname,
a.balance FROM depositor as d, account as
a WHERE d.acct_no a.acct_no (neccessary for
self-joins)
24
The SELECT Clause

• Equivalent to (generalized) projection, despite
  name

• Can use to get all attributes

e.g SELECT FROM loan

• Can write SELECT DISTINCT to eliminate duplicates

• Can write SELECT ALL to preserve duplicates
  (default)

• Can include arithmetic expressions

e.g SELECT bname, acct_no, balance1.05 FROM a
ccount
25
The FROM Clause

• Equivalent to cartesian product (?)

(or , depending on WHERE clause)

• Binds tuples in relations to variable names

• e.g FROM borrower, loan
• Computes borrower ? loan
• Identifies borrower, loan columns in result,
  allowing one to write
• WHERE borrower.lno loan.lno
• e.g FROM borrower as b, loan as l
• allows one to write
• WHERE b.lno l.lno

26
The WHERE Clause

• Equivalent to Selection, despite name

• WHERE predicate can be

• Simple
• attribute relop attribute (or constant)
• (relop , ltgt, lt, gt, lt, gt)
• 2. Complex (using AND, OR, NOT, BETWEEN)
• e.g SELECT lno
• FROM loan
• WHERE amt BETWEEN 90000 AND 100000
• is the same as
• SELECT lno
• FROM loan
• WHERE amt gt 90000 AND amt lt 100000

27
Data Definition Language

• Allows specification of relation schema as well
  as
• Attribute domains
• Integrity constraints
• Security and authorization information
• Creation of Indexes

28
DDL
CREATE TABLE branch (branch-name char(15) not
null, branch-city char(30), assets
integer, primary key (branch-name), check
(assets gt 0))
DROP TABLE branch
ALTER TABLE branch ADD zipcode integer