Dbms Important Topics 236i71

(101IT03) DATABASE MANAGEMENT SYSTEMS (Important Topics) UNIT - I • DBMS Vs File System – View of Data – Data Abstraction – Instances and Schemas • Database Languages – DDL – DML – DCL - TCL – data base s and (DBA) • Database System Structure/ Architecture – Storage Manager – the Query Processor • Data base design – Steps in Deg a database - ER diagrams – Data Models – ER Model & Relational Model - Attributes and Entity sets – Relationships and Relationship sets - Cardinality • Beyond ER Design Entities – Additional features of ER Model or EER (enhanced/extended ER)– includes aggregation, specialization, generalization, weak entity sets etc... • Conceptual Design for Large enterprises – implies Attribute vs Entity, Relation vs entity, Ternary vs Binary relation UNIT - II • Integrity Constraints – Enforcing Integrity constraints – (Not Null, Primary, Foreign, Check etc) - Logical database Design • Introduction to Views – Destroying /altering Tables and Views • Relational Algebra – All Operations – including s, Division • Relational calculus – Tuple relational Calculus (TRC) – Domain relational calculus (DRC) • Go through queries using relational algebra operations and write same queries in SQL as well UNIT - III • Same as your DBMS Lab • Go through all SQL Queries – including Syntax and Examples of Basic SQL Queries – Nested Queries – Correlated Nested Queries – Comparison Operators – Aggregative Operators – String operators - NULL values – Comparison using Null values – Logical connectivity’s – AND, OR and NOT – all S – Disallowing NULL values – • Triggers • Practice all SQL queries in Relational Algebra as well • (You can expect same question to be answered in both SQL and Relational Algebra and TRC) UNIT - IV • Schema refinement – Problems Caused by redundancy – Decompositions – Problem related to decomposition • Functional Dependencies (FD) - Armstrong's Axioms – CLOSURE of FD (F+) and Closure of Attributes • Normal Forms - FIRST, SECOND, THIRD, BCNF (1NF, 2NF, 3NF, BCNF) • Lossless Decomposition – Dependency preserving Decomposition • Multi valued Dependencies – FOURTH Normal Form (4NF) UNIT - V • Transaction Concept- Transaction State diagram- different states – ACID properties • Implementation of Atomicity, Isolation and Durability – Concurrent and Serial Executions – Shadow Database concept • Serializability – Conflict Serializability & View Serializability - Testing for serializability – Precedence Graph • Protocols - Lock based Protocols – Time stamp Based Protocols – Multiple Granularity • Intention Shared & Exclusive Locks vs Normal shared & Exclusive locks – go through lock matrix of each • Recoverability – Log Based Recovery – Checkpoints - Buffer Management - Remote Backup systems • Advance Recovery systems – ARIES algorithm UNIT - VI • File Organization and Indexing – Dense, Sparse, Cluster Indexes, Primary and Secondary Indexes – Index data Structures • Hash Based Indexing – Tree base Indexing • Indexed Sequential Access Methods (ISAM) • B+ Tree (Searching, Inserting, Deleting)