Untitled Document
Uploaded by
naitikpostandelUntitled Document
Uploaded by
naitikpostandel2.
"Provide a detailed explanation of DBMS concepts, starting from
data models, ER diagrams, normalization (1NF to BCNF), SQL
commands (DDL, DML, DCL), transactions, indexing, stored
procedures, database design principles, and different database
architectures with examples."
Answer:
This plan is divided into 9 modules,
covering theory, practical SQL, and architectural understanding with examples and project ideas
for hands-on learning.
1: Introduction & Data
Models
Duration: 2-3 Days
Topics Covered:
What is DBMS? How it differs from traditional file systems Components of DBMS: DBMS
engine,
query processor, etc.
Data Models:
Hierarchical Model
Network Model
Relational Model (most commonly used) Object-Oriented Model
Schemas vs Instances.
Example:
Compare a relational model with an.
Excel spreadsheet. Visualize hierarchical model as a folder-tree structure.
2: Entity-Relationship (ER) Diagrams
Duration: 2-3 Days
Topics Covered:
Entities, Attributes (Simple, Composite, Derived)
Relationships (1:1, 1:N, M:N)
Keys: Primary Key, Candidate Key, Foreign Key Weak Entities and Participation
Constraints
Enhanced ER (EER): Specialization, Generalization, Aggregation.
Practice:
Draw an ER diagram for a university system (students, courses, professors). Identify keys and
cardinality.
3: Normalization
Duration: 3-4 Days
Topics Covered:
What is normalization? Why normalize? Functional Dependencies (FDs)
Normal Forms:
1NF - Eliminate repeating groups 2NF Remove partial dependencies 3NF-Remove transitive
dependencies.
BCNF A stricter version of 3NF
Example:
Given a sample unnormalized table, normalize it step-by-step to BCNF.
4: SQL Basics DDL, DML, DCL
Duration: 5-7 Days
Topics Covered:
DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): INSERT, UPDATE, DELETE, SELECT DCL (Data Control
Language):
GRANT, REVOKE
TCL (Transaction Control Language). COMMIT, ROLLBACK, SAVEPOINT SQL constraints:
NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY
Practice:
Create a sample database for an e-commerce site.
Perform insertions and updates, apply
constraints.
Write queries to retrieve customer orders with filtering, sorting, aggregation.
5: Transactions &
Concurrency Control
Duration: 3-4 Days
Topics Covered:
What is a Transaction? ACID properties (Atomicity, Consistency, Isolation, Durability)
Concurrency Issues:
"Lost Update
Dirty Read
Unrepeatable Read
Concurrency Control Mechanisms:
Locking (shared vs exclusive)
Two-Phase Locking (2PL)
Deadlock prevention
Example:
Simulate two concurrent transactions
trying to update the same bank account.
Module 6: Indexing & Query
Optimization
Duration: 3 Days
Topics Covered:
What is Indexing? How it speeds up
queries
Types of Indexes:
Primary Index
Secondary Index
Composite Index
Unique vs Non-unique
B+ Tree Indexing
Hash Indexing
Practice:
Create indexes on columns in SQL and analyze performance (EXPLAIN query).
Observe performance difference with/
without index on large dataset.
7: Stored Procedures,
Triggers, and Views
Duration: 3-4 Days
Topics Covered:
Stored Procedures: Precompiled blocks
of SQL
Functions vs Procedures
Triggers: Automatically run SQL code on
data changes (AFTER INSERT, BEFORE
DELETE, etc.)
Views Virtual tables for abstraction and
security
Practice:
Write a stored procedure to calculate
total sales per product.
Trigger to log data every time a row is
deleted.
Create a view that only shows
customers from a specific country.
8: Database Design
Principles
Duration: 2-3 Days
Topics Covered:
Principles of good database design.
ER-to-Relational mapping Denormalization vs Normalization
Star Schema & Snowflake Schema (in
Data Warehousing)
Schema refinement: identifying bad
designs
Practice:
Convert an ER diagram into a relational
schema and normalize it
Design a mini hospital database system
with proper tables and relationships,
9: Database Architectures
& Models
Duration: 3-4 Days
Topics Covered:
Centralized DBMS
Client-Server DBMS
Distributed Databases
NoSQL vs SQL: Document-based
(MongoDB), Key-Value Stores, Column
Stores
NewSQL Databases
CAP Theorem: Consistency, Availability,
Partition Tolerance
Example:
Compare relational (MySQL/
PostgreSQL) and document-oriented
(MongoDB) databases.
