Normal Forms Transaction Management By B Santhosh Reddy

Normal Forms & Transaction Management By B. Santhosh Reddy

Agenda • Introduction to DBMS Concepts • Data Redundancy and Anomalies • What is Normalization • Types of Normalization • Introduction to Transactions • Transaction Management

Introduction to DBMS Concepts • DBMS and RDBMS • Database tables, Primary Key, Super Key, Foreign Key and Candidate key • Functional Dependency (FD) ü kind of IC that generalizes the concept of key ü In a relation schema R, FD X Y ( X functionally determines Y) if t 1. X = t 2. X then t 1. Y = t 2. Y Emp_Id Emp_Name Dept_No Dept_Name Dept_Mgr

Data Redundancy and Anomalies caused � Data Redundancy Repetitive data � Anomalies caused by data redundancy ◦ Update Anomaly ◦ Insertion Anomaly ◦ Deletion Anomaly Employee-Department Emp_Id Emp_Name Dept_No Dept_Name Dept_Head Emp_Mgr Dept_Address E 101 John D 101 Human Resources Larry Dennis …………. E 102 Smith D 102 Finance Donald Sandy …………. E 103 Dennis D 101 Human Resources Larry Alan …………. E 104 Sams D 101 Human Resources Larry Alan ………….

Normalization • Eliminates certain kind of redundancy by decomposing the tables and there by reduces inconsistencies • Avoids data anomalies caused by redundancy • Simplifies enforcement of Database integrity

First Normal Form- 1 NF Ø All attribute values are atomic Ø No repeating group, no composite attributes Table not in 1 NF Employee Manager Dept_No Mgr_No Emp_Name D 001 M 001 E 2002 E 2003 Andrew John Donald D 002 M 002 E 3001 E 3002 E 3003 E 3004 James Simon Paul Larry

Table in 1 NF Employee Manager Dept_No Mgr_No Emp_Name D 001 M 001 E 2001 Andrew D 001 M 001 E 2002 John D 001 M 001 E 2003 Donald D 002 M 002 E 3001 James D 002 M 002 E 3002 Simon D 002 M 002 E 3003 Paul D 002 M 002 E 3004 Larry Ø all attribute values are atomic because there are no repeating group and no composite attributes.

Second Normal Form- 2 NF Ø R is in 1 NF Ø All non-prime attributes are fully dependent on the candidate keys Ø No partial dependency Table not in 2 NF Inventory Supplier Part_Id Part_Name Part_Price Ø Primary Key : (Supplier, Part_Id) Ø Part_Name and Part_Price depends on the part of the key i. e. Part_Id; so, partial dependency Qty Total Amount

Table in 2 NF After decomposing the Inventory table… Inventory Supplier Part_Id Qty Total Amount Parts Part_Id Part_Name Part_Price

Third Normal Form- 3 NF Ø R is in 2 NF Ø Removes columns that are not dependent upon the primary key Ø Removes transitive dependency § If A B and B C then A C Ø Def: For every non-trivial functional dependency X A ü X is a Super Key OR ü A is a Key attribute

Table not in 3 NF Hours_Emp SSN Name Rating Hourly_Wages Hours_WOrked Ø Primary Key : SSN Ø Hourly_Wages actually depends upon Rating (dependent on non-prime attribute) but not on SSN for its existence

Table in 3 NF After decomposing Hours_Emp table… Hours_Emp SSN Name Rating Hours_WOrked Rating_Wages Rating Hourly_Wage

Boyce-Codd Normal Form- BCNF Ø Every determinant in a table must be a candidate key for that table Ø Def: For every functional dependency X A ü A έ X ; that is, it is a trivial FD ü X is a super key student A teacher teaches only one subject. For a given subject a given student has only one teacher student teacher subject 3 NF, not BCNF keys: (student, subject) (student, teacher) teacher is a determinant teacher subject BCNF but tables are not independent

What is Transaction ? • A logical unit of work on a database ü An entire program ü A portion of a program ü A single command • The entire series of steps necessary to accomplish a logical unit of work • Successful transactions change the database from one CONSISTENT STATE to another

Example of Transaction: Ø Updating name in Employee record ◦ Locate the Employee record on Disk ◦ Bring record into Buffer ◦ Update name in the Buffer ◦ Writing Data Back to Disk

ACID properties of Transaction Ø Ø Atomic Either all or none Consistent Transaction should leave the database in a consistent state Isolation The final effects of multiple simultaneous transactions must be the same as if they were executed one right after the other Durability If a transaction has been committed, the DBMS must ensure that its effects are permanently recorded in the database (even if the system crashes)

Transaction Schedule Ø Ø Ø Every transaction is seen by the DBMS as series of actions Schedule is a list of actions from set of Transactions The order in which two actions of Transaction T appear in a schedule must be same as the order in which they appear in T A schedule of T 1 and T 2 transactions

Transaction Schedule Contd. Ø Ø Ø Complete Schedule A schedule that contains either an abort or commit for each transaction whose actions listed in it Serial Schedule A schedule in which the actions of different actions are not interleaved Serializable Schedule is a schedule whose effect on any consistent database instance is identical to that of some complete serial schedule

Anomalies with Interleaved Execution Ø Two actions on the same data object conflict, if at least one of them is Write Ø Write-Read(WR) Conflict Ø Read-Write(RW) Conflict Ø Write-Write(WW) Conflict

Write-Read(WR) Conflict Ø Transaction T 2 reads a database object that has been modified by T 1 which has not committed (Dirty Read)

Read-Write(RW) Conflict Ø Transaction T 2 could change the value of an object that has been read by a transaction T 1, while T 1 is still in progress (Unrepeatable Read) T 1: Increment A by 1 T 1: Read. A(5) T 2: Decrement A by 1 Write. A(6) Read. A(5) Write. A(4)

Write-Write(RW) Conflict Ø Transaction T 2 could overwrite the value of an object which has already been modified by T 1, while T 1 is still in progress(Blind Write) T 1: Set A to 1000 Set B to 1000 T 2: Set A to 2000 Set B to 2000 T 1: Set(A, 1000) T 2: Set(B, 1000) Set(A, 2000) Set(B, 2000)

Lock-Based concurrency control Ø Ø Concurrency Control ü Coordination of simultaneous transaction execution in a multiprocessing database system ü Ensure transaction serializability in a multi-user database Strict 2 -phase locking protocol I. If a transaction wants to read (respectively , modify) an object, it first request for a shared (respectively, Exclusive) lock on the object II. All locks held by a transaction are released when transaction is completed

Questions

Sources: � Database Managements Systems by Raghu Ramakirshnan / Johannes Gehrke � http: //cs. ndsu. edu/~perrizo/classes/765/nor malisation. ppt