Database Management Systems (CSE 2-1)

Objectives:

To understand the basic concepts and the applications of database systems.

To master the basics of SQL and construct queries using SQL.

To understand the relational database design principles.

To become familiar with the basic issues of transaction processing and concurrency control.

To become familiar with database storage structures and access techniques.

Syllabus:

UNIT-I

Introduction-Database System Applications, Purpose of Database Systems, View of Data - Data Abstraction, Instances and Schemas, Data Models, Database Languages - DDL, DML,Database Architecture, Database Users and Administrators, History of Data base Systems.Introduction to Data base design , ER diagrams, Beyond ER Design, Entities, Attributes and Entity sets, Relationships and Relationship sets, Additional features of ER Model, Conceptual Design with the ER Model, Conceptual Design for Large enterprises. 

Relational Model:Introduction to the Relational Model - Integrity Constraints over Relations, Enforcing Integrity constraints, Querying relational data, Logical data base Design, Introduction to Views Destroying/ altering Tables and Views.

UNIT-II

Relational Algebra and Calculus: Relational Algebra - Selection and Projection, Set operations, Renaming, Joins, Division, Examples of Algebra Queries, Relational calculus - Tuple relational Calculus - Domain relational calculus - Expressive Power of Algebra and calculus. Form of Basic SQL Query - Examples of Basic SQL Queries, Introduction to Nested Queries, Correlated Nested Queries, Set - Comparison Operators, Aggregate Operators, NULL values- Comparison using Null values - Logical connectives - AND, OR and NOT - Impact on SQL Constructs, Outer Joins, Disallowing NULL values, Complex Integrity Constraints in SQL Triggers and Active Data bases.

UNIT-III

Introduction to Schema Refinement - Problems Caused by redundancy, Decompositions - Problem related to decomposition, Functional Dependencies - Reasoning about FDS, Normal Forms - FIRST, SECOND, THIRD Normal forms - BCNF - Properties of Decompositions - Loss less join Decomposition, Dependency preserving Decomposition, Schema Refinement in Data base Design - Multi valued Dependencies - FOURTH Normal Form, Join Dependencies, FIFTH Normal form, Inclusion Dependencies.

UNIT-IV

Transaction Management - Transaction Concept - Transaction State - Implementation of Atomicity and Durability - Concurrent - Executions - Serializability - Recoverability Implementation of Isolation - Testing for serializability.Concurrency Control - Lock - Based Protocols - Timestamp Based Protocols - Validation - Based Protocols - Multiple Granularity. Recovery System-Failure Classification-Storage Structure-Recovery and Atomicity - Log - Based Recovery - Recovery with Concurrent Transactions - Buffer Management - Failure with loss of nonvolatile storage - Advance Recovery systems - Remote Backup systems.

UNIT-V

Overview of Storage and Indexing: Data on External Storage, File Organization and Indexing - Clustered Indexes, Primary and Secondary Indexes, Index data Structures - Hash Based Indexing, Tree based Indexing, Comparison of File Organizations. Tree Structured Indexing: Intuitions for tree indexes, Indexed Sequential Access Methods(ISAM) B+ Trees: A Dynamic Index Structure, Search, Insert, Delete.

Hash Based Indexing: Static Hashing, Extendable hashing, Linear Hashing, Extendible vs. Linear Hashing.

Material's For D.B.M.S :

Study Material For 5 units(Tutorial Point PDF) : Download

2 Mark's & 10 Mark's For 5 units with Answers : Download