Introduction to DBMS and Relational Model
✅ 1. Introduction to DBMS
DBMS
A software system used to store, retrieve, and manage data efficiently.
Advantages over File System
Reduces data redundancy
Ensures data integrity & security
Supports concurrent access
Backup & recovery
Enforces constraints
Types of DBMS
Hierarchical: Parent–child tree structure
Network: Multiple parent-child relationships
Relational (RDBMS): Data in tables (most used)
Object-Oriented: Data as objects
DBMS Architecture
1-tier: DB & user in same system
2-tier: Client ↔ Database
3-tier: Presentation ↔ Application ↔ Database
✅ 2. Relational Model
Basic Terms
Relation: Table
Tuple: Row
Attribute: Column
Domain: Allowed values of an attribute
Schema: Structure of table
Instance: Actual content at a time Keys
Primary Key: Uniquely identifies a record
Candidate Key: Possible primary keys
Super Key: PK + extra attributes
Foreign Key: Refers to PK of another table
Composite Key: More than one attribute
Alternate Key: Candidate key not chosen as PK
SQL
✅ 3. SQL
DDL
CREATE – creates objects
ALTER – modifies table
DROP – deletes table
TRUNCATE – removes all rows
DML
SELECT, INSERT, UPDATE, DELETE
DCL & TCL
GRANT/REVOKE – permissions
COMMIT – save changes
ROLLBACK – undo
SAVEPOINT – partial rollback
Clauses
WHERE – row filtering
GROUP BY – group rows
HAVING – filter groups
ORDER BY – sort
LIKE, IN, BETWEEN
Joins
INNER: common rows
LEFT: all left & matched right
RIGHT: all right & matched left
FULL: all rows from both
SELF: table joins itself
Subqueries
Single-row: =, >
Multi-row: IN, ANY, ALL
Correlated: executes for every row
Set Operators
UNION: unique rows
UNION ALL: duplicates allowed
INTERSECT: common rows
MINUS (EXCEPT): subtract rows
Functions
Aggregate: COUNT, SUM, AVG, MAX, MIN
String: UPPER, LOWER, LENGTH
Date: NOW, CURDATE, DATEADD
Normalization and ER Modeling
✅ 4. Normalization
Functional Dependency
A → B (B is functionally dependent on A)
Normal Forms
1NF: No multivalued fields; atomic values
2NF: No partial dependency (requires PK with multiple fields)
3NF: No transitive dependency
BCNF: Every determinant must be a candidate key
Denormalization
Intentionally adding redundancy for performance.
✅ 5. ER Modeling
Entities
Objects/things in a system
Strong Entity: Has own PK
Weak Entity: Dependent on another table’s PK
Attributes
Single-valued
Multi-valued
Composite
Derived
Relationships
1–1, 1–Many, Many–Many
ER → Relational Mapping
Entities → Tables
Attributes → Columns
M–N → Create separate relationship table
Transactions, Concurrency, Index and File Organization
✅ 6. Transactions & Concurrency
Transaction
A unit of work — must follow ACID.
ACID
Atomicity: All or nothing
Consistency: Valid state after execution
Isolation: Independent concurrent transactions
Durability: Results persist after commit
Concurrency Issues
Lost Update
Dirty Read
Non-repeatable Read
Phantom Read
Control Techniques
Locks: Shared (read), Exclusive (write)
Timestamp ordering
Two-Phase Locking (2PL)
✅ 7. Indexing & File Organization
Index
Improves data retrieval speed.
Types
Primary Index: On primary key
Secondary Index: On non-PK
Clustered Index: Sorts actual table
Non-Clustered: Logical ordering
B+ Tree: Most common
Hashing: For equality searches
Distributed Databases & NoSQL, SQL Optimization, Stored Procedures, Functions & Triggers
✅ 8. Distributed Databases & NoSQL
Partitioning
Horizontal: Split by rows
Vertical: Split by columns
Replication
Copies stored on multiple nodes.
CAP Theorem
A distributed system can guarantee only 2 of the following:
C: Consistency
A: Availability
P: Partition tolerance
NoSQL Types
Key-value: Redis
Document: MongoDB
Column: Cassandra
Graph: Neo4j
✅ 9. SQL Optimization
Use indexes on frequently searched columns
Avoid SELECT *
Use JOINs instead of subqueries (in many cases)
Use WHERE instead of HAVING** (unless aggregating)
Use proper data types
Analyze execution plan
✅ 10. Stored Procedures, Functions & Triggers
Stored Procedure
Set of SQL statements; supports parameters.
Function
Returns a value; used inside SELECT.
Trigger
Automatic execution on INSERT/UPDATE/DELETE.
⭐ High-Yield Topics for Capgemini
SQL joins & subqueries
Indexing
Keys & constraints
ACID properties
ER diagrams
Normalization