Two people open a railway booking app at the same moment. Both see one berth left on the same train, and both press Book. Only one can get it. How does the system decide who, make sure the berth is never sold twice, and still remember the booking if the server crashes a second later?
That is the question databases exist to answer: how can many people store, search and change a large amount of shared data without ever making it wrong? This lesson shows why plain files fail and what Codd’s relational model changed.
What goes wrong with plain files
Picture a school office in Jind that keeps three Excel files: admissions, fees and marks. Each file repeats the student’s name, class and parent’s phone number. Trouble starts quickly:
- Redundancy: the same fact is stored in many places, so work is repeated.
- Inconsistency: a phone number is corrected in one file but not in the others, so the files disagree.
- Hard questions: “Which students with pending fees scored below 40?” means matching three files by hand.
- Clashes: two clerks open the same file, and the last one to save silently erases the other’s work.
- No rules: nothing stops a mark of 450 out of 100, or a fee for a student who does not exist.
- Weak safety: anyone who can open the file can read all of it, and a crash during saving can damage it.
The phone number that changed
Aarav’s father changes his phone number. The admissions clerk updates the admissions file, but the fees file keeps the old number, so the next fee reminder goes to a stranger. A file cannot warn the clerk, because it does not know that two rows describe one person. A database stores the number once.
Database, DBMS and the relational model
Data means raw facts, such as a name or a mark. A database is an organised collection of related data that many users and programs can share. A DBMS (Database Management System) is the software that stores the data, protects it and answers questions about it. MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server and SQLite are all DBMS products. A good DBMS provides:
- a query language, a way to ask for data without writing a search program;
- integrity rules that reject wrong data;
- concurrency control, so many users can work together safely;
- security, with separate accounts and permissions;
- backup and recovery, so saved data survives a crash.
Before 1970, databases joined records with physical pointers, and a program had to follow them record by record. If the layout on disk changed, the program broke.
In 1970, Edgar F. Codd, a researcher at IBM, published a paper titled A Relational Model of Data for Large Shared Data Banks. His idea: keep all data in plain tables, which he called relations. Let people say what they want, and let the system work out how to fetch it. Keeping the user’s view apart from physical storage is called data independence. A database built on this model is a relational database, and its software is an RDBMS. IBM tested the idea in a research system whose query language grew into SQL, an ANSI standard since 1986. Codd received the Turing Award in 1981.
Codd’s words differ a little from everyday ones:
| Everyday word | Formal word | Meaning |
|---|---|---|
| Table | Relation | rows about one kind of thing |
| Row | Tuple | one item, such as one student |
| Column | Attribute | one property, such as city |
| Allowed values | Domain | what a column may hold |
| Number of columns | Degree | how wide the table is |
| Number of rows | Cardinality | how many items it holds |
SQL and MySQL
SQL (Structured Query Language, said “S-Q-L” or “sequel”) is the standard language of relational databases. MySQL, first released in 1995 and now owned by Oracle Corporation, is a free and widely used RDBMS. MariaDB is a community-built relative that behaves almost the same, and it is what XAMPP, a free package popular with students, installs. Everything in this course works on both.
NoSQL systems, such as MongoDB (documents) and Redis (key-value pairs), store data in other shapes. For clearly structured data like marks, accounts and bookings, a relational database is still the usual choice.
Key points
- A database is an organised, shared collection of data; a DBMS is the software that manages it.
- Plain files cause redundancy, inconsistency, clashes and weak safety; a DBMS prevents them with rules, controls and recovery.
- In 1970 E. F. Codd proposed the relational model: data in simple tables, with users saying what they want and the system deciding how.
- SQL is the standard language of relational databases, and MySQL is the free, popular RDBMS used in this course.