Hello everyone,

these are my notes for Database Managenent Systems, gave a lot of interviews
and after multiple updates I can say that almost everything you will be asked in
interviews is here.

Please let me know if you come across something that is incorrect or can be
improved. You can reach me at

Linkedin: https://www.linkedin.com/in/notabhishek/ Follow for more

k/
Youtube: https://www.youtube.com/channel/UCLPT3aCJwFPWsVjEPqnOYPQ

he
Please subscribe so that you don’t miss the content about to come

is
bh
The end goal should be to able to revise within 15-20 minutes before an

a
interview

ot
/n
/in
PS: The only thing I can remember I was asked out of these were whose job is it to
m

implement ACID? (DBMS/Database/Programmer), How would you implement

o
.c

isolation/ isolation levels.

in
ed
link
w.
w
//w
s:
tp
ht
Database Management System
(DBMS)

1. What is DBMS? How it is Different from Data Structures. 2. Difference Between

Database and Database Management System? 3. Main Memory and Secondary
Memory. 4. Basic Functionality and Advantages of DBMS i.e. Data Retrieve,
Delete, Insert
(Functionality) and Remove Redundancy, Easy access ( Advantages) 5. DBMS Two-Level
and Three-Level Architecture. 6. Entity-Relationship Model (ER Model, Know the Basics

k/
i.e What is Entity, Attributes, and Cardinality) (Hint: They won’t ask You to make a

he
Complex ER diagram) 7. Relational Model ( Difference b/w ER model and Relational

is
Model) 8. All types of Keys in Relational Model ( Primary Key, Super Key, Candidate Key,

bh
Foreign

a
ot
Key) (Hint: Important) 9. Armstrong Axioms and All Types of Functional Dependencies
/n
(Transitive and Relative) 10. Database Anomalies ( Insert, Delete and Update) 11.
/in
Normalization -> What is Normalization? Why do we need Normalization ? 12. All the
m

Types of Normalization, 1NF, 2NF, 3NF, and BCNF. (Hint: They won’t ask you to Convert
o
.c

the Functions into 1NF, 2NF, and All, Just Know the Basics of Each Normal Form & It
in

Would Be Better if you Understand Every NF Form with an Example) (Important topic)
ed

13. SQL Queries ( Learn with one example of Each Query, Not Much, Best Resource :
nk

SQL w3 Resource ) 14. File Structures in Database ( Indexing, Sparse Indexing, B-

li
w.

Tree, B+ trees) (Source

w

GFG) 15. Transactions, Operations of Transactions, Transaction’s ACID

//w

Properties
s:

(Important) 16. State of Transactions i.e Committed,

tp
ht

partially Committed 17. Concurrency Control, Lock and all

over Transactions
1. What is DBMS? How it is Different from Data Structures.

a. DBMS Database Management System

i. Database
1. Collection of interrelated data, allows the efficient insertion,
retrieval, and deletion of data
ii. DBMS
1. The software used to manage databases is known as DBMS
2. DBMS provides the following functionality
a. Data definition

k/
b. Data retrieval

he
c. Data updation

is
d. User administration - monitor users, performance,

bh
enforce data security,

a
3. E.g. MySQL, Oracle, etc.

ot
iii. DBMS vs File System - below are problems with File System, which are
/n
/in
solved by DBMS Read more
1. Redundancy of data
m o

2. Inconsistency of data
.c

3. Difficult data access

in

4. Unauthorized access
ed

5. No concurrent access
nk

6. No backup and recovery

li
w.

iv. DBMS vs Data Structures Read more

w

1.
//w

DBMS Data Structures

s:
tp

Organized collection of data The special format of storing data

ht

to perform some operations

efficiently

Non Volatile Volatile

Types: Relational, NoSQL, Types: Linear and nonlinear data

centralized, hierarchical databases structures

Eg. MySQL, Oracle, MongoDB, Eg. Stack, Queue, Heap, Tree,

etc. Graph
2. Difference Between Database and Database Management System? Read more

a. Database vs DBMS
i. A Database is a collection of interrelated data
ii. DBMS is a software/set of software that help us manage the database
iii.
Database DBMS

Storage A Database can be DBMS maintains a

maintained on database on computer
paper/computers

k/
Data Retrieval Can be done using prog Uses query languages

he
languages like C++, like SQL

is
Python, etc.

a bh
Speed Slow speed without Fast as it uses query

ot
query languages languages
/n
/in
Access Not a lot of people can Provides functionality for
m

use it at the same time multiple users at the

o

same time( handles

.c

transactions )
in
ed

Backup and Recovery No backup/ recovery in Maintains backup, and

nk

case of failure can recover in case of

li

failure
w.
w
//w
s:
tp
ht

3. Main Memory and Secondary Memory. Read more

a. Memory
i. Computer memory is the storage space where data to be processed
and instructions to run are stored
ii. Two types
1. Primary memory
a. The main memory of the computer
b. Smaller, Faster, Costly, Volatile
c. Two types
i. RAM
1. Random Access Memory
2. Volatile
ii. ROM
1. Read Only Memory
2. Non Volatile, Content is written by the
manufacturer and cannot be changed
2. Secondary memory
a. Large, Slower, Inexpensive, Non-volatile

k/
4. Basic Functionality and Advantages of DBMS i.e. Data Retrieve, Delete , Insert

he
(Functionality) and Remove Redundancy, Easy access ( Advantages) Read more

is
bh
a. Features of DBMS

a
ot
i. DBMS provides the following functionality
1. Data definition /n
/in
2. Data insertion
m

3. Data retrieval
o
.c

4. Data updation
in

5. Data deletion
ed

b. Advantages of DBMS
nk

i. Removes data redundancy

li

ii. Data sharing - levels of authorization for sharing

w.

iii. Data Integrity - data is correct at all times inside the DB

w
//w

iv. Data Security/ Privacy - only authorized users can access

v. Data Consistency -
s:
tp

1. Data is the same for all users at all times

ht

2. Any change in DB is reflected to all users

c. DDL vs DML GfG
i. DDL: data definition language, ex: create, alter, rename, drop
ii. DML: data manipulation language, ex: insert, update, merge

5. DBMS Two-Level and Three-Level Architecture. Read more

a. DBMS Architecture
i. Two-tier
1. Basic client-server model
2. Easy to manage/implement
3. Users set up an individual connection with the server
4. Poor performance in case of a large number of users
5. Poor security since everyone accesses the actual server
ii. Three-tier
1. Client - Application Layer - Server
2. Harder to implement and manage
3. Users interact with the Application layer(middle layer)
4. The application layer acts as a medium for the exchange of partially
processed data
5. Enhanced scalability by the distributed deployment of application layer
6. Better data security, since the user only interacts with the application
layer and not with the server

k/
7. Improved data integrity since data corruption can be avoided using a

he
middle layer

is
a bh
ot
/n
/in
6. Entity-Relationship Model (ER Model, Know the Basics i.e What is Entity, Attributes
m

and Cardinality) (Hint: They won’t ask You to make a Complex ER diagram) Read more
o
.c
in
ed

a. Entity-Relationship Model Video

nk

i. It is a conceptual/ logical model

li

ii. Logical representation of entities and relationships

w.

iii. Entity
w

1. An object which has a physical existence

//w

2. E.g. Student, Course, etc

s:

iv. Entity type

tp

1. An Entity is an object of Entity type

ht

2. E.g. E1 is an entity of Entity type Student

v. Entity set
1. Set of all entities of given Entity type
2. E.g. set of students
vi. Relationship
1. Association between two or more entities
vii. Attributes
1. Properties/ Characteristics that define an entity type
2. Just like column names
viii. Entity Type or Schema
1. Student(Roll no, Age, Address)
ix. Representation
1. Entity

2. Relationship

3. Attributes
x. Types of attributes Video
1. Single Valued registration number

2. Multi-Valued Mobile Number

3. Simple

k/
4. Composite Name(composite) -> fname, mname, lname

he
5. Stored

is
6. Derived DOB-> age (derived by using dob)

abh
ot
/n
/in
a.
m

b. Represented by dotted circle/oval

o
.c

7. Key -> unique, can uniquely identify row

in
ed
link

a.
w.

b. Underlined
w
//w

8. Non-Key
9. Required vs Optional attribute
s:
tp

10. Complex - composite + multi-valued

ht

a. eg. address( city, state, country) composite

b. address (home address, office address) multi-valued
xi. Relationship type and Relationship set
1. A relationship represents an association between entity types

2.
3. Represented using diamond
4. A set of relationships of the same type is known as Relationship set
5. E.g.
6. Degree of Relationship set
a. Number of different entity sets participating in the relationship
b. Types
i. Unary

k/
he
1.

is
2. A person married to a Person

bh
ii. Binary

a
ot
1.
/n
/in
2. A student enrolled in a course
m

iii. N-ary
o
.c

1. N entity sets participate in the relationship

in

xii. Cardinality
ed

1. One to One
li nk
w.
w
//w

a.
s:
tp
ht

b.
2. Many to One

a.
b.
3. Many to Many

k/
a.

he
is
a bh
ot
/n
/in
om
.c
in
ed
nk

b.
li

4. Representation in ER diagram
w.
w
//w
s:
tp
ht

a.
xiii. Participation Constraint
1. Total participation
a. Every student must enroll in at least one course -> total
participation of student
b. Represented using double lines
2. Partial participation
a. The entity set may or may not participate in the relationship
b. Some courses may not be enrolled by any student
c. E.g. Total participation of student and partial participation of
Course

d.

k/
he
is
bh
e.

a
xiv. Weak entity types & Identifying relationships

ot
/n
1. There exists some entity for which key attribute can’t be defined
/in
2. There is no existence of Employee-dependants without Employee
m

3. A weak entity type is represented by a double rectangle, and it always

o

has total participation in a relationship

.c

4. The relationship between a weak entity type and its identifying strong
in
ed

entity type is represented by double diamond

nk
li
w.
w
//w
s:

5.
tp

xv. Generalized Entity

ht

1. Uses bottom-up approach

2. Two or more low-level entities combine to form a generalized higher-level
entity
3. Ex :
4.

k/
he
is
abh
ot
5.
/n
/in
xvi. Specialization entity
m

1. Reverse of generalization
o
.c

2. Entity divided into sub-entities

in
ed
nk
li
w.
w
//w
s:
tp
ht

3.
xvii. Aggregation
1. 2 or more entities act as a single entity in a relationship
k/
2.

he
3. A manager manages both employees and the project

is
xviii. Drawing ER model

abh
ot
/n
/in
1.
m

2. Eg.
o

3. 3 entities: Student, Course, Professor

.c
in

4. Relationships:
ed

a. Student is assigned a course

nk

b. Professor delivers a course

li

5. Cardinality
w.

a. A student can be assigned many courses

w

b. A professor only delivers a single course

//w

6. Attributes
s:

a. Student_name, student_id (key)

tp

b. Course_name, course_id(key)
ht

c. Prof_name, prof_id(key)
7. Diagram

a.
7. Relational Model ( Difference b/w ER model and Relational Model) Read more

ER Model Relational Model

Used To describe entities and To represent a collection of

relationships between them tables and relations
between them

Type High level, Conceptual Implementation/

Representational model

k/
Components Entity, Entity Type, Entity Domain, Attributes, Tuples

he
Sets

is
bh
Used by People who don’t have Programmers

a
programming knowledge

ot
Mapping /n
This model describes the Does not describe
/in
mapping of cardinality cardinality
o m
.c
in
ed
nk

8. All types of Keys in Relational Model ( Primary Key, Super Key, Candidate Key,
li

Foreign Key) (Hint: Important)

w.
w
//w

1. Keys
s:

a. KEYS in DBMS is an attribute or set of attributes which helps you to identify a

tp

row(tuple) in a relation(table). They allow you to find the relation between two
ht

tables
b. Types :
i. Primary Key
1. It uniquely identifies any tuple of the table, there can be many keys
but only 1 primary key
ii. Candidate Key
1. All candidates for primary key(as strong as the primary key but
weren’t chosen as primary key)
iii. Super Key
1. Superset of candidate key
2. (Name, Roll), Roll, (Age, Aadhar) could be superkeys
iv. Foreign Key
1. The primary key of some other table used to link 2 tables eg.
DeptId

9. Armstrong Axioms and All Types of Functional Dependencies (Transitive and

Relative)

a. Functional Dependency
i. Functional Dependency is a constraint between two sets of attributes in relation
to a database. A functional dependency is denoted by an arrow (→). If an
attribute A functionally determines B, then it is written as A → B.
ii. A function dependency A → B means for all instances of a particular value of A,
there is the same value of B.
iii. Types
1. Trivial FD

k/
a. X->Y and Y is a subset of X e.g ABC->BC

he
2. Non Trivial FD

is
a. X->Y and Y is not a subset of X e.g. AB->C

bh
i. Completely non Trivial: X intersection Y = NULL

a
ot
ii. Semi non Trivial: X intersection Y != NULL
b. Armstrong’s Axioms GFG /n
/in
● Set of inference rules or axioms, used to test the logical implication of
m

Functional dependencies
o

● Axioms
.c
in

○ Axiom of Reflexivity
ed

■ If B is a subset of A, then A-> B is trivial property

nk

○ Axiom of Augmentation
li

■ A->B then AC->BC

w.

○ Axiom of Transitivity
w

■ A->B and B->C then A->C

//w

● Secondary Rules
s:

○ Union: A->B, A->C => A->BC

tp
ht

○ Composition: A->B, X->y => AX->BY

○ Decomposition A->BC => A->B, A->C
○ Pseudo Transitivity: A->B, BC->D => AC->D

10. Database Anomalies ( Insert, Delete and Update) GFG

a. Due to redundancy in table
b. Anomalies
i. Example table
1.
id name branch hod

1 a cse u

2 b cse u

3 c cse u

4 d mech v

ii. Insertion Anomaly

1. When certain data cannot be inserted into table without insertion of
some other data
2. E.g. you cannot insert data about civil branch until at least 1 student is

k/
enrolled in civil

he
iii. Deletion Anomaly

is
1. Deletion of some data causes deletion of some wanted data

bh
2. E.g. You delete tuple with id 4, then you lose info about mech branch

a
iv. Modification/Updation Anomaly

ot
/n
1. When updating a single data causes updates to all of its copies
/in
2. E.g. if hod of cse changes then you will have to update everywhere
m

3. If for some reason some copies aren’t updated then it causes

o

inconsistency in data e.g. you won’t be able to determine who is hod

.c

of cse since it will have different values in different tuples

in
ed

v. Solution: Normalization
nk
li
w.
w

11. Normalization -> What is Normalization? Why do we need Normalization ?

//w
s:
tp

Database normalization is the process of organizing the attributes of the database

ht

to reduce or eliminate data redundancy (having the same data but at different
places) .

12. All the Types of Normalization, 1NF, 2NF, 3NF, and BCNF. (Hint: They won’t ask you to
Convert the Functions into 1NF, 2NF, and All, Just Know the Basics of Each Normal Form
& It Would Be Better if you Understand Every NF Form with an Example) (Important
topic) GFG link | javapoint

1. 1NF
a. A relation is in 1NF if it contains an atomic value.
b. There should be no multi valued attribute
2. 2NF
a. Should be in 1NF
b. Partial dependency: a proper subset of candidate key determines a non prime
attribute
c. There should be no Partial Dependency
d. I.e. No non-prime attribute is determined by a subset of Candidate key
3. 3NF
a. Should be in 2NF
b. There should be no Transitive Dependency
c. I.e. No F.D of the form X->Y where both X,Y are non prime
d. For every Functional Dependency A->B, one of these must hold
i. A is superkey or
ii. B is prime attribute(subset of Candidate Key)

k/
4. BCNF, Boyce-Codd Normal Form

he
a. Should be in 3NF

is
b. For every functional dependency A->B

bh
i. A is super key

a
ot
/n
/in
m

13. SQL Queries ( Learn with one example of Each Query, Not Much, Best Resource :
o
.c

SQL w3 Resource ) Practice queries on Leetcode

in
ed
nk
li

14. File Structures in Database ( Indexing, Sparse Indexing, B- Tree, B+ trees) (Source
w.

GFG)
w
//w

Q. Where are indexes stored?

s:

1. Indexing
tp

a. Primary
ht

i. Sorted and Unique

ii. Index size = number of blocks in Hard Drive
iii. Cost = logN + search in 1 block
iv. Sparse index
b. Clustered
i. Sorted and not unique
ii. Index same as primary ( points to block )
iii. Cost = logN + search in more than 1 blocks e.g. key = 2 can be present
from 5 - 15 blocks
c. Secondary
i. Dense index
ii. On Key attribute (unsorted):
1. Make dense index in sorted order and binary search
iii. On Non-Key attribute (unsorted)
1. Make dense index in sorted order but keep unique values, then
each unique value points to an intermediate block containing
pointers to a location with that data
2. I.e Index-> Block containing all pointers with given index->
actual record
2. Btree vs B+ tree YT Gfg

k/
he
is
a bh
ot
/n
/in
o m
.c
in
ed
nk

a.
li
w.
w
//w

15. Transactions, Operations of Transactions, Transaction’s ACID Properties

s:

(Important) Gfg
tp

1. Transaction
ht

a. A transaction is a single logical unit of work which

accesses and possibly modifies the contents of a
database. Transactions access data using read and
write operations.
k/
he
is
a bh
ot
b. /n
/in
c. Atomicity:
m

i. Either complete or none

o
.c

ii. If some failure occurs in between

in

transactions it is aborted and rolled back

ed

iii. After completion, we commit and

nk

changes are permanent

li

d. Consistency:
w.

i. Total money before transaction = total

w
//w

money after the transaction

e. Isolation : isolation levels
s:
tp

i. 2 transactions don’t interfere with each

ht

other
ii. Isolation levels
1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serialize
f. Durability:
i. Changes persist.

16. State of Transactions i.e Committed, partially Committed

1. To avoid dirty data due to failures, transactions
support 2 operations
a. Commit
i. When all operations of a
transaction are done, only then the
database gets updated, until then
all operations are done on buffer
b. Rollback
i. In case of failure, the changes are
not committed and get rolled back

k/
he
is
17. Concurrency Control, Lock and all over Transactions

bh
GfG

a
1. Schedule:

ot
/n
a. When 2 or more transactions are taking
/in
place at the same time, depending on the
m

order of operations, we might have

o

inconsistency in the database

.c

b. To solve this, we define an order of

in
ed

operations called schedule

2. 2 types of schedule
nk

a. Serial schedule
li
w.

i. T2 takes place only after T1 has

w

completely finished
//w

ii. Always consistent

s:

b. Concurrent Schedule
tp

i. T1 and T2 take place at the same

ht

time, we need to Serialize the

schedule to avoid inconsistency
3. Conflict serializability GfG
a. If we can order the schedule in such a
way that guarantees consistency =>
conflict serializable schedule