1 / 46

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

The ACID Properties AND Database Design: Chapter 10 AND Normal Forms AND Chapters 13,14,16. 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

ashlyn
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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)

More Related