Structure of the Database System

Structure of the Database System

A database is an organized collection of data.
One of the most common data structures in a database is table.
A database table consists of rows and columns.
In database terminology, each row is called a record.
A record is also called an object or an entity.
In other words, a database table is a collection of records.
A field corresponds to a column in the table and represents a single value for each record.
A field is also called an attribute.
In other words, a record is a collection of related attributes that make up a single database entry.
Example: Customers table. Each customer has a unique identifier (Customer ID), a name, and a telephone number. These are the fields. The first row is called the header row and indicates the name of each field. Following the header row, each record is a unique customer.
Database is responsible to store huge amounts of data and is capable of handling multiple requests from users.
One can imagine a database as a brain! How is the structure of the brain?

It can be considered as a user-friendly web page where the user enters the requests.
Here sh/he simply enters the details that sh/he needs and presses buttons to get the data.

They are the real users of the database.
They can be developers, designers, administrator or the actual users of the database.
The end-user, who interacts with system from online workstation or terminals.
Use the interface provided as an integral part of the database system software.
The various categories of end-users are:

These Users occasionally access the database but may need different information each time. For example, High-level Managers who access the data weekly or biweekly.

These users frequently query and update the database using standard types of Queries. For example: - Reservation clerks for airlines/hotels check availability for given request and make reservations.

Those end users who interact with the database directly via on-line terminal or indirectly through Menu or graphics-based Interfaces. Example:-Library Management System.

Data Definition Language (DDL) is a query fired to create database, schema, tables, mappings etc in the database.
These are the commands used to create the objects like tables, indexes in the database for the first time. In other words, they create structure of the database.

This part of database is responsible for processing the DDL commands.
That means this compiler actually breaks down the command into machine-understandable codes.
It is also responsible for storing the metadata information like table name, space used by it, number of columns in it, mapping information etc.

When the user inserts, deletes, updates or retrieves the record from the database, sh/he will be sending request which sh/he understands by pressing some buttons.
But for the database to work/understand the request, it should be broken down to object code. This is done by the compiler.
One can imagine this as when a person is asked some question, how this is broken down into waves to reach the brain!

When user fires some request, sh/he is least bothered how it will be fired on the database.
Sh/He is not all aware of database or its way of performance.
But whatever be the request, it should be efficient enough to fetch, insert, update or delete the data from the database.
The query optimizer decides the best way to execute the user request which is received from the DML compiler.
It is similar to selecting the best nerve to carry the waves to brain!

It converts the requests received from query optimizer to machine-understandable form. It makes actual request inside the database. It is like fetching the exact part of the brain to answer.
It helps to maintain consistency and integrity by applying the constraints. That means, it does not allow inserting / updating / deleting any data if it has child entry. Similarly it does not allow entering any duplicate value into database tables.
It controls concurrent access. If there is multiple users accessing the database at the same time, it makes sure, all of them see correct data. It guarantees that there is no data loss or data mismatch happens between the transactions of multiple users.
It helps to backup the database and recover data whenever required. Since it is a huge database and when there is any unexpected exploit of transaction, and reverting the changes are not easy. It maintains the backup of all data, so that it can be recovered.

Some of the processed DML statements (insert, update, delete) are stored in it so that if there is similar requests, it will be re-used.

It contains all the information about the database.
As the name suggests, it is the dictionary of all the data items.
It contains description of all the tables, view, materialized views, constraints, indexes, triggers etc.