DBMS Tutorial

Essential Terminology

Database: Database is a collection of data which inter-related. It helps in efficient retrieval, deletion, and insertion of the data in the database and organizes it in the form of data tables, schemas, views, reports, and other forms.

Database Management System: Data management system is also often known as DBMS. DBMS is the software which is used to manage the database. MySQL and Oracle are some of the most popular database management systems.

DBMS allows users tasks like:

1. Data Definition: It can create, modify, and remove definitions which are used to define the organization of data in the database.
2. Data Updation: It can modify, insert, or delete data in the actual database.
3. Data Retrieval: It helps in the retrieval of the data from its location in the database for various purposes.
4. User Administration: It can be used for registering, monitoring users, monitoring performance, enforcing data security, dealing with concurrency control, maintaining data integrity, and recovering information corrupted due to unexpected failure.

Shift to DBMS from File System

File system works by storing data on hard disks where they can be modified, created, and deleted as per what their requirement. But this system has multiple issues. These issues include:

1. Redundancy of Data: Redundancy of data is when the same data has multiple copies across the database or has data which is outdated despite being updated. This issue can be frequently seen in the file system.
2. Inconsistency of Data: Inconsistency of data occurs when the data is not the same across all of its available copies. Since the file system has multiple copies, this issue could arise quite often. This issue might occur due to typing errors or not updating the information in all copies.
3. Difficult Data Access: In the file system, the user must know the exact location of the file or data to be able to access it whereas, in DBMS, just the relation of the data is enough to find the data.
4. Unauthorized Access: In the file system, anyone can have access to the files, even unauthorized people, which could lead to devastating consequences.
5. No Concurrency: Systems using the file system cannot provide access to the same file to multiple users simultaneously.
6. No Backup or Recovery: File system also does not offer and back up or recovery in case the data is corrupted.

The DBMS three-tier architecture is divided into three parts. These parts, though inter-related, are individual modules which can act independently. The three modules are:

1. Physical Level: At this level in the architecture, the information about the location of the database objects is stored.
2. Conceptual level: In this level, the data is represented by a number of database tables which help them be visible at the application level, but the client side is unaware about their locations in the server.
3. External Level: In this level, a view is specified for the database tables seen in the conceptual level. Each view from the external level is used for a specified set of users and their needs.

Data Independence

Data Independence in the simplest terms is that the change in data at a certain level in the architecture does not affect data at any other level. The two types of data independence are:

1. Physical Data Independence: This type of data independence is that any change in the location of the database table physically must not affect the database at a conceptual or external level. This type of data independence is relatively easy to achieve and can be seen in most DBMS implementations.
2. Conceptual Data Independence: In this type, the data at the external level must not change due to change in the conceptual level. This comparatively difficult to achieve since changes at the conceptual level bring about a change in the view of the user.

Phases of Database Design

The real-world application database designing starts from capturing the requirements to the physical implementation using the DBMS software. This consists of three steps. These steps are:

1. Conceptual Design: Using the high-level conceptual data model, the requirements of the client are captured.
2. Logical Design: The data is stored in a relational model in the logical design.
3. Physical Design: The data in the relational model is implemented using a standard commercial DMBS in this design phase.

Advantages of DBMS

The advantages of DBMS are as follows:

1. Backup and Recovery Mechanism: In case of catastrophic failure, DBMS avoid data loss as well as data inconsistency due to its backup and recovery mechanism.
2. Concurrent Access to the Data: The data on the server side can be used by many clients simultaneously in DBMS.
3. Data Security: The data available in DBMS can be accessed only by people who are authorized. There is also the option to encrypt the data in DBMS. This provides a higher level of security to the data.
4. Multiple Data Views: DBMS offers multiple views of the data to different users. This can help cater to the specific need to the user or set of users. Certain data can be hidden from users that do not need access to it or who are not authorized to view it in the database.
5. Simplified Data Access: The process in DBMS is very simple. The user might not need to know the exact location of the data in the database, but the name of the relation is enough for access.
6. Data Consistency and Minimized Redundancy: Data in the DBMS is normalized. This lead to an increased level of data consistency, which in turn helps in reducing the redundancy of the data in the DBMS.

DBMS Tutorial List