Schema, Sub-schema and Languages of DBMS

Architecture of DBMS
1) It helps in development, implementation, designing and maintenance of a database that stores and organizes information for business, agency and institution etc.

2) The whole concept of DBMS depends upon its Architecture.

1-tier Architecture
The database is directly available to the DBMS user for executing the SQL queries and storing data in it. Any changes or updates that are done here will be reflected directly to the database in the DBMS.

2-tier Architecture
It is based on client-server machine. In this type of architecture, applications on the client-side can interact directly with the database on the server side.
Examples are Oracle, Sybase, Microsoft SQL Server, etc.

3-tier Architecture

It consists of the following layers:
1) Presentation layer - This layer is also known as client layer. It is the front end layer in the 3-tier architecture and it consists of a user-interface. The main purpose of this layer is to communicate with the application layer.

2) Application layer - This layer is also known as business logic layer. It acts as a middle layer between the client and the database server for exchange of partially processed data.

3) Database layer - The data or information is stored in this layer. This layer contains a method to connect with the database and to perform operations such as insert, update and delete.

DBMS Schema
1) It is the structure of database.
2) It does not change by performing operations like insertion, deletion and updation/modification.
3) It describes integrity constraints, domain of all attributes, Primary Key, Foreign Key and all other keys etc.
4) It describes the overall database.

Example of Schema
"Company"
1) Emp(eID, eName, eAddress)
2) DeptId(dID,dName, dLocation)
3) Project(pID,EmpID,dID)
4) Dependent(dID,EmpID,dNo)

Sub-Schema - It is a subset of the schema having the same properties that a schema has. It allows the user to view only that part of the database that is of interest to him.

Instances
They are values of a table at a particular time.
EMP(eID, eName, eMobile, eEmail)

DBMS Languages

I) Data Definition Language (DDL)- It describes the structure of database,i.e. tables (attributes, domain, key) constraints.

Examples:
1) Create
2) Rename
3) Truncate
4) Drop
5) Alter

II) Data Modification Language (DML) - It manipulates data in the table (or) selects data from table.

Examples:
1) Select
2) Insert
3) Update
4) Delete

III) Data Control Language (DCL) - It is used to access stored or saved data.

Examples:
1) Grant
2) Revoke

IV) Transaction Control Language (TCL) - They are used for controlling transactions.

Examples:
1) Commit
2) Rollback

If you enjoyed reading so far...
Thank you so much
See your beautiful face in the next article.