DBMS cheatsheet
1. DBMS - Database Management System
2. SQL - Structured Query Language
3. ACID - Atomicity, Consistency, Isolation, Durability
4. 1NF - First Normal Form
5. 2NF - Second Normal Form
6. 3NF - Third Normal Form
7. DDL - Data Definition Language
8. DML - Data Manipulation Language
9. ER - Entity-Relationship
10. OLTP - Online Transaction Processing
11. OLAP - Online Analytical Processing
Questions and Answers
1. Database: Organized data collection in tables for easy access and
management.
2. DBMS: Software for defining, manipulating, retrieving, and
managing database data.
3. SQL: Language for interacting with relational databases.
4. Primary Key and Foreign Key: Unique identifier for records; links
tables by referencing primary key.
5. ACID Properties: Ensures reliable database transactions (Atomicity,
Consistency, Isolation, Durability).
6. Normalization: Organizing data to minimize redundancy (1NF, 2NF,
3NF).
7. Join Types: Combines data from tables (INNER, LEFT, RIGHT, FULL
JOIN).
8. Index: Improves query performance by allowing faster data
retrieval.
9. View: Virtual table from a query, enhances security and simplifies
queries.
10. Transaction: Sequence of operations ensuring data integrity,
adheres to ACID properties.
11. Trigger: Automatic execution of database procedures on specific
events.
12. Stored Procedure: Collection of SQL statements saved in the
database for reusable operations.
13. Composite Key: Primary key with two or more columns uniquely
identifying rows.
14. Deadlock: Transactions wait indefinitely for each other, causing a
block.
15. Data Integrity: Ensures data accuracy, consistency, and reliability
through constraints and ACID properties.
16. Denormalization: Introduces redundancy for faster query
performance, often in reporting databases.
17. Surrogate Key: Artificial, auto-generated primary key used when no
natural key exists.
18. Referential Integrity: Ensures foreign keys correspond to primary
keys, maintaining logical data connections.
19. Schema: Blueprint defining database structure, including tables,
fields, and relationships.
20. DDL and DML: DDL defines database structure (CREATE, ALTER,
DROP); DML modifies data (INSERT, UPDATE, DELETE).
21. ER Model: Visual representation of entities and their relationships in
data structures.
22. WHERE vs. HAVING: WHERE filters rows before grouping; HAVING
filters groups after aggregation.
23. Constraints in DBMS: Rules for data accuracy like NOT NULL,
UNIQUE, and FOREIGN KEY.
24. Cursor: Retrieves query results row-by-row for complex operations.
25. Subquery: Query within another query, used for filtering and
aggregation.
26. UNION vs. UNION ALL: UNION combines results and removes
duplicates; UNION ALL retains duplicates.
27. Data Redundancy: Storing the same data in multiple places, leading
to inconsistencies and increased costs.
28. Referential and Entity Integrity: Referential integrity ensures
foreign keys match primary keys; entity integrity ensures unique,
non-null primary keys.
29. OLTP vs. OLAP: OLTP for transaction-oriented applications with
quick updates; OLAP for complex queries and analytics.
30. DROP, DELETE, TRUNCATE: DELETE removes rows based on
condition, TRUNCATE removes all rows quickly, DROP deletes the
table structure.
