Depending on storage method used by database, it can be classified in various models. Such as, Relational database, Network Database, Object Oriented Database etc. definition can be found over Wikipedia. The most commonly used Database Model today is Relational Database Model, in which all information stored as a relation. This model was originally formulated by E.F.Codd, also known as father of modern Relational Database. All DBMS, e.g. Oracle, SQL Server, IBM DB2, MySql follow this model.
As of today, Relational DBMS is the most widely used DBMS, and that’s I will write about. Also will explain the main reason we use DMBS instead of file system (again …). Below figure shows how RDBMS constructed internally (and again, all RDBMS follows this model, they are just represented differently) which i found on Wikipedia.
RDBMS model contains five basic engines. Namely, SQL Engine, Relational Engine, Storage Engine, Transaction Engine and User Interface.
User Interface: Provides interface for users to interact with DBMS.
SQL Engine: It is brain of DBMS, it consist of Compiler, Optimizer and Executor. When we write and sql query, at first Compiler compiles the query and generates more then one execution plans, out of that optimizer chooses optimized plan which is executed by executor.
Transaction Engine: Its main job is to make sure that DBMS always maintain ACID property.
Relational Engine: Job of this engine is to maintain relational objects such as tables, reference keys , indexes etc.
Storage Engine: Here lies OS of DBMS itself, its main job is to handle writing / reading of files to/from memory. Also it is closely connected with Transaction Engine to provide storage for redo / undo logs, transaction logs, metadata and all other sort of storage required to comply ACID property.
For the side, there is also another component called Memory (management) whose main job is store dirty pages (data), execution plans, fetched data etc temporarily
Now, you may have got confused with this word “ACID property” … right ?? Well, its nothing like testing DBMS by pouring ACID on it :-p , its just a guideline that every DBMS has to follow to be called it as DBMS. And its the main reason why we don’t use File Systems because they don’t have ACID property compliance !!!
ACID is acronym of, Atomicity Consistency Isolation and Durability.
Atomicity: gives guarantee that either all tasks will be executed or no task will be executed for any given transaction. Means, if in one transaction we were updating records of 1000 customer and in middle of that process, something happens and system gets crash, then all that records will be rolled back if the process has not completed. And by doing so, it ensures that we do not endup in updating/writing multiple times.
Consistency: guarantees that, database will always be in consistent state before and after execution of transaction. And if that transaction somehow violates consistency then whole transaction will be rolled back or if succeeds then it takes database from one state that is consistent with the rules to another state that is also consistent with the rules.
Isolation: guarantees that when one transaction is already in session, another transactions can’t access resource (e.g. tables / rows) required by transaction already in session. So that two users doesn’t endup in editing same record at same time (well it happens sometimes and both users can get stuck it is called DEAD LOCKING)
Durability: guarantees that once transaction is committed and stored, it is available to use anytime. Many databases implement durability by writing all transactions into a transaction log that can be played back to recreate the system state right before a failure. A transaction can only be deemed committed after it is safely in the log.Durability does not imply a permanent state of the database. Another transaction may overwrite any changes made by the current transaction without hindering durability.
That’s it for today.
Remember its just a thought. (well, its not just a thought, its real world app. :-D)
