Database and Database Management System
There are many books written about Database and Database Management System, in this post I will try to provide overview of database and DBMS.
This post is a very brief on the field of Database and Database Management Systems, so I strongly
recommend reading books about DBMS and DB. This post will provide you with basic concepts.
Enjoy ):
What is Database (DB):
Is a set of related data organized in form that makes it easy to handle.
What is Database Management Systems (DBMS):
It is collection of programs to manage, access the database, and provide consistency, safety, efficiency, and durability.
It provides a way to store and retrieve database information in efficient and reliable way.
DBMS uses to provide multiple users, and manage the database when you insert the data to a table. DBMS is responsible of how this data must be inserted, so it manages memory, and operation.
Transaction: Transaction is a set of actions. for example when some person want to transfer money to another there are many actions.
Action #1: Deduct the amount to be transfered from person number one.
Action #2: Increase the amount of the second person by the deducted amount from person one.
ACID:
Atomicity: either all actions of transactions reflected at all or none for all.
That means if we have those transactions:
If account number 111 wants to transfer 5000 to account 222. For this operation if we reflect the deduction only (Execute the first action) there is a problem and vice versa, so we have to reflect it all or none at all.
T1: Action #1: deduct from account number = 111 amount of 5000.
T1:Action #2: update the amount of account number = 222 to become old amount + 5000.
Commit.
Consistency: that means the database must be consistent, if we reflect a transaction we must ensure that transaction doesn’t affect the consistency of the database.
Isolation: if there are many transaction executed concurrently, the same order we execute those transactions must ensure that the final result in which transaction executed doesn’t affect other transaction.
If there are two users work on same table each of them update the table the DBMS must ensure that the result must be done as if we execute them in a serial order.
If x = 36;
User one:
T1: Action #1: Read x.
T1: Action #2 update x = x + 50;
User two:
T2 Action #1: Read x.
T2 Action #2: Update y = y * 1.34.
If these transactions executed in serial order there is no problem.
First scenario: user one start and finish before user two.
User one gets that:
y = 36 and after update y = 36 + 50 = 86.
User two get that:
y = 86 and after update y = 86 * 1.34 = 115.24
This result is acceptable if user one commit his change.
If user one doesn’t commit his change then user two update his data base on dirty data (y = 86) but he must update his data based on (y = 36)
Second scenario: user one and user two start at the same time but user one finish before user two.
User one gets that: y = 36.
User two get that: y = 36.
User one get y = 36 + 50 = 86.
User two get y = 36 * 1.34 = 48.24
So user two overwrite user’s one result.
Third scenario: user one and user tow start at the same time but user two finish before user one.
User one gets that: y = 36.
User two get that: y = 36.
User two get y = 36 * 1.34 = 48.24
User one get y = 36 + 50 = 86.
So user one overwrite user user’s two result.
And you think about many scenarios for a lot of transaction.
There is mechanism that DBMS use to ensure this it use locking (For more information read about DBMS). The basic idea is that when user one read y he get shared lock (S) any other user want to read y he get this shared object (The share object can be obtained for any number of user while there is no exclusive share on that object). When user one want to update x he must get exclusive lock (X) this lock is not shared with any other user, if user one and user two get shared lock on y when user two try to get exclusive lock on y (he must wait until user two release the lock).
So the transactions must be done as below:
User 1:
Get shared (S) on y, y = 36.
Get exclusive lock on y, y = 36 + 50 = 86;
Release the lock.
User 2:
Get shared (S) on y, y = 86.
Get exclusive lock on y, y = 86 * 1.34 = 115.4
Release lock.
User one in this case = T1, User two = T2.
* User may issue many transaction.
But suppose that user two start before user one, that means when user two get shared lock on y he read y = 36. Try to get the rest of calculation.
Please try to read any book about DBMS to get knowledge about the lock mechanism and types of locks and other important concepts related to locking.
Durability: after data written successfully the DBMS must ensure the data are available even with system failure.
If user commit his transaction and after that system crash happen, DBMS must redo the transaction and ensure is reflected to user when he search about it.
Data Abstraction:
Physical Level: it is complex level about how data are actually stored. It has complex data structure.
Logical Level: it represents data in logical form in form of relation, tables.
View Level: it represents sub set of database.
Written by: Elmozamil Elamir Hamid Elamir.
recommend reading books about DBMS and DB. This post will provide you with basic concepts.
Enjoy ):
What is Database (DB):
Is a set of related data organized in form that makes it easy to handle.
What is Database Management Systems (DBMS):
It is collection of programs to manage, access the database, and provide consistency, safety, efficiency, and durability.
It provides a way to store and retrieve database information in efficient and reliable way.
DBMS uses to provide multiple users, and manage the database when you insert the data to a table. DBMS is responsible of how this data must be inserted, so it manages memory, and operation.
Transaction: Transaction is a set of actions. for example when some person want to transfer money to another there are many actions.
Action #1: Deduct the amount to be transfered from person number one.
Action #2: Increase the amount of the second person by the deducted amount from person one.
ACID:
Atomicity: either all actions of transactions reflected at all or none for all.
That means if we have those transactions:
If account number 111 wants to transfer 5000 to account 222. For this operation if we reflect the deduction only (Execute the first action) there is a problem and vice versa, so we have to reflect it all or none at all.
T1: Action #1: deduct from account number = 111 amount of 5000.
T1:Action #2: update the amount of account number = 222 to become old amount + 5000.
Commit.
Consistency: that means the database must be consistent, if we reflect a transaction we must ensure that transaction doesn’t affect the consistency of the database.
Isolation: if there are many transaction executed concurrently, the same order we execute those transactions must ensure that the final result in which transaction executed doesn’t affect other transaction.
If there are two users work on same table each of them update the table the DBMS must ensure that the result must be done as if we execute them in a serial order.
If x = 36;
User one:
T1: Action #1: Read x.
T1: Action #2 update x = x + 50;
User two:
T2 Action #1: Read x.
T2 Action #2: Update y = y * 1.34.
If these transactions executed in serial order there is no problem.
First scenario: user one start and finish before user two.
User one gets that:
y = 36 and after update y = 36 + 50 = 86.
User two get that:
y = 86 and after update y = 86 * 1.34 = 115.24
This result is acceptable if user one commit his change.
If user one doesn’t commit his change then user two update his data base on dirty data (y = 86) but he must update his data based on (y = 36)
Second scenario: user one and user two start at the same time but user one finish before user two.
User one gets that: y = 36.
User two get that: y = 36.
User one get y = 36 + 50 = 86.
User two get y = 36 * 1.34 = 48.24
So user two overwrite user’s one result.
Third scenario: user one and user tow start at the same time but user two finish before user one.
User one gets that: y = 36.
User two get that: y = 36.
User two get y = 36 * 1.34 = 48.24
User one get y = 36 + 50 = 86.
So user one overwrite user user’s two result.
And you think about many scenarios for a lot of transaction.
There is mechanism that DBMS use to ensure this it use locking (For more information read about DBMS). The basic idea is that when user one read y he get shared lock (S) any other user want to read y he get this shared object (The share object can be obtained for any number of user while there is no exclusive share on that object). When user one want to update x he must get exclusive lock (X) this lock is not shared with any other user, if user one and user two get shared lock on y when user two try to get exclusive lock on y (he must wait until user two release the lock).
So the transactions must be done as below:
User 1:
Get shared (S) on y, y = 36.
Get exclusive lock on y, y = 36 + 50 = 86;
Release the lock.
User 2:
Get shared (S) on y, y = 86.
Get exclusive lock on y, y = 86 * 1.34 = 115.4
Release lock.
User one in this case = T1, User two = T2.
* User may issue many transaction.
But suppose that user two start before user one, that means when user two get shared lock on y he read y = 36. Try to get the rest of calculation.
Please try to read any book about DBMS to get knowledge about the lock mechanism and types of locks and other important concepts related to locking.
Durability: after data written successfully the DBMS must ensure the data are available even with system failure.
If user commit his transaction and after that system crash happen, DBMS must redo the transaction and ensure is reflected to user when he search about it.
Data Abstraction:
Physical Level: it is complex level about how data are actually stored. It has complex data structure.
Logical Level: it represents data in logical form in form of relation, tables.
View Level: it represents sub set of database.
Figure 1.1 Data abstraction
Famous Database Management System (DBMS) Software Available:- Oracle.
- SQL Server.
- MySQL.
- SQLLite.
- PostgresSQL.
- and many Others.
Written by: Elmozamil Elamir Hamid Elamir.
we prefer to honor numerous other online websites on the net, even when they aren?t linked to us, by linking to them. Beneath are some webpages worth checking out.
ReplyDeleteOracle Training