MySQL + SQL · Lesson 4

File System vs DBMS

What is a File System?

File System is the traditional method of storing data in separate files on a computer — such as text files, Excel sheets, or Word documents. Each department manages its own files independently, with no central control or connection between files.

Examples of File System usage:

School keeps student list in one Excel file, fee records in another, and library records in a third — all separate.
Office stores employee data in Word documents and salary in a calculator file.
No automatic connection between files — manual work required to combine them.

What is DBMS?

DBMS (Database Management System) is software that stores, manages and retrieves data in a structured, centralized database. It provides security, concurrent access, data integrity and easy querying through SQL.

Examples: MySQL, Oracle, MS SQL Server, PostgreSQL, MS Access.

All school data in one database — students, fees, library, attendance all linked.
Any authorized user can query data instantly with SQL.
Multiple users access simultaneously without data conflict.

File System vs DBMS – Full Comparison

Feature	File System	DBMS
Data Storage	Separate, unconnected files	Centralized, structured database
Data Redundancy	High — same data in many files	Low — data stored once, linked by keys
Data Inconsistency	High — updating one file doesn't update others	Low — one update reflects everywhere
Data Sharing	Difficult — files must be manually shared	Easy — multiple users query same database
Data Security	No user-level access control	Role-based permissions (admin, teacher, student)
Data Integrity	No automatic constraints	PRIMARY KEY, NOT NULL, CHECK, FOREIGN KEY
Concurrent Access	Two users editing same file = data loss	Transactions + locking prevent conflicts
Backup & Recovery	Manual copy of files — error-prone	mysqldump, automated backup tools
Query/Search	Manual searching through file contents	SQL SELECT — instant search with conditions
Crash Recovery	Data lost if system crashes during write	Transaction rollback saves data on crash
Scalability	Very poor — slow as files grow	Good — indexes and optimization handle large data
Cost	Low initial setup	Higher setup but lower long-term cost

Problems of File System (Detailed)

1. Data Redundancy: Student name stored in fee file, result file and library file separately. If name has a typo, it must be corrected in all 3 files manually.

2. Data Inconsistency: Student address updated in fee register but forgotten in library card → two different addresses for same student → which is correct?

3. Data Isolation: Fee file in Excel, library in Word, attendance in paper register → impossible to combine and analyze together.

4. No Concurrent Access: Two clerks open the same Excel fee file → one overwrites the other's changes → data loss.

5. Security Issues: Any teacher with computer access can open fee records, modify salary data, or delete important files accidentally.

6. No Integrity Rules: A student's marks can be entered as "abc" or -50 — file system doesn't prevent it.

7. Difficult Querying: To find "students from Khurja who paid fee and have overdue library books" — manually search 3 files and cross-reference → hours of work vs. 1 SQL JOIN query in DBMS.

SQL Demo – DBMS Solves File System Problems

-- In DBMS: One database, all data connected
CREATE DATABASE school_db;
USE school_db;

CREATE TABLE students (
  roll_no INT PRIMARY KEY,        -- integrity: no duplicate students
  name VARCHAR(80) NOT NULL,      -- integrity: name cannot be blank
  city VARCHAR(50)
);

CREATE TABLE fee_records (
  fee_id INT PRIMARY KEY AUTO_INCREMENT,
  roll_no INT NOT NULL,
  amount DECIMAL(10,2),
  paid_date DATE,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)  -- no orphan records
);

CREATE TABLE library (
  lib_id INT PRIMARY KEY AUTO_INCREMENT,
  roll_no INT,
  book_name VARCHAR(100),
  return_date DATE,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

INSERT INTO students VALUES (1,'Aarav Sharma','Khurja'),(2,'Priya Verma','Aligarh');
INSERT INTO fee_records (roll_no,amount,paid_date) VALUES (1,5000,'2025-04-10'),(2,5000,'2025-04-15');
INSERT INTO library (roll_no,book_name,return_date) VALUES (1,'Physics NCERT','2025-05-01');

-- Complex query that would take hours in file system — seconds in DBMS
SELECT s.name, s.city, f.amount AS fee_paid, l.book_name AS book_borrowed
FROM students s
JOIN fee_records f ON s.roll_no = f.roll_no
LEFT JOIN library l ON s.roll_no = l.roll_no
WHERE s.city = 'Khurja';

Common Mistakes

Thinking file system is always worse: For very small, simple, one-user data — files can work. DBMS overhead isn't always needed.
Not using foreign keys in DBMS: Without foreign keys, DBMS behaves like a file system — no referential integrity.
Confusing DBMS with Database: Database is the data; DBMS is the software that manages it (MySQL is DBMS, school_db is Database).
Not setting user privileges: Installing MySQL and giving everyone root access defeats the security advantage of DBMS.

Practice Tasks

Draw a diagram showing how a school stores data in File System vs DBMS — show connections between fee, library and student data.
List 5 specific problems a school would face if it used only Excel files for student data. For each problem, write how DBMS solves it.
Create the school_db from the SQL demo above. Write a query to find all students who have a library book pending return.
Viva: "What is data redundancy? Give an example from a file system." — Write a 3-line answer.

Summary

File System = Separate files, no control, high redundancy, poor security.
DBMS = Centralized, structured, secure, concurrent, with SQL querying.
DBMS solves: Redundancy, Inconsistency, Isolation, Security, Integrity, Concurrency issues.
Key DBMS examples: MySQL, Oracle, MS SQL Server, PostgreSQL.
In real projects: Always use DBMS for multi-user, multi-table, scalable applications.

File System क्या है?

File System data को separate files में store करने का traditional तरीका है — जैसे text files, Excel sheets, या Word documents। हर department अपनी files अलग manage करता है, कोई central control नहीं होता।

School में student list एक Excel में, fee दूसरी में, library तीसरी में — सब अलग।
Files के बीच कोई automatic connection नहीं — manually combine करना पड़ता है।

DBMS क्या है?

DBMS (Database Management System) वह software है जो data को एक structured, centralized database में store, manage और retrieve करता है। यह security, concurrent access, data integrity और SQL querying provide करता है।

Examples: MySQL, Oracle, MS SQL Server, PostgreSQL, MS Access.

File System vs DBMS – पूरा अंतर

Feature	File System	DBMS
Data Storage	Separate, unconnected files	Centralized, structured database
Data Redundancy	High — same data कई files में	Low — data एक बार, keys से linked
Data Inconsistency	High — एक file update, दूसरी नहीं	Low — एक update सब जगह
Data Sharing	मुश्किल — manually share करना पड़े	आसान — multiple users एक database
Data Security	No access control	Role-based permissions
Data Integrity	कोई constraints नहीं	PRIMARY KEY, NOT NULL, CHECK
Concurrent Access	Two users = data loss	Transactions + locking
Backup & Recovery	Manual file copy	mysqldump, automated backup
Search/Query	Manual searching	SQL SELECT — instant
Crash Recovery	Data loss on crash	Transaction rollback

File System की समस्याएं (Detail में)

1. Data Redundancy: Student का नाम fee file, result file और library file — तीनों में अलग-अलग। एक जगह typo हो तो तीनों में manually fix करो।

2. Data Inconsistency: Address fee register में update हुआ, library card में नहीं → एक ही student के दो अलग addresses → कौन सा सही?

3. Data Isolation: Fee Excel में, library Word में, attendance paper पर → combine करके analyze करना impossible।

4. No Concurrent Access: दो clerk एक Excel file खोलें → एक दूसरे का data overwrite करे → data loss।

5. Security Issues: Computer access वाला कोई भी teacher fee records खोल सकता है, salary modify कर सकता है।

6. No Integrity: Marks field में "abc" या -50 enter हो सकता है — कोई रोकने वाला नहीं।

7. Difficult Query: "Khurja के students जिन्होंने fee दी और library book भी है" — 3 files manually search करो। DBMS में: 1 SQL JOIN query = seconds में result।

SQL Demo

CREATE DATABASE school_db;
USE school_db;

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  city VARCHAR(50)
);

CREATE TABLE fee_records (
  fee_id INT PRIMARY KEY AUTO_INCREMENT,
  roll_no INT NOT NULL,
  amount DECIMAL(10,2),
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

-- तीनों "files" (tables) को एक SQL query से combine करें
SELECT s.name, s.city, f.amount AS fee_paid
FROM students s
JOIN fee_records f ON s.roll_no = f.roll_no
WHERE s.city = 'Khurja';

File system में: 3 files manually search करो — घंटों का काम।
DBMS में: एक SQL query — seconds में result।

Common Mistakes

File system हमेशा बुरा नहीं: बहुत छोटे, single-user data के लिए files काम कर सकती हैं।
Foreign keys न use करना: Foreign keys के बिना DBMS भी file system जैसा behave करता है।
DBMS और Database confuse करना: MySQL = DBMS (software); school_db = Database (data)।
सबको root access देना: यह DBMS के security advantage को खत्म करता है।

Practice Tasks

School में File System vs DBMS का diagram बनाएं — fee, library और student data के connections दिखाएं।
5 problems लिखें जो school को Excel files use करने से होंगी। हर problem का DBMS solution लिखें।
SQL demo से school_db बनाएं। Query लिखें: सभी students जिनकी library book return नहीं हुई।
Viva: "Data redundancy क्या है? File system में example दें।" — 3 lines में answer।

Summary

File System = Separate files, no control, high redundancy, poor security।
DBMS = Centralized, structured, secure, concurrent, SQL querying।
DBMS solve करता है: Redundancy, Inconsistency, Isolation, Security, Integrity, Concurrency।
Real projects में: हमेशा DBMS use करें — multi-user, scalable applications के लिए।