Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Database Management

DBMS Architecture and Database Languages Explained

Updated 11 October 2026 · Fact-checked

DBMS architecture describes how a database system is organised: one-tier, two-tier or three-tier for access, and three schema levels (external, conceptual, internal) for data views. Database languages are DDL (structure), DML (data), DCL (permissions) and TCL (transactions). Learn the definition, an example and a business use for each.

Understand DBMS Architecture and Database Languages

A database management system (DBMS) is software that stores, organises and controls access to data. Architecture answers two questions: where do the parts of the system run, and how is the data viewed at different levels.

First, the tier view. In one-tier architecture, the user, the application and the database sit on one machine. A desktop database used by one person is an example. In two-tier architecture, a client application talks directly to a database server. Many small office systems work this way. In three-tier architecture, a middle application tier sits between the client (presentation tier) and the database tier. The client never touches the database directly. This is better for security, scale and maintenance, and most web and ERP systems use it.

Second, the schema view, often called the three-schema architecture. The external level is what each user or group sees (views). The conceptual level describes the whole database: entities, attributes, relationships and constraints. The internal level describes physical storage: files, indexes and access paths. Each level hides detail from the one above.

This hiding gives data independence. Logical data independence means you can change the conceptual schema (add a column, say) without rewriting external views or application programs. Physical data independence means you can change the internal schema (new index, different storage device) without changing the conceptual schema. Physical independence is easier to achieve than logical.

Finally, the languages. DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) works on the data: INSERT, UPDATE, DELETE and, in many textbooks, SELECT. DCL (Data Control Language) controls access: GRANT, REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT. Some texts treat SELECT as a separate query language (DQL). State this if you use it.

Key rules to remember

Tier architecture: One-tier: user + application + data together | Two-tier: client ↔ database server | Three-tier: client ↔ application server ↔ database server; The key difference is that in three-tier the client never accesses the database directly.
Three schema levels: External (user views) → Conceptual (whole logical design) → Internal (physical storage); Remember the order from user side to storage side.
Data independence: Logical = change conceptual schema without changing external views/programs | Physical = change internal schema without changing conceptual schema; Mappings between levels make this possible.
Database languages: DDL: CREATE, ALTER, DROP, TRUNCATE | DML: INSERT, UPDATE, DELETE (SELECT) | DCL: GRANT, REVOKE | TCL: COMMIT, ROLLBACK, SAVEPOINT; Placement of SELECT varies by textbook; mention that it retrieves data.

How to solve DBMS Architecture and Database Languages questions

Use this method for definition, difference or application questions on architecture and languages.

1Read the question and mark the keyword: tier, schema level, independence, or a language type.
2Define the term in one plain sentence.
3Explain how it works, naming each component and what it does.
4Give a short example from a company setting, such as a payroll or share-register database.
5Add the advantage, limitation or compliance link (security, access control, audit trail).
6If asked to differentiate, write points side by side on the same basis, such as purpose, commands and effect on data.
7Close with a one-line conclusion tying the answer to the scenario.

Quickest way: Layer and verb shortcut

When to use it: Use when you have little time and need a structured answer fast.

For tiers, count the layers between user and data: zero, none, one middle layer.
For schemas, write External-Conceptual-Internal and give one example for each: a view, a table design, an index.
For independence, ask which level changes: conceptual means logical, internal means physical.
For languages, ask what the command acts on: structure is DDL, rows is DML, permissions is DCL, commits is TCL.
Write one command example per language.

Common mistakes in DBMS Architecture and Database Languages

Saying the client talks to the database in three-tier architecture.
Students mix up two-tier and three-tier diagrams.
Fix: Write that the client talks only to the application tier, which talks to the database.
Mixing up logical and physical data independence.
The names sound alike and students learn them without examples.
Fix: Link logical to the conceptual schema (adding a column) and physical to the internal schema (adding an index).
Placing DROP or TRUNCATE under DML.
They seem to act on data.
Fix: Remember that they change or remove structure or the table itself, so list them under DDL.
Treating GRANT and REVOKE as TCL.
Both groups look like control commands.
Fix: TCL deals with transactions (COMMIT, ROLLBACK). GRANT and REVOKE deal with privileges, so they are DCL.
Confusing the external level with the physical storage level.
Students assume external means outside the system.
Fix: External means the user's view. Storage details belong to the internal level.
Giving definitions with no example.
Students prepare from short notes.
Fix: Add a one-line business example and a command to every answer.

Worked examples

Example 1

A company runs an online share-transfer portal. Explain why a three-tier architecture suits it and name the role of each tier.

Show the solution

Identify the tiers: presentation, application and database.
The presentation tier is the web page or app where the shareholder submits a request.
The application tier holds business rules, such as checking the folio and validating the transfer, and sends queries to the database.
The database tier stores shareholder records and transaction data.
The client never accesses the database directly, so access can be restricted to the application server. This improves security.
Each tier can be scaled or changed separately, which eases maintenance.

Answer: Three-tier architecture suits the portal because the application tier sits between users and data. It improves security, scalability and maintainability. The tiers are presentation (user interface), application (business logic) and database (storage).

Example 2

A database administrator performs these actions: (a) adds a column to the EMPLOYEE table, (b) inserts a new employee row, (c) gives the HR user permission to read the table, (d) saves the changes permanently. Classify each action by language and give the command.

Show the solution

(a) Adding a column changes structure, so it is DDL. Command: ALTER TABLE.
(b) Adding a row works on data, so it is DML. Command: INSERT.
(c) Giving permission controls access, so it is DCL. Command: GRANT.
(d) Saving changes permanently ends a transaction, so it is TCL. Command: COMMIT.

Answer: (a) DDL - ALTER; (b) DML - INSERT; (c) DCL - GRANT; (d) TCL - COMMIT.

Exam tips

Case questions usually give a business scenario. Name the architecture or language and justify it from the facts.
For differences, use a point-by-point format with at least four bases.
Always attach one command or one example to each language and each schema level.
Draw a small labelled diagram of the three tiers or three schema levels if time permits. Keep it neat and short.
Link the answer to practical points, such as access control and data security, where the question allows.

Practice questions from Database Management

DBMS Architecture and Database Languages: frequently asked questions

What is the difference between two-tier and three-tier architecture?

In two-tier, the client application connects directly to the database server. In three-tier, an application server sits in the middle and handles business logic. Three-tier gives better security and scalability.

What is the difference between DDL, DML, DCL and TCL?

DDL defines or changes structure, such as CREATE and ALTER. DML works on data, such as INSERT, UPDATE and DELETE. DCL controls permissions with GRANT and REVOKE. TCL manages transactions with COMMIT and ROLLBACK.

What is data independence with an example?

Data independence means changes at one schema level do not force changes at the level above. Adding an index is a physical change and does not affect the table design. Adding a column to a table is a logical change and should not break existing user views.

Is SELECT part of DML?

Many textbooks place SELECT under DML because it reads data. Others put it in a separate query language (DQL). In your answer, state which approach you follow.