MySQL + SQL · Lesson 3
Need for Database
What is a Database?
Database is an organized collection of related data stored in a structured way so that it can be easily accessed, managed, updated and retrieved. A DBMS (Database Management System) is the software that manages this database — for example, MySQL, Oracle, MS Access.
Before databases existed, organizations stored data in paper files, registers and file cabinets. Later, computers used flat files and spreadsheets. But as data grew, these methods caused serious problems — which is why databases were needed.
Problems with File-Based Systems
In a file-based system, each department stores its own files separately. For example, a school stores student data in one file, fee data in another, and library data in a third — all unconnected.
| Problem | Explanation | School Example |
|---|---|---|
| Data Redundancy | Same data stored in multiple files unnecessarily | Student name stored in fee file, library file and result file separately |
| Data Inconsistency | Same data updated in one file but not in others — causes conflict | Address changed in fee register but not in library card |
| Difficult Data Access | No easy way to search or filter combined data | Cannot find which students paid fee AND borrowed books this month |
| Data Isolation | Data scattered across different files in different formats | Fee file in Excel, library file in Word — cannot connect them |
| Security Problems | Cannot set user-level access on plain files | Any teacher can open and edit fee records accidentally |
| Integrity Problems | No automatic rules to prevent wrong data entry | Roll number entered as text instead of number — no validation |
| Concurrency Issues | Two people editing same file at same time causes data loss | Two clerks updating same student record simultaneously — one loses changes |
Why We Need a Database (DBMS)
A DBMS solves all the above file-system problems. Here is why every school, hospital, bank and e-commerce site needs a database:
- Centralized Storage: All data stored in one place — no duplication across departments.
- Easy Search: Use SQL to find any record in seconds — no manual searching.
- Data Safety: Transactions (COMMIT/ROLLBACK) ensure data is never half-saved.
- Access Control: Different users get different permissions — admin, teacher, student roles.
- Backup & Recovery: Database can be backed up and restored after any failure.
- Data Sharing: Multiple users access the same database simultaneously without conflict.
- Reduced Redundancy: Primary key and foreign key relationships eliminate repeated data.
Advantages of DBMS
| Advantage | Meaning |
|---|---|
| Data Independence | Changing storage structure does not affect application programs |
| Data Integrity | Constraints (NOT NULL, UNIQUE, CHECK) keep data correct and consistent |
| Data Security | User privileges and authentication protect sensitive data |
| Data Sharing | Multiple users can read/write simultaneously using transaction control |
| Reduced Redundancy | Normalization and foreign keys remove repeated data |
| Backup & Recovery | mysqldump and point-in-time recovery protect against data loss |
| Query Language | SQL makes it easy to insert, update, delete and retrieve data |
| Concurrent Access | DBMS handles multiple users at same time using locks and isolation levels |
Real-World Examples
- School Management: Student records, fee payment, attendance, library — all linked in one database. Changing a student's name updates everywhere automatically.
- Bank: Account balance updated safely using transactions — money never disappears between debit and credit operations.
- Hospital: Patient records, doctor appointments, medicine stock — all connected. Doctor can see full patient history instantly.
- E-commerce (like Amazon): Product catalog, orders, payments, delivery — millions of records searched in milliseconds.
- Railway Reservation: Seat availability checked and booked by thousands of users simultaneously without double-booking.
SQL Demo – See the Need in Action
Without a database (problem simulation)
-- Imagine student name stored in 3 separate flat files:
-- fee_records: Aarav Sharma → Khurja
-- library_records: Aarav Sharma → Khurja
-- result_records: Aarav Sharma → Khurja
-- If name changes → must update in 3 places manually → risk of inconsistency!
With a database (solution)
CREATE DATABASE school_db;
USE school_db;
-- One central students table
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
city VARCHAR(50)
);
-- Fee table linked with foreign key
CREATE TABLE fee_records (
fee_id INT PRIMARY KEY AUTO_INCREMENT,
roll_no INT,
amount DECIMAL(10,2),
paid_date DATE,
FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);
INSERT INTO students VALUES (1, 'Aarav Sharma', 'Khurja');
INSERT INTO fee_records (roll_no, amount, paid_date) VALUES (1, 5000.00, '2025-04-10');
-- Update name in ONE place only
UPDATE students SET name = 'Aarav Kumar Sharma' WHERE roll_no = 1;
-- Now fee_records automatically shows correct student via JOIN
SELECT s.name, f.amount, f.paid_date
FROM students s
JOIN fee_records f ON s.roll_no = f.roll_no;
Output:
+--------------------+---------+------------+
| name | amount | paid_date |
+--------------------+---------+------------+
| Aarav Kumar Sharma | 5000.00 | 2025-04-10 |
+--------------------+---------+------------+
Name updated in one place, visible everywhere — no inconsistency!
+--------------------+---------+------------+
| name | amount | paid_date |
+--------------------+---------+------------+
| Aarav Kumar Sharma | 5000.00 | 2025-04-10 |
+--------------------+---------+------------+
Name updated in one place, visible everywhere — no inconsistency!
Common Mistakes
- Thinking files are enough: For small data, files work — but when data grows, files become unmanageable.
- Skipping primary keys: Without primary keys, duplicate records enter the database — defeating its purpose.
- No foreign keys: Without foreign key relationships, data becomes disconnected — same as file-based system.
- No backup plan: Assuming database will never fail — always set up automated backups.
- All users as admin: Giving everyone full access removes the security benefit of DBMS.
Practice Tasks
- List 5 real problems that happen in your school if student data is stored only in Excel files. How would a database solve each one?
- Create a
hospital_dbwith tables:patients,doctors,appointments. Add foreign keys to link them. Insert 3 rows each. - Write one difference each between: Data Redundancy vs Data Inconsistency, Data Security vs Data Integrity.
- Write a viva question: "What is the main disadvantage of a file-based system?" — Answer it in 3 points.
Summary
- File-based systems cause redundancy, inconsistency, poor security and no concurrent access.
- DBMS solves all these by providing centralized, structured, secure and efficient data management.
- Key advantages: Data integrity, security, sharing, backup, independence and SQL querying.
- Every real-world application — school, bank, hospital, e-commerce — depends on a DBMS.
- MySQL is a popular, free and powerful DBMS used in web projects and educational labs.