Day 11: DBMS structure, DBA, 3-level architecture, schema, dependency

Hello Dear Students,
   Hope you all are doing good....

Today we are going to cover the topics structure, schema, instances, 3 level architecture,dependencies, DBA  topics..

Let's get started..

DBMS - DBMS stands for DataBase Management System. It is a software basically used to manage the database.

PHYSICAL STRUCTURE OF DBMS - DBMS acts as a mediator between user and the database. The user requests to access the data by queries and the the components which performs the operations to provide the data to the user are -

  1. DDL COMPILER - Data Definition Language Compiler converts the DDL statements into tabular form. These tables includes metadata information. Then these tables are stored in system catalog/data dictionary.
  2. DML COMPILER - Data Manipulation Language Compiler converts the DML statements into object code. The object is then optimized by the query processor and then it is send to the database manager.
  3. QUERY COMPILER/PROCESSOR - It transfers user queries into instructions. Then these are send to database manager for execution.
  4. DATA DICTIONARY MANAGER - The data dictionary manager do maintain the data dictionary and manage the access to the user.
  5. DATABASE MANAGER - The Database Manager is the central software component of DBMS and it is also known as the Database Control System as it controls, maintains and manages the database. It basically converts the user queries into physical file system. 
  6. DATA FILES - It basically contains the data of the database, metadata, user data, application metadata etc.
  7. DATA DICTIONARY - Data dictionary is the repository of the description of the data in the database. It contains all the information such as-
  • table names. attributes, rows, in tables
  • constraints on the data
  • information of physical database design and storage
  • description of all users, authenticate users
  • used to control data integrity basically


SCHEMA - Schema is basically the overall description of the database, however, the relationship between two tables are not defined in the schema. At 3-level architecture, every level has its one schema atleast. The schema describes the table data types and the constraints as well. For example,

CUSTOMER
 Cust_IDCust_Name Cust_Addr Cust_Balance 

The above is a schema in for a table "Customer" where the data types may be--
Cust_ID - varchar
Cust_Name - character
Cust_Addr - varchar
Cust_Balance - number

In schema there can also be constraints defined such as here, Cust_ID not null.

*Entity can be any object about which the data is collected such as Employee, Customer, etc. and the attribute is characteristics of that entity such as Emp_ID, Cust_Name, and so on...


INSTANCE - An instance is basically information being stored at a particular time period. It is the collection of the information which is stored in the database at a particular time period. For example, in 1990 there are 10 records in Customer table database and in 1995 there are 100 records in Customer table database. So, there are 2 instances here. Basically, instance means at a particular time which information is stored in the database. Instances keeps on changing as there are insertion, deletion operations performed on the database.


SUBSCHEMA - It is the lower level or the sub level of the Schema as the very name suggests. It is a user view and application programmer view of data.
For example, suppose here is a Customer table, and the access authority is given to the programmer and the user. The programmer can access all the attributes but user can access say 2 attribute. So the user has a subschema of 2 attributes which is different from the programmer subschema.



3-LEVEL DATABASE ARCHITECTURE

The 3-level architecture basically separates user's view from physical structure of the database. User access data according to its requirements. The ANSI/SPARC 3-level architecture consists of following 3 levels which are -

  • EXTERNAL LEVEL - External level is the highest level of 3-level DBMS architecture. It is also known as individual user view. In this level, it describes the user's view of the database. This level ensures that the user view only those entities which belongs to the user and hides all the database information . The user being unaware from the other entities. The external views are basically described by the external schema. There are many external schema that are written in DDL(Data Definition Language), one for each user.
  • CONCEPTUAL LEVEL - This level is also known as Logical level. It describes the logical structure of the entire database. It is a middle level in the 3-level architecture and defines the conceptual schema While designing the database, the conceptual schema is the first to be defined using the DDL(Data Definition Language). This level describes all the database entities, attributes and relationships with the constraints, semantic information about security information.
  • INTERNAL LEVEL - Internal level is the lowest level of the 3-level architecture where all the information about data is there, storage space allocation(B-Tree, Hashing, etc.), data encryption and compression techniques, internal structure of the database, and so on. It describes the internal schema written in DDL(Data Definition Language). It has all the internal details of the database.


DATA DEPENDENCIES - Independence as you all know means the "freedom". So data independence here means that the upper level of the database architecture remains same even when change in lower level. The data independence is the major objective of the 3-level architecture. There are basically 2 types of data independence-
  • PHYSICAL DATA INDEPENDENCE - Physical data independence states that the logical/conceptual schema does not change by changing the physical/internal schema. The physical schema can be changed due to-
  1. a different storage device is used.
  2. a different storage structure is used.
  3. a new index is added to the table.
However these changes does not change the conceptual schema.

  • LOGICAL DATA INDEPENDENCE - Logical data independence states that the physical/internal schema does not change by changing the logical/conceptual schema. One can change the constraints on the database, modifying, deleting, inserting, operations can also be done but the external view does not change.


DATABASE ADMINISTRATOR (DBA)

DBA is a person/team or persons who is responsible for the Database Management System. DBA is a highly skilled person who has the full responsibility and authority for the database. DBA is the top level authority among all persons connected to the database. DBA can grant permissions, create, modify, maintain the database, and so on..

MAIN RESPONSIBILITIES OF DBA -

  1. DBA identifies the kind of information to be stored in the database. 
  2. DBA decides the representation of the data in the database. Defines the storage structure.
  3. DBA do mapping between physical schema and conceptual schema.
  4. DBA is responsible for the accurate data and thus defines the integrity constraints.
  5. DBA grants the permissions or authorization to the user for specific data access.
  6. DBA defines the strategies for backup and restoration of the data in the database.
  7. DBA ensures that the data is available every-time for the users.
  8. Plans the future storage requirements.
  9. Installing/upgrading the DBMS.
  10. Monitor and Manage the entire DBMS.


Best of Luck Students,
   Do share, subscribe and comment if you like our efforts.