Database Management System - Syllabus

Embark on a profound academic exploration as you delve into the Database Management System course (DBMS) within the distinguished Tribhuvan university's CSIT department. Aligned with the 2074 Syllabus, this course (CSC260) seamlessly merges theoretical frameworks with practical sessions, ensuring a comprehensive understanding of the subject. Rigorous assessment based on a 60 + 20 + 20 marks system, coupled with a challenging passing threshold of , propels students to strive for excellence, fostering a deeper grasp of the course content.

This 3 credit-hour journey unfolds as a holistic learning experience, bridging theory and application. Beyond theoretical comprehension, students actively engage in practical sessions, acquiring valuable skills for real-world scenarios. Immerse yourself in this well-structured course, where each element, from the course description to interactive sessions, is meticulously crafted to shape a well-rounded and insightful academic experience.

Course Description: The course covers the basic concepts of databases, database system

concepts and architecture, data modeling using ER diagram, relational model, SQL, relational

algebra and calculus, normalization, transaction processing, concurrency control, and

database recovery.

Course Objective: The main objective of this course is to introduce the basic concepts of

database, data modeling techniques using entity relationship diagram, relational algebra and

calculus, basic and advanced features SQL, normalization, transaction processing,

concurrency control, and recovery techniques.

Database and Database Users

Introduction; Characteristics of the Database Approach; Actors on the Scene; Workers behind

the Scene; Advantages of Using the DBMS Approach

Database System – Concepts and Architecture

Data Models, Schemas, and Instances; Three-Schema Architecture and Data Independence;

Database Languages and Interfaces; the Database System Environment; Centralized and

Client/Server Architectures for DBMSs; Classification of Database Management Systems

Data Modeling Using the Entity-Relational Model

Using High-Level Conceptual Data Models for Database Design; Entity Types, Entity Sets,

Attributes, and Keys; Relationship Types, Relationship Sets, Roles, and Structural Constraints;

Weak Entity Types; ER Diagrams, Naming Conventions, and Design Issues; Relationship Types

of Degree Higher Than Two; Subclasses, Superclasses, and Inheritance; Specialization and

Generalization; Constraints and Characteristics of Specialization and Generalization

The Relational Data Model and Relational Database

Relational Model Concepts; Relational Model Constraints and Relational Database Schemas;

Update Operations, Transactions, and Dealing with Constraint Violations

The Relational Algebra and Relational Calculus

Unary Relational Operations: SELECT and PROJECT; Relational Algebra Operations from Set

Theory; Binary Relational Operations: JOIN and DIVISION; Additional Relational Operations;

the Tuple Relational Calculus; the Domain Relational Calculus

SQL

Data Definition and Data Types; Specifying Constraints; Basic Retrieval Queries; Complex

Retrieval Queries; INSERT, DELETE, and UPDATE Statements; Views

Relational Database Design

Relational Database Design Using ER-to-Relational Mapping; Informal Design Guidelines for

Relational Schemas; Functional Dependencies; Normal Forms Based on Primary Keys; General

Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form; Multivalued

Dependency and Fourth Normal Form; Properties of Relational Decomposition

Introduction to Transaction Processing Concepts an

Introduction to Transaction Processing; Transaction and System Concepts; Desirable Properties

of Transactions; Characterizing Schedules Based on Recoverability; Characterizing Schedules

Based on Serializability

Concurrency Control Techniques

Two-Phase Locking Technique; Timestamp Ordering; Multiversion Concurrency Control;

Validation (Optimistic) Techniques and Snapshot Isolation Concurrency Control

Database Recovery Techniques

Recovery Concepts; NO-UNDO/REDO Recovery Based on Deferred Update; Recovery

Technique Based on Immediate Update; Shadow Paging; Database Backup and Recovery from

Catastrophic Failures

Laboratory Works:

The laboratory work includes writing database programs to create and query databases using

basic and advanced features of structured query language (SQL) like

• Data definition and data Types
• Specifying constraints (primary key, foreign key, referential integrity etc.)
• Basic and complex retrieval queries
• Aggregate functions
• INSERT, DELETE, and UPDATE Statements
• Using join and views