Database System Structure & architecture

 Database System Structure & architecture 
•        Used by the computer system on which the database system runs.
•         Database systems can be centralized, or client-server.
•         A database system is divided into modules that deal with different responsibilities of the overall system.
•        The functional components are broadly divided into the users, interface, query processor components, and the storage manager.

 •        Primary goal:- retrieving information from and storing new information into the database.
 

I.               SYSTEM USERS

four different types of database-system users,

1. Application programmers

1. Sophisticated users and Specialized users:-

1. Database Administrator

1. Naive users

 
1. Application programmers
•        Computer professionals and write application programs.
•        They interact with the system through DML calls.
•         DML calls are embedded in a program written in a host language (for example PHP, python, c).
•         These programs are commonly referred to as application programs.
 
2. Sophisticated users and Specialized users:-
•        Interact with the system without writing programs.
•        They request in a database query language as query form.
•        Each query is submitted to a query processor.
•        Query processor is to break down DML statements into instructions that the storage manager understands.
•        They work as an analyst.
•         Specialized users are sophisticated users who write specialized database applications like computer-aided design Systems, knowledgebase, and expert systems, etc.
• The user uses complex data types (for example, graphics data and audio data).
 

(3) Database Administrator (DBA)
•        DBA has central control over the system.
•         Responsible for following functions:
                        (i)  Schema Design and Maintenance, 
                      (ii)  Physical Schema and Organization Modification,
(iii) Authorization and Security,        
(iv) Integrity Constraint Specification,
(v) Recovery from Failure,                
(vi) Database Upgradation
 

(4) Naive users
•        Naive users are unsophisticated users.
•        Interact with the system by the permanent application programs that have been written previously.
•        For example, the clerk at the ticket booking window, he uses an application program to do his job of making reservations for a passenger.
 

Query Processor:
•        A query processor helps the database system simplify and facilitate data access.
•        System users are not required to know physical details of the implementation of the system.
•        Quick processing of updates.
•        Queries are written in a nonprocedural language, at the logical "level.
•        results are stored into an efficient sequence of operations at the physical level.
 
Query Processor Components: -
These components are used in evaluating DDL and DML queries. These are:-
(i) DDL Interpreter
•        Interprets DDL statements and store the definitions in the data dictionary, as metadata.

(ii) DML Queries
•        Sophisticated or Specialized user’s requests in a database query language.
•         Each query is submitted to a query tools
•         Query tools break down it into DML queries and low-level instructions.
•        These are sent to the DML compiler and Organizer, and the query evaluation engine for further processing.
 
(iii) DML Compiler and Organizer
•        DML calls are usually started by a special character/code so that the appropriate code can be generated.
•        A special preprocessor, called the DML precompiles, converts the DML statement to normal procedure calls in the host language.
•        The resulting program is then run through the host-language compiler, which generates appropriate object code ( a set of low-level instructions that can be used by query evaluation engine.)

 (iv) Application Program Object Code
•        It converts DML statements embedded in an application program to normal procedure calls in the host language.
•        These pre-compilers consult the DML compiler to generate the appropriate code.

 (v) Compiler and Linker: -
•         Application programmer writes program application.
•         The source codes compiled by the compiler and linker-linked application program object code to DML queries and send to query evolution engine.      

   
 (iv) Query evaluation Engine: -
•        A query can usually be translated into any of a number of alternative evaluation plans that all give the same result.
•        The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.
•        This component is responsible for interpreting and executing the SQL query.
•        It contains three major components
Compiler - builds a data structure from the SQL statement and then does semantic checking on the query such as whether the table exists, field ‘exists, etc.
Optimizer - transforms the initial query plan (data structure created by the compiler), into the sequence of operations usually pipelined together to achieve fast execution.
It refers to the metadata (dictionary) and statistical information stored about the data to decide which sequence of operations is likely to be faster and based on that it creates the optimal query plan.  Both cost and rule-based optimizers are used.
•    Execution Engine - executes each step in the query plan chosen by Optimizer.
 It interacts with the relation engine to retrieve and store records. 

 Storage Manager:

•        A storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
•        The storage manager is responsible for the interaction with the file manager.
•        The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system.
•        The storage manager translates the various DML statements into low-level file system commands.
•        The storage manager is responsible for storing, retrieving, and updating data in the database.
•        A large amount of storage space is required for storing corporate databases (which may range from hundreds to gigabytes to terabytes of data) and to manage this storage manager is required.
•        Data are to move between disk storage and main memory as per requirement because the main memory of the computer cannot store this much information.

The storage manager components–
(i) File Manager
•           It manages disk space allocation and the data structures used to store the data.
•           File manager maps disk pages of the file to the memory pages at the disk in physical form and does the actual disk I/O operations in case of major faults generated "by buffer manager module.

 (ii) Buffer Manager
•           Buffer manager responsible for loading pages(fetching) from disk to main memory and to managing the buffer pool based on Least Recently Used (LRU) algorithm and deciding the caching strategy suitable for the application.
•           It is a critical part of the database system, it enables the database to handle data sizes that are much large than the size of main memory for this has a special-purpose allocator for storing control information, which is transient.
 •           Buffer pool is the memory space used by buffer manager to cache disk pages associated with records, index information, Metadata information.
•           Some database systems have space limits at the individual level and some at the global level for buffer pool size.

(iii) Transaction Manager
•           The transaction manager creates transaction objects and manages their atomicity and durability.
•           Applications request the creation of a transaction object by calling the transactions manager’s begin Transaction method.
•           When a resource manager first participates in a transaction, it calls the Enlist method to enlist in the transaction.
•           The transaction manager tracks all the resource managers who enlist in the transaction.
•           It ensures that the database remains in a consistent (correct) state despite system failures and that concurrent transaction executions proceed without conflict.
One of the following three results can occur:
1. The application either commits or aborts the transaction.
2 . A resource manager aborts the transaction.
3. A failure occurs.

(iv) Authorization and Integrity Manager
• This manager is responsible for granting access to the database or portions thereof only to authorized users and preventing access to unauthorized users.
• It tests for the satisfaction of integrity constraints and checks the authority of users to access data.
• It uses all the integrity constraints and authorization rules specified by the DBA.
• Integrity manager must assure data integrity during normal database operations as well as during the database failures-
 

Disk Storage
• A DBMS can use several kinds of data structures as a part of physical system implementation in the form of disk storage.
• Each structure has its own importance.
• Following are some common data structures.
• Disk storage is the central repository for storing all kinds of data in the database.

(i) Data
•        It stores the database itself on the disk in the Data files.
 
(ii) Data Dictionary
•        Information relating to the structure and usage of data contained in the database, the metadata, is maintained in a data dictionary.
•         The data dictionary is a database itself, documents the data.
•         Each database user can consult the data dictionary to pick up what each piece of data and the various synonyms of the data fields mean.
•        In a system the data dictionary is part of the DBMS (Integrated system) the data dictionary stores information concerning the source of each data-field value, the frequency of its use, and an audit trail (verification of account) concerning updates, including the who and when of each update.
•        Currently data dictionary systems are available as add-ons to the DBMS.
 
The data dictionary stores:
•      Names of relations
•      Names of the attributes of each relation
•      Domains, and lengths of attributes
•      Names of views defined on the database, and definitions of those views
•      Names of authorized users                       
•      Accounting information about users
•      Number of tuples in each relation
•      Method of storage used for each relation
•     Name of the index
•      Name of the relation being indexed
•      Attributes on which the index is defined
•      Type of index formed
 
(iii) Indices
•        Indices, which can provide fast access to data items.
•        A database index provides pointers to those data items that hold a particular value.
•        Hashing is an alternative to indexing that is faster in some but not all cases.

(iv) Statistical Data
•         It stores statistical information about the data stored in the database, like the number of records, blocks, etc. in a table.
•        This information can be used to execute a query efficiently.