Example: Row Level Trigger

1. Example: Row Level Trigger CREATE TRIGGER NoLowerPrices AFTER UPDATE OF price ON Product REFERENCING OLD AS OldTuple NEW AS NewTuple WHEN (OldTuple.price > NewTuple.price) UPDATE Product SET price = OldTuple.price WHERE name = NewTuple.name FOR EACH ROW

2. Example: Row Level Trigger CREATE TRIGGER NoLowerPrices INSTEAD OF UPDATE OF price ON Product REFERENCING OLD AS OldTuple NEW AS NewTuple WHEN (OldTuple.price < NewTuple.price) UPDATE Product SET price = NewTuple.price WHERE name = NewTuple.name FOR EACH ROW

3. Statement Level Trigger CREATE TRIGGER average-price-preserve INSTEAD OF UPDATE OF price ON Product REFERENCING OLD_TABLE AS OldStuff (the old versions of the tuples) NEW_TABLE AS NewStuff (the new versions) WHEN (1000 < (SELECT AVG (price) FROM ((Product EXCEPT OldStuff) UNION NewStuff)) DELETE FROM Product WHERE (name, price, company) IN OldStuff; INSERT INTO Product (SELECT * FROM NewStuff)

4. Bad Things Can Happen CREATE TRIGGER Bad-trigger AFTER UPDATE OF price IN Product REFERENCING OLD AS OldTuple NEW AS NewTuple WHEN (NewTuple.price > 50) UPDATE Product SET price = NewTuple.price * 2 WHERE name = NewTuple.name FOR EACH ROW

5. 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??

6. Generic Architecture Query update User/ Application Query compiler Query execution plan Execution engine Record, index requests Index/record mgr. Page commands Buffer manager Read/write pages Storage manager storage

7. Query Optimization Goal: Declarative SQL query Imperative query execution plan: buyer  City=‘seattle’ phone>’5430000’ SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’ (Simple Nested Loops) Buyer=name 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!

8. Alternate Plans Find names of people who bought telephony products buyer buyer   Category=“telephony” Category=“telephony” (hash join) (hash join) prod=pname Buyer=name (hash join) (hash join) Product Person Buyer=name prod=pname Person Purchase Product Purchase But what if we’re only looking for Bob’s purchases?

9. 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.

10. 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.

11. The Memory Hierarchy Main MemoryDisk 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 nano’s

12. Tracks Arm movement Arm assembly Disk Space Manager • Task: manage the location of pages on disk (page = block) • Provides commands for: • allocating and deallocating a page • on disk • reading and writing pages. • Why not use the operating system • for this task? • Portability • Limited size of address space • May need to span several • disk devices. Spindle Disk head Sector Platters