Schema, Sub-schema and Languages of DBMS
Tags: database, computerscience
Published: 2021-06-04 11:03:41

**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**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6n8jfm2jxulshqt13lqs.png)

**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)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvdcnt1cugkf6xdbg0vx.png)

**DBMS Languages**

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ay5mv0ubgc8c24gt73i.png)

**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.