Relational Database Management System Syllabus

• Definition and Importance of Databases
• Differences between DBMS and RDBMS
• Real-world Applications of RDBMS
• Characteristics of RDBMS
• Evolution of RDBMS
• Overview of Commercial RDBMS Software: MySQL, Oracle, PostgreSQL, SQL Server

• Data Structures: Tables, Records, and Fields
• Relational Model Basics
• Types of Relationships (1:1, 1:M, M:N)
• Keys: Super Key, Primary Key, Foreign Key, Unique Key, Composite Key
• Relational Integrity Constraints: Entity Integrity, Referential Integrity

• Entities, Attributes, and Identifiers
• Strong and Weak Entities
• ER Diagram Notation
• Relationships and Cardinality
• Converting ER Diagrams to Relational Models
• Enhanced ER Models: Generalization, Specialization, Aggregation

Data Definition Language (DDL):

• CREATE, ALTER, DROP, TRUNCATE
• Data Types and Constraints

Data Manipulation Language (DML):

• INSERT, UPDATE, DELETE

Data Query Language (DQL):

• SELECT Statements
• Filtering Records (WHERE, IN, BETWEEN, LIKE)
• Sorting and Grouping Data (ORDER BY, GROUP BY, HAVING)

Advanced SQL:

• JOINS: INNER, LEFT, RIGHT, FULL, SELF
• Subqueries (Single and Multi-row)
• Nested and Correlated Queries
• Set Operations: UNION, INTERSECT, EXCEPT
• CASE Statements
• Common Table Expressions (CTE)

Data Control Language (DCL):

• GRANT, REVOKE

Transaction Control Language (TCL):

• COMMIT, ROLLBACK, SAVEPOINT

• Basics of Relational Algebra: Select, Project, Join, Rename, Union, Set Difference
• Use of Relational Algebra in Query Optimization
• Tuple and Domain Relational Calculus

• Introduction to Redundancy and Anomalies
• Functional Dependencies
• First (1NF), Second (2NF), Third (3NF), and Boyce-Codd Normal Forms (BCNF)
• Fourth and Fifth Normal Forms
• De-normalization Techniques
• Case Studies in Normalization

• Types of Indexes (Single-column, Composite, Unique, Full-text)
• B-Tree and Hash Indexing
• Performance Tuning and Query Optimization Techniques
• Use of EXPLAIN and Execution Plans

• Transactions and ACID Properties
• Transaction States and Schedule Types
• Concurrency Problems: Lost Update, Dirty Read, Phantom Read
• Locking Mechanisms: Shared vs Exclusive Locks
• Deadlocks and Deadlock Prevention
• Isolation Levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable)

• Roles and Privileges
• User Account Management
• Authentication and Authorization
• Data Encryption Concepts
• Backup and Recovery Techniques
• Point-in-Time Recovery
• Auditing and Logging

• Installation and Configuration of RDBMS Tools: MySQL, Oracle, PostgreSQL
• Writing SQL Scripts for Schema and Data Management
• Building Normalized Schemas
• Executing Real-World Queries
• Creating Stored Procedures, Triggers, and Views
• Hands-on Practice with Live Databases

• Real-Time Use Case (e.g., E-Commerce, Hospital Management, Banking System)
• Schema Design and ER Modeling
• SQL Query Development
• Data Insertion and Retrieval
• Reporting and Data Export
• Final Project Presentation and Evaluation