1 / 17

The ACID Properties AND Normal Forms

The ACID Properties AND Normal Forms. 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

nike
Download Presentation

The ACID Properties AND Normal Forms

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 PropertiesANDNormal Forms

  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. 3rd normal form • 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. 3NF: remove transitive dependencies Customer ID Address ZIP 18 112 First 80304 17 123 Ash 80303 16 123 Ash 80303

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

  15. Fourth NormalForm SSN PhoneN ChildSSN 111111 123-4444 222222 111111 123-4444 333333 111111 321-5555 222222 111111 321-5555 333333 222222 987-6666 444444 222222 777-7777 444444 222222 987-6666 555555 222222 777-7777 555555 Person redundancy • Relation has redundant data • Yet it is in BCNF (since there are no non-trivial FDs) • Redundancy is due to set valued attributes (in the E-R sense), not because of the FDs

  16. 4NF: Separate pairs of MVDs Mothers_PhoneFathers_PhoneChild_Name Break into: Adult Phone joe 1 joe 2 sue 3 sue 4 And Adult Child joe Tommy joe Bobby sue Tommy sue Bobby Note: both fields needed for PK

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

More Related