Courses - AICPE

Course Syllabus

Course Title: Database Management Systems

Course Overview: This course is designed to provide students with a comprehensive understanding of the fundamentals of database management systems. The course covers a wide range of topics such as database design, relational algebra, SQL concepts, query processing, query optimization, transaction management, and security.

Course Objectives: By the end of this course, students should be able to:

Understand the basic concepts of DBMS
Design and implement a relational database using E-R diagrams and normalization techniques
Write complex SQL queries to manipulate and retrieve data from a database
Implement transaction management and concurrency control in a database
Apply security mechanisms to protect the database from unauthorized access
Use PL/SQL to write stored procedures and functions

Teaching Methodology: The course will be taught through a combination of lectures, discussions, and hands-on exercises. Students will be provided with access to a database management system to practice their skills. The course will also include case studies and real-world examples to help students understand how database management systems are used in industry.

Importance for Learners: Database management systems are a critical component of modern information systems. By acquiring skills in DBMS, students can improve their chances of finding employment in the technology industry. Furthermore, students who plan to start their own businesses can benefit from the ability to manage their data effectively and efficiently.

Course Contents:

Introductory concepts of DBMS

Introduction and applications of DBMS
Purpose of database
Data independence
Database system architecture
Database users and DBA

Relational Model

Structure of relational databases
Domains and relations
Relational algebra
Relational algebra queries
Tuple relational calculus

Entity-Relationship Model

Basic concepts
Design process
Constraints and keys
Design issues
E-R diagrams
Weak entity sets
Extended E-R features

Relational Database Design

Functional Dependency
Normalization
Decomposition using FD
Dependency preservation
BCNF
Multi-valued dependency
Join dependency and 5NF

Query Processing and Query Optimization

Overview
Measures of query cost
Selection operation
Sorting and Join
Evaluation of expressions
Transformation of relational expressions
Estimating statistics of expression results
Evaluation plans
Materialized views

Transaction Management

Transaction concepts
Properties of transactions
Serializability of transactions
Testing for serializability
System recovery
Two-Phase Commit protocol
Recovery and Atomicity
Log-based recovery
Concurrent executions of transactions and related problems
Locking mechanism
Solution to concurrency-related problems
Deadlock and two-phase locking protocol
Isolation
Intent locking

Security

Introduction
Discretionary access control
Mandatory Access Control
Data Encryption

SQL Concepts

Basics of SQL
DDL, DML, DCL
Structure - creation, alteration
Defining constraints - Primary key, foreign key, unique, not null, check, IN operator
Functions - aggregate functions
Built-in functions - numeric, date, string functions
Set operations, sub-queries, correlated sub-queries
Use of group by, having, order by, join and its types
Exist, Any, All, view and its types
Transaction control commands - Commit, Rollback, Savepoint