Database Management System course

Why Concurrency Control is needed? Problems that can occur for certain transaction schedules without appropriate concurrency control mechanisms: The Lost Update Problem This occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect. The Temporary Update (or Dirty Read) Problem This occurs when one transaction updates a database item and then the transaction fails for some reason. The updated item is accessed by another transaction before it is changed back to its original value. The Incorrect Summary Problem If one transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of these records, the aggregate function may calculate some values before they are updated and others after they are updated. a) The Lost Update Problem The update performed by T1 gets lost; possible solution: T1 locks/unlocks database object

TRANSACTION MANAGEMENT T r ansaction A transaction is a unit of a program execution that accesses and possibly modifies various data objects (tuples, relations). A transaction is a Logical unit of database processing that includes one or more access operations (read -retrieval, write - insert or update, delete). A transaction (set of operations) may be stand-alone specified in a high level language like SQL submitted interactively, or may be embedded within a program. A transaction (collection of actions) makes transformations of system states while preserving the database consistency. A user’s program may carry out many operations on the data retrieved from the database, but the DBMS is only concerned about what data is read/written from/to the database. A transaction is the DBMS’s abstract view of a user program: a sequence of reads and writes. PROPOERTIES OF TRANSACTION The DBMS need to ensure the following properties of transactions: 1. Atomicity – Transactions are eith

Multi-valued Dependencies and Fourth Normal Form (a) The EMP relation with two MVDs: ENAME —>> PNAME and ENAME —>> DNAME. (b) Decomposing the EMP relation into two 4NF relations EMP_PROJECTS and EMP_DEPENDENTS. (c) The relation SUPPLY with no MVDs is in 4NF but not in 5NF if it has the JD(R1, R2, R3). (d) Decomposing the relation SUPPLY into the 5NF relations R1, R2, and R3.   Multi-valued Dependencies and Fourth Normal Form Definition: A multi-valued dependency (MVD) X —>> Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R: If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R - (X υ Y)): t3[X] = t4[X] = t1[X] = t2[X]. t3[Y] = t1[Y] and t4[Y] = t2[Y]. t3[Z] = t2[Z] and t4[Z] = t1[Z]. An MVD X —>> Y in R is called a trivial MVD if (a) Y is a subset of

DATABASE DESIGN -2 DESIGNING A SET OF RELATIONS The Approach of Relational Synthesis (Bottom-up Design): Assumes that all possible functional dependencies are known. First constructs a minimal set of FDs Then applies algorithms that construct a target set of 3NF or BCNF relations. Additional criteria may be needed to ensure the the set of relations in a relational database are satisfactory (see Algorithms 11.2 and 11.4). Goals: Lossless join property (a must) Algorithm 11.1 tests for general losslessness. Dependency preservation property Algorithm 11.3 decomposes a relation into BCNF components by sacrificing the dependency preservation. Additional normal forms 4NF (based on multi-valued dependencies) 5NF (based on join dependencies) Properties of Relational Decompositions Relation Decomposition and Insufficiency of Normal Forms: Universal Relation Schema: A relation schema R = {A1, A2, …, An} that includes all the attributes of the database. Universal relation assumpti

D a tabase Design Informal Design Guidelines for Relation Schemas; Functional Dependencies; Normal Forms Based on Primary Keys; General Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form. Fourth Normal Form; and Fifth Normal Form; INFORMAL DESIGHN GUIDELINES FOR RELATIONAL SCHEMA 1. Semantics of the Attributes 2. Reducing the Redundant Value in Tuples. 3.Reducing Null values in Tuples. 4.Dissallowing spurious Tuples. 1. Semantics of the Attributes Whenever we are going to form relational schema there should be some meaning among the attributes.This meaning is called semantics.This semantics relates one attribute to another with some relation. 2. Reducing the Redundant Value in Tuples Mixing attributes of multiple entities may cause problems Information is stored redundantly wasting storage Problems with update anomalies Insertion anomalies Deletion anomalies Modification anomalies The main goal of the schema diagram is to minimize the storage space th