My notes for clear
concepts part 1
2
LECTURE 1
❑ What is Data , Database, Database Management
System(DBMS)
❑ Why DBMS over EXCEL
❑ Introduction to SQL What is SQL and used for What
❑ Relational Database Management System
❑ Types of SQL Commands
a)DML , b)DDL , c)DRL/DQL , d)TCL , e)DCL
Each Explained briefly With Practical
WHAT IS DATA?
WHAT IS A DATABASE?
Data is everywhere everything
you see around which you
observe is data the facts which
you remember are also data
WHAT IS DATA?
WHAT IS A DATABASE?
Database is simply a container
where data can be stored
managed, accessed and updated
WHAT IS DBMS?
Database Management
system is a software that
interacts with the database,
users and applications to
manage the data efficiently
DBMS VS EXCEL
WHAT IS SQL AND WHAT IS IT’S USE
7
SQL(structured query language)
SQL is used as a communicator for us. With
the help of SQL, we can give commands to
the DBMS. The DBMS, after understanding
the query, follows it and responds to what is
asked—basically, just like Hindi and
English, which we use as mediums to talk
with each other. SQL is the language that
DBMS understands.
RDBMS 8
A Relational Database Management
System (RDBMS) is a type of DBMS that
stores data in tables—structured as rows
and columns—where each table
represents a specific entity (like customers,
orders, or products).
Popular RDBMS Tools:
▪ MySQL
▪ PostgreSQL
▪ Oracle
▪ Microsoft SQL Server
RELATIONAL DATABASE
MANAGEMENT SYSTEM
10
ID Name Job DOJ performance
1 Vineet Python dev 12/03/2025 Excellent
2 Sejal Web dev 21/06/2025 Good
3 Chahat Data analyst 13/06/2025 Outstanding
STRUCTURE IN RELATIONAL DATABASE
A schema is like a folder or container inside a
database that holds related objects—such as
tables, views, procedures, and functions. It
helps organize and manage database objects
logically.
table is a tool
RDBMS ACID PROPERTIES LISTED 12
ACID Properties in RDBMS
ACID stands for Atomicity, Consistency, Isolation, and
Durability. These four principles ensure that database
transactions are reliable, accurate, and safe, even in the
face of failures or concurrent access.
➢ Atomicity – All or Nothing
➢ Consistency – Valid State Before & After
➢ Isolation – No Interference Between Transactions
➢ Durability – Changes Persist Forever
ATOMICITY – ALL OR NOTHING
13
•Ensures that a transaction is treated as a single
unit.
•Either all operations succeed, or none do.
•If one step fails, the entire transaction is rolled
back.
•Example: Transferring ₹100 from Account A to B. If
debit from A succeeds but credit to B fails, the
whole transaction is undone.
CONSISTENCY – VALID STATE 14
BEFORE & AFTER
1. Guarantees that a transaction brings the database from
one valid state to another.
2. Enforces rules, constraints, and data integrity.
3. Example: If the total balance in a bank must remain
₹1000, a transaction must not violate that rule—even
if it fails midway.
.
ISOLATION – NO INTERFERENCE 15
BETWEEN TRANSACTIONS
•Ensures that concurrent transactions don’t affect each
other.
•Each transaction behaves as if it’s the only one
running.
•Prevents issues like:
•Dirty reads (reading uncommitted data)
•Non-repeatable reads (data changes between reads)
•Phantom reads (new rows appear unexpectedly)
•Example: Two users booking the last train seat—only
one should succeed, and the other should see the
updated availability
.
16
DURABILITY – CHANGES PERSIST FOREVER
•Once a transaction is committed, its changes are
permanent.
•Survives system crashes, power failures, etc.
•Example: After updating a customer’s address, the
change remains even if the server crashes
immediately after
17
WHY ACID MATTERS: PREVENTS DATA
• Prevents data corruption
• Enables safe multi-user access
• Supports recovery after failure
• Ensures trustworthy transactions
in banking, healthcare, e-commerce,
and more
KEYS 18
Uniquely identifies each record in a table.
Primary Key PRIMARY KEY (column_name)
Cannot be NULL or duplicate.
FOREIGN KEY (column_name)
Links one table to another by referencing a
Foreign Key REFERENCES
primary key.
other_table(column_name)
A column or set of columns that can qualify as No direct SQL syntax; used
Candidate Key
a primary key. conceptually
No direct SQL syntax; can be
Alternate Key Candidate keys not chosen as the primary key.
enforced using UNIQUE
A combination of two or more columns that
Composite Key PRIMARY KEY (col1, col2)
uniquely identify a record.
Any combination of columns that uniquely Conceptual; not directly defined in
Super Key
identify a row (includes extra attributes). SQL
Ensures all values in a column are unique. Can
Unique Key UNIQUE (column_name)
accept NULLs.
Artificial key (like an auto-increment ID) used id INT AUTO_INCREMENT
Surrogate Key
when no natural key exists. PRIMARY KEY (MySQL)
Similar to composite key, but may include
Compound Key PRIMARY KEY (col1, col2)
foreign keys.
PRIMARY KEY 19
What Is a Primary Key?
A primary key is a column (or set of columns) in a table that uniquely
identifies each row. It’s like a fingerprint—no two rows can have the same
value in the primary key, and it cannot be NULL.
Think of it like:
A student’s roll number
A product’s barcode
🪪 A passport number
These identifiers are unique, mandatory, and non-repeating—just like a
primary key.
“Primary key is like your Aadhaar—it’s unique, mandatory, and links you to
everything.”
20
What Qualifies a Column to Be a Primary Key?
To be a good candidate for a primary key, a column must meet all of these criteria:
Criteria Why It Matters
Uniqueness Ensures no two rows are identical
NOT NULL Every row must have a value
Stable Values shouldn’t change over time
Minimal Should be as short/simple as possible
Indexed Automatically gets a unique index for fast lookups
21
TYPES OF PRIMARY KEYS
Primary Key
Foreign Key
Type Description Example Candidate
Key
Simple Key One column student_id Alternate
Key
Composite
Composite Key Multiple columns combined order_id + product_id Key
Super Key
Artificial key (e.g., auto- user_id INT
Surrogate Key
increment ID) AUTO_INCREMENT Unique Key
Surrogate
Key
Compound
Key
A)COMPOSITE KEY 22
A combination of two or more columns that together
Primary Key
uniquely identify a row. Foreign Key
Like a seat number + flight number—only together do they Candidate Key
pinpoint your exact booking. Alternate Key
Composite Key
Like a course you joined in an organization having multiple Super Key
courses and different course has same kind of ids of students so Unique Key
both student id and course id together help identify student Surrogate Key
uniquely Compound Key
B)SURROGATE KEY 23
An artificial column, usually an auto-incremented number, created solely
to serve as a unique identifier.
A surrogate key is a column added to a table just to uniquely identify
each row. It’s not based on real-world data—it’s artificial, usually a
number that auto-increments.
Think of it like:
A receipt number at a store: It doesn’t tell you what you bought, but it
uniquely identifies your transaction.
A system-generated user ID: Even if two people have the same name,
their user IDs will be different.
DIFFERENCE BETWEEN SUPER KEY 24
AND COMPOSITE KEY
Feature Super Key Composite Key
A minimal set of multiple
Any set of columns that uniquely
Definition columns that uniquely identify a
identify a row
row
No (must be multiple
Can be single column Yes
columns)
May contain extras Yes No (only essential columns)
Used for uniqueness Yes Yes
Example student_id, student_id + name course_id + student_id
25
FOREIGN KEY
A foreign key is like a “relationship bridge” between two tables in a relational
database. It’s used to link one table to another, ensuring that the data stays consistent
and meaningful across both.
In Simple Terms:
Imagine two tables:
• Students (with each student having a student_id)
• Enrollments (showing which courses students are taking)
The Enrollments table might have a column called student_id—this is the foreign key. It
refers back to the student_id in the Students table. So every value in this foreign key
column must match an existing student_id from the Students table.
CONSTRAINT FK_CustomerOrder
26
PARENT VS CHILD COLUMNS IN
FOREIGN KEY RELATIONSHIPS
Definition
• Parent Column: The column in the referenced table—
usually a primary key.
• Child Column: The column in the referencing table—
defined as a foreign key.
Foreign key syntax
CREATE TABLE Customers ( CustomerID INT PRIMARY
KEY, Name VARCHAR(50) ); CREATE TABLE Orders (
OrderID INT PRIMARY KEY, CustomerID INT,
CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID) );
27
COMPOUND KEY
•A student can enroll in multiple courses.
•A course can have multiple students.
•But the same student can’t enroll twice in the same course —
that's enforced by the compound UNIQUE constraint on
(student_id, course_id).
Compound key syntax
CREATE TABLE Enrollments
( student_id INT, course_id VARCHAR(10), enrollment_date DATE,
UNIQUE (student_id, course_id) );
28
DATATYPES
• Numeric Data Types
• Character/String Data Types
• Date & Time Data Types
• Boolean & Binary Types
• Special Types
NUMERIC DATA TYPES
29
Data Type Storage Range Use Case Example
Age, rating out of
TINYINT 1 byte 0 to 255
10
Store codes, small
SMALLINT 2 bytes -32,768 to 32,767
counters
-2147483648 to
Employee IDs,
INT 4 bytes 2147483647
product count
Upto 10 digits
-
922337203685477
5808 to Views, large
BIGINT 8 bytes
+92233720368547 financial data
75807
upto 19 digits
30
CHARACTER/STRING DATA TYPES
Data Type Storage Max Length Notes / Use Case
Fixed-length, e.g.
CHAR(n) n bytes 8,000 chars
country codes
Variable-length, e.g.
VARCHAR(n) n bytes 8,000 chars
names
Long text, e.g. blog
VARCHAR(MAX) Up to 2GB 2^31-1 chars
content
Avoid in new
TEXT Deprecated 2GB
designs
31
c h a h a t
a r a v
DIFFERENCE BETWEEN CHAR AND 32
VARCHAR
VARCHAR(n) (Variable-
Feature CHAR(n) (Fixed-Length)
Length)
Always stores exactly n Stores up to n characters,
Length Behavior
characters based on actual input
No padding—stores only
Padding Pads with spaces if input < n
actual characters
Actual length + 2 bytes
Storage Size Exactly n bytes
overhead
Slightly faster for fixed-size Slightly slower due to
Performance
operations length tracking overhead
Fixed-size codes (e.g., Names, emails, comments—
Use Case
country codes) variable-length text
Predictability Easier to predict row size Row size varies with content
Up to 8,000 characters or
Max Length Up to 8,000 characters
VARCHAR(MAX)
DATE & TIME DATA TYPES 33
Type What It Stores Example
DATE Only date '2025-07-20'
TIME Only time '13:45:00'
Date + time, high '2025-07-20
DATETIME2
precision 13:45:00.1234567'
SMALLDATETIME Compact date-time '2025-07-20 13:45'
'2025-07-20 13:45:00
DATETIMEOFFSET With timezone
+05:30'
Type What It Stores Example
BIT 0 or 1 (False/True) is_active = 1
34
ORDER WE WILL BE LEARNING
•Create tables → CREATE
•Insert some data → INSERT
•Read data → SELECT, WHERE, ORDER BY
•Alter structure → ALTER, DROP
•Update/delete data → UPDATE, DELETE
•Control logic → JOIN, GROUP BY, HAVING
•Enable permissions → GRANT, REVOKE
•Manage transactions → BEGIN, COMMIT, ROLLBACK
TYPES OF SQL COMMANDS 35
• a)DML
• b)DDL
• c)DRL/DQL
• d)TCL
• e)DCL
36
DDL DATA DEFINATION LANGUAGE
Commands are essential for managing database
structures.
• CREATE: It is used to make new tables or
databases.
• ALTER TABLE: Allows you to add or remove
columns from an existing table.
• DROP: It is used to delete tables, databases, or
indexes permanently from the system.
37
DML DATA MANIPULATION LANGUAGE
Commands are crucial for managing data in SQL
databases.
• INSERT: To add new rows.
• UPDATE: To modify existing data.
• DELETE: To remove rows from tables.
• Mastering these commands allows you to efficiently
handle and manipulate your database records.
38
DRL DATA RETRIEVAL LANGUAGE
• DRL (Data Retrieval Language):
Used to query data from the
database
• SELECT: Select data from database
39
TCL TRANSACTION CONTROL LANGUAGE
• Used to manage changes made by
DML statements.
• COMMIT: Write transaction to
database
• ROLLBACK: Undo a transaction
block
40
DCL DATA CONTROL LANGUAGE
1.GRANT
• Gives specific privileges to users.
• Allows users to perform actions like SELECT,INSERT,
UPDATE, DELETE
• Can be applied to tables, views, procedures, etc.
2. REVOKE
• Removes previously granted privileges from users.
• Prevents users from performing certain actions.
41
SEE YOU AGAIN GUYS TILL THEN STAY
HAPPY KEEP LEARNING
THANK
YOU FOR WATCHING
LIKE AND FOLLOW FOR MORE
CONTENT LIKE THIS. IF YOU FOUND
THESE NOTES USEFULL REPOST IT SO
THAT IT COULD REACH MORE
PEOPLE
