Skip to content

Lesson 1: Purpose of a Database System

Why we abandoned files and invented the DBMS

About this lesson

Course: Database Management Systems

Unit 1: Introduction to Database · 5 Lecture Hours

Lesson duration: 1 lecture (50 minutes)

Reading: Elmasri & Navathe, Ch. 1 · Silberschatz, Ch. 1

Learning Outcomes

By the end of this lesson you will be able to:

  1. Define the terms data, information, database, and DBMS precisely.
  2. Explain the classic problems of the file-processing approach.
  3. Justify each problem with a concrete college or bank example.
  4. State how a DBMS solves each problem.
  5. Identify when a DBMS is not the right choice.

1.1 From Data to Database

Definitions

Data — raw, unprocessed facts with no context of their own. 21, "Ankit", 2004-08-19 are data.

Information — data placed in a context so that it answers a question. "Ankit, aged 21, was born on 19-Aug-2004" is information.

Database — a shared, integrated, self-describing collection of logically related data, designed to meet the information needs of an organisation.

DBMS (Database Management System) — a collection of programs that enables users to create, maintain, query and control access to a database. Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.

Database System — the database + the DBMS + the application programs + the users, taken together.

Unpack the four words in the definition of database; each is a design commitment:

Word What it commits you to
Shared Many users and many applications use the same copy of the data, concurrently.
Integrated Formerly separate files are merged so that a fact is stored once.
Logically related The data describes one mini-world (a college, a bank) — not a random heap.
Self-describing The database stores its own structure (the catalog or data dictionary), not just the values.

Mini-world (Universe of Discourse)

The slice of the real world that a database represents. For our course-wide running example the mini-world is a college: its students, the courses they take admission in, the societies they enrol in, and the teachers in charge.

The self-describing property — the sharpest difference

A traditional program file, say students.dat, is meaningless on its own. The knowledge that "bytes 0-7 are the roll number, bytes 8-37 are the name" lives inside the C or COBOL program that reads it. Lose the program, lose the data.

A database stores that knowledge inside itself, in the catalog:

-- The catalog is itself queryable data
SELECT column_name, data_type, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'STUDENT';
+-------------+-----------+-------------+
| column_name | data_type | is_nullable |
+-------------+-----------+-------------+
| Rollno      | varchar   | NO          |
| Name        | varchar   | NO          |
| Dateofbirth | date      | YES         |
+-------------+-----------+-------------+

This is why a DBMS is general-purpose software: it does not need to be recompiled to hold a new kind of data. You describe the new structure; the DBMS reads its own catalog and adapts.

1.2 The File-Processing Approach

Before databases — and still, in badly built systems today — each department wrote its own programs owning its own files.

        FILE-PROCESSING APPROACH  (the "before" picture)

  ADMISSION SECTION            EXAM SECTION              LIBRARY
  +----------------+          +----------------+     +----------------+
  | admission.exe  |          |   result.exe   |     |  library.exe   |
  | (written in C) |          |   (in COBOL)   |     |  (in FoxPro)   |
  +-------+--------+          +-------+--------+     +-------+--------+
          |                           |                      |
          v                           v                      v
  +----------------+          +----------------+     +----------------+
  | students.dat   |          |  marks.dat     |     |  members.dat   |
  | rollno, name,  |          | roll_no, name, |     | mem_id, sname, |
  | dob, course    |          | subj, marks    |     | dob, books     |
  +----------------+          +----------------+     +----------------+
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        A student's NAME and DOB are stored THREE times, in three
        different formats, owned by three different programs.

Figure 1.1 — Islands of automation. Each application owns its private files.

The classic problems

Problem 1 — Data Redundancy and Inconsistency

Redundancy = the same fact stored in more than one place. Inconsistency = those copies disagree.

Example. Student X2019 is Ankit Sharma in students.dat, in marks.dat, and in members.dat. He gets his name legally corrected to Ankit Kumar Sharma. The admission clerk updates students.dat. Nobody tells the exam section.

Result: the degree certificate prints Ankit Sharma, the library card says Ankit Sharma, the admission register says Ankit Kumar Sharma. The database no longer knows the truth. This is an update anomaly, and it is the single most damaging failure of the file approach.

Redundancy also wastes storage, but inconsistency is the real disease; wasted bytes are only a symptom.

Problem 2 — Difficulty in Accessing Data

File systems answer only the questions their programs were written to answer.

The Principal asks: "Give me the names of students born in 2001 who are enrolled in at least one part-time course." No program does that. Someone must write, compile, test and deploy a new program — a week's work for a one-off question.

In a DBMS this is one statement, answered in milliseconds:

SELECT DISTINCT s.Name
FROM   STUDENT s
JOIN   ADMISSION a ON s.Rollno = a.Rollno
JOIN   COURSE    c ON a.SID    = c.SID
WHERE  YEAR(s.Dateofbirth) = 2001
  AND  c.Coursetype = 'Parttime';

The DBMS provides a general-purpose query language, so unanticipated questions cost nothing.

Problem 3 — Data Isolation

Data is scattered across files in incompatible formats — one CSV, one fixed-width binary, one dBase table, with dates as DD/MM/YY here and YYYYMMDD there. Writing a program that combines them is mostly format-conversion drudgery, not logic.

Problem 4 — Integrity Problems

Business rules — "Total seats must be positive", "course type is only Fulltime or Parttime", "a roll number in ADMISSION must exist in STUDENT" — are buried inside program code in the file approach. Every new program must re-implement every rule, and one programmer who forgets corrupts the data for everybody.

A DBMS lets you declare the rule once, at the data:

CHECK (TotalSeats > 0),
CHECK (Coursetype IN ('Fulltime','Parttime')),
FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno)

The DBMS then enforces it against every access path — SQL prompt, web app, batch job, DBA — with no exceptions.

Problem 5 — Atomicity Problems

A transfer of 5,000 rupees from account A to B is two writes: debit A, credit B. If the machine crashes between them, 5,000 rupees has evaporated.

Atomicity means all-or-nothing: either both writes happen or neither does. File systems have no notion of this; DBMSs implement it with logging and recovery.

Problem 6 — Concurrent Access Anomalies

Two clerks book the last seat of a course at the same instant.

Time Clerk 1 Clerk 2 Vacant in file
t1 reads Vacant = 1 1
t2 reads Vacant = 1 1
t3 writes Vacant = 0 0
t4 writes Vacant = 0 0

Two students admitted, one seat consumed. This is a lost update. A DBMS prevents it with concurrency control (Unit 6).

Problem 7 — Security Problems

File permissions are all-or-nothing on the whole file. But a clerk should see a student's address and not their fee-defaulter status. A DBMS grants privileges per table, per column, per operation:

GRANT SELECT (Rollno, Name) ON STUDENT TO 'clerk'@'localhost';

1.3 How the DBMS Answers

        DATABASE APPROACH  (the "after" picture)

   Admission App     Exam App      Library App     Ad-hoc SQL
        |                |               |              |
        +--------+-------+-------+-------+------+-------+
                          |
                 +--------v---------+
                 |      D B M S     |   <-- query processor, transaction
                 |  (MySQL/Oracle)  |       manager, security, catalog
                 +--------+---------+
                          |
              +-----------v------------+
              |  ONE SHARED DATABASE   |
              |  STUDENT   COURSE      |
              |  ADMISSION SOCIETY     |
              |  + CATALOG (metadata)  |
              +------------------------+

   Every fact stored ONCE. Every rule enforced ONCE. Every app agrees.

Figure 1.2 — All applications share one integrated, self-describing database.

# File-system problem DBMS facility that removes it
1 Redundancy and inconsistency Integration + normalisation (Unit 5)
2 Difficulty of access SQL — declarative, general-purpose query language (Unit 4)
3 Data isolation One uniform data model + one catalog (Unit 3)
4 Integrity problems Declarative constraints: PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
5 Atomicity Transactions — COMMIT / ROLLBACK, write-ahead log (Unit 6)
6 Concurrency anomalies Concurrency control — locking, serialisable schedules (Unit 6)
7 Security CREATE USER, GRANT, REVOKE, roles, views (Unit 4)

1.4 Advantages of the Database Approach

  1. Controlled redundancy. Redundancy is not banned outright — it is controlled. A little is sometimes kept deliberately for performance, but the DBMS then knows about it and keeps copies in step.
  2. Data sharing. Multiple users and applications, concurrently, on one consistent copy.
  3. Enforcement of standards. One naming convention, one date format, one documentation standard, imposed by the DBA.
  4. Improved data integrity and security.
  5. Backup and recovery provided as a service — no application needs to write its own.
  6. Data independence. Change the storage, or even the logical design, without rewriting applications (Lesson 3).
  7. Faster application development. The hard parts — concurrency, recovery, security, indexing — are already written.
  8. Economy of scale. One powerful server for the organisation beats a dozen departmental silos.
  9. Balanced conflicting requirements. The DBA arbitrates centrally between departments.
  10. Availability of up-to-date information. An update is instantly visible to everyone.

1.5 Costs, and When Not to Use a DBMS

Be honest about the trade-offs — this is a favourite examination question.

Costs: high initial cost of software, hardware and DBA salaries; the overhead of generality (a DBMS performs security, concurrency, recovery and integrity checks on every operation, so a single simple read is slower than a raw file read); and a central point of failure.

A DBMS is unnecessary or undesirable when...

  • The database and applications are simple, well-defined and not expected to change.
  • There are stringent real-time requirements that the DBMS overhead would violate (e.g. embedded instrument control).
  • No multi-user access is needed.
  • Embedded systems with very limited storage — a raw file, or a tiny library such as SQLite, is used instead of a server-class DBMS.

1.6 Solved Questions

Solved Question 1

Q. Distinguish between data and information with an example from a college database.

A. Data are raw facts recorded without interpretation; information is data processed and placed in context so that it answers a question and supports a decision.

In the college database the row ('X2019', 'Ankit Sharma', '2001-03-14') is data — three values with no stated meaning. Once the schema tells us that the columns are (Rollno, Name, Dateofbirth), and we compute from it that "Ankit Sharma, roll number X2019, is 24 years old and therefore eligible for the senior scholarship", we have information.

The bridge between them is the metadata (the catalog) plus the processing. This is exactly what the self-describing property of a database supplies and a flat file does not.

Solved Question 2

Q. "Redundancy is bad because it wastes disk space." Criticise this statement.

A. The statement is true but trivial, and it identifies the wrong danger. Disk is cheap; correctness is not.

The serious consequence of redundancy is inconsistency. If a student's address is stored in the admission file, the library file and the examination file, then an address change updated in only one of them leaves the organisation with three different answers to the same question, and no way to tell which is right. Reports disagree, letters go to the wrong place, and audits fail.

A second non-space cost is wasted effort and increased error probability: every update must be applied n times, so the chance of a mistake is roughly n times larger, and each application must be coded to remember all n places.

Note further that redundancy is not always wrong: a DBMS may keep a controlled redundant copy (an index, a materialised view, a denormalised summary) precisely because it can then guarantee that all copies stay synchronised. Uncontrolled redundancy is the problem.

Solved Question 3

Q. A college keeps students.dat (admission section) and members.dat (library). Both store a student's name and date of birth. List four distinct problems, and state for each which DBMS feature removes it.

A.

Problem Explanation DBMS remedy
Redundancy / inconsistency Name and DOB duplicated; a correction applied to one file only makes them disagree. Integration into a single STUDENT table; normalisation removes duplication.
Difficulty of access "List library members who are part-time students" spans two files with no common program. SQL join over STUDENT and MEMBER tables.
Data isolation Library uses DD-MM-YY text dates, admission uses a binary date; formats are incompatible. One DATE domain enforced by the DBMS type system.
Integrity Nothing stops the library from registering a member who was never admitted. FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno).

(Also acceptable: security — the library clerk can read the whole admission file if given access, whereas a DBMS could grant only SELECT(Rollno, Name).)

Solved Question 4

Q. Show, with a time-line, how two concurrent seat bookings can over-admit a student when files are used. Name the anomaly.

A. Let Vacant = 1 initially.

Time Transaction T1 (Clerk 1) Transaction T2 (Clerk 2) Stored value
t1 read(Vacant) returns 1 — 1
t2 — read(Vacant) returns 1 1
t3 test 1 > 0, so admit — 1
t4 — test 1 > 0, so admit 1
t5 Vacant := 1 - 1 = 0; write(0) — 0
t6 — Vacant := 1 - 1 = 0; write(0) 0

Two students have been admitted but Vacant fell by only one. T2's read happened before T1's write, so T1's update was overwritten and lost.

This is the lost update problem. A DBMS prevents it by making the read-modify-write sequence a transaction and serialising conflicting transactions with locks (Unit 6), so the observable result is the same as if T1 ran entirely before T2 — and T2 would then correctly see Vacant = 0 and refuse the admission.

Solved Question 5

Q. Give two situations where using a full DBMS is not justified. Justify each.

A.

  1. A hard real-time embedded controller — e.g. firmware in an anti-lock braking system. Every read must complete within a guaranteed microsecond bound. A DBMS adds query parsing, locking, logging and integrity checking to each access, and cannot give a hard worst-case bound. A fixed, hand-written memory structure is correct here.

  2. A single-user application with a tiny, frozen data structure — e.g. a program storing user preferences in a 2 KB configuration file, used by one person, whose structure will never change. The cost of installing, licensing and administering a DBMS vastly exceeds the benefit, and none of the file-system problems actually bite: there is no sharing, no concurrency, no ad-hoc querying and no redundancy.

(Other valid answers: a device with severe storage limits; a purely archival write-once dataset accessed by one batch program.)

Solved Question 6

Q. Explain the self-describing nature of a database and why it makes a DBMS general-purpose software.

A. A database stores, alongside the user data, a complete description of its own structure — table names, column names, data types, sizes, constraints, views, users and privileges. This description is called the catalog, data dictionary or metadata, and in SQL systems it is itself stored as tables (information_schema) which can be queried with ordinary SQL.

Consequence: the DBMS software contains no knowledge of any particular application. When it is asked to read STUDENT, it first reads the catalog to learn what STUDENT looks like, and then interprets the stored bytes accordingly. The same MySQL binary therefore runs a college database, a bank database and an airline database without a single line being changed or recompiled.

Contrast with file processing, where the record layout is compiled into the application program. There, a new kind of record means new code; and if the program is lost, the file becomes an undecipherable stream of bytes.

1.7 Exercises

A. Short answer (2 marks each)

  1. Define database and list the four properties implied by the definition.
  2. What is metadata? Where is it stored in a DBMS?
  3. Differentiate between redundancy and inconsistency.
  4. What is meant by a mini-world?
  5. Why is atomicity impossible to guarantee in a plain file system?
  6. Name any three components of a database system.

B. Long answer (5-6 marks each)

  1. Explain the problems of the file-processing approach, giving one college-domain example for each, and state the DBMS feature that solves it.
  2. "A DBMS is not always the right answer." Discuss the costs of the database approach and list four circumstances in which files are preferable.
  3. Explain the self-describing property of databases. How does it differ from the way a C program stores a record layout? Why does this make a DBMS general purpose?
  4. List and explain any eight advantages of the database approach over the file-processing approach.

C. Application and analytical

  1. A hospital keeps three files: patients.dat (Patient section), billing.dat (Accounts), pharmacy.dat (Pharmacy). All three store the patient's name, age and blood group. a. Identify every instance of redundancy. b. Construct a scenario in which the three files disagree about a patient's blood group, and state the possible real-world consequence. c. Redesign the data as an integrated set of tables and say which duplication disappears.
  2. For each requirement below, decide DBMS or plain file, and justify in one sentence: a. Storing the high-score list of a single-player mobile game. b. A university's examination result system for 60,000 students. c. A satellite's attitude-control telemetry buffer. d. An e-commerce site's order and inventory system.
  3. Write, in plain English, three questions about the college mini-world that a file-based system would need a new program for, but that a DBMS answers with one query. Then write the SQL for one of them.
  4. A bank transfers 10,000 rupees from account A to account B. Enumerate every low-level step, then mark the exact points at which a crash would leave the database inconsistent. Which ACID property is at stake?

D. Objective — state True or False with a one-line reason

  1. A DBMS eliminates all redundancy from an organisation's data.
  2. Metadata is stored in the application programs.
  3. The lost-update problem is a consequence of poor security.
  4. A DBMS always performs faster than direct file access for a single record read.
  5. Data isolation refers to keeping different users' data separate for privacy.
  6. Declaring CHECK (TotalSeats > 0) enforces the rule for every application that touches the table.

Answers to Section D

  1. False. It provides controlled redundancy; some is retained deliberately (indexes, materialised views) and kept synchronised.
  2. False. It is stored in the database's own catalog / data dictionary.
  3. False. It is a concurrency-control failure, not a security failure.
  4. False. Generality costs overhead; a raw file read of one known record can be faster. The DBMS wins on flexibility, integrity, sharing and complex queries.
  5. False. Data isolation means data is scattered across files in incompatible formats, making combined access hard.
  6. True. The constraint lives with the data, so every access path — SQL prompt, application, batch job — is checked.

Lesson Summary

  • A database is a shared, integrated, logically related, self-describing collection of data about a mini-world; a DBMS is the general-purpose software that manages it.
  • The file-processing approach fails on redundancy/inconsistency, difficulty of access, data isolation, integrity, atomicity, concurrency and security.
  • The DBMS answers with integration, SQL, a uniform model, declarative constraints, transactions, concurrency control and privileges.
  • The self-describing catalog is what makes a DBMS general purpose.
  • The database approach has real costs; it is not appropriate for simple, single-user, hard-real-time or storage-starved situations.