Database Management System Syllabus - BIM (TU)

Course Objective

The main objective of this module is to provide strong theoretical and practical knowledge of the database management system.

Course Description

Database system, Data Abstraction, Data Models, Database users, Entity-Relation Model, Constraints, E- R Diagrams, Design of E-R Database Schema, Relational Data Model, Structure of Relational Database, Relational Algebra, Fundamental Operations, Additional Operating, Modifying the database, Structured Query Language Data Definition Language, Data manipulation Language, Transaction Control Language, Join Operations, Integrity Constraints, Assertion, Triggers, Relational database design issues, Normalization, Transaction Management, Database System Architectures.

Course Details

Unit 1: Introduction – Database Management Systems                                                    LH 4

Purpose of Database Systems. Data Abstraction. Data Models: The E-R Model, The Object- Oriented Model, The Relational Model, The Network Model, The Hierarchical Model, Physical Data Models. Instances and Schemes. Data Independence. Database Administrator. Database Users. Application Architecture (One tier, two tier and n-tire). Overall Database System Structure and Components.

Unit 2: Entity-Relationship Model                                      LH 8

Entities and Entity Sets. Relationships and Relationship Sets. Attributes. Mapping Constraints. Keys (Super key, Candidate key and Primary key): Primary Keys for Entity Sets and Relationship Sets. The Entity Relationship Diagram. Reducing E-R Diagrams to Tables: Representation of Strong Entity Sets, Representation of Weak Entity Sets, Representation of Relationship Sets. Generalization and Specialization. Aggregation. Mapping Cardinalities: Representation of Mapping Cardinalities in E-R Diagram. Use of Entity or Relationship Sets. Use of Extended E-R Features. Design of an E-R Database Scheme (Case study).

Unit 3: Structured Query Language (SQL)                                                                    LH 15

Background, Data Definition Language: Domain Types in SQL, Schema Definition in SQL. Data Manipulation Language: The select Clause, The where Clause, The from Clause, The Rename Operation, Tuple Variables, String Operations, Ordering the Display of Tuples, Duplicate Tuples. Set Operations. Aggregate Functions. Null Values. Nested Subqueries: Set Membership, Set Comparison, Test for Empty Relations, Test for the Absence of Duplicate Tuples. Derived Relations: Views. Modification of the Database: Deletion, Insertion, Updates, Updates, Update of a view. Joined Relations: Join types and Conditions. Embedded SQL. Dynamic SQL. Transaction Control Language (Commit, Rollback).

Unit 4: Integrity Constraints                                                LH 5

Domain Constraints. Referential Integrity: Basic Concepts, Referential Integrity in the E-R Model, Database Modification, Referential Integrity in SQL

Unit 5: Relational Database Design                                    LH 6

Pitfalls in Relational DB Design. Representation of Information: Anomalies. Functional Dependencies: Basic Concepts, Closure of a Set of Functional Dependencies, Closure of Attribute Sets. Decomposition: Lossless-Join Decomposition, Dependency Preservation. Normalization: First Normal Form, Second Normal Form, Third Normal Form, Boyce-Codd Normal Form, Comparison of BCNF and 3NF.

Unit 6: Transaction Management                                             LH 5

ACID Properties. Transaction States: Implementation of Atomicity and Durability, Serializability, Basic Concept of Concurrency Control and Recovery, Locking Protocols, Time Stamp Based Protocol.

Unit 7: Case Study                                                                    LH 5

MSSQL server, ORACLE, MYSQL

Note:

• The students are required to undertake a project The project work can be done individually or in group (at most 4-5 students). The format of the project report is as follows:
  • Project Description
  • Description of entities or object considered in the project
  • Algorithm or Diagram showing description of project
  • Conclusion of the project

The project report should be original, and the reproduction of others' work is strictly prohibited. Number of pages of the report should be at least 4.

References:

Abraham Silberchatz, Henry F. Korth, S. Sudarshan; Database System Concepts, McGraw Hill 4^th ed. Date, C.J.; An Introduction to Database System, Addison Wesley, 8^th ed.

RAMEZ ELMASRI, B. NAVATHE, Fundamentals of Database System, Pearson Education Asia, Fifth Edition.