DBMS Cheatsheet – SQL Queries, ER Diagrams, Normalization

Database Management System (DBMS) is one of the most important subjects for computer science and engineering students. Whether you’re preparing for semester exams, competitive tests like GATE, or technical interviews, a DBMS cheatsheet can help you quickly revise the most essential concepts without going through bulky textbooks.

In this guide, we’ll cover the core DBMS concepts, SQL queries, ER diagrams, normalization techniques, and keys that every student must know. This quick revision sheet is structured in a simple and scannable way, so you can revise faster and score better in exams.

What is DBMS? (Quick Overview)

A Database Management System (DBMS) is software that allows users to store, retrieve, and manage data efficiently. It acts as a bridge between the user and the database.

Key Features of DBMS:

• Data security and consistency
• Efficient query processing
• Support for concurrent access
• Backup and recovery
• Data independence

Types of DBMS:

1. Hierarchical DBMS
2. Network DBMS
3. Relational DBMS (RDBMS)
4. Object-Oriented DBMS

Most modern systems like MySQL, Oracle, and PostgreSQL are Relational DBMS.

DBMS Architecture

• 1-Tier: Direct connection to the database.
• 2-Tier: Client-server architecture.
• 3-Tier: Client → Application Server → Database Server.

Important DBMS Keys

Keys are used to uniquely identify rows in a table.

• Primary Key: Uniquely identifies each record.
• Candidate Key: Any attribute that can qualify as a primary key.
• Super Key: Set of attributes that uniquely identify a row.
• Foreign Key: Refers to the primary key in another table.
• Composite Key: Combination of two or more attributes.

Entity-Relationship (ER) Diagrams

ER Diagrams are a visual way to represent entities and their relationships in a database.

Components of ER Diagram:

• Entity: Represented by rectangles.
• Attributes: Represented by ovals.
• Relationships: Represented by diamonds.
• Cardinality: 1:1, 1:N, M:N relationships.

SQL Queries Cheatsheet

SQL (Structured Query Language) is the standard language for interacting with relational databases.

Data Definition Language (DDL)

• CREATE TABLE table_name (...);
• DROP TABLE table_name;
• ALTER TABLE table_name ADD column_name datatype;

Data Manipulation Language (DML)

• INSERT INTO table_name VALUES (...);
• UPDATE table_name SET column = value WHERE condition;
• DELETE FROM table_name WHERE condition;

Data Query Language (DQL)

• SELECT * FROM table_name;
• SELECT name, age FROM students WHERE age > 20;

Data Control Language (DCL)

• GRANT SELECT ON table_name TO user;
• REVOKE UPDATE ON table_name FROM user;

Transaction Control Language (TCL)

• COMMIT;
• ROLLBACK;
• SAVEPOINT savepoint_name;

Most Common SQL Queries for Exams

1. Find all students with marks greater than 80:

SELECT * FROM students WHERE marks > 80;

2. Count number of employees in each department:

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

3. Find maximum salary:

SELECT MAX(salary) FROM employees;

4. Inner Join Example:

SELECT students.name, courses.course_name
FROM students 
INNER JOIN courses 
ON students.course_id = courses.course_id;

Normalization in DBMS

Normalization is a process of organizing data to minimize redundancy and dependency.

Normal Forms:

1. 1NF (First Normal Form):
   • Remove repeating groups.
   • Each column must hold atomic values.
2. 2NF (Second Normal Form):
   • Must be in 1NF.
   • Remove partial dependency.
3. 3NF (Third Normal Form):
   • Must be in 2NF.
   • Remove transitive dependency.
4. BCNF (Boyce-Codd Normal Form):
   • Every determinant must be a candidate key.

DBMS Transactions & Concurrency Control

• ACID Properties: Atomicity, Consistency, Isolation, Durability.
• Concurrency Control Techniques:
  • Lock-based protocols (shared/exclusive locks)
  • Timestamp ordering
  • Two-phase locking (2PL)

Indexing in DBMS

• Primary Index: Based on primary key.
• Clustered Index: Physical order of rows is same as index.
• Non-Clustered Index: Separate structure for indexing.

SQL Joins Cheatsheet

• INNER JOIN: Returns only matching rows.
• LEFT JOIN: Returns all from left, matching from right.
• RIGHT JOIN: Returns all from right, matching from left.
• FULL JOIN: Returns all rows when there is a match in one of the tables.

DBMS Exam Preparation Tips

• Revise all DDL, DML, DCL, TCL commands.
• Understand ER diagrams and normalization.
• Practice at least 20–30 SQL queries.
• Focus on transaction concepts and ACID properties.
• Learn differences between SQL vs NoSQL databases.

FAQ – DBMS Cheatsheet

1. What are the most important SQL queries for exams?
SELECT, INSERT, UPDATE, DELETE, aggregate functions (MAX, MIN, COUNT), and JOIN queries are most commonly asked.

2. What is the difference between Primary Key and Foreign Key?
Primary Key uniquely identifies a row, while Foreign Key refers to a Primary Key in another table.

3. Why is normalization important?
Normalization reduces redundancy, avoids anomalies, and ensures data integrity.

4. What are ACID properties in DBMS?
They ensure reliable transaction processing — Atomicity, Consistency, Isolation, Durability.

5. What is the difference between SQL and NoSQL?
SQL is relational and structured, while NoSQL is non-relational and used for unstructured/big data applications.

Conclusion

This DBMS cheatsheet covered all the essential topics: keys, ER diagrams, SQL queries, normalization, indexing, and transactions. By using this quick revision guide, engineering students can prepare effectively for exams and interviews.

If you want to save even more time, bookmark this page or download the PDF version (coming soon on BTech Cheatsheets).

[CTA BUTTON: Download More Cheatsheets — Link: https://btechcheatsheets.com]