The Three-Schema Architecture divides a database system into three different levels:
1. External Level
2. Conceptual Level
3. Internal Level
The main purpose is to separate what users see from how data is logically organized and how it is physically stored.
> External → Conceptual → Internal
User → Database Design → Storage
> External = What user sees
Conceptual = What database contains
Internal = How database stores it
The External Level is the highest level of the database.
It describes the database from the user's point of view.
Different users can see different views of the same database.
Consider a college database.
A student may see:
Student Name
Register Number
Marks
Attendance
A teacher may see:
Student Name
Register Number
Marks
Attendance
Internal Marks
Assignment Marks
The administrator may see:
Student Details
Fee Details
Attendance
Marks
Course Details
Staff Details
All these users are accessing the same database, but they see different information.
Also called View Level.
It is closest to the user.
Shows only required information.
Hides unnecessary information.
Multiple external views can exist.
> External = Eye of the user
The Conceptual Level is the middle level.
It describes the complete logical structure of the database.
It tells us:
What tables exist
What attributes exist
What relationships exist
What constraints exist
A college database may have:
The conceptual level describes how these tables are related.
For example:
Also called Logical Level.
Describes the complete database logically.
Hides physical storage details.
Defines tables, relationships and constraints.
Usually designed by the database administrator/database designer.
> Conceptual = Complete database map
The Internal Level is the lowest level.
It explains how the data is actually stored inside the computer.
It deals with:
Files
Disk blocks
Storage locations
Indexes
Data structures
Record placement
Access methods
For example, the database may store student records in:
Users don't need to know these details.
Also called Physical Level.
Closest to physical storage.
Describes how data is stored.
Deals with storage efficiency and access speed.
Hidden from normal users.
> Internal = Inside the storage
The levels need to communicate with each other.
This is done using mappings.
There are mainly two mappings:
Connects the external views with the conceptual database.
It determines how a user's view corresponds to the logical database.
Connects the conceptual level with the internal storage.
It determines how logical data is stored physically.
> External ↔ Conceptual ↔ Internal
One of the most important advantages of three-schema architecture is Data Independence.
> Changes at one level should not greatly affect the other levels.
There are two types.
It means we can change the physical storage without changing the logical database.
Suppose student data is stored using:
Old: Sequential files
We change it to:
New: Indexed files
The tables and applications can continue working normally.
> Physical changes → Logical structure remains same
It means we can change the logical structure without changing users' views.
Suppose we add:
Phone_Number
to the STUDENT table.
Existing users who don't need the phone number can continue using their existing views.
> Logical changes → User views remain same
Changes in storage or logical structure have less effect on users.
Users can be given only the information they need.
Example:
Student → Marks
Accountant → Fees
Teacher → Attendance
Different users can have different views of the same database.
Database administrators can manage storage separately from user views.
Unnecessary implementation details are hidden.
Changes can be made at one level without completely changing the entire system.
> S-M-M-A-M-M
You can remember the important advantages as:
Security + Multiple views + Maintenance + Abstraction + Independence + Management
Managing three levels and mappings can be complicated.
Designing and maintaining mappings requires additional effort.
Mappings between levels may introduce some processing overhead.
Proper separation between physical, logical and external levels requires skilled database designers.
Although data independence is provided, some changes may still require modifications at other levels.
| Level | Simple Meaning | Memory |
|---|---|---|
| External | What user sees | 👁️ User View |
| Conceptual | Logical structure | 🧠 Database Map |
| Internal | How data is stored | 💾 Storage |
> USER → LOGIC → STORAGE
External → Conceptual → Internal
DBMS stands for Database Management System.
It is software used to:
Create databases
Store data
Organize data
Retrieve data
Update data
Delete data
Protect data
MySQL
Oracle Database
PostgreSQL
Microsoft SQL Server
SQLite
Instead of storing college student information in many separate files:
a DBMS can manage them as a connected database.
> User → DBMS → Database → Result
DBMS stores large amounts of data in an organized manner.
Users can quickly find required information.
This retrieves students who scored above 80.
DBMS allows users to:
Insert
Update
Delete
data.
Add student
Change marks
Delete student
DBMS protects data from unauthorized users.
Student → Can view marks
Teacher → Can update marks
Admin → Can manage everything
DBMS ensures that stored data is accurate and valid.
A student's mark cannot normally be:
if the allowed range is 0–100.
Redundancy means unnecessary duplication of data.
Without DBMS:
DBMS reduces this unnecessary duplication.
> DBMS = Less duplicate data
Multiple users can access the database at the same time.
DBMS can recover data after:
System failure
Power failure
Hardware failure
Software failure
Multiple users can work with the database simultaneously while maintaining data consistency.
Two bank employees may access customer accounts at the same time.
DBMS ensures that database operations are completed properly.
For example, in a bank transfer:
Both operations should happen correctly.
This is a very important exam comparison.
| DBMS | File System |
|---|---|
| Reduces data redundancy | More duplicate data |
| Better data security | Less security |
| Supports data sharing | Limited sharing |
| Provides backup and recovery | Recovery is difficult |
| Maintains data consistency | Inconsistency can occur |
| Supports concurrent users | Limited concurrency |
| Provides data independence | Low data independence |
| Provides integrity constraints | Difficult to enforce |
| Easy data retrieval using SQL | Searching can be difficult |
| Centralized data management | Data scattered across files |
In a file system, the same data may be stored in many files.
The same student information may be repeated.
DBMS reduces unnecessary duplication.
DBMS allows access permissions.
Student → View marks
Teacher → Enter marks
Admin → Full access
This is harder to manage in a normal file system.
If the same data exists in multiple files, one file may be updated while another is not.
This causes inconsistency.
DBMS helps maintain a consistent version of data.
Many users can access the same database.
DBMS provides mechanisms for recovering data after system failures.
DBMS can enforce rules.
Age > 0
Marks between 0 and 100
Student ID must be unique
This helps prevent invalid data.
Multiple users can access the same database simultaneously.
DBMS manages these simultaneous operations.
Using SQL, required data can be searched easily.
The DBMS quickly returns the required records.
DBMS is used almost everywhere large amounts of organized data are needed.
Banks use DBMS to store:
Customer details
Account information
Transactions
Loans
Balance
When you withdraw ₹5,000:
Railway systems use DBMS to manage:
Passenger information
Train details
Seat availability
Ticket booking
Cancellation
Hospital DBMS stores:
Patient details
Doctor details
Medical records
Appointments
Billing
Medicine information
A doctor can retrieve a patient's previous records from the database.
Colleges and universities use DBMS for:
Student records
Marks
Attendance
Fees
Courses
Examination results
Websites such as online shopping platforms use databases to manage:
Products
Customers
Orders
Payments
Delivery details
Social media applications use DBMS to manage:
User accounts
Posts
Comments
Likes
Followers
Messages
Airlines use DBMS for:
Flight schedules
Passenger details
Seat reservations
Ticketing
Airport information
Telecom companies use DBMS for:
Customer information
Call records
Recharge information
Billing
Data usage
.
DBMS architecture explains how users interact with the database through the DBMS and how different components work together.
> User → Interface → DBMS → Database
The important components are:
1. Users
2. User Interface / Application Programs
3. Query Processor
4. Storage Manager
5. Database
6. Metadata / Data Dictionary
Users are the people or applications that interact with the database.
They directly use applications.
They create programs that communicate with the database.
The DBA manages the entire database.
The DBA handles:
Security
User permissions
Backup
Recovery
Database maintenance
Performance
> User = Person who uses the database
Users normally don't directly access the database storage.
They use an application or interface.
College App
Banking App
Railway Booking App
Online Shopping Website
The application sends requests to the DBMS.
A student clicks:
The application sends a query to the DBMS.
The Query Processor is one of the most important components of DBMS.
Its job is to understand and execute SQL queries.
The query processor processes this command and finds the required records.
Understand SQL commands
Check SQL syntax
Convert queries into an executable form
Find an efficient way to execute queries
Execute the query
Processes DDL commands such as:
CREATE
ALTER
DROP
Processes DML commands such as:
SELECT
INSERT
UPDATE
DELETE
Finds an efficient way to execute a query.
If there are several ways to search a table, the optimizer selects a faster method.
Actually executes the query and produces the result.
> Query Processor = Understand → Optimize → Execute
The Storage Manager manages the data stored on physical storage.
It acts as a bridge between the database and physical storage.
Store data
Retrieve data
Update data
Manage files
Manage memory
Maintain security
Maintain consistency
Manages how database files are stored on disk.
It handles:
Data files
File organization
Storage space
Manages data between main memory and disk.
It loads required data into memory when needed.
Accessing RAM is generally much faster than accessing disk storage, so proper buffer management improves performance.
Manages database transactions.
Suppose ₹1,000 is transferred:
The transaction manager ensures the operation is handled correctly.
It checks:
Who is allowed to access data
Whether data follows database rules
Student → View marks
Teacher → Update marks
Admin → Full access
The database contains the actual stored data.
For example, a college database may contain:
STUDENT
COURSE
MARKS
ATTENDANCE
FEES
STAFF
The data is stored in tables and other database structures.
Metadata means "data about data."
It stores information about the database structure.
It can also contain information about:
Tables
Columns
Data types
Constraints
Users
Permissions
Indexes
> Database = Actual data
Metadata = Information about the data
Suppose a student wants to see marks.
> Ask → Process → Find → Show
| Component | Main Job |
|---|---|
| Users | Use database |
| Application | Provides interface |
| Query Processor | Processes SQL |
| Storage Manager | Manages stored data |
| File Manager | Manages files |
| Buffer Manager | Manages memory |
| Transaction Manager | Manages transactions |
| Authorization Manager | Controls access |
| Database | Stores actual data |
| Metadata | Stores database structure information |
DCL and TCL are two important groups of SQL commands.
> DCL = Control Access
TCL = Control Transactions
DCL stands for Data Control Language.
DCL is used to control access and permissions in a database.
The two important DCL commands are:
1. GRANT
2. REVOKE
> GRANT = Give permission
REVOKE = Take permission back
The GRANT command gives permissions to a user.
This gives user1 permission to view the Student table.
Now the user can:
View data
Insert data
Update data
| Privilege | Meaning |
|---|---|
| SELECT | Read data |
| INSERT | Add data |
| UPDATE | Modify data |
| DELETE | Delete data |
| ALL | All available privileges |
The REVOKE command removes previously granted permissions.
Now user1 cannot update the Student table.
First:
Later:
Now:
SELECT → Allowed
INSERT → Not allowed
TCL stands for Transaction Control Language.
TCL is used to manage transactions in a database.
A transaction is a group of database operations treated as one unit.
Important TCL commands are:
1. COMMIT
2. ROLLBACK
3. SAVEPOINT
> COMMIT = Save
ROLLBACK = Undo
SAVEPOINT = Mark a point
COMMIT permanently saves the changes made during the transaction.
After COMMIT, the inserted data is saved.
> COMMIT = Confirm
ROLLBACK cancels changes that have not been committed.
The update is cancelled and the previous value is restored, subject to the DBMS's transaction behavior.
> ROLLBACK = Go back
SAVEPOINT creates a temporary point inside a transaction.
This allows you to roll back to that particular point.
The second update can be undone while keeping the earlier work.
> SAVEPOINT = Checkpoint
| DCL | TCL |
|---|---|
| Data Control Language | Transaction Control Language |
| Controls access | Controls transactions |
| Mainly manages permissions | Manages changes |
| GRANT | COMMIT |
| REVOKE | ROLLBACK |
| Related to security | Related to transaction management |
> DCL → Who can access?
TCL → What changes should be saved/undone?
SQL commands are divided into different categories.
For this question, focus on:
> Data Definition Language
DML
> Data Manipulation Language
> DDL = Design the table
DML = Deal with the data
DDL is used to create and modify the structure of database objects.
1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME
Used to create a new database object such as a table.
This creates a table called Student.
> CREATE = Make something new
Used to modify the structure of an existing table.
For example, adding a column.
Now the table contains:
Student_ID
Name
Marks
> ALTER = Change structure
Used to completely remove a database object.
The table and its data are removed.
> DROP = Destroy the object
DROP removes the table structure and its data.
Used to remove all records from a table while keeping the table structure.
After this:
Student table → Still exists
Student records → Removed
> TRUNCATE = Empty the table
Used to change the name of a database object.
The table name changes from:
Student
to:
Students
Note: Exact RENAME syntax can vary between database systems.
> RENAME = Change the name
C – CREATE
A – ALTER
D – DROP
T – TRUNCATE
R – RENAME
Think:
> Create → Alter → Drop → Truncate → Rename
DML is used to work with the data stored inside tables.
The common DML commands are:
1. INSERT
2. UPDATE
3. DELETE
Used to add new records into a table.
The record is added:
> INSERT = Add
Used to modify existing records.
The student's marks change:
> UPDATE = Change
Used to remove records from a table.
The record of student 101 is deleted.
> DELETE = Remove data
Although many syllabi classify SELECT as DQL, it is frequently discussed along with DML.
It is used to retrieve data.
This displays students who scored more than 80.
> SELECT = See/Search data
This comparison is very important for exams.
| DDL | DML |
|---|---|
| Data Definition Language | Data Manipulation Language |
| Deals with database structure | Deals with data |
| Creates/modifies objects | Inserts/changes/deletes records |
| CREATE | INSERT |
| ALTER | UPDATE |
| DROP | DELETE |
| TRUNCATE | SELECT* |
| RENAME | — |
| Example: Create table | Example: Add student |
*SELECT is commonly classified as DQL in many textbooks.
Imagine a college notebook.
Create pages
Change page structure
Remove pages
Rename sections
DML = Writing inside the notebook
Add information
Change information
Delete information
Read information
> DDL → Structure
DML → Data
Aggregate functions are SQL functions that perform a calculation on multiple rows and return one result.
> Many values → One answer
The five marks become one answer: 80.
The five important aggregate functions are:
1. COUNT() → Counts values
2. SUM() → Adds values
3. AVG() → Finds average
4. MAX() → Finds highest value
5. MIN() → Finds lowest value
> C-S-A-M-M
Count
Sum
Average
Maximum
Minimum
Or remember:
> COUNT = How many?
SUM = How much total?
AVG = What is the average?
MAX = What is highest?
MIN = What is lowest?
Consider the following Student table:
| Student_ID | Name | Department | Marks |
|---|---|---|---|
| 101 | Arun | AI&ML | 80 |
| 102 | Ravi | AI&ML | 70 |
| 103 | Priya | CSE | 90 |
| 104 | Kumar | CSE | 60 |
| 105 | Anu | AI&ML | 100 |
We will use this table for all examples.
COUNT() is used to count the number of rows or non-NULL values.
Result
There are 5 students.
To count all rows:
Result:
> COUNT = Count how many
SUM() calculates the total of a numeric column.
Calculation:
Result:
> SUM = Total
AVG() calculates the average value.
Calculation:
Result:
> AVG = Average
MAX() returns the largest value.
Result:
> MAX = Highest
MIN() returns the smallest value.
Result:
> MIN = Lowest
We can use multiple functions in a single query.
Result
| Total Students | Total Marks | Average | Highest | Lowest |
|---|---|---|---|---|
| 5 | 400 | 80 | 100 | 60 |
This is a very good example to write in the exam because it demonstrates all five aggregate functions together.
Aggregate functions can be used with WHERE to calculate values for selected rows.
Find the average marks of AI&ML students:
AI&ML marks:
Average:
GROUP BY is used when we want separate aggregate results for different groups.
Find the average marks for each department:
Possible result:
| Department | Average Marks |
|---|---|
| AI&ML | 83.33 |
| CSE | 75 |
Without GROUP BY:
> Give one overall answer.
With GROUP BY:
> Give one answer for each group.
COUNT() → Number
SUM() → Total
AVG() → Average
MAX() → Highest
MIN() → Lowest
An E-R (Entity-Relationship) Diagram represents:
Entities
Attributes
Relationships
For a Student Course Registration System, the main entities can be:
1. STUDENT
2. COURSE
3. REGISTRATION
Attributes:
Student_ID
Student_Name
Department
Attributes:
Course_ID
Course_Name
Credits
Attributes:
Registration_ID
Registration_Date
Semester
A student can register for many courses.
A course can have many students.
Therefore:
But relational databases cannot directly represent an M:N relationship as a simple pair of tables.
So we create an intermediate entity:
Suppose:
One student can register for many courses.
Similarly:
One course can have many students.
Therefore:
This represents the original:
Now convert each entity into a table.
Create a STUDENT table.
Example:
| Student_ID | Student_Name | Department | |
|---|---|---|---|
| 101 | Arun | arun@email.com | AI&ML |
| 102 | Ravi | ravi@email.com | CSE |
Create a COURSE table.
Example:
| Course_ID | Course_Name | Credits |
|---|---|---|
| C101 | DBMS | 4 |
| C102 | Java | 4 |
| C103 | AI | 3 |
Create a REGISTRATION table.
Example:
| Registration_ID | Student_ID | Course_ID | Date | Semester |
|---|---|---|---|---|
| 1 | 101 | C101 | 2026-08-01 | III |
| 2 | 101 | C102 | 2026-08-01 | III |
| 3 | 102 | C101 | 2026-08-02 | III |
This is an important point to remember for the exam.
Suppose we directly put courses inside Student:
This creates problems because one student can have many courses.
The registration table converts the M:N relationship into two 1:M relationships.
> Many-to-Many → Create a middle table
Remember these 5 steps:
> Find entities
Step 2
> Find attributes
Step 3
> Find relationships
Step 4
> Identify primary keys
Step 5
> Convert relationships using foreign keys/intermediate tables
> Entity → Attribute → Relationship → Key → Table
An attribute is a property or characteristic of an entity.
Here, Student_ID, Name, Email, and Department are attributes.
> Entity = Thing
Attribute = Information about the thing
Name = Attribute
Age = Attribute
Email = Attribute
Cannot be divided further.
Age
Gender
Student_ID
Can be divided into smaller attributes.
Another example:
> Simple = Cannot split
Composite = Can split
Has only one value for an entity.
A student normally has one student ID.
Can have multiple values.
A student may have multiple phone numbers.
In an E-R diagram, a multivalued attribute is usually represented using a double oval.
Its value can be calculated from another attribute.
Age can be calculated from Date of Birth.
Usually represented using a dashed oval in an E-R diagram.
An attribute whose value is actually stored in the database.
Date_of_Birth
The age can be calculated from it.
Age → Derived
A key is an attribute or group of attributes used to identify records uniquely.
Two students should not have the same Student ID.
> Key = Identity
A Super Key is any attribute or combination of attributes that can uniquely identify a record.
Student_ID
Student_ID + Name
Student_ID + Email
All can identify a student uniquely if Student_ID is unique.
Therefore they are super keys.
A Candidate Key is a minimal super key.
It uniquely identifies a record and contains no unnecessary attribute.
Student_ID
If both are unique, both can be candidate keys.
> Candidate = Possible Primary Key
The Primary Key is the candidate key selected to uniquely identify each record.
Rules:
Must be unique
Cannot normally be NULL
Identifies each record
> Primary Key = Main identity
Candidate keys that are not selected as the primary key are called alternate keys.
Example:
Candidate keys:
Student_ID
If we select:
Then:
> Alternate = Candidate key not chosen
A Foreign Key is an attribute that refers to the primary key of another table.
This connects the tables.
> Foreign Key = Connection between tables
A Composite Key consists of two or more attributes together that uniquely identify a record.
A student can register for multiple courses.
The combination:
can uniquely identify a registration for a particular student-course combination.
> Composite = Combination of keys
Constraints are rules applied to data to maintain correctness and consistency.
> Constraint = Rule
Specifies the valid values that an attribute can contain.
Age → Positive number
Marks = 150 ❌
Marks = 85 ✅
Ensures that key values uniquely identify records.
Duplicate Student IDs are not allowed for a primary key.
A primary key cannot contain NULL.
Every student must have a valid identifier.
> Entity Integrity = Primary Key cannot be NULL
A foreign key must refer to an existing primary key value in the related table, subject to the database's referential-action rules.
> Referential Integrity = Foreign Key must refer to valid parent data
Ensures that a column cannot have a NULL value.
A student's name must be provided.
Ensures that values in a column are not duplicated.
Two students cannot have the same email, if the constraint is defined this way.
Ensures that values satisfy a condition.
Therefore:
Marks = 150 → ❌
Provides a default value when no value is supplied.
If no semester is entered:
Mapping Cardinality describes how many entities of one entity set can be associated with how many entities of another entity set.
> Cardinality = How many are connected to how many?
We ask:
How many students can take one course?
Based on the answer, there are four types:
1. One-to-One (1:1)
2. One-to-Many (1:N)
3. Many-to-One (N:1)
4. Many-to-Many (M:N)
> 1:1 → One ↔ One
1:N → One → Many
N:1 → Many → One
M:N → Many ↔ Many
In a one-to-one relationship, one entity in Entity A is related to only one entity in Entity B, and vice versa.
Example: Person and Passport
One person has one passport.
One passport belongs to one person.
Example data
Another example
One employee has one company ID card.
> 1:1 = One person → One thing
In a one-to-many relationship, one entity in Entity A can be related to many entities in Entity B.
But each entity in B is related to only one entity in A.
Example: Department and Employee
One department can have many employees.
But each employee belongs to one department.
Example
One department → Many employees.
> 1:N = One → Many
A many-to-one relationship is the reverse of one-to-many.
Many entities in Entity A are related to one entity in Entity B.
Example: Employees and Department
Many employees belong to one department.
Example
Many employees → One department.
> N:1 = Many → One
In a many-to-many relationship, many entities in Entity A can be related to many entities in Entity B.
Example: Student and Course
One student can register for many courses.
One course can have many students.
A better relational representation is:
Example
So:
Course → Many Students
> M:N = Many ↔ Many
| Cardinality | Meaning | Example |
|---|---|---|
| 1:1 | One entity ↔ One entity | Person–Passport |
| 1:N | One entity → Many entities | Department–Employee |
| N:1 | Many entities → One entity | Employee–Department |
| M:N | Many entities ↔ Many entities | Student–Course |
Ask two questions.
> How many B's can one A have?
Question 2:
> How many A's can one B have?
Student ↔ Course
One student → ?
Many courses.
One course → ?
Many students.
One department → ?
Many employees.
One employee → ?
One department.
One person → ?
One passport.
One passport → ?
One person.
Think of the arrows:
1 : 1 → One ↔ One
1 : N → One → Many
N : 1 → Many → One
M : N → Many ↔ Many
And remember:
> "Ask both sides: How many?"
That is the easiest way to identify mapping cardinality.
For a Bank Management System, we need to store information about:
Customers
Accounts
Branches
Loans
Transactions
The main entities are:
1. CUSTOMER
2. ACCOUNT
3. BRANCH
4. LOAN
5. TRANSACTION
A customer has:
Customer_ID
Customer_Name
Address
Phone
Primary Key:
An account has:
Account_No
Account_Type
Balance
Open_Date
Primary Key:
A bank branch has:
Branch_ID
Branch_Name
Location
Primary Key:
A loan has:
Loan_ID
Loan_Type
Amount
Loan_Date
Primary Key:
A transaction has:
Transaction_ID
Transaction_Date
Transaction_Type
Amount
Primary Key:
Now determine how the entities are connected.
A customer can have many accounts.
An account can also be jointly owned by multiple customers in a banking system.
Therefore:
We can use an intermediate relationship/table such as CUSTOMER_ACCOUNT.
One branch manages many accounts.
Each account belongs to one branch.
Therefore:
A customer can have multiple loans.
A loan can potentially have multiple borrowers in a system that supports joint loans.
Therefore:
For a simple exam model, if your syllabus assumes one loan belongs to one customer, you may instead represent it as CUSTOMER 1:N LOAN. The M:N design is more flexible.
One account can have many transactions.
Each transaction belongs to one account.
Therefore:
For an exam, you can draw the entities as rectangles, attributes as ovals, and relationships as diamonds if your faculty expects the traditional Chen notation.
You can represent the major relationships like this:
The basic conversion rules are:
Each strong entity becomes a table.
Each attribute becomes a column.
The identifying attribute becomes the table's primary key.
Put the primary key of the 1-side into the N-side as a foreign key.
Create a new table containing the primary keys of both entities.
ER Entity:
Relational Schema:
The relationship is:
So Branch_ID is added to the ACCOUNT table as a foreign key.
Why?
Relationship:
Therefore, Account_No becomes a foreign key in TRANSACTION.
Because:
we create a new table:
The combination:
can serve as a composite primary key.
Create a new table:
Again:
forms a composite key for the relationship table.
You can also demonstrate the relational schema using SQL:
0 Comments