Database Management System

Course Title: Database Management System

Course No: BDS152

Nature of the Course: Theory + Lab

Semester: 2

Full Marks: 45 + 30

Pass Marks: 18 + 12

Credit Hours: 3

Course Description

A course in database management systems provides an in-depth understanding of how to design, implement, and maintain a database. The course covers the fundamentals of database management system, data modeling, relational database, structured query language, normalization, transaction, concurrency control, and recovery.

Course Objectives

The main objective of this course is to provide fundamental concepts of database management system including data modeling, database design, SQL, transaction, concurrency control and database recovery.

Course Contents

1. Introduction to Database and Database Users

2 hrs

1.1. Introduction to Database, Database Management System

1.2. Database System Environment

1.3. Characteristics of Database Approach

1.4. Actors in Scene

1.5. Workers behind Scene

1.6. Advantages of DBMS

2. Database System Concepts

4 hrs

2.1. Data Models, Schemas, Instances

2.2. Three Schema Architecture, Data Independence

2.3. DBMS Languages, DBMS Interfaces

2.4. Database System Environment

2.5. Centralized and Client/Server Architectures for DBMS

2.6. Classification of DBMS

3. Data Modelling using ER Model

7 hrs

3.1. Data Models, Hierarchical Model, Network Model, Relational Model, Object Base Data Model, Entity Relationship Model

3.2. Entity Types, Entity Sets, Attributes, Keys, Relationship Types, Relationship Sets, Roles, Structural Constraints

3.3. Weak Entity Types

3.4. ER Diagrams, Naming Conventions, Design Issues

3.5. Relationship Types: Binary, Ternary, N-ary

3.6. Extended Entity Relationship, Subclasses, Superclasses, and Inheritance

3.7. Specialization and Generalization

3.8. Constraints and Characteristics of Specialization and Generalization, Union Types

4. Relational Data Model and Constraints

5 hrs

4.1. Relational Model Concepts, Domains, Attributes, Tuples, Relations, Characteristics of Relations, Relational Model Notation

4.2. Relational Model Constraints and Relational Database Schemas, Domain Constraints, Key Constraints, Null Value Constraints, Relational Databases, Relational Database Schemas

4.3. Entity Integrity, Referential Integrity, Foreign Keys

4.4. Update Operations and Dealing with Constraint Violations, Insert, Delete and Update Operations, ER to Relational Mapping

5. Structured Query Language (SQL)

12 hrs

5.1. Data Definition and Data Types, CREATE Schema, CREATE Table, Attribute Data Types and Domains

5.2. Specifying Constraints, Attribute Constrains and Defaults, Specifying Keys and Referential Integrity Constraints, Naming Constraints, Constraint using CHECK

5.3. Basic Retrieval Queries, SELECT-FROM-WHERE, Ambiguous Attribute Name, Aliasing, Renaming, Unspecified WHERE Clause, Using Asterisk, Pattern Matching and Arithmetic Operators, ORDER BY

5.4. Complex Retrieval Queries, Dealing with NULL Values, Nested Queries, Correlated Nested Queries, EXISTS and UNIQUE Functions, Inner Join and Outer Joins, Aggregate Functions, GROUP BY and HAVING Clauses

5.5. INSERT, DELETE, and UPDATE Statements

5.6. Views, DROP and ALTER Commands

6. Functional Dependencies and Normalization for Relational Databases

6 hrs

6.1. Informal Design Guidelines for Relational Schemas Insertion, Deletion, Modification Anomalies, Spurious Tuples

6.2. Functional Dependencies

6.3. Normalization, First, Second and Third Normal Forms

6.4. Boyce-Codd Normal Form

6.5. Multivalued Dependency and Fourth Normal Form

6.6. Properties of Relational Decomposition, Dependency Preservation, Nonadditive(lossless) Join Properties

7. Concepts of Transaction Processing

7 hrs

7.1. Introduction to Transaction Processing, Transactions, Database Items, Read Write Operations, Need of Concurrency Control, Need of Recovery

7.2. Transaction and System Concepts, Transaction States and Operations, Log, Commit Point

7.3. Properties of Transactions

7.4. Schedules, Conflicting Operations, Characterizing Schedules Based on Recoverability

7.5. Characterizing Schedules Based on Serializability, Serial, Non-serial and Conflict-Serializable Schedule, Conflict Equivalence, Serializable Schedules, Precedence Graph for Serializability Testing, Using Serializability for Concurrency Control

8. Concurrency Control and Database Recovery Techniques

5 hrs

8.1. Two-Phase Locking Techniques, Binary Locks, Shared/Exclusive Locks, Basic, Conservative, Strict, and Rigorous Two-Phase Locking

8.2. Deadlock and Starvation Timestamp Ordering

8.3. ,Timestamp, Timestamp Ordering Algorithm for Concurrency Control, Recovery Concepts

8.4. Immediate and Differed Updates, Buffering, Write Ahead Logging, Steal/No-Steal, Force/No-Force, Checkpoints, Cascading Rollback, NO-UNDO/REDO Recovery Based on Deferred Update

8.5. Recovery Technique Based on Immediate Update

8.6. Shadow Paging

8.7. Database Backup

Laboratory Works

The laboratory work includes designing conceptual ER diagrams using some CASE tools. The laboratory work further should include writing database queries in SQL for creating databases, schemas, and tables, inserting values, updating, manipulating, altering and deleting data as well as databases, tables and schemas.

1.Database Management System Laboratory

Reference Books

1.Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems; Seventh Edition, Pearson Education.
2.Avi Silberschatz, Henry F Korth, S Sudarshan, Database System Concepts, Seventh Edition, McGraw-Hill.
3.Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, Third Edition; McGraw-Hill.
4.C. J. Date, An Introduction to Database Management Systems, 8th Edition, Pearson
5.Jaffrey D. Ullman, Jennifer Widom, A First Course in Database Systems, Third Edition, Pearson Education Limited.

Notes:

This syllabus follows the official BDS curriculum of Tribhuvan University. In case of any doubt or revision, the university's published syllabus shall be considered authoritative.

Database Management System

Database Management System

Select University

Select Program

TabFlux . Database Management System . TU . BDS