Database Management System

Course Title: Database Management System

Course No: ENCT 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 hrs3 marks

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 hrs9 marks

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 hrs9 marks

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 hrs9 marks

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 hrs6 marks

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 hrs8 marks

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 hrs8 marks

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 hrs4 marks

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 hrs4 marks

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. (45 hours)

1.Database server installation and configuration
2.DB client installation and connection to DB server. Introduction and practice with SELECT command with the existing DB
3.Further practice with DML queries – Select, insert, update and delete
4.Advanced queries with joins and subqueries
5.Aggregation and grouping
6.Practice with DDL commands – Create/alter/drop table, integrity constraints and views
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 BCT 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-NEW