Course Content

Other Courses

Tribhuvan university BIT BIT Curriculum Database Management System

Database Management System - Syllabus

Course Overview and Structure
Syllabus Notes Old Questions Unit Wise Questions Question Bank

Embark on a profound academic exploration as you delve into the Database Management System course (DBMS) within the distinguished Tribhuvan university's BIT department. Aligned with the BIT Curriculum, this course (BIT202) 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 Objectives: 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.

Units

Key Topics

  • Introduction to Databases
    DA-1

    Introduction to databases, including examples and basic concepts.

  • Database Management System
    DA-2

    Introduction to Database Management Systems (DBMS), including advantages and examples.

  • Database Users
    DA-3

    Types of database users, including actors on the scene and workers behind the scene.

  • Benefits of Databases
    DA-4

    Advantages and benefits of using databases.

  • Data Models
    DA-5

    Types of data models, including hierarchical, network, ER, relational, and object models.

  • Three-Schema Architecture
    DA-6

    Three-schema architecture, including internal, conceptual, and external views.

  • Data Independence
    DA-7

    Data independence, including logical and physical independence.

  • Database Languages
    DA-8

    Types of database languages, including DDL, DML, SDL, and VDL.

  • Database Interfaces
    DA-9

    Types of database interfaces, including menu-based, mobile apps, form-based, and GUI.

  • Database System Environment
    DA-10

    The database system environment, including DBMS component modules, utilities, tools, and communications facilities.

  • Database Architectures
    DA-11

    Types of database architectures, including centralized, client/server, one-tier, two-tier, three-tier, and N-tier.

  • Classification of DBMS
    DA-12

    Classification of Database Management Systems, including single and multi-user, centralized and distributed, homogeneous and heterogeneous, and general-purpose and special-purpose.

Key Topics

  • Introduction to Databases
    DA-1

    Introduction to databases, including examples and basic concepts.

  • Database Management System
    DA-2

    Introduction to Database Management Systems (DBMS), including advantages and examples.

  • Database Users
    DA-3

    Types of database users, including actors on the scene and workers behind the scene.

  • Benefits of Databases
    DA-4

    Advantages and benefits of using databases.

  • Data Models
    DA-5

    Types of data models, including hierarchical, network, ER, relational, and object models.

  • Three-Schema Architecture
    DA-6

    Three-schema architecture, including internal, conceptual, and external views.

  • Data Independence
    DA-7

    Data independence, including logical and physical independence.

  • Database Languages
    DA-8

    Types of database languages, including DDL, DML, SDL, and VDL.

  • Database Interfaces
    DA-9

    Types of database interfaces, including menu-based, mobile apps, form-based, and GUI.

Key Topics

  • Relational Model Concepts
    TH-1

    This topic covers the fundamental concepts of the relational model, including domains, attributes, tuples, and relations, as well as the characteristics of relations.

  • Relational Model Constraints
    TH-2

    This topic explores the different types of constraints in the relational model, including domain constraints, key constraints, and constraints on null values.

  • Relational Database Schemas
    TH-3

    This topic discusses the concept of relational database schemas, including relational database state, entity integrity, referential integrity, and foreign keys.

  • Update Operations and Transactions
    TH-4

    This topic covers update operations, transactions, and how to deal with constraint violations, including insert, delete, and update operations, as well as restrict, cascade, set null, and set default.

  • Basic Relational Algebra Operations
    TH-5

    This topic introduces basic relational algebra operations, including unary operations (select, project, rename) and binary operations (set theory, Cartesian product, join, and outer join).

Key Topics

  • Data Definition and Data Types
    SQ-01

    This topic covers the basics of data definition and data types in SQL, including attribute data types and domains, creating databases and tables, and dropping constraints and databases.

  • Specifying Constraints
    SQ-02

    This topic explains how to specify constraints in SQL, including attribute constraints, attribute defaults, key and referential integrity constraints, and naming constraints using CHECK.

  • Basic Retrieval Queries
    SQ-03

    This topic covers the basics of retrieval queries in SQL, including the SELECT-FROM-WHERE structure, ambiguous attribute names, aliasing, renaming, and tuple variables, as well as unspecified WHERE clauses and the use of * in Select.

  • Complex Retrieval Queries
    SQ-04

    This topic explores advanced retrieval queries in SQL, including the use of IS NULL, logical connectives, nested queries, correlated nested queries, and aggregate functions, as well as JOIN, Natural JOIN, OUTER JOIN, and GROUP BY and HAVING clauses.

  • Data Manipulation Statements
    SQ-05

    This topic covers data manipulation statements in SQL, including INSERT, DELETE, and UPDATE statements.

  • Views
    SQ-06

    This topic explains how to create and drop views in SQL.

Key Topics

  • Relational Database Design Using ER-to-Relational Mapping
    RE-1

    Learn how to design relational databases using ER-to-relational mapping, including mapping of regular entities, weak entities, relationship types, multivalued attributes, and N-ary relationships.

  • Informal Design Guidelines for Relational Schemas
    RE-2

    Understand informal design guidelines for relational schemas, including semantics of attributes in relations, redundant information in tuples and update anomalies, NULL values in tuples, and generation of spurious tuples.

  • Functional Dependencies
    RE-3

    Study functional dependencies, including definition, inference rules, Armstrong's axioms, attribute closure, equivalence of functional dependencies, and minimal sets of functional dependencies.

  • Normal Forms Based on Primary Keys
    RE-4

    Explore normal forms based on primary keys, including First Normal Form, Second Normal Form, Third Normal Form, and their general definitions.

  • Boyce-Codd Normal Form
    RE-5

    Learn about Boyce-Codd Normal Form, a higher normal form that ensures a relational schema is in a good structure.

  • Multivalued Dependency and Fourth Normal Form
    RE-6

    Understand multivalued dependency and Fourth Normal Form, which eliminates multivalued dependencies in a relational schema.

  • Properties of Relational Decomposition
    RE-7

    Study properties of relational decomposition, including attribute preservation, dependency preservation, and loss-less/non-additive join.

Key Topics

  • Introduction to Transaction Processing
    TR-1

    This topic introduces the concept of transaction processing, highlighting the differences between single user and multi-user systems, read/write operations, and the need for concurrency control to avoid problems such as lost update, temporary update, incorrect summary, and unrepeatable read.

  • Transaction and System Concepts
    TR-2

    This topic covers the fundamental concepts of transactions, including transaction states, system log, and commit point of transaction.

  • Desirable Properties of Transactions
    TR-3

    This topic discusses the desirable properties of transactions, namely atomicity, consistency, isolation, and durability (ACID).

  • Schedules and Concurrency Control
    TR-4

    This topic explores schedules, conflicting operations, and characterizing schedules based on recoverability and serializability, including serial, non-serial, and conflict serializable schedules.

  • Concurrency Control Techniques
    TR-5

    This topic introduces concurrency control techniques, including two-phase locking and timestamp ordering.

  • Two-Phase Locking
    TR-6

    This topic delves into two-phase locking, including types of locks, basic, conservative, strict, and rigorous two-phase locking, deadlock and starvation, deadlock prevention, and deadlock detection.

  • Timestamp Ordering
    TR-7

    This topic covers timestamp ordering, including timestamp, read timestamp, write timestamp, basic timestamp ordering, and strict timestamp ordering.

Key Topics

  • Introduction to Databases
    DA-1

    Introduction to databases, including examples and basic concepts.

  • Database Management System
    DA-2

    Introduction to Database Management Systems (DBMS), including advantages and examples.

  • Database Users
    DA-3

    Types of database users, including actors on the scene and workers behind the scene.

  • Benefits of Databases
    DA-4

    Advantages and benefits of using databases.

  • Data Models
    DA-5

    Types of data models, including hierarchical, network, ER, relational, and object models.

  • Three-Schema Architecture
    DA-6

    Three-schema architecture, including internal, conceptual, and external views.

  • Data Independence
    DA-7

    Data independence, including logical and physical independence.

Structured and Unstructured Data; Introduction to NoSQL Databases (NoSQL Database, Types of NoSQL Database, Advantage of NoSQL); Discussion of basic architecture of Hbase, Cassandra and MongoDb;

Lab works

 The laboratory work includes design and implementation of database. Students should practice logical design of databases (ER Models) using case tools like draw.io, MS-Visio etc. The so designed database should be implemented by writing database programs using MYSQL, ORACLE etc. The database programs should include the DDL and DML statements like;

- Create, Drop, Alter, Rename of database/table/constraints

- INSERT, DELETE, and UPDATE Statements

- Specifying constraints (primary key, foreign key, referential integrity etc.)

- Basic and complex DML queries using select from where clause including the use of Order By, LIKE, IN, Exists, Between, Logical Connectives, LIMIT etc. - Join Operations

- Set Theory Operations Union, Intersect, Set Difference

- Nested Queries

- Aggregate functions

- Group By Having Clause

- Create Views / Drop Views

- Concepts of transaction, Backup and recovery

In addition, the students should practice and get familiar with the basics of NoSQL Database