Why databases exist and what a table really is

Start with the pain, not the definition

Every college department has that one file. students_marks_final_FINAL_v3.xlsx. It works, until it does not.

Here is what goes wrong, and it goes wrong in the same order every time.

Duplication. A student's name and phone number get typed again on the attendance sheet, the fees sheet and the marks sheet. Three copies of the same fact.

Update anomaly. The student changes her phone number. Someone updates one sheet. Now two sheets are lying, and you cannot tell which one.

Two people at once. The exam cell opens the file. The office opens the same file. Both save. One person's work silently disappears.

Crash in the middle. The power goes at 2 p.m. while the file is being written. The file is now half old and half new, and no one knows which half.

Searching. Finding one roll number in 200 rows is fine. In 2,00,000 rows, a linear scan is slow, and a spreadsheet has no idea how to be clever about it.

A database management system (DBMS) is a program written specifically to solve those five problems. That is the whole pitch. Not "storing data" — a text file stores data. A DBMS gives you safe, shared, fast, non-duplicated data.

What a table really is

Students learn "a table is rows and columns" and stop there. Be more precise, because the precision is what makes SQL make sense later.

A table is a set of rows, where every row has the same named, typed columns, and every row states one fact about one thing.

Read that again with the three important words.

Set — a set has no order. If you do not write ORDER BY, the database is allowed to hand you rows in any order it likes, and it will change its mind between runs. This surprises people in the lab exam.

Typed — a column is not just "text". It is INT or VARCHAR(60) or DATE. The type is a promise the database enforces. You physically cannot put "hello" into an INT column, so that bug can never reach production.

One fact about one thing — a row in students describes exactly one student. Not a student and her three subject marks stuffed into one row. That rule alone prevents most of the mess you will meet in the normalisation lesson.

Making a real table

CREATE TABLE students (
    roll_no   INT PRIMARY KEY,
    name      VARCHAR(60) NOT NULL,
    branch    VARCHAR(10) NOT NULL,
    year      INT NOT NULL,
    cgpa      DECIMAL(3,2)
);

INSERT INTO students (roll_no, name, branch, year, cgpa) VALUES
    (101, 'Anita Sharma',  'CSE', 2, 8.40),
    (102, 'Ravi Kumar',    'CSE', 2, 7.10),
    (103, 'Meera Nair',    'ECE', 3, 9.05),
    (104, 'Imran Qureshi', 'CSE', 1, 6.80);

Look at what you just declared, not just what you typed.

roll_no INT PRIMARY KEY says: this column identifies the row, it can never repeat, and it can never be empty. Try inserting a second student with roll 101 and the database refuses. The rule now lives in the data, not in someone's memory.

name VARCHAR(60) NOT NULL says a name is text of at most 60 characters and must be present.

cgpa DECIMAL(3,2) says three total digits, two after the decimal point. So 8.40 and 9.05 fit, 10.00 does not. Use DECIMAL for money and marks, never FLOAT — floating point cannot represent 0.1 exactly and your fee totals will be off by paise.

Rows, columns, and the language

SQL is a declarative language. You describe the result you want. You do not describe the loop. There is no for anywhere in SQL. You say "give me the CSE students in year 2, sorted by cgpa", and the database decides how to find them — index scan, table scan, whatever is cheaper today.

That is why SQL feels strange at first if you came from C. In C you tell the machine the steps. In SQL you state the goal and let the query planner pick the steps.

In the next lesson you will pull data back out of this exact table.