What is Database Architecture?
Database Architecture
is the way a database is organized so that users can easily access data
while the database remains secure, efficient, and independent of physical
storage.
The ANSI/SPARC model divides the
database into three levels:
1.
External Level (View Level)
2.
Conceptual Level (Logical Level)
3.
Internal Level (Physical Level)
Simple Diagram
Users
/ Applications
|
-------------------
| | |
User A User B
User C
| | |
-------------------------
External Level (View)
-------------------------
|
Conceptual Level (Logical Database)
|
Internal Level
(Physical Storage)
|
Hard
Disk / SSD
1. External Level (View Level)
Definition
The External Level is the level where different
users see only the data they need.
Each user has a different view of the
database.
Simple Meaning "What
the user sees."
Example: Consider
a College Database.
Student wants to see
|
Student ID |
Name |
Marks |
|
101 |
Rahul |
85 |
Student cannot see salary of teachers.
Teacher wants to see
|
Student Name |
Marks |
Attendance |
Teacher cannot see accounts information.
Accountant wants to see
|
Student Name |
Fees |
Payment Status |
Accountant does not need student marks.
Advantages
- Security
- Easy
to use
- Different
views for different users
- Data
privacy
-------------------------------------------------------------------------------------------------------------
2. Conceptual Level (Logical Level)
Definition
The Conceptual Level describes the complete
structure of the database.
It contains
- Tables
- Relationships
- Constraints
- Data
Types
This level hides physical storage
details.
Simple Meaning "How
the entire database is logically organized."
Example
College Database
There are multiple Tables:
Student
StudentID
StudentName
Course
Faculty
FacultyID
FacultyName
Department
Course
CourseID
CourseName
Fees
-------------------------------------------------------------------------------------------------------------
Relationship
One Student
|
Many Courses
The conceptual level defines
- Tables
- Primary
Keys
- Foreign
Keys
- Relationships
Advantages
- Easy
database design
- Reduces
redundancy
- Maintains
relationships
- Data
integrity
3. Internal Level (Physical Level)
Definition
The Internal Level describes how data is
actually stored inside the computer.
Users never see this level.
Simple Meaning "How
data is physically stored in memory or disk."
Example
Suppose student data is stored as Student
Table
Actually, Oracle stores it
like
Block 15 Ã Row 1, Row 2, Row
3 etc.
Stored inside
Hard Disk
↓
Data Files
↓
Blocks
↓
Rows
Database Administrator (DBA) manages
this level.
Advantages
- Faster
data retrieval
- Efficient
storage
- Better
performance
- Backup
and recovery
Practical Example: Imagine a College Management System.
External Level
Student Login: Name,
Course, Marks, Attendance
Teacher Login: Student
Name, Marks, Attendance, Internal Marks
Account Login: Fees,
Payment Status, Fine
Each user sees only the required
information.
Conceptual Level
Actual Database
Student, Faculty, Course, Result, Fees, Attendance
Relationships
Student ---- Course
Student ---- Fees
Student ---- Attendance
Internal Level
Stored as
Database
↓
Data Files
↓
Blocks
↓
Records
Users never know where the
files are stored.
Mapping Between Architecture Levels
A mapping is the connection
between two levels that allows data to flow
correctly while keeping each level independent.
There are two mappings:
1. External–Conceptual Mapping
Definition
Connects the External Level with the Conceptual
Level.
It translates the user's view into the
logical database structure.
Example
Student sees only
StudentID, Name, Marks
But Conceptual Database contains many fields
StudentID, Name, DOB, Address, Phone, Marks, Fees, Attendance
Only the required fields are shown to
the student.
2. Conceptual–Internal Mapping
Definition
Connects the Conceptual Level with
the Internal Level.
It converts the logical
database structure into the physical storage format.
Example
Conceptual Table
Student
Internally stored as
Student.dbf
↓
Block 100
↓
Rows
The
user does not need to know where or how the file is stored.
Real-Life Example (Library)
Think of a Library:
External Level
Readers search only for:
- Book
Name, Author, Availability
Conceptual Level
The librarian manages:
- Book
ID, Book Name, Author, Publisher, Price, Rack Number, Issue Records
Internal Level
Books are physically arranged on:
- Shelves,
Racks, Cupboards, Storage rooms
Readers do not know the exact storage
process.
Difference Between the Three Levels
|
Feature |
External Level |
Conceptual Level |
Internal Level |
|
Also Called |
View Level |
Logical Level |
Physical Level |
|
Focus |
User view |
Database structure |
Physical storage |
|
Used By |
End Users |
Database Designers |
DBA/System |
|
Shows |
Required data only |
Complete database |
Storage details |
|
Security |
High |
Medium |
Very High |
|
Example |
Student marks |
Student, Course tables |
Data files, blocks |
Quick Revision
|
Level |
Simple Meaning |
Example |
|
External Level |
What the user sees |
Student sees only marks and attendance |
|
Conceptual Level |
How the database is organized |
Tables like Student, Faculty, Course |
|
Internal Level |
How data is stored |
Data files, blocks, rows on disk |
|
External–Conceptual Mapping |
Connects user views to the logical database |
Student view mapped to Student table |
|
Conceptual–Internal Mapping |
Connects logical design to physical storage |
Student table stored in database files |
One-Line Memory Trick
- External
Level = User's View
- Conceptual
Level = Database Design
- Internal
Level = Physical Storage
- Mapping = Bridge between levels
This three-level architecture helps achieve data
independence,
meaning changes at one level (such as how data is stored) usually do not affect the other levels (such as what users see).
Or follow my blog from the below link
){kind=link}
0 Comments