Database Management System is a software that is used to store & retrieve the data simply like an Excel sheet.
Let’s take an example called the Company database.
In the Company database, we may have tables like an employee table, customer table, product table, transaction table, etc.
Let's jump into the 6 important concepts of DBMS,
1. ACID Properties:
A — Atomicity
C — Consistency
I — Isolation
D — Durability
“A transaction is a very small unit of a program”
Here, the transaction means a group of tasks, which means it consists of multiple tasks.
Once all of these tasks are completed, our transaction is complete. It could be any set of flow. A transaction is supposed to have these four properties.
Let’s look into it one by one,
It means either an entire transaction takes place at once or nothing. Consider Fig-a, If you complete task 1 and task 2, suddenly some system error occurs, you cannot move further to the next task.
Now, what atomicity says that either you can complete task 3 and task 4 or you can rollback. You cannot be in the midway of any transactions.
Here, what rollback means go back to the zero state
Let’s take a quick example,
You had ₹500 in X person’s account and you had ₹200 in Y person’s account. So the total was ₹700.
Let’s say, you do a transaction for transferring ₹200 from X to Y.
Now, the Consistency means “Total amount before transaction” is equal to the “Total amount after transaction”.
Isolation means the concurrent execution of transactions.
Here, TRANS means transaction
You can execute TRANS (1) parallelly you can execute TRANS (2) and then only you can go to the TRANS (3) that is what we refer to as Isolating transactions from one another.
It simply says that even on system failure, changes that you do to the database at persistent.
2. Types Of SQL Statements:
It is classified into four categories,
DDL (Data Definition Language)
DML (Data Malipulation Language)
DQL (Data Query Language)
DCL (Data Control Language)
It means to define your database or create your database.
Example: CREATE, ALTER, DROP.
It means anything you want to change or update your database.
Example: INSERT, UPDATE, DELETE.
It means query your database or you want to select something from your database.
It means to control your database. For example, you want to remove one user from your database or you want to add one user or you want to protect your database from others simply like admin console.
Example: GRANT, REVOKE, ALTER PASSWORD.
3. ER Model:
ER Model defines the conceptual view of the database simply says “diagrammatical view of database”. ER diagrams comprise of,
Entity and Entity Set
An entity is an object and it can be a person, place, or company. The entity set means a set of entities of the same type that can share the same properties. Example: All persons having account in the bank.
Relationship and Relationship Set
The association among entities is called relationship. Example: An employee works at a department, A student enrolls in a course. A set of relationships of similar types is called a relationship set. Example: Association between patient and doctor, Association between teacher and student.
Types of Relationship set
- One to One ( Ex: One student have one roll number)
- One to Many ( Ex: A doctor have Multiple Patients )
- Many to One ( Ex: Many products from one store )
- Many to Many ( Ex: Many products from many stores)
If a database design is not perfect, that means it may contain some anomalies(errors). Normalization is a method to remove all anomalies and brings the database to a consistent state.
- Minimizing redundancy.
- Minimizing insertion, deletion, and update anomalies.
Types of normalization
1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (Fourth Normal Form)
5NF (Fifth Normal Form)
5. SQL Joins:
What joins simply do means, it combines rows from two or more tables based on the related columns between them.
Types of SQL Joins
- Inner Join
— It returns the common values in both tables
- Outer Join
— It returns all records when there is a match in either left or the right table.
- Left Outer Join
— It returns all records from the left table and common records from the right table.
- Right Outer Join
— It returns all records from the right table and common records from the left table.
- Self Join
— It means to join a table to itself.
6. SQL Constraints:
SQL Constraints are rules that are used to limit the type of data that can go into the table, to maintain the integrity and accuracy of the data inside the table.
Example: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT.