1. Transactions :-
A logical unit of work on database.
An action or series of action that are performed by a single user or application program, which reads or updates the contents of a database. It must follow all the acid properties.
For example :- When i send money to my friend the updation of the data is happending it is a action which is performing here.
ACID
A -> Atomicity :
The hole transaction takes place at on ce or does not happen at all. If something happen in between transaction will cancel out.
For example :- When i send money to my friend if something happen in between my friend will not get the money and the money will be deducted from my account for perventing this we use atomicity
C -> Consistency :
It means correctness that any given database transaction must change affected data only in allowed ways. For a database to be consistent, data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, or any combination.
For example :- When is send money to my friend the sum of my balance after and before the transaction should br equal to the sum of the balance of my friend before and after the transaction.
I -> Isolation:
It means that if multiple transaction is happening
This property ensures that transactions are isolated from one another, and each transaction must behave as if it's the only transaction being executed on the database. In other words, the changes made by one transaction should not be visible to other transactions until the first transaction has been completed.
D -> Durability:
This property ensures that once a transaction is committed, its changes will be permanent and will survive any subsequent system failures, such as power outages or hardware failures. In other words, once a transaction is committed, the changes made during that transaction will be stored on disk and will not be lost even if the database crashes or the system shuts down unexpectedly.
2. CAP Theorem
The CAP theorem is a theorem about distributed computing systems. It states that a computer system can at best provide two of the three properties.
Consistency :
Consistency means that all clients see the same data at the same time, no matter which node they connect to. For this to happen, whenever data is written to one node, it must be instantly forwarded or replicated to all the other nodes in the system before the write is deemed ‘successful.’
Availability :
Availability means that any client making a request for data gets a response, even if one or more nodes are down. Another way to state this—all working nodes in the distributed system return a valid response for any request, without exception.
Partition tolerance :
A partition is a communications break within a distributed system—a lost or temporarily delayed connection between two nodes. Partition tolerance means that the cluster must continue to work despite any number of communication breakdowns between nodes in the system.
3.Joins
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Joins are of 5 types.
1.Inner Join :
It return the common data record that have matching values in both tables.
Syantax for inner join:
SELECT column_name(s) FROM table1
INNER JOIN table2
ON table1.column_name =
table2.column_name;
2.Left Join :
It returns all records from the left table, and the matched records from the right table.
Syantax for left join :
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name =
table2.column_name;
3.Right Join :
It returns all records from the right table, and the matched records from the left table.
Syantax for right join :
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name_ =
table2.column_name;
4.Full Outer Join :
It returns all records all the data from both the table either there is a match or not.
Syantax for full join :
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name WHERE condition;
5.Self Join :
A self join is a regular join, but the table is joined with itself.
Syantax for self join:
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
4. Indexes
Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
The CREATE INDEX statement is used to create indexes in tables.
CREATE INDEX index_name
ON table_name (column1, column2, ...);
5.Triggers
It is a stored procedure in database which automatically invokes whenever a special event in the database occurs.
For example:- A trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
In Structured Query Language, triggers are called only either before or after the below events:
INSERT Event :
This event is called when the new row is entered in the table.
UPDATE Event:
This event is called when the existing record is changed or modified in the table.
DELETE Event:
This event is called when the existing record is removed from the table.
6. Normalization
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.
Normalization divides a database into two or more tables and defines relationships between them.
Types of normalization:-
Each table represents a distinct entity or concept, and the relationships between the tables are based on the attributes that they share.
1NF: A relation is in 1NF if all its attributes have an atomic value.
2NF: A relation is in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the candidate key in DBMS.
3NF: A relation is in 3NF if it is in 2NF and there is no transitive dependency.
BCNF: A relation is in BCNF if it is in 3NF and for every Functional Dependency, LHS is the super key.
7. Aggregations, Filters in queries
Aggregations and filters are common operations used in SQL queries to summarize and manipulate data.
Some common aggregations:-
1. sum :
To calculate total column values.
2. AVG :
It returns the average value of a numeric column.
3. count :
It returns the number of rows that matches a specified criterion.
4. MAX :
It return max value of column.
5. MIN :
It returns min value of column
Some filter technique:-
WHERE
The WHERE clause specifies a condition that must be met for each row to be selected in the result set.You can also use comparison operators such as
less than (<),greater than (>),equal to (=),not equal to (!= or <>)and logical operators such asANDandOR,BETWEENto create more complex filter conditions. Here's an example:
select *
from sales
where (product = 'product_name' and price > 100) or price > 20
8. Locking mechanism
Locking is a fundamental mechanism in database systems that allows
multiple users to access shared resources without interfering with each other.
Types of Locking Mechanism :-
There are different types of locking mechanism showing below.
Shared lock(S):-
A shared lock can be imposed by several transactions at the same time over the same page or row and in that way several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data. In addition, a shared lock will allow write operations, but no DDL changes will be allowed.
Exclusive lock :-
The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. An exclusive lock can be imposed to a page or row only if there is no other shared or exclusive lock imposed already on the target.
Update lock :-
An update lock is acquired when a transaction intends to modify a resource and wants to prevent other transactions from acquiring an exclusive lock on the same resource.
9. Database Isolation Levels
The SQL standard defines four isolation levels.
Read Uncommitted :-
The lowest isolation level allowed by SQL is read uncommitted. In this level, transactions can read data that is not committed by other transactions, permitting dirty read.
Read Committed :-
This isolation level allows for the reading of data after it is committed by a transaction. This means no dirty reads are possible. This isolation is unable to prevent non-repeatable reads.
Repeatable Read :-
This isolation tries to improve on the previous isolation level by preventing both dirty reads and non-repeatable reads. This prevention is done by applying locks on rows that are read and rows that perform write operations.
Serializable :-
The highest isolation level allowed is serializable. This isolation level looks like a serial execution, with almost no concurrency. All the three read phenomena are prevented in this isolation level, but it compromises concurrent execution.
Top comments (0)