Introduction
Before SQL, before tables — it's worth understanding why databases exist at all, by seeing what goes wrong with the alternative: storing data in plain, separate files.
Concept
A File Processing System (the "before" picture) stores related data in separate files managed by individual programs, with no central coordination. This causes real, recurring problems:
- Data Redundancy — the same data gets duplicated across multiple files (a student's name might be typed into a marks file, an attendance file, and a fees file separately).
- Data Inconsistency — a direct consequence of redundancy: if that student's name is updated in one file but not the others, the same real-world fact now disagrees with itself across files.
- Data Isolation — related data scattered across incompatible files/formats is hard to combine or cross-reference.
A DBMS (Database Management System) solves these by centralizing data in one place, under one system, so every program and user sees the exact same, single, consistent copy. Beyond fixing redundancy and inconsistency, a DBMS also adds:
- Security — access can be restricted, so only authorized users see or change specific data.
- Backup and Recovery — built-in tools to save and restore data if something goes wrong.
- Integrity Constraints — rules (like "Marks cannot be negative") the DBMS enforces automatically, rejecting bad data before it's ever stored.
- Concurrent Access — multiple users or programs can safely use the data at the same time, without one user's changes corrupting another's.
Explanation
Notice the cause-and-effect chain: redundancy (duplicated data) is what causes inconsistency (disagreeing copies) — they're not two unrelated problems, one leads directly to the other, which is exactly why eliminating redundancy is a DBMS's central goal.
Note: "Different users see the same, consistent data" is solved specifically by removing Data Inconsistency — a DBMS keeps exactly one authoritative copy of each fact, instead of many private, drifting copies.
Summary
File Processing Systems suffer from data redundancy (duplicated data) which causes data inconsistency (disagreeing copies), plus data isolation across incompatible files. A DBMS centralizes data into one consistent, shareable system, eliminating these problems while adding security, backup/recovery, integrity constraints, and safe concurrent access.
Why not just use Excel?
Before databases (SQL), we stored data in flat files (like Notepad, Excel, or CSV). This is called a File System. It had major disadvantages that you must memorize for 2-mark theory questions.
The 3 Major Problems Solved
1. Data Redundancy
Definition: Duplication of data.
In a file system, the same student's name and address might be stored in the "Admission File" AND the "Library File". If you have 1000 students, this wastes huge amounts of storage space.
DBMS Solution: Stores data in one place, referenced by everyone.
2. Data Inconsistency
Definition: Mismatch of data.
If a student changes their phone number, and you update it in the "Admission File" but forget to update it in the "Library File", the data becomes inconsistent (different values for the same person).
DBMS Solution: Since data is stored in one place, an update happens everywhere automatically.
3. Data Isolation
Definition: Difficulty in accessing data.
In a file system, data is scattered across different formats (some in Excel, some in Text). Writing a program to combine them is very hard.
DBMS Solution: SQL provides a standard way to access all data easily.
Case Study: The School Office
Imagine a school where the Accountant has an Excel sheet for Fees, and the Class Teacher has a Register for Attendance.
- The Problem: Student "Ravi" moves to a new house. He tells the Class Teacher. The Teacher updates the Register. But the Accountant doesn't know.
- The Result: The Accountant sends the Fee Bill to the old address. This is Data Inconsistency.
- The Fix: Use a Central School Database. Both the Teacher and Accountant look at the same "Student Address" record.
Introduction
The relational model is the theory behind every SQL table you'll write in this chapter — it gives precise, formal names to what you'd otherwise just call "a table," "a row," and "a column."
Concept
- Relation — the formal term for a table.
- Tuple — the formal term for a row (one single record).
- Attribute — the formal term for a column (one field).
- Degree — the number of attributes (columns) in a relation. A relation must have at least one attribute — degree can never be zero.
- Cardinality — the number of tuples (rows) in a relation. Cardinality changes constantly as rows are added or removed; Degree stays fixed unless the table structure itself is altered.
- Domain — the set of all permissible values an attribute can hold (e.g., a "Marks" column's domain might be every integer from 0 to 100).
Explanation
Degree and Cardinality are the pair most often confused — and the most reliable way to keep them straight is: Degree is about the table's structure (how many columns it was designed with), Cardinality is about its current data (how many rows happen to be in it right now). Deleting rows changes Cardinality but never Degree.
Note: If a table has Cardinality 10 and you DELETE 2 rows, the new Cardinality is 8 — a direct subtraction, since Cardinality just counts current rows.
Summary
A Relation is a table, a Tuple is a row, an Attribute is a column. Degree counts attributes (fixed by structure, minimum 1), Cardinality counts tuples (changes as rows are added or removed), and Domain is the set of valid values an attribute may hold.
Speak Like a Database Admin
The "Relational Model" stores data in Tables. However, in exams, we use formal mathematical terms for the parts of a table.
| Common Name | Formal Name | Exam Definition |
|---|---|---|
| Table | RELATION | A collection of data organized in rows and columns. |
| Row | TUPLE | A single horizontal record in a relation. |
| Column | ATTRIBUTE | A vertical data field or property of the relation. |
| Count of Columns | DEGREE | The number of attributes (columns) in a relation. |
| Count of Rows | CARDINALITY | The number of tuples (rows) in a relation. |
Introduction
A table is only useful if you can uniquely identify one specific row out of possibly thousands — and reliably connect rows across different tables. That's the entire job of database keys.
Concept
- Super Key — any column, or combination of columns, that can uniquely identify each row — even if it includes extra columns that aren't actually needed for uniqueness.
- Candidate Key — a minimal Super Key: it uniquely identifies each row using the fewest possible columns, with nothing redundant left in it. A table can have several candidate keys.
- Primary Key — the ONE candidate key actually chosen to uniquely identify rows. A table can have many candidate keys, but only ever one primary key. It can never contain NULL.
- Alternate Key — every candidate key that was NOT chosen as the primary key.
- Composite Key — a Primary (or Candidate) Key made up of more than one column together, used when no single column is enough to uniquely identify a row on its own.
- Foreign Key — a column in one table that refers to the Primary Key of another table, creating a link between the two.
Explanation
Foreign Keys have one property that surprises people: unlike a Primary Key, a Foreign Key is allowed to contain NULL — because not every row necessarily needs to reference a matching row in the other table. A student record with no assigned class yet, for example, could have a NULL foreign key for "ClassID" until one is assigned. The rule that a Foreign Key's value must either be NULL or actually match an existing row in the other table is called Referential Integrity — it's what stops a table from linking to a row that doesn't exist.
Note: Primary Key = never NULL, uniquely identifies THIS table's own rows. Foreign Key = CAN be NULL, refers to another table's Primary Key. Mixing up which one allows NULL is one of the most common exam mistakes.
Summary
A Super Key is anything that can uniquely identify a row; a Candidate Key is a minimal Super Key; the Primary Key is the one Candidate Key chosen (never NULL, sometimes a Composite Key of multiple columns); the rest become Alternate Keys; a Foreign Key links to another table's Primary Key, is allowed to be NULL, and Referential Integrity is the rule that keeps that link honest.
Why do we need Keys?
In a database, every single row (tuple) must be unique. You cannot have two identical records, or the computer won't know which one to update.
Keys are the attributes (columns) used to identify these rows uniquely.
The Key Hierarchy (Detailed Definitions)
1. Candidate Key (The "Potentials")
A Candidate Key is an attribute (or a set of attributes) that represents a minimal set capable of uniquely identifying a record.
- Keyword: "Minimal". (It shouldn't have extra unnecessary columns).
- Note: A table can have multiple Candidate Keys.
- Example: In a classroom, 'RollNo', 'AadharNumber', and 'Email' are all Candidate Keys because each is unique to a student.
2. Primary Key (The "Chosen One")
The Primary Key (PK) is the one specific Candidate Key selected by the database designer to be the main identifier for the table.
- Constraint 1: It must be UNIQUE (No duplicates).
- Constraint 2: It can NEVER BE NULL (Every row must have a value).
- Limit: There can be only ONE Primary Key per table.
3. Alternate Key (The "Leftovers")
An Alternate Key is any Candidate Key that was not selected as the Primary Key.
Formula: Alternate Keys = Candidate Keys - Primary Key
Key Differences Table
| Key Type | Role | Can it be Null? | How many per table? |
|---|---|---|---|
| Candidate Key | Capable of identifying rows. | Usually No (but technically allows Null if not PK). | One or Many. |
| Primary Key | The official identifier. | NEVER. | Only ONE. |
| Foreign Key | Links to another table's Primary Key. | YES (Values can be Null). | Zero, One, or Many. |
4. Foreign Key (Referential Integrity)
A Foreign Key is a column in one table that refers to the Primary Key of another table.
- Purpose: To establish a relationship between two tables.
- Rule: The value in the Foreign Key column must either match a value in the linked Primary Key column OR be NULL. It cannot have a value that doesn't exist in the main table.
- Example: If you have a `Student` table and a `Course` table, the `CourseID` inside the Student table is a Foreign Key linking to the Course table.