Fundamentals of DBMS and Storage

Overview

What data, information, databases and DBMSs are.
How data models and the three‑schema architecture work.
How Oracle is structured at a high level.
How integrity constraints, triggers and tablespaces keep data correct and well‑organized.

1. Data, Databases and DBMS

Data vs information
- Data are raw facts (Ahmed, 23, CS).
- Information is data with meaning (“Ahmed is a 23‑year‑old CS student”).

Database
- An organized collection of related data (tables, rows, columns).
- Goals include easy access, consistent updates, shared use by many applications and users.

DBMS (Database Management System)
Software layer between applications and data files.
Main functions:
- Data definition and storage management,
- Query processing (SQL),
- Security and integrity enforcement,
- Concurrency control,
- Backup and recovery.
Examples: Oracle, PostgreSQL, MySQL, SQL Server.

Why not just files?
- Redundant and inconsistent copies of data,
- Hard to query (each report needs a custom program),
- No data independence (file changes break apps),
- Weak integrity and security,
- Poor support for concurrent access and recovery.
- Further reading: Difference between File System and DBMS

2. Data Models and Three‑Schema Architecture

Data model
A framework describing:
- Structure of data (tables/attributes/relationships),
- Allowed operations (SELECT, INSERT, UPDATE, DELETE),
- Constraints and behaviour (business rules).
Enables data abstraction: users see a logical view; storage details are hidden.

Main kinds of models
Relational model uses tables and SQL (used by Oracle).
Other models include hierarchical, network and object‑relational approaches (historical or specialized).

2.1 Three‑schema architecture

External level (user views)
Each user or application sees its own view of the data.
Example:
- Payroll officer sees only names and salaries.
- Project manager sees employees and projects, but not salaries.

Conceptual level (logical schema)
Global logical structure of the database, including:
- Tables (EMPLOYEES, DEPARTMENTS, …),
- Columns and data types,
- Relationships and constraints.
The conceptual schema is independent of physical storage.

Internal level (physical schema)
How data is actually stored, including:
- Files, data blocks, extents, segments,
- Indexes, clustering, compression.
This level is tuned by DBAs for performance and space.

Data independence
- Physical independence means changing storage structures without changing the logical schema.
- Logical independence means evolving the logical schema while preserving user views as much as possible.
- Further reading: DBMS Three Schema Architecture

3. Oracle Architecture and Connections

Client–server model
The server runs the Oracle instance and database, listening on an IP and port.
Clients are tools (SQL*Plus, SQL Developer, OEM) and applications.

Connecting with SQL*Plus (examples)

-- As an administrator
sqlplus sys/your_password@host:1521/orclcdb AS SYSDBA

-- As a regular user
sqlplus dev1/dev1@host:1521/orclcdb

The connection format is user/password@host:port/SID.
AS SYSDBA is reserved for highly privileged accounts (DBA work).

4. Integrity Constraints

Integrity constraints keep the data consistent automatically.

Primary key
Uniquely identifies each row; not null.

CREATE TABLE employees (
  emp_id  NUMBER PRIMARY KEY,
  name    VARCHAR2(50),
  salary  NUMBER
);

Foreign key
Enforces referential integrity between tables.

CREATE TABLE employees (
  emp_id   NUMBER PRIMARY KEY,
  name     VARCHAR2(50),
  dept_id  NUMBER REFERENCES departments(dept_id)
);

UNIQUE
No two rows can have the same value in the column.

email VARCHAR2(100) UNIQUE

NOT NULL
Forbids missing values.

hire_date DATE NOT NULL

Domain / CHECK
Restricts values to a range or set.

CHECK (salary BETWEEN 20000 AND 200000)

These constraints are checked automatically whenever data is inserted or updated.

5. Triggers and Advanced Integrity

Some rules need procedural logic. They are implemented using triggers, PL/SQL blocks that fire on INSERT, UPDATE or DELETE.
- Video: SQL triggers tutorial

5.1 General syntax (simplified)

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT OR UPDATE ON employees
  FOR EACH ROW
BEGIN
  -- PL/SQL logic here
END;

5.2 Examples

Minimum salary

CREATE OR REPLACE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 2000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be greater than 2000.');
  END IF;
END;

Manager vs employee salary
Enforce “an employee’s salary cannot exceed their manager’s salary”.
The trigger:
Reads the manager’s salary, compares it to :NEW.salary, and raises an error if the rule is violated.

6. Tablespaces and Physical Storage in Oracle

Oracle separates logical and physical storage.

6.1 Storage hierarchy

Physical (OS level)
OS blocks (e.g. 512 bytes, 4 KB).
Datafiles are binary files on disk.

Logical (Oracle level)
Data blocks are the smallest I/O unit (2, 4, 8, 16, 32 KB).
Extents are groups of contiguous data blocks.
Segments are collections of extents for one object (table, index).
Tablespaces are logical containers grouping segments and datafiles.

6.2 Tablespaces

Tablespaces
Logical storage units that hold segments for tables, indexes, etc., and can span multiple datafiles.

Common tablespaces
SYSTEM: core data dictionary and system objects.
SYSAUX: auxiliary system data.
TEMP: temporary space for sorts and joins.
UNDO: undo segments (rollback, read consistency, flashback).
USERS: typical default tablespace for user objects.

6.3 Basic management commands

-- Create a tablespace
CREATE TABLESPACE example_ts
  DATAFILE '/u01/app/oracle/oradata/mydb/example01.dbf'
  SIZE 100M;

-- Add another datafile
ALTER TABLESPACE example_ts
  ADD DATAFILE '/u01/app/oracle/oradata/mydb/example02.dbf'
  SIZE 200M;

-- Resize a datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/example01.dbf'
  RESIZE 500M;

-- Enable autoextend and bring a tablespace online
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/example01.dbf'
  AUTOEXTEND ON;
ALTER TABLESPACE example_ts ONLINE;

Further reading: Tablespaces and Datafiles

7. Key Takeaways

A DBMS solves the main limitations of file‑based data management (integrity, security, concurrency, recovery).
The three‑schema architecture (external, conceptual, internal) provides powerful data independence.
Oracle follows a client–server model and uses SQL and PL/SQL for data access and logic.
Integrity constraints and triggers enforce business rules at the database level.
Tablespaces, segments, extents and datafiles organize how Oracle stores data on disk.