MYMahak YadavTech Mentor & TrainerBook a session
Data Science

DBMS & SQL Complete Notes: From Basics to Advanced

6 November 202620 min

A complete reference guide for Database Management Systems (DBMS) and SQL. This covers everything from basic concepts to advanced topics.

What is a Database?

What is a Database?

A database is a collection of related data which represents some aspect of the real world. A database system is designed to be built and populated with data for a certain task.

Database Management System (DBMS) is software for storing and retrieving users' data while considering appropriate security measures.

Problems DBMS Solves: 1. Data redundancy and inconsistency 2. Difficulty in accessing data 3. Data isolation (multiple files and formats) 4. Integrity problems 5. Atomicity of updates 6. Concurrent access by multiple users 7. Security problems

ER Diagrams

ER diagram (Entity Relationship diagram) is a conceptual model that gives the graphical representation of the logical structure of the database.

Components:

Entity Sets
Attributes
Relationship Sets

Entity Sets

Strong Entity Set: Contains sufficient attributes to uniquely identify all its entities (has primary key)

Weak Entity Set: Does not contain sufficient attributes to uniquely identify entities (has discriminator/partial key)

Relationships

Unary: One entity set participates
Binary: Two entity sets participate
Ternary: Three entity sets participate
N-ary: 'n' entity sets participate

Cardinality Constraints

One-to-One: Entity in A associated with at most one in B
One-to-Many: Entity in A associated with any number in B
Many-to-One: Entity in A associated with at most one in B
Many-to-Many: Any number of associations both ways

Types of Attributes

Simple: Cannot be divided further (e.g., Age)
Composite: Composed of many simple attributes (e.g., Name, Address)
Multi-Valued: Can take more than one value (e.g., Mobile No, Email)
Derived: Can be derived from other attributes (e.g., Age from DOB)
Key: Can identify an entity uniquely (e.g., Roll No)

Keys in DBMS

Super Key: Set of attributes that can identify each tuple uniquely
Candidate Key: Minimal set of attributes for unique identification
Primary Key: Selected candidate key (unique and NOT NULL)
Alternate Key: Unused candidate keys
Foreign Key: References primary key of another table
Composite Key: Primary key with multiple attributes
Unique Key: Unique for all records, may have NULL

Normalization

Process of organizing data to reduce redundancy and ensure data integrity.

First Normal Form (1NF) - All columns contain atomic values - Each column contains single type values - Each column has unique name

Second Normal Form (2NF) - Already in 1NF - No partial dependency exists

Third Normal Form (3NF) - Already in 2NF - No transitive dependency for non-prime attributes

Boyce-Codd Normal Form (BCNF) - Already in 3NF - For each non-trivial FD 'A → B', A is a super key

Transactions

A transaction is a single logical unit of work formed by a set of operations.

Transaction States: 1. **Active:** Instructions being executed 2. **Partially Committed:** Last instruction executed 3. **Committed:** Changes stored in database 4. **Failed:** Execution cannot continue 5. **Aborted:** Changes undone (rolled back) 6. **Terminated:** Life cycle ends

ACID Properties

Atomicity: Transaction occurs completely or not at all
Consistency: Database remains consistent before and after
Isolation: Multiple transactions can occur without inconsistency
Durability: Changes are permanent after successful execution

SQL Commands

DDL (Data Definition Language) - CREATE, ALTER, DROP, TRUNCATE, RENAME

DML (Data Manipulation Language) - SELECT, INSERT, UPDATE, DELETE, MERGE

DCL (Data Control Language) - GRANT, REVOKE

TCL (Transaction Control Language) - COMMIT, ROLLBACK, SAVEPOINT

Essential SQL Syntax

SELECT SELECT column1, column2 FROM table_name; SELECT * FROM table_name;

WHERE SELECT * FROM table_name WHERE condition;

ORDER BY SELECT * FROM table_name ORDER BY column ASC|DESC;

INSERT INSERT INTO table_name (col1, col2) VALUES (val1, val2);

UPDATE UPDATE table_name SET col1 = val1 WHERE condition;

DELETE DELETE FROM table_name WHERE condition;

Aggregate Functions

MIN(): Returns smallest value
MAX(): Returns largest value
COUNT(): Returns number of rows
AVG(): Returns average value
SUM(): Returns total sum

JOIN Types

INNER JOIN Returns records with matching values in both tables

LEFT JOIN Returns all records from left table, matching from right

RIGHT JOIN Returns all records from right table, matching from left

FULL OUTER JOIN Returns all records when there's a match in either table

GROUP BY and HAVING

GROUP BY: Groups rows with same values into summary rows

HAVING: Filters groups (used with aggregate functions where WHERE cannot be used)

LIKE Operator Patterns

'a%': Starts with "a"
'%a': Ends with "a"
'%or%': Contains "or"
'_r%': "r" in second position
'a_%': Starts with "a", at least 2 characters

Table Operations

CREATE TABLE CREATE TABLE table_name (column1 datatype, column2 datatype);

DROP TABLE DROP TABLE table_name;

TRUNCATE TABLE TRUNCATE TABLE table_name;

ALTER TABLE ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name DROP COLUMN column_name;

This comprehensive guide covers all essential DBMS and SQL concepts needed for interviews and practical database work.

Article Info

CategoryData Science
Read time20 min

Summary

Comprehensive guide covering Database Management Systems, ER diagrams, normalization, transactions, ACID properties, relational algebra, and complete SQL syntax with examples.

Want to master this topic?

Explore Courses
Back to Blog