DBMS Notes
This unit forms the backbone of practical database work. While Unit 1 introduced conceptual modeling with ER diagrams, this unit translates those conceptual designs into
Unit Overview
This unit forms the backbone of practical database work. While Unit 1 introduced conceptual modeling with ER diagrams, this unit translates those conceptual designs into the relational model and teaches you SQL — the language used to create, manipulate, and query databases in virtually every organization worldwide.
The relational model, proposed by Edgar F. Codd in 1970, revolutionized data management by providing a mathematical foundation for organizing data into tables with well-defined relationships. SQL (Structured Query Language) emerged as the standard interface for interacting with relational databases, and despite being over four decades old, it remains the most important language in the data world.
Topics Covered in This Unit
| Topic | Description | Key Concepts |
|---|---|---|
| Relational Model | Mathematical foundation for table-based data | Relations, tuples, attributes, domains, keys |
| Relational Algebra | Procedural query language with set operations | Select, project, join, union, intersection, division |
| Relational Calculus | Declarative query specification | Tuple calculus, domain calculus, safe expressions |
| Tuple Relational Calculus | Variable ranges over tuples | Existential/universal quantification |
| Domain Relational Calculus | Variable ranges over domain values | QBE-style specifications |
| SQL Introduction | Overview of SQL and its components | DDL, DML, DCL, TCL categories |
| DDL Commands | Defining database structure | CREATE, ALTER, DROP, TRUNCATE |
| DML Commands | Manipulating data | SELECT, INSERT, UPDATE, DELETE |
| DCL and TCL Commands | Access control and transaction management | GRANT, REVOKE, COMMIT, ROLLBACK |
| Constraints in SQL | Enforcing data integrity rules | PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULL |
| Joins in SQL | Combining data from multiple tables | INNER, LEFT, RIGHT, FULL, CROSS, SELF joins |
| Nested Queries | Subqueries within queries | Correlated, non-correlated, EXISTS, IN, ANY, ALL |
| Views in SQL | Virtual tables from stored queries | CREATE VIEW, updatable views, materialized views |
| Functions and Procedures | Reusable database logic | Stored procedures, user-defined functions, parameters |
| Cursors | Row-by-row processing of result sets | DECLARE, OPEN, FETCH, CLOSE cursor operations |
| Triggers in SQL | Automatic actions on data events | BEFORE/AFTER triggers, row/statement level |
From Theory to Practice
This unit bridges the gap between database theory and hands-on implementation. The relational algebra and relational calculus topics provide the theoretical query languages that SQL is based on. Every SQL query you write has an equivalent expression in relational algebra — understanding this connection helps you write better queries and understand how the database optimizer works internally.
The progression follows a natural learning path: first understand how data is structured in the relational model, then learn the theoretical query languages (algebra and calculus), and finally master the practical SQL commands that you will use daily in real database work.
SQL Command Categories at a Glance
| GRANT | Give permissions to users |
| REVOKE | Take permissions away |
| COMMIT | Save transaction permanently |
| ROLLBACK | Undo entire transaction |
| SAVEPOINT | Mark a point inside transaction |
Learning Objectives
After completing this unit, you should be able to:
- Define relations formally — specify domains, attributes, tuples, and integrity constraints in the relational model
- Write relational algebra expressions — express queries using selection, projection, join, division, and set operations
- Translate between algebra and SQL — understand how SQL queries map to relational algebra operations
- Write complex SQL queries — including joins across multiple tables, nested subqueries, aggregate functions, and grouping
- Create and manage database objects — tables, views, indexes, stored procedures, functions, and triggers
- Enforce data integrity — apply primary keys, foreign keys, check constraints, and unique constraints appropriately
- Use advanced SQL features — cursors for procedural processing, triggers for automated actions, and views for abstraction
Prerequisites
Before studying this unit, you should be familiar with the ER model and basic database concepts from Unit 1. The ER-to-relational mapping process connects the two units — conceptual ER diagrams are translated into relational tables that you then manipulate using SQL. Understanding keys, relationships, and cardinality from Unit 1 is essential for grasping foreign key constraints and join operations in this unit.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Unit 2 — Relational Model & SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, unit 2 — relational model & sql
Related Database Management Systems (DBMS) Topics