BITM Database Management System
bitmsemester 4
Database Management System
Subject Code: IT220
Course Title: Database Management System
Course No: IT220
Nature of Course: Theory & Practical
Full Marks: 60 + 40
Pass Marks: 30 + 20
Credit Hours: 3
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 Objective
The main objective of this module is to provide strong theoretical and practical knowledge of the database management system.
Course Contents
| Course Contents | Teaching Methodology | Teaching Hours |
|---|---|---|
| Unit 1: Introduction – Database Management Systems (4 Hrs.) | ||
| Purpose of Database Systems. | Lecture | |
| 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 (8 Hrs.) | ||
| Entities and Entity Sets. | Lecture | |
| 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) (15 Hrs.) | ||
| Background, Data Definition Language: Domain Types in SQL, Schema Definition in SQL. | Lecture | |
| 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 (5 Hrs.) | ||
| Domain Constraints. | Lecture | |
| Referential Integrity: Basic Concepts, Referential Integrity in the E-R Model, Database Modification, Referential Integrity in SQL | ||
| Unit 5: Relational Database Design (6 Hrs.) | ||
| Pitfalls in Relational DB Design. | Lecture | |
| 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 (5 Hrs.) | ||
| ACID Properties. | Lecture | |
| 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 (5 Hrs.) | ||
| MSSQL server, ORACLE, MYSQL | Lecture |
Reference Books
1. Abraham Silberchatz, Henry F. Korth, S. Sudarshan; Database System Concepts, McGraw Hill 4th ed.
2. Date, C.J.; An Introduction to Database System, Addison Wesley, 8th ed.
3. RAMEZ ELMASRI, B. NAVATHE, Fundamentals of Database System, Pearson Education Asia, Fifth Edition.