Database Management System
Units Duration (Hrs)
Unit 1: Introduction to Databases 9
Unit 2: E-R Data Modeling 10
Unit 3: Basic SQL Query 9
Unit 4: Normalization Concepts 8
Unit 5: Transaction Control and Concurrency 9
Total hrs 45
Course Learning Outcomes
Course Learning Outcomes Short Description
Relating the fundamental concepts, components, and DBMS concepts and
CLO 1
architectures of Database architecture.
Solving design for an entity relationship diagram for data
CLO 2 Design ER diagrams
requirements.
Master SQL query
Write and execute SQL queries for managing and manipulating
CLO 3 creation and
databases.
execution.
Apply normalization to design efficient, consistent relational Normalize database
CLO 4
database schemas. schema
Comprehending distributed database systems, ensuring Ensure distributed
CLO 5
concurrency, recovery, and transaction integrity concurrency
Course Objective:
1. To develop an understanding of DBMS concepts, components, and architectures.
2. To Relate and Modify an Entity relationship diagram for data requirements.
3. To enable students to proficiently write and execute SQL queries for comprehensive
database management and advanced data manipulation.
4. To Optimize database schemas using normalization.
5. To use transaction, concurrency control, and recovery in the database.
Course Outline
Hours Outlines
Unit 1: Introduction to Databases
● Introduction to Database
● Basic Definitions of Data and Databases
● Database Management Systems (DBMS) Overview
● Data, Information, and Knowledge
● Importance of DBMS and Its Advantages
● Types of Databases
● Characteristics of the Database Approach
● Components and Users of DBMS
9 ● When to Use and When Not to Use DBMS
● Data Models and Their Categories
● Schemas, Instances, and Database State
● Three-Schema Architecture and Data Independence
● DBMS Languages and Interfaces
● DBMS Component Modules and Utilities
● Database Architectures: Centralized and Client/Server
● Classification of Database Management Systems
● ACID Properties in DBMS
Unit 2: E-R Data Modeling
● Introduction to Entity-Relationship (ER) Model
● Entities, Attributes, and Keys in ER Model
● Relationship Types and Constraints in ER Model
● ER Diagrams and Design Considerations
● Mapping Constraints and DBMS Keys
● Converting ER Diagrams into Tables
● Relationships of Higher Degree in Databases
10 ● Concepts of the Relational Model
● Basic Operators in Relational Algebra
● Extended Operators in Relational Algebra
● Join Operations: Inner, Outer, and Cross Joins
● Integrity Constraints in Relational Databases
● Applying Integrity Constraints in Database Design
● Introduction to Tuple Relational Calculus
● Expressions and Applications of Tuple Relational Calculus
Unit 3: Basic SQL Query
● Introduction to SQL and Data Types
● Data Definition Language (DDL)
9
● Data Manipulation Language (DML)
● Data Control Language (DCL)
● Transaction Control Language (TCL)
Unit 4: Normalization Concepts
● Functional Dependency and Inference Rules
● Introduction to DBMS Normalization
● First Normal Form (1NF)
● Second Normal Form (2NF)
● Third Normal Form (3NF)
● Boyce-Codd Normal Form (BCNF)
8
● Case Studies and Examples of Normalization
● Functional Dependency Closure
● Lossless Decomposition
● Dependency Preservation
● Real-world Examples of Normalization
● Challenges in Database Design
Unit 5: Transaction Control and Concurrency
● Transaction Processing and Properties
● States of a Transaction
● Concurrency Control in Databases
● Deadlock in DBMS and Handling Techniques
● DBMS Scheduling and Serializability
● Concurrency Control Techniques:
○ Lock-based Protocol
○ Timestamping Protocol
9 ○ Validation-based Protocol
● Recovery Mechanisms in DBMS
○ Failure Classification
○ Log-based Recovery
○ Checkpointing in DBMS
● Introduction to Web Databases
● Overview of Multimedia Databases
● Basics of Spatial Databases
● Mobile Databases and Their Applications
Textbook:
● Database System Concepts – Abraham Silberschatz, Henry F. Korth, S. Sudarshan, 7th
edition, 2020, McGraw-Hill. ISBN: 978-0078022159
● Database Management Systems – Raghu Ramakrishnan, Johannes Gehrke, 4th edition, 2019,
McGraw-Hill. ISBN: 978-0072465631
● Fundamentals of Database Systems – Ramez Elmasri, Shamkant B. Navathe, 7th edition,
Pearson. ISBN: 978-0133970777
Reference book:
● Database System Implementation – Hector Garcia-Molina, J.D. Ullman, Jennifer Widom, 2nd
Edition, 2008, Prentice Hall. ISBN: 978-0130402646
● Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design –
Michael J. Hernandez, 3rd Edition, 2013, Addison-Wesley Professional. ISBN:
978-0321884497
● SQL Cookbook: Query Solutions and Techniques for Database Developers – Anthony
Molinaro, 2nd edition, 2018, O'Reilly Media. ISBN: 978-1492077442
Mapping of Course Outcomes with Program Outcomes
Program Specific
Program Learning Outcomes
Outcomes
Course
Learning
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PSO1 PSO2 PSO3
Outcomes
(CLOs)
CO1 3 2 3 - - - - - 3 - 2
CO2 - 3 - 3 - - - - 2 3 -
CO3 - - 3 2 3 - - - 3 - 2
CO4 3 - - 3 2 2 - - - 3 -
CO5 - 3 2 - - 3 2 3 - - 3
1. Objective addressed 3. Objective addressed
2. Objective addressed moderately
lightly substantive
List of Experiment
Topics
● Software Introduction
● Software Installation
● Analyze the problem and come up with the entities involved. Identify what attributes
have to be persistent in the databases.
○ ROADWAY TRAVELS
○ COLLEGE DATABASE
○ COMPANY DATABASE
● Create an Entity-Relationship-Diagram for the identified problem.
● Convert the Entity-Relationship to a Relational Model(tables)
● Implementation of DDL commands in SQL with suitable examples.
○ Create Table
○ Alter Table
○ Drop Table
● Learn How to Remove Columns, Constraints, and Modify Data Types in SQL
● Execute drop and truncate table operations in SQL
● Study and Implementation of Different Types of Constraints.
● Implement the various keys in the SQL with suitable examples.
● Implementation of DML commands in SQL with suitable examples
○ Insert
○ Update
○ Delete
● Implementation of an aggregate function, Group by, Having Clause, and Order By with
suitable examples.
● Apply advanced SQL queries involving functions, comparison operators, and grouping.
● Implementation of different types of joins
○ Inner Join
○ Outer Join
○ Natural Join, etc.
● Practice Ordering Results and Performing Different Types of Joins In SQL
Note:
● The list of experiments provided above is subject to potential updates in the future to
accommodate the needs of students.