May 13th, 2002

1
Lecture 19

• May 13th, 2002

2
Agenda

• Trip Report
• End of constraints triggers.
• Systems aspects of SQL read chapter 8 in the
  book.
• Going under the lid!

3
Triggers

• Enable the database programmer to specify
• when to check a constraint,
• what exactly to do.
• A trigger has 3 parts
• An event (e.g., update to an attribute)
• A condition (e.g., a query to check)
• An action (deletion, update, insertion)
• When the event happens, the system will check the
  constraint, and
• if satisfied, will perform the action.
• NOTE triggers may cause cascading effects.
• Database vendors did not wait for standards with
  triggers!

4
Elements of Triggers (in SQL3)

• Timing of action execution before, after or
  instead of triggering
• event
• The action can refer to both the old and new
  state of the database.
• Update events may specify a particular column or
  set of columns.
• A condition is specified with a WHEN clause.
• The action can be performed either for
• once for every tuple, or
• once for all the tuples that are changed by the
  database operation.

5
Example Row Level Trigger
CREATE TRIGGER NoLowerPrices AFTER UPDATE OF
price ON Product REFERENCING OLD AS
OldTuple NEW AS NewTuple WHEN
(OldTuple.price gt NewTuple.price) UPDATE
Product SET price OldTuple.price
WHERE name NewTuple.name FOR EACH ROW
6
Statement Level Trigger
CREATE TRIGGER average-price-preserve INSTEAD OF
UPDATE OF price ON Product REFERENCING
OLD_TABLE AS OldStuff NEW_TABLE AS
NewStuff WHEN (1000 lt (SELECT AVG
(price) FROM ((Product EXCEPT
OldStuff) UNION NewStuff)) DELETE FROM Product
WHERE (name, price, company) IN
OldStuff INSERT INTO Product (SELECT FROM
NewStuff)
7
Bad Things Can Happen
CREATE TRIGGER Bad-trigger AFTER UPDATE OF
price IN Product REFERENCING OLD AS OldTuple
NEW AS NewTuple WHEN
(NewTuple.price gt 50) UPDATE Product
SET price NewTuple.price 2
WHERE name NewTuple.name FOR EACH ROW
8
A Naïve Database System

• Store data in text files
• Schema
• Students(sid, name, dept), Courses(cid, name),
  Takes(sid,cid)
• Schema file
• Students sidINT nameSTRdeptSTR
• CoursescidINTnameSTR
• TakessidINTcidINT
• Data file
• Smith123CSE
• John456EE

9
A Naïve DBMS

• Query processing
• Execution
• Read/parse query
• Read schema file to determine attributes
• Execute as nested loops
• Print results

SELECT Students.nameFROM Students, Takes,
CoursesWHERE Students.sidTakes.sid AND
Takes.cidCourses.cid AND
Courses.nameDatabases
10
What is Wrong with the Naïve DBMS

• Tuple layout is rigid what do we do on updates ?
• Search is expensive always read the entire
  relation
• Query processing is by brute force more clever
  ways exists

11
What is Wrong with the Naïve DBMS

• No way to buffer data in memory
• No concurrency control
• No reliability we can lose data in a crash
• No security

12
What Should a DBMS Do?

• Store large amounts of data
• Process queries efficiently
• Allow multiple users to access the database
  concurrently and safely.
• Provide durability of the data.
• How will we do all this??

13
Generic Architecture
Query update
User/ Application
Query compiler/optimizer
Query execution plan
Record, index requests
Transaction commands
Execution engine
Index/record mgr.

• Transaction manager
• Concurrency control
• Logging/recovery

Page commands
Buffer manager
Read/write pages
Storage manager
storage
14
Query Optimization
Goal
Imperative query execution plan
Declarative SQL query
buyer
?
SELECT Q.sname FROM Purchase P, Person Q WHERE
P.buyerQ.name AND Q.cityseattle AND
Q.phone gt 5430000
Cityseattle
phonegt5430000
(Simple Nested Loops)
Buyername
Person
Purchase
Plan Tree of R.A. ops, with choice of alg for
each op.
Ideally Want to find best plan. Practically
Avoid worst plans!
15
Alternate Plans
Find names of people who bought telephony products
buyer
buyer
?
?
Categorytelephony
Categorytelephony
(hash join)
(hash join)
prodpname
Buyername
(hash join)
(hash join)
Product
Buyername
Person
prodpname
Person
Purchase
Product
Purchase
But what if were only looking for Bobs
purchases?
16
ACID Properties
Atomicity all actions of a transaction happen,
or none happen. Consistency if a transaction
is consistent, and the database starts
from a consistent state, then it will
end in a consistent
state. Isolation the execution of one
transaction is isolated from other
transactions. Durability if a transaction
commits, its effects persist in the
database.
17
Problems with Transaction Processing
Airline reservation system Step 1 check if
a seat is empty. Step 2 reserve the
seat. Bad scenario (but very common) Customer
1 - finds a seat empty Customer 2 - finds the
same seat empty Customer 1 - reserves the
seat. Customer 2 - reserves the seat. Customer 1
will not be happy spends night in airport hotel.
18
The Memory Hierarchy
Main Memory Disk
Tape

• 5-10 MB/S
• transmission rates
• 2-10 GB storage
• average time to
• access a block
• 10-15 msecs.
• Need to consider
• seek, rotation,
• transfer times.
• Keep records close
• to each other.

• 1.5 MB/S transfer rate
• 280 GB typical
• capacity
• Only sequential access
• Not for operational
• data

• Volatile
• limited address
• spaces
• expensive
• average access
• time
• 10-100 nanoseconds

Cache access time 10 nanos
19
Main Memory

• Fastest, most expensive
• Today 512MB are common on PCs
• Many databases could fit in memory
• New industry trend Main Memory Database
• E.g TimesTen
• Main issue is volatility

20
Secondary Storage

• Disks
• Slower, cheaper than main memory
• Persistent !!!
• Used with a main memory buffer

21
Buffer Management in a DBMS
Page Requests from Higher Levels
BUFFER POOL
disk page
free frame
MAIN MEMORY
DISK
choice of frame dictated by replacement policy

• Data must be in RAM for DBMS to operate on it!
• Table of ltframe, pageidgt pairs is maintained.
• LRU is not always good.

22
Buffer Manager
Manages buffer pool the pool provides space for
a limited
number of pages from disk. Needs to decide on
page replacement policy. Enables the higher
levels of the DBMS to assume that the needed data
is in main memory. Why not use the Operating
System for the task?? - DBMS may be able to
anticipate access patterns - Hence, may also be
able to perform prefetching - DBMS needs the
ability to force pages to disk.
23
Tertiary Storage

• Tapes or optical disks
• Extremely slow used for long term archiving only

24
The Mechanics of Disk
Cylinder

• Mechanical characteristics
• Rotation speed (5400RPM)
• Number of platers (1-30)
• Number of tracks (lt10000)
• Number of bytes/track(105)

Spindle
Disk head
Sector
Platters
25
Disk Access Characteristics

• Disk latency time between when command is
  issued and when data is in memory
• Disk latency seek time rotational latency
• Seek time time for the head to reach cylinder
• 10ms 40ms
• Rotational latency time for the sector to
  rotate
• Rotation time 10ms
• Average latency 10ms/2
• Transfer time typically 10MB/s
• Disks read/write one block at a time (typically
  4kB)

26
The I/O Model of Computation

• In main memory algorithms we care about CPU time
• In databases time is dominated by I/O cost
• Assumption cost is given only by I/O
• Consequence need to redesign certain algorithms
• Will illustrate here with sorting