Problems with Old File Systems

1. Data Redundancy (Same data stored multiple times)

Example: A student studying Computer Science and Math has their phone number stored in both CS department file and Math department file. If they change their number, both files need updating - often one gets forgotten!

2. Hard to Access Data

Example: A clerk wants a list of students from Delhi with 60+ credits. No existing program does this, so they either:

• Print all students and manually filter (tedious!)

• Ask programmer to write new code (expensive!)

3. Data Integrity Problems

Example: Rule says "Department budget cannot be negative." But this rule is coded in multiple programs. Adding a new rule means changing ALL programs - nightmare!

4. Atomicity Problems

Example: Transferring ₹500 from Account A to Account B:

• Step 1: Deduct ₹500 from A ✓

• Step 2: Add ₹500 to B ✗ (System crashes!)

• Result: Money disappears! Both steps must happen together or not at all.

5. Concurrent Access Problems

Example: Two students register for a course (limit: 40 students, current: 39)

• Both see "39 enrolled"

• Both register and update to "40"

• Result: 41 students enrolled (limit violated!)

6. Security Problems

Example: A student working part-time in admin accidentally sees all teachers' salaries. Different users should see different data!

Database System Components

Three Levels of Data Abstraction

Think of it like looking at a car:

1. Physical Level (Engine details)

   • How data is actually stored on hard disk

   • Complex technical details

   • Example: Data stored in binary blocks, indexes, etc.

2. Logical Level (Car's features)

   • What data exists and relationships

   • Database admin works here

   • Example: Student table connected to Course table

3. View Level (Dashboard for driver)

   • What each user sees

   • Customized for each user type

   • Example: Students see their grades, teachers see their class lists

Schema vs Instance

Schema = Structure/Design (rarely changes)

• Like a form template with fields: Name, Age, Address

Instance = Actual data (changes frequently)

• Like filled forms: "John, 20, Delhi" or "Mary, 22, Mumbai"

Data Independence

Physical Data Independence: You can change how data is stored without affecting programs.

Example: Moving from hard disk to SSD storage - your apps still work the same!

Database Models

1. Relational Model (Most popular)

   • Data in tables (rows and columns)

   • Like Excel spreadsheets but smarter

   • Relation is defined using Referential Keys

2. ER Model (For design)

   • Shows entities (things) and relationships

   • Example: Student "enrolls in" Course

3. Object-Oriented Model

   • Combines data with functions

   • Like programming objects

4. Semi-Structured data model

Database Languages

DDL (Data Definition Language) - The Architect

• Defines structure of database

• Creates tables, sets rules

• Example: "Create table Student with ID, Name, Age"

• Interpreted

DML (Data Manipulation Language) - The User

• Procedural: Tell exactly HOW to get data

• Declarative: Just say WHAT you want (SQL is this type)

• Example: "Show me all students from Delhi" (system figures out how)

• Compiled

• Procedural, Declarative

Transactions - The ACID Test

A transaction = Group of operations that must ALL succeed or ALL fail.

ACID Properties:

• Atomicity: All or nothing

• Consistency: Database rules always followed

• Isolation: Transactions don't interfere with each other

• Durability: Once done, changes are permanent

Example: Online shopping

1. Check item availability ✓

2. Deduct money from account ✓

3. Reserve item ✓

4. Send confirmation ✓

If step 3 fails, steps 1-2 must be undone automatically!

---

Important and Tough Questions

Conceptual Questions

1. Explain why physical data independence is crucial for large organizations. Give a real-world scenario where changing physical storage would be necessary.

2. A bank's database crashes during a money transfer between accounts. Explain how the ACID properties ensure data integrity and what would happen without them.

3. Compare procedural vs declarative DML with examples. Why do modern databases prefer declarative approaches?

4. Design a three-level architecture for a hospital management system. What would each level contain and who would access each level?

Scenario-Based Questions

5. An e-commerce company stores customer data in multiple files (orders.txt, customers.txt, products.txt). List ALL problems they might face and how DBMS solves each one.

6. Two customers simultaneously try to buy the last item in stock. Without proper concurrency control, what problems could occur? Design a solution.

7. A university wants to add a new constraint: "Students cannot enroll in more than 6 courses per semester." Explain why this is difficult in file-based systems but easy in DBMS.

Technical Questions

8. Distinguish between schema and instance using a social media platform example. What happens when the platform adds a new feature like "Stories"?

9. Explain why DDL statements are processed differently from DML statements. What role does the data dictionary play?

10. A database has three views: StudentView, TeacherView, and AdminView, all based on the same logical schema. How does this provide both convenience and security?

Application Questions

11. Design a transaction for "booking a movie ticket" that satisfies all ACID properties. What happens if the payment gateway fails?

12. Why can't you implement proper atomicity in a file-based system? Use a real example to illustrate the challenges.

13. A company claims their file-based system is better because it's "simpler." Debate this claim by comparing maintenance costs, data consistency, and scalability.

Advanced Thinking Questions

14. If you were designing a DBMS for a space mission where communication delays are hours, how would you modify the traditional ACID properties?

15. Explain how the three levels of abstraction enable a database to serve both a mobile app and a web application simultaneously without conflicts.