Syllabus (COMPUTER APPLICATION)

Course Type: MAJ-4

Semester: 4

Course Code: BBCAMAJ04C

Course Title: Database Management Systems

(L-P-Tu): 4-2-0

Credit: 6

Practical/Theory: Combined

Course Objective: Course Objectives: Understand fundamental concepts, trends, and functions of databases. Recognize the importance of database analysis and design. Explain the role of transaction processing in data consistency and integrity. Explore concurrency control mechanisms for coordinating transactions. Understand recovery and security measures for data integrity and availability.

Learning Outcome: Course Outcomes: After the completion of this course, students will be able to: Analyze data organization requirements and establish inter-relationships. Apply appropriate data models for efficient data storage and retrieval. Design and execute queries to maintain and retrieve useful information. Evaluate physical database design for performance and optimize query processing. Apply best practices for indexing, transaction control, and concurrency maintenance.

Syllabus:

Unit I: Theory Credit: 4 (L 60)

Introduction: Traditional File Systems, Advantages of DBMS, Layered Architecture of DBMS, Data Independence, Data Models, Schemas and Instances, Database Users, DBA, Data Dictionary, Functional Components of a DBMS. [L 6]

Entity-Relationship Modelling: Entities and their types, Attributes and their types, Relationships, Degree and types of Relationship, Structural Constraints, Keys, Weak Entity Set, ER Diagrams, Specialization and Generalization, Aggregation. [L 8]

Relational Model: Basic Concepts of a Relational Model, Relational Algebra, Introduction to Relational Calculus. [L 8]

Relational Database Design: Integrity Constraints, Database Anomalies, Functional Dependencies, Armstrong’s Axioms, Closure of FD Sets, Minimal FD Set, Equivalence of FD Sets, Relational Decomposition, Lossless Decomposition, Dependency Preservation, Normalization, 1NF, 2NF, 3NF, BCNF. [L 14]

SQL: Basic Structure, DDL, DML, DCL, Constraints, Basic SQL Queries (SELECT, INSERT, DELETE, UPDATE), ORDER BY Clause, Complex Queries, Aggregate Functions, GROUP BY Clause, Nested Subqueries, Joins. [L 8]

File Structure and Indexing: Fixed-length and Variable-length Records, Spanned and Unspanned Organization of Records, File Organization (Unordered, Sequential, Hashed), Indexing Structures for Files (Primary Index, Secondary Index, Clustering Index), Multilevel Indexing using B Trees and B+ Trees. [L 8]

Transaction Processing: ACID Properties, Transaction States, Concurrent Execution, Serializability, Concurrency Control Protocols (Lock-Based Protocols). [L 8]

Unit II: DBMS Lab Credit: 2 (L 60)

Creating and managing databases, querying data, and performing basic data analysis using SQL.

Reading References:

1. R. Elmasri, S.B. Navathe, Fundamentals of Database Systems 6th Edition, Pearson Education.
2. R. Ramakrishnan, J. Gehrke, Database Management Systems, 3rd Edition, McGraw-Hill.
3. A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts 6th Edition, McGraw Hill.
4. R. Elmasri, S.B. Navathe, Database Systems Models, Languages, Design and Application Programming, Pearson Education.
5. Evan Bayross, “SQL, PL/SQL, The Programming Language of Oracle”, BPB Publication.