What you will learn
- Explain what a database stores and why structure matters
- Identify tables, rows, columns, primary keys and foreign keys
- Read simple SELECT queries and understand basic relationships
What is a database?
A database is an organized collection of information that a computer system can store, retrieve and update. A school might store students and courses. A shop might store products, customers and orders. A website may store accounts, posts and comments.
Many applications use a database so information remains available after a page is closed or a computer is restarted. The database is not the same as the application screen: it is the managed store of records behind the interface.
Tables, rows and columns
In a relational database, information is commonly organized into tables. A table represents one type of thing, such as a student or a book. Each row represents one record. Each column represents an attribute of that record.
For example, a Books table might contain book_id, title, author and published_year. A row could describe one book. The column type tells the database what kind of value is expected, such as text, a whole number or a date.
Why keys matter
A primary key is a field or set of fields used to identify a row uniquely. Two books may have the same title, so a library can use a separate book ID to distinguish them.
A foreign key is a value that refers to a key in another table. A Loans table can store a member ID that points to a Members table. This connects related records without repeating every member detail on each loan row.
Read a simple SQL query
SQL is a language used by many relational databases. A query asks the database to return or change information. This example retrieves books from a table:
SELECT title, author
FROM Books
WHERE published_year >= 2020
ORDER BY title;
SELECT names the columns to return. FROM identifies the table. WHERE filters the rows. ORDER BY sorts the result. The query does not change the stored records.
Connect information with a join
Suppose a library stores members separately from loans. The loan row contains the member ID, while the member's name is stored in Members. A join can combine the related information:
SELECT Members.name, Loans.due_date
FROM Loans
JOIN Members
ON Loans.member_id = Members.member_id;
The ON condition explains how the rows are related. A join does not mean that the tables have been permanently merged; it produces a result for the query.
Keep data trustworthy
Good database design reduces unnecessary duplication and helps protect accuracy. Use appropriate data types, define required fields, create relationships deliberately and validate values before saving them. Access controls should limit who can read or change records.
Applications should also keep user input separate from SQL command structure. Parameterized queries help reduce SQL injection risk; do not build a database command by joining untrusted text into the query string.
Worked example: a library loan
Imagine a library lends one book to one member. The system might store the book in Books, the member in Members and the borrowing event in Loans. The loan record links the other two using their IDs and records the checkout and due dates. This structure allows the library to find the member, identify the book and calculate which loans are overdue without copying full book and member details into every record.
Practice task
- Design a Students table with a unique student ID, name and email.
- Design a Courses table with a unique course ID and title.
- Design an Enrolments table that links a student to a course.
- Explain why the student name should not be the primary key.
- Write a SELECT query that displays the titles of all courses.
Quick review
- A table groups records of one type.
- A row is one record; a column is one attribute.
- A primary key identifies a record uniquely.
- A foreign key connects a record to another table.
- SQL queries retrieve or change data according to their commands.