Database Management System(DBMS) Syllabus

Course Description: The course covers the basic concepts of databases, database system

concepts and architecture, data modeling using ER diagram, relational model, SQL, relational

algebra and calculus, normalization, transaction processing, concurrency control, and

database recovery.

Course Objective: The main objective of this course is to introduce the basic concepts of

database, data modeling techniques using entity relationship diagram, relational algebra and

calculus, basic and advanced features SQL, normalization, transaction processing,

concurrency control, and recovery techniques.

1. Database and Database Users

teaching hours: 2 hrs

Introduction; Characteristics of the Database Approach; Actors on the Scene; Workers behind

the Scene; Advantages of Using the DBMS Approach

2. Database System – Concepts and Architecture

teaching hours: 3 hrs

Data Models, Schemas, and Instances; Three-Schema Architecture and Data Independence;

Database Languages and Interfaces; the Database System Environment; Centralized and

Client/Server Architectures for DBMSs; Classification of Database Management Systems

3. Data Modeling Using the Entity-Relational Model

teaching hours: 6 hrs

Using High-Level Conceptual Data Models for Database Design; Entity Types, Entity Sets,

Attributes, and Keys; Relationship Types, Relationship Sets, Roles, and Structural Constraints;

Weak Entity Types; ER Diagrams, Naming Conventions, and Design Issues; Relationship Types

of Degree Higher Than Two; Subclasses, Superclasses, and Inheritance; Specialization and

Generalization; Constraints and Characteristics of Specialization and Generalization

4. The Relational Data Model and Relational Database

teaching hours: 3 hrs

Relational Model Concepts; Relational Model Constraints and Relational Database Schemas;

Update Operations, Transactions, and Dealing with Constraint Violations

5. The Relational Algebra and Relational Calculus

teaching hours: 5 hrs

Unary Relational Operations: SELECT and PROJECT; Relational Algebra Operations from Set

Theory; Binary Relational Operations: JOIN and DIVISION; Additional Relational Operations;

the Tuple Relational Calculus; the Domain Relational Calculus

6. SQL

teaching hours: 8 hrs

Data Definition and Data Types; Specifying Constraints; Basic Retrieval Queries; Complex

Retrieval Queries; INSERT, DELETE, and UPDATE Statements; Views

7. Relational Database Design

teaching hours: 7 hrs

Relational Database Design Using ER-to-Relational Mapping; Informal Design Guidelines for

Relational Schemas; Functional Dependencies; Normal Forms Based on Primary Keys; General

Definitions of Second and Third Normal Forms; Boyce-Codd Normal Form; Multivalued

Dependency and Fourth Normal Form; Properties of Relational Decomposition

8. Introduction to Transaction Processing Concepts an

teaching hours: 4 hrs

Introduction to Transaction Processing; Transaction and System Concepts; Desirable Properties

of Transactions; Characterizing Schedules Based on Recoverability; Characterizing Schedules

Based on Serializability

9. Concurrency Control Techniques

teaching hours: 4 hrs

Two-Phase Locking Technique; Timestamp Ordering; Multiversion Concurrency Control;

Validation (Optimistic) Techniques and Snapshot Isolation Concurrency Control

10. Database Recovery Techniques

teaching hours: 3 hrs

Recovery Concepts; NO-UNDO/REDO Recovery Based on Deferred Update; Recovery

Technique Based on Immediate Update; Shadow Paging; Database Backup and Recovery from

Catastrophic Failures

Laboratory Works:

The laboratory work includes writing database programs to create and query databases using

basic and advanced features of structured query language (SQL) like

• Data definition and data Types
• Specifying constraints (primary key, foreign key, referential integrity etc.)
• Basic and complex retrieval queries
• Aggregate functions
• INSERT, DELETE, and UPDATE Statements
• Using join and views