Introduction of DBMS (Database Management System) | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Database: A database is an organized collection of related information that is structured to enable efficient retrieval, insertion and deletion of data. Databases typically arrange data into tables, views, schemas, and reports.
For example, a university database might store information about students, faculty, and staff to facilitate efficient data management.

Database Management

DDL (Data Definition Language)

DDL is the short name of Data Definition Language, which deals with database schemas and descriptions of how the data should reside in the database.

• CREATE: Used to create a database and its components, such as tables, indexes, views, stored procedures, functions, and triggers.
• ALTER: Alters the structure of an existing database.
• DROP: Eliminates objects from the database.
• TRUNCATE: Removes all records from a table, including all spaces allocated for the records that are removed
• COMMENT: Adds comments to the data dictionary.
• RENAME: Changes the name of an object within the database.

DML (Data Manipulation Language)

DML is the short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements, such as SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.

• SELECT: Retrieves data from the database.
• INSERT: Adds new data into a table.
• UPDATE: Modifies existing data within a table.
• DELETE: Remove records from a database table.
• MERGE: The MERGE command in SQL is a DML command used to combine INSERT, UPDATE, and DELETE operations into a single statement. It is also known as "upsert" because it can update existing rows or insert new ones if no match is found.
• CALL: Invokes a PL/SQL or Java subprogram.
• EXPLAIN PLAN: Provides insights into the data access path for query optimization.
• LOCK TABLE: Controls access to a table to ensure data integrity during concurrent operations.

Database Management System (DBMS)

The software which is used to manage database is called Database Management System (DBMS). For Example, MySQL, Oracle etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks:

• Data Definition: It helps in the creation, modification and removal of definitions that define the organization of data in a database.
• Data Updation: It helps in the insertion, modification and deletion of the actual data in the database.
• Data Retrieval: It helps in retrieval of data from the database which can be used by applications for various purposes.
• User Administration: Manages user registration and monitoring, enforces data security, oversees system performance, maintains data integrity, controls data concurrency, and recovers data in the event of unexpected failures.

Shift from File System to DBMS

 In a File System, data is managed through files stored on a hard disk. Users have the ability to create, delete, and update these files as needed. For example, in a file-based University Management System, student data is accessible to various sections such as Departments, Academics, Results, Accounts, and the Hostel Office. Some information, like Roll No, Name, Father's Name, Address, and Phone number, is common to all sections, while other details, like the hostel allotment number, are only available to the hostel office. However, this system has several issues: 

• Redundancy of Data: Redundant data occurs when the same information is stored in multiple places. For instance, if a student needs to change their Phone number, they must update it across several sections. Additionally, old records must be removed from all sections where that student is represented.
• Inconsistency of Data: Data is inconsistent when different copies do not match. For example, if a student’s Phone number is recorded differently in the Accounts and Academics Sections, it leads to inconsistency. This can happen due to typing mistakes or not updating every copy.
• Difficult Data Access: Users need to know the exact location of a file to access data, making the process cumbersome. Searching for a student’s hostel allotment number among 10,000 unsorted records can be very challenging.
• Unauthorized Access: The File System can allow for unauthorized data access. For example, if a student has access to a file containing their marks, they might change it without permission.
• No Concurrent Access: Concurrency refers to multiple users accessing the same data simultaneously. Traditional file systems usually have limited concurrency, allowing only one user to access data at a time.
• No Backup and Recovery: The File System does not provide backup or recovery options if a file is lost or corrupted.

These issues are the main reasons for moving from file systems to Database Management Systems (DBMS). 

3-Tier Architecture in DBMS

• Physical Level:
  • This level is concerned with the physical storage of data.
  • It keeps track of where database objects, such as tables and indexes, are stored on secondary storage devices like disks and tapes.
  • Users of the DBMS do not need to know the exact locations of these objects; the system handles this complexity.
  • The physical level describes how data is saved on these devices, providing details about storage structures and mechanisms.
• Conceptual Level:
  • At this level, data is presented to users as various database tables.
  • For example, in a STUDENT database, users may see tables like STUDENT and COURSE, but they are unaware of the underlying storage details.
  • This level is also known as the logical schema, as it defines what types of data are stored in the database without revealing how they are stored.
• External Level:
  • The external level provides different views of the data based on the conceptual level tables.
  • Each external view is customized to meet the specific needs of different user groups.
  • For instance, faculty members may need to see student course details, while students may want to access their academic records, accounts, and hostel information.
  • The primary goal of the external level is to provide data abstraction, simplifying the data access process for users by presenting only the relevant information.

Data Independence

Data independence is the capability to modify data at one level without affecting another level. In this context, there are two types of data independence:

1. Physical Data Independence: This type of data independence ensures that changes in the physical storage of tables and indexes do not impact the conceptual level or the external view of the data. Physical data independence is generally easier to achieve, although its implementation may vary across different Database Management Systems (DBMS).
2. Conceptual Data Independence: Conceptual data independence requires that the data at the conceptual schema and external schema remain independent of each other. This means that modifications to the conceptual schema should not affect the external schema. For instance, adding or removing attributes from a table should not change how users perceive that table. Achieving conceptual data independence is more challenging than physical data independence because changes in the conceptual schema can influence users' views.

Phases of Database Design

The process of designing a database for a real-world application involves several phases, starting from gathering requirements and ending with physical implementation using DBMS software. The key phases are:

1. Conceptual Design: In this initial phase, the focus is on capturing the database requirements using a high-level model. One common approach for conceptual design is the Entity-Relationship (ER) model.
2. Logical Design: During this phase, the data is represented in a relational format. The ER diagram created during the conceptual design phase serves as the basis for developing the relational model.
3. Physical Design: The final phase involves implementing the data in the relational model using commercial Database Management Systems (DBMS) such as Oracle and DB2.

Advantages of DBMS

• Reduced Redundancy and Enhanced Consistency: DBMS minimizes data redundancy through normalization, which helps maintain data consistency. For example, student information can be stored in a single location and accessed by various users, thanks to the use of primary keys and foreign keys.
• Simplified Data Access: Users can access data easily by simply knowing the name of the relation, making the process straightforward.
• Multiple Data Views: DBMS allows the creation of different views of the same data to cater to varying user needs. For instance, faculty salary details can be hidden from students but made visible to administrators.
• Data Security: Only authorized users can access data in a DBMS, and data can also be encrypted to enhance security.
• Concurrent Data Access: Multiple users can access data simultaneously in a DBMS without any issues.
• Backup and Recovery Mechanism: The backup and recovery system in a DBMS helps prevent data loss and maintains consistency during major failures.