Database Management System

Course Title: Database Management System

Course No: CT 301

Nature of the Course: Theory + Lab

Semester: 5

Full Marks: 40 + 60 + 50

Pass Marks: 16 + 24 + 20

Credit Hours: 3

Course Description

This course provides a comprehensive understanding of the principles and practices involved in the design and implementation of database systems. It enables students to develop data models, and perform data modification, processing, and management efficiently. The course also introduces advanced concepts such as object-oriented databases, data warehousing, data processing, and big data management.

Course Objectives

The objective of this course is to provide a comprehensive understanding of the principles and practices involved in the design and implementation of database systems. It enables students to develop data models, and perform data modification, processing, and management efficiently. The course also introduces advanced concepts such as object-oriented databases, data warehousing, data processing, and big data management.

Course Contents

1. Introduction

3 hrs

1.1. Application and evolution of database

1.2. Data abstraction (Physical, logical, and view level) and data independence

1.3. Schema and instances

2. Data Models

7 hrs

2.1. Introduction to data models (Entity-relationship, relational, object model, hierarchical, network, graph data models)

2.2. E-R model

Entities and entity sets

Attributes and keys

Strong and weak entity sets

Relationship and relationship sets (Mapping cardinalities)

Specialization, generalization and aggregation

2.3. Relational model

Concept of relational model, key constraints

Converting ER model into relational model

3. Relational Query Languages

7 hrs

3.1. Relational algebra

3.2. Concept of DDL, DML and DCL

3.3. Overview of the SQL query language-DDL and DML queries

3.4. Set operations

3.5. Aggregate functions – GROUP BY – HAVING

3.6. Joins and types of joins

3.7. Nested sub queries

3.8. Database modification (Insert, update, delete)

3.9. Views

3.10. Triggers and stored procedures

3.11. Privilege and roles management – GRANT and REVOKE statements

4. Database Constraints and Normalization

6 hrs

4.1. Integrity constraints and domain constraints

4.2. Assertions

4.3. Functional dependencies

4.4. Different normal forms (1NF, 2NF, 3NF, BCNF)

5. Query Processing and Optimization

4 hrs

5.1. Query processing, optimization and evaluation

5.2. Transformation of relational expressions

5.3. Techniques of implementing query optimization - Cost based optimization and heuristic optimization

5.4. Query evaluation -Materialization and pipelining

5.5. Denormalization for performance

5.6. Materialized view

5.7. Performance tuning

6. File Structure and Hashing

5 hrs

6.1. Disks and storage

6.2. Records organizations

6.3. Ordered indices

6.4. B+ tree index

6.5. Hashing concepts - Static and dynamic hashing

7. Transaction Processing and Concurrency Control

5 hrs

7.1. Transaction and transaction model - State diagram

7.2. Acid properties

7.3. Concurrent execution of transactions

7.4. Serializability (Conflict and view serializability)

7.5. Lock based protocols

7.6. Deadlock handling and prevention

7.7. Multiple granularity

8. Crash Recovery

4 hrs

8.1. Failure classification

8.2. Recovery and atomicity

8.3. Log-based recovery

8.4. Shadow paging

8.5. High availability using remote backup systems

9. Advanced Database Concepts

4 hrs

9.1. Concept of object-oriented databases

9.2. Distributed database model

9.3. Concept of data warehousing and online analytical processing

9.4. Basic concepts of NoSQL and big data

Laboratory Works

Practical sessions covering database server installation, DML and DDL queries, advanced queries with joins and subqueries, aggregation, triggers, stored procedures, query optimization, performance tuning, database administration, and a group project work.

1.Database server installation and configuration
2.DB client installation and connection to DB server
3.Further practice with DML queries
4.Advanced queries with joins and subqueries
5.Aggregation and grouping
6.Practice with DDL commands
7.Triggers and stored procedures
8.Query processing, optimization, performance tuning and database administration
9.Group project work

Text Books

1.Silberschatz, A., Korth, H.F., Sudarshan, S. (2019). Database system concepts. McGraw-Hill.
2.Elmasri, R., Navathe, S.B. (2021). Fundamentals of database systems. Pearson.

Reference Books

1.Ramakrishnan, R., Gehrke, J. (2002). Database management systems. McGraw-Hill.
2.Connolly, T.M., Begg, C.E. (2021). Database systems: A practical approach to design, implementation, and management. Pearson.

Notes:

This syllabus follows the official BCE curriculum of Tribhuwan University. In case of any doubt or revision, the university's published syllabus shall be considered authoritative. https://ioe.tu.edu.np/pages/computer-engineering-curriculum-structure-2635

Database Management System

Database Management System

Select University

Select Program

TabFlux . Database Management System . TU . BCT