Introduction to Database Management Systems (DBMS)
Database Management System (DBMS)
A Database Management System (DBMS) is software that allows access to data stored in a database. It provides an easy and effective method of:
- Defining information
- Storing information
- Manipulating information
- Protecting information from system crashes or data theft
- Differentiating access permissions for different users
Data Theft
Data theft occurs when someone steals information stored on databases and servers.
Components of a Database System
A database system is divided into three main components: Query Processor, Storage Manager, and Disk Storage.
1. Query Processor
The Query Processor interprets requests (queries) received from end-users via an application program into instructions. It also executes user requests received from the DML compiler. The Query Processor contains the following components:
- DML Compiler: Processes DML statements into low-level instructions (machine language) for execution.
- DDL Interpreter: Processes DDL statements into a set of tables containing metadata (data about data).
- Embedded DML Pre-compiler: Processes DML statements embedded in an application program into procedural calls.
- Query Optimizer: Executes the instructions generated by the DML Compiler.
2. Storage Manager
The Storage Manager provides an interface between the data stored in the database and the queries received. It is also known as the Database Control System. It maintains the consistency and integrity of the database by applying constraints and executing DCL statements. It is responsible for updating, storing, deleting, and retrieving data in the database. The Storage Manager contains the following components:
- Authorization Manager: Ensures role-based access control, checking whether a user has the privilege to perform the requested operation.
- Integrity Manager: Checks integrity constraints when the database is modified.
- Transaction Manager: Controls concurrent access by executing transactions in a scheduled manner, ensuring database consistency before and after execution.
- File Manager: Manages file space and data structures used to represent information in the database.
- Buffer Manager: Responsible for cache memory and data transfer between secondary storage and main memory.
3. Disk Storage
Disk Storage contains the following components:
- Data Files: Store the actual data.
- Data Dictionary: Contains information about the structure of database objects. It is a repository for metadata.
- Indices: Provide faster retrieval of data items.
Generalization and Specialization
Generalization is the process of identifying common characteristics from multiple things and creating a more general category to represent them. For example, generalizing cars, trucks, and motorcycles into the category “Vehicle.”
Specialization is the opposite of generalization. It involves creating more specific categories from a general category by identifying unique characteristics. For example, specializing “Vehicle” into “Car,” “Truck,” and “Motorcycle.”
Data Definition Language (DDL)
DDL stands for Data Definition Language. It defines the database structure or pattern, including creating schemas, tables, indexes, and constraints. DDL statements create the database skeleton and store metadata information.
DDL Tasks:
- Create: Creates objects in the database.
- Alter: Alters the structure of the database.
- Drop: Deletes objects from the database.
- Truncate: Removes all records from a table.
- Rename: Renames an object.
- Comment: Adds comments to the data dictionary.
Data Manipulation Language (DML)
DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database, handling user requests.
DML Tasks:
- Select: Retrieves data from a database.
- Insert: Inserts data into a table.
- Update: Updates existing data within a table.
- Delete: Deletes records from a table.
- Merge: Performs UPSERT operations (insert or update).
- Call: Calls a structured query language or Java subprogram.
- Explain Plan: Explains data parameters.
- Lock Table: Controls concurrency.
Data Control Language (DCL)
DCL stands for Data Control Language. It retrieves stored or saved data. DCL execution is transactional and has rollback parameters (except in Oracle databases).
DCL Tasks:
- Grant: Gives user access privileges to a database.
- Revoke: Takes back permissions from a user.
Revokable operations include CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE, and SELECT.
Transaction Control Language (TCL)
TCL runs changes made by DML statements. TCL can be grouped into logical transactions.
TCL Tasks:
- Commit: Saves the transaction on the database.
- Rollback: Restores the database to its original state since the last Commit.
Codd’s 12 Rules
Rule 0: The Foundation Rule
The database must be structured relationally for the system’s relational capabilities to manage it.
Rule 1: The Information Rule
All data in a database must be stored in tables as columns and rows.
Rule 2: The Guaranteed Access Rule
Any single piece of data (atomic value) can be retrieved using the primary key value, column name, and table name.
Rule 3: The Systematic Treatment of Null Values
Defines how null values (missing data, unsuitable information, etc.) are handled in database records.
Rule 4: The Dynamic/Active Online Catalog on the basis of the Relational Model
An online database dictionary describes the logical structure of the database, providing user access and a query language similar to the database itself.
Rule 5: The Comprehensive Data SubLanguage Rule
The relational database supports various languages, which must have a well-defined syntax and support operations like view definition, integrity constraints, data manipulation, and transaction management. Accessing data without a language violates this rule.
Rule 6: The View Updating Rule
View tables can theoretically be updated, and database systems must support this in practice.
Rule 7: The Relational Level Operation Rule
Database systems must adhere to high-level relational operations (update, insert, delete) at each level or single row. Operations like intersection, union, and minus are also included.
Rule 8: The Physical Data Independence Rule
Stored data must be physically independent for applications to access the database. Changes to the physical structure should not affect external applications.
Rule 9: The Logical Data Independence Rule
Similar to physical data independence, changes at the logical level (table structures) should not impact user experience. For example, splitting a table should not affect the application’s user view.
Rule 10: The Integrity Independence Rule
The database must guarantee integrity independence when using SQL to insert data. Entered values should not be changed, and data integrity should not rely on external components.
Rule 11: The Distribution Independence Rule
The database must function correctly even if distributed across multiple locations and used by various users. Users should be unaware of other users accessing the same data.
Rule 12: The Non-Subversion Rule
RDBMS is defined by SQL for storing and manipulating data. Accessing the database system using a different language should not bypass or subvert data integrity.
bvert data integrity.
Keep learning & stay tuned 2 get the latest updates on the GATE Exam along with GATE Eligibility Criteria, GATE Syllabus 4 CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, & more.