DBMS Architecture – 1-Tier, 2-Tier, and 3-Tier Explained with Oracle Database Examples

DBMS Architecture — 1-tier, 2-tier & 3-tier (Oracle Examples)

This post explains the three common DBMS architectures — 1-tier, 2-tier, and 3-tier — with concrete Oracle Database examples and commands so you can deploy or demonstrate each model.

1. 1-Tier Architecture (Standalone)

All components (UI, application logic and database) run on the same machine. Typical for learning, demos or embedded scenarios.

Oracle setup (local)
Install Oracle Database and use SQL*Plus or SQLcl locally. Everything runs on a single host.

-- Create table locally in SQL*Plus
CREATE TABLE student (
  student_id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  course VARCHAR2(30)
);

INSERT INTO student VALUES (101, 'Ahsan', 'DBMS');
COMMIT;

SELECT * FROM student;

Text diagram:

[SQL*Plus / App + Oracle DB files] — same machine

When to use

• Learning and demos
• Standalone utilities (local desktop tools)
• Prototyping with a local Oracle XE

2. 2-Tier Architecture (Client–Server)

In 2-tier architecture, the client (UI or thick client) directly connects to an Oracle Database Server. Communication uses Oracle Net (TCP port 1521 by default).

Oracle connection example (client side)

-- Connect using SQL*Plus from client to remote Oracle DB
sqlplus hr/hr@//192.168.1.50:1521/orcl

-- Remote query example
SELECT emp_id, name, salary FROM employee WHERE salary > 50000;

Text diagram:

[Client App (Forms/SQL Developer)]  <--TCP/Oracle Net-->  [Oracle DB Server]

Oracle Server details

On the server side you might have:

-- Example: Employee table on Oracle Server
CREATE TABLE employee (
  emp_id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  salary NUMBER(10,2)
);

Advantages & drawbacks

• Centralized data management — easier backup and security.
• Clients talk directly to DB — simpler but coupled.
• Schema changes can impact many clients.

3. 3-Tier Architecture (Presentation – Application – Data)

Three separate layers: presentation, application (business logic), and database. Clients call application servers (APIs) which interact with Oracle DB.

Typical stack (example)

• Presentation: React web app or HTML pages
• Application: Java Spring Boot (JDBC/Oracle driver) or Python Flask with cx_Oracle
• Database: Oracle Database on dedicated server

JDBC code (Java) — application > Oracle:

// JDBC example (thin driver)
String url = "jdbc:oracle:thin:@//192.168.1.50:1521/orcl";
Connection conn = DriverManager.getConnection(url, "hr", "hr");
PreparedStatement ps = conn.prepareStatement("SELECT name, course FROM student WHERE student_id=?");
ps.setInt(1, 101);
ResultSet rs = ps.executeQuery();

Text diagram:

[Browser/App UI] --HTTP--> [App Server (Java/Python)] --JDBC/SQL--> [Oracle DB]

Oracle-specific best practices for 3-tier

• Use connection pooling (e.g., HikariCP, Oracle UCP) in the application server.
• Keep database schema changes backward-compatible where possible.
• Use Oracle Wallet or Vault for secure credential management (don’t hardcode passwords).

Deployment & examples summary

Architecture	Oracle Example	Typical use
1-Tier	Oracle XE + SQL*Plus on same laptop	Learning, local tools
2-Tier	Oracle Server (orcl) + clients using SQL Developer / Forms	Small office, departmental apps
3-Tier	React frontend → Java API (JDBC) → Oracle DB	Web apps, enterprise systems

Extra: Simple SVG diagram (pasteable)

Copy-paste this inline SVG into your blog to show a 3-tier diagram:

<svg width="600" height="140" xmlns="http://www.w3.org/2000/svg">
  <rect x="20" y="20" width="160" height="80" rx="8" fill="#f0f8ff" stroke="#2b8cff"/>
  <text x="40" y="55" font-family="Arial" font-size="12" fill="#000">Presentation</text>
  <rect x="220" y="20" width="160" height="80" rx="8" fill="#fff8f0" stroke="#ffaa33"/>
  <text x="250" y="55" font-family="Arial" font-size="12" fill="#000">Application Server</text>
  <rect x="420" y="20" width="160" height="80" rx="8" fill="#f7fff0" stroke="#33aa44"/>
  <text x="455" y="55" font-family="Arial" font-size="12" fill="#000">Oracle DB</text>
  <line x1="180" y1="60" x2="220" y2="60" stroke="#666" stroke-width="2"/>
  <line x1="380" y1="60" x2="420" y2="60" stroke="#666" stroke-width="2"/>
</svg>

Quick tips:

1. For production 3-tier apps use connection pooling and secure credential storage.
2. Use Oracle Data Guard or RMAN for backups in multi-tier production.
3. Monitor performance with Oracle Enterprise Manager (OEM) when using large deployments.

DBMS architecture, Oracle examples, 1-tier, 2-tier, 3-tier, JDBC, SQL*Plus, SQL Developer