Database Management System Syllabus IOE

Evaluation Scheme

*There may be minor variations in marks distribution.

Course Outline

1. Introduction [3 Hours]

• 1.1 Concepts and Applications
• 1.2 Objective and Evolution of DBMS
• 1.3 Data Abstraction and Data Independence
• 1.4 Schema and Instances
• 1.5 Concepts of DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language)

2. Data Models [7 Hours]

• 2.1 Logical, Physical, and Conceptual Data Models
• 2.2 E-R (Entity-Relationship) Model
• 2.3 Entities and Entity Sets
• 2.4 Relationships and Relationship Sets
• 2.5 Strong and Weak Entity Sets
• 2.6 Attributes and Keys
• 2.7 E-R Diagram
• 2.8 Alternative Data Models (Hierarchical, Network, Graph)

3. Relational Languages and Relational Model [7 Hours]

• 3.1 Introduction to SQL
• 3.2 Features of SQL
• 3.3 Queries and Sub-Queries
• 3.4 Set Operations
• 3.5 Relations (Joined, Derived)
• 3.6 Queries under DDL and DML Commands
• 3.7 Embedded SQL
• 3.8 Views
• 3.9 Relational Algebra
• 3.10 Database Modification
• 3.11 QBE (Query By Example) and Domain Relational Calculus

4. Database Constraints and Normalization [6 Hours]

• 4.1 Integrity Constraints and Domain Constraints
• 4.2 Assertions and Triggers
• 4.3 Functional Dependencies
• 4.4 Multi-Valued and Join Dependencies
• 4.5 Different Normal Forms (1st, 2nd, 3rd, BCNF, DKNF)

5. Query Processing and Optimization [4 Hours]

• 5.1 Query Cost Estimation
• 5.2 Query Operations
• 5.3 Evaluation of Expressions
• 5.4 Query Optimization
• 5.5 Query Decomposition
• 5.6 Performance Tuning

6. File Structure and Hashing [4 Hours]

• 6.1 Record Organization
• 6.2 Disks and Storage
• 6.3 Remote Backup Systems
• 6.4 Hashing Concepts (Static and Dynamic Hashing)
• 6.5 Ordered Indices
• 6.6 B+ Tree Index

7. Transaction Processing and Concurrency Control [6 Hours]

• 7.1 ACID Properties
• 7.2 Concurrent Executions
• 7.3 Serializability Concepts
• 7.4 Lock-Based Protocols
• 7.5 Deadlock Handling and Prevention

8. Crash Recovery [4 Hours]

• 8.1 Failure Classification
• 8.2 Recovery and Atomicity
• 8.3 Log-Based Recovery
• 8.4 Shadow Paging
• 8.5 Advanced Recovery Techniques

9. Advanced Database Concepts [4 Hours]

• 9.1 Concepts of Object-Oriented and Distributed Database Models
• 9.2 Properties of Parallel and Distributed Databases
• 9.3 Concept of Data Warehouse Databases
• 9.4 Concept of Spatial Databases

Practical Work

1. Introduction and operations of MS Access, MySQL, or any suitable DBMS
2. Database Server Installation and Configuration (MS SQL Server, Oracle)
3. DB Client Installation and Connection to DB Server, Introduction, and practice with the SELECT Command
   4-5. Further practice with DML Commands
   6-7. Practice with DDL Commands (Create Database and Tables)
4. Practice of Procedures/Triggers and DB Administration with MySQL, PostgreSQL, DB2
   9-11. Group Project Development
5. Project Presentation and Viva

Reference Books

1. H. F. Korth and A. Silberschatz, Database System Concepts, McGraw Hill, 2010
2. A. K. Majumdar and P. Bhattacharaya, Database Management Systems, Tata McGraw Hill, India, 2004