Three Level Architecture - Learning Module

Loading content...

0/241

Conceptual Level (Logical)

The Heart of the Database

In the previous page, we explored the external level—multiple views tailored for different users. But what ties these disparate views together? Where is the single source of truth that defines what data actually exists, what it means, and how different pieces relate to each other?

The answer is the Conceptual Level—the central, unified description of the entire database's logical structure. If the external level is the many windows into the database, the conceptual level is the building itself.

The conceptual level defines what data is stored (not how or for whom), establishing the organization's complete, coherent data model that all external views are derived from and all physical storage structures must implement.

What You Will Learn

By the end of this page, you will understand the conceptual level's role as the logical backbone of the database, the components of a conceptual schema (entities, attributes, relationships, constraints), how it differs from external and internal levels, and why it's considered the most important level in the three-level architecture.

Understanding the Conceptual Level

The conceptual level (also called the logical level or community level) represents the overall logical structure of the entire database, as seen by the Database Administrator (DBA) and enterprise architects. It describes the complete data model independent of any particular user's view or any physical storage mechanism.

Definition and Purpose

The conceptual level contains the conceptual schema—a comprehensive specification of all the data types, relationships, integrity constraints, and semantic rules that govern the database. It answers the fundamental question: What data does this organization maintain, and what are the rules that govern it?

Formal Definition:

The conceptual schema is a complete, unified description of the database's logical structure, defining all entities, attributes, relationships, and constraints in a way that is independent of physical storage and user-specific views.

Key Characteristics of the Conceptual Level
Characteristic	Description	Implication
Complete Coverage	Describes ALL data in the database	Nothing exists in the database that isn't in the conceptual schema
Logical Independence	Defines structure without storage details	Can change physical storage without modifying conceptual schema
Semantic Richness	Captures meaning, not just structure	Business rules and constraints encoded directly
Integration Point	All external views derive from it	Source of truth for the entire enterprise
Stability	Changes less frequently than views or storage	Provides long-term data architecture stability

The Blueprint Analogy

If you're constructing a building, the conceptual level is the architectural blueprint. It shows all the rooms, their connections, what each room is for, and the rules about what can go where. The blueprint doesn't specify whether walls are made of brick or concrete (internal level), nor how each tenant will decorate their office (external level). It defines the structure that everyone must work within.

Components of the Conceptual Schema

A conceptual schema is composed of several key elements that together define the database's complete logical structure. Understanding each component is essential for database design and analysis.

Core Components of a Conceptual Schema

•Entity Definitions — Real-world objects or concepts that are tracked (Customer, Product, Order, Employee). Each entity has a unique identifier and a set of properties.
•Attribute Specifications — Properties of entities (CustomerName, ProductPrice, OrderDate). Includes data types, allowed values, and nullability rules.
•Relationship Declarations — Connections between entities (Customer places Order, Order contains Product). Includes cardinality (1:1, 1:N, M:N) and participation constraints.
•Integrity Constraints — Rules that ensure data validity (primary keys, foreign keys, unique constraints, check constraints, business rules).
•Domain Definitions — Specifications of valid value sets (Status can be 'Active', 'Inactive', 'Pending'; Age must be 0-150).

Entity and Attribute Definitions

Entities represent the core concepts that an organization needs to track. Each entity is described by:

Entity Name — A meaningful identifier (Employee, Department, Project)
Attributes — Properties that describe the entity
Primary Key — Attribute(s) that uniquely identify each instance
Constraints — Rules governing attribute values

conceptual_schema_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Conceptual Schema for a University Database
-- Note: This is the logical structure, not concerned with indexes or storage
 
-- Entity: Student
CREATE TABLE Student (
    student_id      CHAR(10)      PRIMARY KEY,       -- Identifier
    first_name      VARCHAR(50)   NOT NULL,          -- Required attribute
    last_name       VARCHAR(50)   NOT NULL,
    email           VARCHAR(100)  UNIQUE NOT NULL,   -- Domain constraint
    date_of_birth   DATE          NOT NULL,
    enrollment_date DATE          NOT NULL,
    gpa             DECIMAL(3,2)  CHECK (gpa >= 0.0 AND gpa <= 4.0), -- Value constraint
    major_id        CHAR(6)       NOT NULL,          -- Foreign key (relationship)
    advisor_id      CHAR(8),                         -- Optional relationship
    
    CONSTRAINT valid_enrollment CHECK (enrollment_date >= date_of_birth + INTERVAL '16 years'),
    CONSTRAINT fk_major FOREIGN KEY (major_id) REFERENCES Major(major_id),
    CONSTRAINT fk_advisor FOREIGN KEY (advisor_id) REFERENCES Faculty(faculty_id)
);
 
-- Entity: Course
CREATE TABLE Course (
    course_id       CHAR(8)       PRIMARY KEY,
    course_name     VARCHAR(100)  NOT NULL,
    credits         INTEGER       CHECK (credits BETWEEN 1 AND 6),
    department_id   CHAR(4)       NOT NULL REFERENCES Department(department_id),
    description     TEXT,
    prerequisites   CHAR(8)[],    -- Array of course_ids
    
    CONSTRAINT unique_course_name UNIQUE (department_id, course_name)
);
 
-- Relationship Entity: Enrollment (M:N between Student and Course)
CREATE TABLE Enrollment (
    student_id      CHAR(10)      REFERENCES Student(student_id),
    course_id       CHAR(8)       REFERENCES Course(course_id),
    semester        CHAR(6)       NOT NULL,  -- e.g., 'F2024' for Fall 2024
    grade           CHAR(2)       CHECK (grade IN ('A','B','C','D','F','W','I','IP')),
    enrollment_date DATE          DEFAULT CURRENT_DATE,
    
    PRIMARY KEY (student_id, course_id, semester),
    CONSTRAINT valid_semester CHECK (semester ~ '^[FS][0-9]{4}$')
);
 
-- Relationship Entity: Faculty-Department (with additional relationship attributes)
CREATE TABLE FacultyDepartmentAssignment (
    faculty_id      CHAR(8)       REFERENCES Faculty(faculty_id),
    department_id   CHAR(4)       REFERENCES Department(department_id),
    role            VARCHAR(30)   CHECK (role IN ('Chair', 'Professor', 'Associate', 'Assistant', 'Lecturer', 'Adjunct')),
    start_date      DATE          NOT NULL,
    end_date        DATE,
    is_primary      BOOLEAN       DEFAULT FALSE,
    
    PRIMARY KEY (faculty_id, department_id),
    CONSTRAINT valid_dates CHECK (end_date IS NULL OR end_date > start_date)
);

Logical, Not Physical

Notice that this schema says nothing about how data is stored on disk, what indexes exist, how tables are partitioned, or where files are located. These are internal level concerns. The conceptual schema focuses purely on what data exists and what rules govern it.

Relationships and Cardinality

Relationships define how entities connect to each other. The conceptual level must precisely specify these connections, including their cardinality (how many entities can participate) and participation (whether participation is required or optional).

Types of Relationships

Relationship Cardinalities
Cardinality	Symbol	Example	Implementation
One-to-One (1:1)	1:1	Employee has Desk	Foreign key in either table (or merged table)
One-to-Many (1:N)	1:N	Department has Employees	Foreign key in the 'many' side table
Many-to-Many (M:N)	M:N	Student enrolls in Courses	Junction table with composite key

Participation Constraints

Beyond cardinality, relationships have participation constraints:

Total Participation (Mandatory) — Every instance must participate in the relationship. An Employee MUST belong to a Department.
Partial Participation (Optional) — Instances may or may not participate. An Employee MAY manage a Department (but most don't).

These constraints are crucial for data integrity and are enforced through NOT NULL constraints and foreign key relationships.

relationship_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- 1:1 Relationship: Employee has Office (optional on both sides)
CREATE TABLE Employee (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL
);
 
CREATE TABLE Office (
    office_id     SERIAL PRIMARY KEY,
    building      VARCHAR(20) NOT NULL,
    room_number   VARCHAR(10) NOT NULL,
    employee_id   INTEGER UNIQUE REFERENCES Employee(employee_id)
    -- UNIQUE ensures 1:1; NULL allowed means optional
);
 
-- 1:N Relationship: Department has Employees (mandatory on Employee side)
CREATE TABLE Department (
    dept_id       SERIAL PRIMARY KEY,
    dept_name     VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE Employee_v2 (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    dept_id       INTEGER NOT NULL REFERENCES Department(dept_id)
    -- NOT NULL enforces total participation: every employee MUST have dept
);
 
-- M:N Relationship: Student enrolls in Course
CREATE TABLE Student (
    student_id    SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL
);
 
CREATE TABLE Course (
    course_id     SERIAL PRIMARY KEY,
    course_name   VARCHAR(100) NOT NULL
);
 
-- Junction table captures the M:N relationship
CREATE TABLE Enrollment (
    student_id    INTEGER REFERENCES Student(student_id) ON DELETE CASCADE,
    course_id     INTEGER REFERENCES Course(course_id) ON DELETE CASCADE,
    enrolled_at   TIMESTAMP DEFAULT NOW(),
    grade         CHAR(2),
    PRIMARY KEY (student_id, course_id)
);
 
-- Recursive Relationship: Employee supervises Employee
-- 1:N relationship where an entity relates to itself
CREATE TABLE Employee_v3 (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    supervisor_id INTEGER REFERENCES Employee_v3(employee_id)
    -- NULL means no supervisor (e.g., CEO)
);
 
-- Ternary Relationship: Supplier supplies Part to Project
-- Some relationships involve more than two entities
CREATE TABLE Supply (
    supplier_id   INTEGER REFERENCES Supplier(supplier_id),
    part_id       INTEGER REFERENCES Part(part_id),
    project_id    INTEGER REFERENCES Project(project_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    supply_date   DATE NOT NULL,
    PRIMARY KEY (supplier_id, part_id, project_id)
);

Relationship Attributes

Relationships themselves can have attributes. In the Enrollment relationship, 'grade' and 'enrolled_at' are properties of the relationship, not of Student or Course individually. These attributes only make sense in the context of a specific student taking a specific course.

Integrity Constraints at the Conceptual Level

Integrity constraints are rules that ensure the accuracy and consistency of data. The conceptual level defines these constraints, and the DBMS enforces them automatically. This is one of the most powerful features of a well-designed conceptual schema.

Key constraints ensure entity uniqueness and proper identification:

Primary Key Constraint

Uniquely identifies each row in a table
Cannot contain NULL values
Only one primary key per table
Can be single attribute or composite (multiple attributes)

Unique Constraint

Ensures values in a column (or combination) are unique
Allows NULL values (usually one NULL allowed)
Multiple unique constraints allowed per table

Foreign Key Constraint

Establishes relationships between tables
References primary or unique key of another table
Enforces referential integrity

key_constraints.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Primary Key: Single attribute
CREATE TABLE Customer (
    customer_id   INTEGER PRIMARY KEY,
    email         VARCHAR(100)
);
 
-- Primary Key: Composite (multiple attributes)
CREATE TABLE OrderItem (
    order_id      INTEGER,
    line_number   INTEGER,
    product_id    INTEGER,
    quantity      INTEGER,
    PRIMARY KEY (order_id, line_number)  -- Combination uniquely identifies
);
 
-- Unique Constraint: Alternative keys
CREATE TABLE Product (
    product_id    SERIAL PRIMARY KEY,
    sku           VARCHAR(20) UNIQUE NOT NULL,    -- Business identifier
    upc           CHAR(12) UNIQUE,                -- Bar code (nullable)
    product_name  VARCHAR(100)
);
 
-- Foreign Key with Referential Actions
CREATE TABLE OrderItem (
    item_id       SERIAL PRIMARY KEY,
    order_id      INTEGER NOT NULL,
    product_id    INTEGER NOT NULL,
    quantity      INTEGER,
    
    FOREIGN KEY (order_id) 
        REFERENCES Order(order_id) 
        ON DELETE CASCADE         -- Delete items when order deleted
        ON UPDATE CASCADE,        -- Update if order_id changes
        
    FOREIGN KEY (product_id)
        REFERENCES Product(product_id)
        ON DELETE RESTRICT        -- Cannot delete product if in orders
        ON UPDATE CASCADE
);

Constraints Are Not Optional

Skipping constraints to 'keep things simple' is a common mistake that leads to data corruption. Constraints should be defined at the conceptual level and implemented strictly. The cost of enforcing constraints at insert/update time is far less than the cost of cleaning up corrupted data later.

Conceptual Level in Context

To fully understand the conceptual level, we must see how it relates to the external and internal levels. Each level serves a distinct purpose and addresses different concerns.

Three-Level Architecture Comparison
Aspect	External Level	Conceptual Level	Internal Level
Purpose	User-specific views	Complete logical structure	Physical storage details
Describes	What each user can see	What data exists and its rules	How data is stored
Audience	End users, applications	DBAs, enterprise architects	System programmers, DBAs
Number	Many (one per user group)	One (unified schema)	One (storage schema)
Abstracts	Irrelevant data from users	Storage from logical, users from complexity	Nothing (lowest level)
Contains	Views, derived columns	Tables, relationships, constraints	File structures, indexes, paths
Changes	Frequently (new views)	Occasionally (new entities)	For performance tuning
Example	Sales_By_Region view	Order table with constraints	B-tree index on order_date

Converting Mermaid diagram...

The Conceptual Level Is Central

The conceptual level is the linchpin of the three-level architecture. All external views are derived from it—they cannot include data not defined conceptually. The internal level implements it—storage must accommodate all conceptual structures. Changes to the conceptual level potentially affect both other levels.

Data Models at the Conceptual Level

The conceptual schema is expressed using a data model—a formal framework for describing data structure and semantics. The choice of data model profoundly affects how the conceptual level is defined.

Common Data Models

Data Models for Conceptual Schemas
Model	Core Concept	Structure	Best For
Entity-Relationship (ER)	Entities with relationships	Rectangles, diamonds, ovals in diagrams	Initial conceptual design, documentation
Relational	Relations (tables)	Tables with rows and columns	Implementation, formal theory
Object-Oriented	Objects with methods	Classes, inheritance, encapsulation	Complex structures, OOP integration
Document	Self-describing documents	JSON/XML hierarchies	Flexible schemas, semi-structured data
Graph	Nodes and edges	Property graphs	Connected data, relationship-heavy domains

The Relational Model Dominance

In practice, the relational model dominates conceptual schema definition for structured data. Its mathematical foundation (relational algebra, relational calculus) provides:

Formal semantics — Precise meaning for all operations
Query optimization theory — Provable query equivalences
Normalization theory — Principled design guidelines
Constraint mechanisms — Declarative integrity enforcement

However, the ER model is often used first for high-level conceptual design (capturing entities and relationships visually), then translated to the relational model for implementation.

ER Model (High-Level Design)

Used during requirements gathering and initial design. Focuses on:

Identifying entities
Discovering relationships
Defining attributes
Visual communication with stakeholders

Relational Model (Implementation)

Used for actual schema definition. Provides:

Precise table structures
Formal constraint language
Implementation in SQL
Optimization framework

Model Translation

There are well-defined rules for converting ER diagrams to relational schemas. Entities become tables. Relationships become foreign keys or junction tables (for M:N). This translation is covered in depth in the ER-to-Relational Mapping chapter.

Designing the Conceptual Schema

Creating a well-designed conceptual schema is both art and science. Here are principles that guide professional database designers:

Conceptual Schema Design Principles

•Faithfulness — The schema must accurately represent the real-world domain. If the business distinguishes between 'prospects' and 'customers', the schema should too.
•Completeness — All data needs of all anticipated applications must be accommodatable. Missing entities discovered later require schema evolution.
•Minimality — Avoid redundancy. Each fact should be stored once. Redundancy leads to update anomalies and inconsistency.
•Clarity — Names should be meaningful and consistent. 'cust_id' in one table and 'customer_number' in another creates confusion.
•Normalization — Apply normal forms (at least 3NF) to eliminate update anomalies. Denormalize consciously, not accidentally.
•Constraint Richness — Define all known business rules as constraints. The more rules encoded in the schema, the fewer bugs possible in applications.
•Extensibility — Design for anticipated change. Use flexible patterns where requirements are uncertain.

design_evolution.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Evolution of a Conceptual Schema: Address Handling
 
-- Version 1: Simple (Too Rigid)
CREATE TABLE Customer_v1 (
    customer_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100),
    street        VARCHAR(100),
    city          VARCHAR(50),
    state         CHAR(2),
    zip           VARCHAR(10)
);
-- Problem: Only one address per customer
 
-- Version 2: Multi-Address (Better)
CREATE TABLE Customer_v2 (
    customer_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100)
);
 
CREATE TABLE CustomerAddress_v2 (
    address_id    SERIAL PRIMARY KEY,
    customer_id   INTEGER REFERENCES Customer_v2(customer_id),
    address_type  VARCHAR(20) CHECK (address_type IN ('billing', 'shipping', 'home', 'work')),
    street        VARCHAR(100),
    city          VARCHAR(50),
    state         CHAR(2),
    zip           VARCHAR(10),
    is_primary    BOOLEAN DEFAULT FALSE,
    
    -- Business rule: Only one primary per type
    UNIQUE (customer_id, address_type, is_primary) 
);
-- Problem: Address components still too rigid for international
 
-- Version 3: Flexible International (Best)
CREATE TABLE Address (
    address_id    SERIAL PRIMARY KEY,
    country_code  CHAR(2) NOT NULL REFERENCES Country(country_code),
    address_lines JSONB NOT NULL,  -- Flexible for different formats
    postal_code   VARCHAR(20),
    region        VARCHAR(100),    -- State/Province/Prefecture
    city          VARCHAR(100),
    formatted     TEXT             -- Full formatted address for display
);
 
CREATE TABLE CustomerAddress_v3 (
    customer_id   INTEGER REFERENCES Customer_v3(customer_id) ON DELETE CASCADE,
    address_id    INTEGER REFERENCES Address(address_id),
    address_type  VARCHAR(20),
    is_primary    BOOLEAN DEFAULT FALSE,
    valid_from    DATE DEFAULT CURRENT_DATE,
    valid_until   DATE,
    
    PRIMARY KEY (customer_id, address_id),
    
    -- Only one primary address per type
    UNIQUE (customer_id, address_type) WHERE is_primary = TRUE
);
-- Benefits: Reusable addresses, international support, validity tracking

Schema Design Is Iterative

No schema is perfect on first attempt. Start with a minimal viable schema, validate it against requirements and sample data, and refine iteratively. The goal is 'good enough for now with room to evolve,' not 'perfect for all time.'

Summary: The Conceptual Level

We've explored the conceptual level comprehensively. Here are the essential takeaways:

Key Takeaways

•The conceptual level is the unified logical model — It describes ALL data in the database, independent of user views or physical storage.
•The conceptual schema defines structure and semantics — Entities, attributes, relationships, domains, and integrity constraints are all specified here.
•Relationships have cardinality and participation — 1:1, 1:N, M:N relationships with mandatory/optional participation must be precisely defined.
•Integrity constraints encode business rules — Key, domain, and semantic constraints prevent invalid data at the database level.
•Data models provide the formal framework — Relational and ER models are the most common for expressing conceptual schemas.
•Good design follows principles — Faithfulness, completeness, minimality, clarity, normalization, and extensibility guide schema creation.

What's Next:

We've now covered the external level (user views) and the conceptual level (logical structure). The final piece is the internal level—how data is physically stored and accessed on disk. This level deals with file organization, indexing, and storage optimization.

Page Complete

You now understand the conceptual level—the heart of the three-level architecture. You can explain its components (entities, relationships, constraints), how it relates to external and internal levels, and principles for designing effective conceptual schemas. Next, we explore the internal level—where logical structures meet physical storage.