Databases Explained: Tables, Keys and SQL for Beginners

What is a database, really?

A database is an organised collection of data that a program can search, update and protect reliably. A spreadsheet is fine for a short list, but it breaks down when thousands of users, linked records and simultaneous edits are involved. Databases solve that: they store data in tables, enforce rules about what can be stored, and answer questions with a language called SQL (Structured Query Language, pronounced “sequel”).

Tables, rows and columns — the spreadsheet you already understand

A table is like one sheet in a spreadsheet. Each column is one kind of information (name, semester, marks). Each row is one record (one student). A students table might have columns id, name and semester; a courses table might have id, title and credit_hours. So far, so familiar — the power comes from the next two ideas.

Primary keys — every row needs a unique identity

A primary key is the column (or columns) that uniquely identifies one row. Two students can share a name, but they cannot share a student ID. That is why the ID, not the name, is the primary key. Without a primary key, you cannot reliably say “update this student” — you might update the wrong person with the same name.

Foreign keys and relationships — how tables connect

When one student takes many courses, you do not copy the student's name into every course row — you use a third table, enrolments, with two foreign keys: student_id pointing at the students table, and course_id pointing at the courses table. A foreign key is simply a column whose values must match a primary key in another table. If you try to enrol a student who does not exist, the database refuses — that refusal is a feature, not a bug. It protects your data from nonsense.

Your first SQL — four verbs that do almost everything

-- Read data
SELECT name, semester FROM students WHERE semester = 3;

-- Add data
INSERT INTO students (id, name, semester) VALUES (7, 'Ayesha', 3);

-- Change data
UPDATE students SET semester = 4 WHERE id = 7;

-- Remove data
DELETE FROM students WHERE id = 7;

SELECT reads, INSERT adds, UPDATE changes, DELETE removes — together they are called CRUD. WHERE filters rows, ORDER BY sorts them, and JOIN combines tables through their keys:

SELECT students.name, courses.title, enrolments.marks
FROM enrolments
JOIN students ON enrolments.student_id = students.id
JOIN courses ON enrolments.course_id = courses.id;

That one query answers a question no single table could: which student got which marks in which course.

Why not just one giant table?

Putting everything in one table means repeating the same course title on every student's row. Change the title once and you must change it everywhere — miss one copy and your data now disagrees with itself. Splitting data into linked tables, called normalisation, keeps each fact in exactly one place. It is the same idea as not writing your address on every page of a notebook when one address page will do.

Practice task (45 minutes)

Design a library database on paper: tables for books, members and loans, with primary keys marked and foreign keys drawn as arrows. Write five queries in words first (“all books borrowed by member 3”, “average loan length per book”), then in SQL. Practise it hands-on in Lab 9, using invented data only — never real people's names or ID numbers in a practice database.

Common mistakes to avoid

• Using names as keys — names repeat, IDs do not
• One giant table with the same facts copied on every row
• Forgetting WHERE in UPDATE or DELETE and changing every row by accident — always test with SELECT first
• Typing real personal data into practice databases, screenshots or AI tools