Definition:
The Three-Schema Architecture is a database design framework that separates database systems into three distinct levels of abstraction to achieve data independence and system flexibility.
| Schema Level | Purpose | Audience |
|---|---|---|
| Internal Schema | Physical storage structure | Database administrators |
| Conceptual Schema | Logical database structure | Database designers |
| External Schema | User-specific views | End users & applications |
Example:
-
- University Database System
Internal Schema:
-- Storage details, indexing, file organization
CREATE TABLE STUDENT (
student_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
) ENGINE=InnoDB
PARTITION BY HASH(student_id) PARTITIONS 4;
-- B-tree index on student_id
CREATE INDEX idx_student_name ON STUDENT(name) USING BTREE;Conceptual Schema:
ENTITIES:
- Student (student_id, name, email, enrollment_date)
- Course (course_id, title, credits, department)
- Enrollment (student_id, course_id, grade, semester)
RELATIONSHIPS:
- Student ←→ Enrollment ←→ Course (Many-to-Many)External Schema:
-- Student Portal View
CREATE VIEW StudentPortal AS
SELECT s.name, c.title, e.grade
FROM Student s
JOIN Enrollment e ON s.student_id = e.student_id
JOIN Course c ON e.course_id = c.course_id;
-- Faculty View
CREATE VIEW FacultyView AS
SELECT c.title, COUNT(e.student_id) as enrolled_count
FROM Course c
LEFT JOIN Enrollment e ON c.course_id = e.course_id
GROUP BY c.course_id, c.title;