The ACID Properties AND Database Design: Chapter 10 AND Normal Forms AND Chapters 13,14,16

1. The ACID PropertiesAND Database Design: Chapter 10 AND Normal Forms AND Chapters 13,14,16

2. ACID Transactions • Atomic: Either all of a transaction or None of it affects the database • Consistent: When a transaction ends, the database obeys all constraints • Isolated: Two running transactions cannot pass values to each other, via the database or other data store • Durable: Once a transaction has “committed”, its updates are permanent

3. Atomicity • Use a local log to store a transaction’s partial result • If a transaction does something illegal, toss out the log

4. Consistent • Check constraints in phase 1 • Some are immediate, like domains • Others don’t have to be true until the commit point, like FKs

5. Isolated • Transactions commit in a linear order • Serializability is enforced • Results become available only after atomic commit point

6. Durable • Database has one state and it is in nonvolatile storage • Keep checkpoints and transaction logs

7. Deadlock • Loops of transactions wait on each other • Detection: use time-outs • Prevention: use “waits for” graph

8. The DB Design Process • Start with an entity model • Map to tables • Create PKs and FKs • Create other constraints • Normalize tables

9. Our focus: normalization • Goals • Minimize redundant data • Minimize “update anomalies”

10. Functional and Multivalued Dependencies • FD  • We say that ai FD-> aj • Or “ai functionally determines aj” • MVD-> • We say that ai MVD-> aj • Or “ai multivalued determines aj” • Note: the right side of an FD or an MVD can be a set of attributes

11. First 3 normal forms • First (1NF) The value stored at the intersection of each row and column must be a scalar value, and a table must not contain any repeating columns. • Second (2NF) Every non-key column must depend on the entire primary key. • Third (3NF) Every non-key column must depend only on the primary key.

12. NF3 fixed and NF4 • Boyce-Codd (BCNF) A non-key column can’t be dependent on another non-key column. • Fourth (4NF) A table must not have more than one multivalued dependency, where the primary key has a one-to-many relationship to non-key columns.

13. Example: 1NF

14. Example: 2NF

15. Example: 2NF, continued

16. 3NF: remove transitive dependencies Customer ID Address ZIP 18 112 First 80304 17 123 Ash 80303 16 123 Ash 80303

17. 3NF, continued Break into two tables: Customer ID Address Address Zip

18. 4NF: Separate pairs of MVDs Mothers_PhoneFathers_PhoneChild_Name Break into: Mothers_PhoneChild_Name 3030000000 Sue 3031111111 Sue AndFathers_PhoneChild_Name 3032222222 Sue 3033333333 Sue Note: both fields needed for PK

19. Tradeoffs • “Decomposition” makes it harder to misunderstand the database schema • But Decomposition create narrow tables that might not correspond to forms in the real world • And Decomposition leads to extra joins • One solution is to pre-join data

20. Autocommit • Used when manipulating a MySQL database interactively • Automatically and immediately commits INSERT UPDATE DELETE commands • Use the transaction protocol to override this

21. Chapter 14: Transactions in MySQL • A transaction is the unit of work in a relational database • Not available with the MyISAM engine • InnoDB does support transactions • Storage engines • InnoDB is the default • MyISAM has no foreign keys, but has full text search

22. Transactions • Often used within stored procedures, which are compiled programs that can be called by an application • Operations • START TRANSACTION • COMMIT • ROLLBACK and SAVEPOINT • Used when you don’t want to undo an entire transactions

23. A Transaction CREATE PROCEDURE test() BEGIN DECLARE sql_error TINYINT DEFAULT FALSE; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = TRUE; START TRANSACTION; INSERT INTO invoices VALUES (115, 34, 'ZXA-080', '2012-01-18', 14092.59, 0, 0, 3, '2012-04-18', NULL); INSERT INTO invoice_line_items VALUES (115, 1, 160, 4447.23, 'HW upgrade');

24. Continued… INSERT INTO invoice_line_items VALUES (115, 2, 167, 9645.36, 'OS upgrade'); IF sql_error = FALSE THEN COMMIT; SELECT 'The transaction was committed.'; ELSE ROLLBACK; SELECT 'The transaction was rolled back.'; END IF; END//

25. Savepoints USE ap; START TRANSACTION; SAVEPOINT before_invoice; INSERT INTO invoices VALUES (115, 34, 'ZXA-080', '2012-01-18', 14092.59, 0, 0, 3, '2012-04-18', NULL); SAVEPOINT before_line_item1; INSERT INTO invoice_line_items VALUES (115, 1, 160, 4447.23, 'HW upgrade'); SAVEPOINT before_line_item2;

26. Continued… INSERT INTO invoice_line_items VALUES (115, 2, 167, 9645.36,'OS upgrade'); ROLLBACK TO SAVEPOINT before_line_item2; ROLLBACK TO SAVEPOINT before_line_item1; ROLLBACK TO SAVEPOINT before_invoice; COMMIT;

27. Another view of transactions • Prevents • Lost updates from one of two transactions • Dirty reads when a transaction reads an uncommitted value • Nonrepeatable reads in one transaction because the value gets updated in between • Phantom reads are when a selection query is run twice in a transaction and returns different results

28. Transaction Isolation Leves • Set transaction level • Next (no keyword) sets the transaction in the current session • Session sets all transactions in a session • Global sets all transactions for all sessions

29. Continued… • Options • Serializable isolates transactions completely and is the highest level of protection • Read uncommitted lets our four problems occur – no locks • Read committed prevents dirty reads but allows the other problems by not allowing uncommitted writes from being read • Repeatable read is the default and it means that a transaction will always read a given value the same because the values are locked

30. Deadlock • Detect by closing transactions that have been open a long time • Use the lowest acceptable locking level • Try to do heavy update transactions when database can be completely reserved

31. Stored programs • Stored procedures (can be called by an application) • Stored functions (can be called by an SQL program) • Triggers (tied to an operation like INSERT) • Events (tied to a clock)

32. Flow of control in SQL • IF - ELSE • CASE – WHEN - ELSE • WHILE – DO - LOOP • REPEAT - UNTIL – END REPEAT

33. Variables • DECLARE statement • SET statement • DEFAULT statement • INTO (from a SELECT clause)

34. Example (stored procedure)… CREATE PROCEDURE test() BEGIN DECLARE max_invoice_total DECIMAL(9,2); DECLARE min_invoice_total DECIMAL(9,2); DECLARE percent_difference DECIMAL(9,4); DECLARE count_invoice_id INT; DECLARE vendor_id_var INT; SET vendor_id_var = 95; SELECT MAX(invoice_total), MIN(invoice_total), COUNT(invoice_id) INTO max_invoice_total, min_invoice_total, count_invoice_id FROM invoices WHERE vendor_id = vendor_id_var;

35. Example, continued SET percent_difference = (max_invoice_total - min_invoice_total) / min_invoice_total * 100; SELECT CONCAT('$', max_invoice_total) AS 'Maximum invoice', CONCAT('$', min_invoice_total) AS 'Minimum invoice', CONCAT('%', ROUND(percent_difference, 2)) AS 'Percent difference', count_invoice_id AS 'Number of invoices'; END//

36. Domain types – chapter 8 • Character • Integers • Reals • Date • Time • Large object, BLOB and CLOB • 2D vector spatial types • Enumerated

37. Conversion • Automatic (implied) • CAST is the standardized operator • CONVERT is similar

38. Cursor syntax • Declare a cursor • DECLARE cursor_name CURSOR FOR select_statement; • Declare an error handler for when no rows are found in the cursor • DECLARE CONTINUE HANDLER FOR NOT FOUND handler_statement; • Open the cursor • OPEN cursor_name; • Get column values from the row and store them in a series of variables • FETCH cursor_name INTO variable1 • [, variable2][, variable3]...; • Close the cursor • CLOSE cursor_name;

39. Example with a cursor DELIMITER // CREATE PROCEDURE test() BEGIN DECLARE invoice_id_var INT; DECLARE invoice_total_var DECIMAL(9,2); DECLARE row_not_found TINYINT DEFAULT FALSE; DECLARE update_count INT DEFAULT 0; DECLARE invoices_cursor CURSOR FOR SELECT invoice_id, invoice_total FROM invoices WHERE invoice_total - payment_total - credit_total > 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET row_not_found = TRUE; OPEN invoices_cursor; WHILE row_not_found = FALSE DO FETCH invoices_cursor INTO invoice_id_var, invoice_total_var;

40. Example with a Cursor IF invoice_total_var > 1000 THEN UPDATE invoices SET credit_total = credit_total + (invoice_total * .1) WHERE invoice_id = invoice_id_var; SET update_count = update_count + 1; END IF; END WHILE; CLOSE invoices_cursor; SELECT CONCAT(update_count, ' row(s) updated.'); END//

41. Triggers • ON event IF precondition THEN action • All three actions could be SQL • Precondition would be a yes/no, based on results • When are they used? • DELETE, UPDATE, INSERT statements

42. Syntax of MySQL Triggers:Chapter 16 CREATE TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name FOR EACH ROW !! Notice that we can look over the threshold of a state change!!

43. Trigger example CREATE TRIGGER vendors_before_update BEFORE UPDATE ON vendors FOR EACH ROW BEGIN SET NEW.vendor_state = UPPER(NEW.vendor_state); END// ** this puts the field in upper case UPDATE vendors SET vendor_state = 'wi' WHERE vendor_id = 1 ** This illustrates a row level trigger ** otherwise, it’s a statement level trigger

44. Constraint Trigger Example CREATE TRIGGER invoices_before_update BEFORE UPDATE ON invoices FOR EACH ROW BEGIN DECLARE sum_line_item_amount DECIMAL(9,2); SELECT SUM(line_item_amount) INTO sum_line_item_amount FROM invoice_line_items WHERE invoice_id = NEW.invoice_id; IF sum_line_item_amount != NEW.invoice_total THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'Line item total must match invoice total.'; END IF; END

45. Examples of Audit Constraints CREATE TRIGGER invoices_after_insert AFTER INSERT ON invoices FOR EACH ROW BEGIN INSERT INTO invoices_audit VALUES (NEW.vendor_id, NEW.invoice_number, NEW.invoice_total, 'INSERTED', NOW()); END// CREATE TRIGGER invoices_after_delete AFTER DELETE ON invoices FOR EACH ROW BEGIN INSERT INTO invoices_audit VALUES (OLD.vendor_id, OLD.invoice_number, OLD.invoice_total, 'DELETED', NOW()); END//

46. Key Terms! • Triggering events (insert, delete, update) • Trigger preconditions (basically a where clause) • Trigger actions (basically a query) • Trigger execution (before or after event)