Database Management Systems

Course Title: Database Management Systems

Course No: CSIT.223

Nature of the Course: Theory + Lab

Semester: 4

Full Marks: 60 + 20 + 20

Pass Marks: 24 + 10 + 10

Credit Hours: 3

Course Description

The purpose of this course is to introduce the fundamental concepts of database management, including aspects of data models, database languages, and database design. At the end of this course, a student will be able to understand and apply the fundamental concepts required for the use and design of database management systems.

Course Objectives

Become proficient at modelling databases at conceptual and logical levels of design; develop database schemas with principled design that enforce data integrity; become knowledgeable in the creation, altering, and manipulation of tables, indexes, and views using relational algebra and SQL; become proficient at casting queries in SQL; write database application programs with an understanding of transaction management, concurrency control, and crash recovery.

Course Contents

1. Unit I: Database System Introduction

5 hrs

1.1. Basic Terminologies: Data vs Information, Data Hierarchy, Database, Database Management System, Database System, Relational Database Management Systems

1.2. Data Management Approaches: File Management Systems, Database Management Systems, Limitations, Advantages, and Applications

1.3. Database Schema and Instance, Data Abstraction (views of Data), Data Independence, Database Languages, Database Users and Administrator, Transaction Management

1.4. Data Models: Hierarchical, Network, Entity Relationship, Relational, and Object Oriented data model

1.5. Database System Structure, Database Application Architecture, Classification of DBMSs

2. Unit II: Entity Relationship Data Modeling

5 hrs

2.1. ER Model and ER Diagrams, Components of ER Model, Types of Attributes

2.2. Degree of Relationship, Constraints on ER Model (Mapping Cardinalities and Participation Constraints), Keys and Types of Keys, Weak Entity Sets

2.3. Extended ER Modelling: Subclass/Superclass Relationship, Specialization and Generalization, Constraints on Specialization/Generalization, Aggregation, Hierarchies, Lattices, Shared Subclasses, Categories

2.4. Relational Model: Introduction, Structure of Relational Databases, Schema Diagram, Mapping ER Model to Relational Database

3. Unit III: Relational Algebra and Relational Calculus

8 hrs

3.1. Introduction of Relational Algebra (RA), Fundamental Operations of RA: Select, Project, Set Union, Set Difference, Cartesian Product and Rename Operations

3.2. Additional Relational Algebra Operations: Set Intersection, Natural Join, Division and Assignment Operation

3.3. Extended Relational Algebra Operations: Generalized Projection, Outer Join and Aggregate Functions

3.4. Database Modification: Insert, Delete and Update Operation

3.5. Null Values, Advantages and Limitations of Relational Algebra

3.6. Relational Calculus: Introduction and Expressive Power of Relational and Domain Calculus, Sample Queries Using Relational and Domain Calculus

3.7. Introduction to Query by Example (QBE) and Sample Queries

4. Unit IV: Structured Query Language

8 hrs

4.1. Introduction: Basic Structure of SQL Query, SELECT, FROM and WHERE clause, Using Multiple Relations

4.2. Strings and Pattern Matching, Ordering the Display of Tuples, Join Operations: Join Types and Join Conditions

4.3. Nested Queries: Set Membership Test, Set Comparison and Test for Empty Relations

4.4. Aggregate Functions, Group by Clause and Having Clause

4.5. Database Modifications: Insert, Delete and Update Operations

4.6. Data Definition Language: Domain Types in SQL, Create, Alter and Drop statements

4.7. View and Modification of Views, Embedded and Dynamic SQL

5. Unit V: Integrity Constraints

3 hrs

5.1. Concept and Importance of Integrity Constraints, Data Integrity

5.2. Domain Constraints: Not Null Constraints, Unique Constraints, Primary Key Constraints, Check Constraints

5.3. Referential Integrity: Using Referential Integrity, Cascading Actions

5.4. Assertions and Triggers: Creating and Deleting Assertions, Creating and Deleting Triggers, Assertions vs Triggers

6. Unit VI: Relational Database Design

4 hrs

6.1. Introduction, Database Modification Anomalies, Functional Dependencies (FDs), Types of FDs, FD Inference Rules

6.2. Closure of Set of FDs, Closure of Set of Attributes, Covers

6.3. Normalization: Purpose and Concept of Normalization, Forms of Normalization: 1-NF, 2-NF, 3-NF, BCNF

6.4. Lossless Decomposition

7. Unit VII: Authentication and Authorization

2 hrs

7.1. Authentication vs Authorization, Classification of DB Security, Levels of DB Security

7.2. Types of Authorization, Creating Users, Granting and Revoking Authorizations in SQL, CASCADE and RESTRICT

7.3. Concept of Roles, Authorization using Roles

8. Unit VIII: Indexing

2 hrs

8.1. Concept of Indexing, Index File vs Data File, Index Key Structure

8.2. Types of Indices: Ordered vs Unordered Indices, Primary vs Secondary Indices

8.3. Primary Indices: Dense and Sparse Indices with their Strengths and Drawbacks, Indexing Evaluation

9. Unit IX: Transaction and Recovery

6 hrs

9.1. Transaction Processing: Desirable Properties of Transactions, Concurrent Executions, Schedules and Recoverability, Testing for Serializability

9.2. Concurrency Control: Overview of Concurrency Control, Locking Techniques, Lock-Based Protocols, Timestamp-Based Protocols, Commit Protocols, Granularity of Data Items, Time Stamp Ordering Multi Version Concurrency Control

9.3. Database Recovery: Failure Classification, The Storage Hierarchy, Transaction Model, Log-Based Recovery Techniques, Buffer Management, Checkpoints, Shadow Paging, Failure with Loss of Non-volatile Storage

Laboratory Works

Students design ER diagrams of an organization or subsystem using tools like Visio. Those ER diagrams are converted into relational model and database schema created using DDL. Relations are populated with data and queries written to cover all DML features. Creating views and indices for the database is also required. Students can use DBMS systems like Oracle, MySQL, SQL Server etc. (MS Access not accepted as lab platform).

1.ER Diagram Design
2.ER to Relational Model Conversion and Schema Creation
3.DML Queries and Data Manipulation
4.Views and Indices

Text Books

1.Silberschatz, H.F. Korth, and S. Sudarshan, Database System Concepts, 6th Edition, McGraw Hill, 2010.

Reference Books

1.Raghu Ramakrishnan, and Johannes Gehrke, Database Management Systems, 3rd Edition, McGraw-Hill, 2007.
2.Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems, 6th Edition, Pearson Addison Wesley, 2010.

Notes:

This syllabus follows the official CSIT curriculum of Far Western 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 Systems . FWU . BSc. CSIT