Database basics

A database (DB) is a collection of related data organized by certain rules and stored on a computer. A school register, a library catalog and the product list of an online shop are all examples of databases. Unlike storing data in files without order, a database keeps it without repetition and in a form that allows quick searching and changing.

The most widespread model is the relational model: data is stored in tables. Each column of a table is a field (for example name, class, year of birth), and each row is a record (the full information about one student). Every field has a type: integer, text, date. To identify each record uniquely, a primary key is set, a field whose values never repeat (usually id).

The tables of a database are linked to one another. For example, in the “Students” table each student has an id, and in the “Grades” table the field student_id refers to that id; such a field is called a foreign key. Because of this, a student’s name is not written in the grades table again and again: the data is stored in one place and stays correct everywhere when changed.

Creating a database, filling it and accessing it is done by a database management system (DBMS). Well-known systems are SQLite, MySQL and PostgreSQL. A DBMS maintains data integrity (for example it does not allow duplicate primary keys), supports many users working at once, makes backups and manages access rights. The SQL language is normally used to access the data.

Key takeaways

A database is an organized collection of related data.
In the relational model data is stored in tables: a column is a field, a row is a record.
A primary key identifies each record uniquely.
A foreign key links tables and reduces repetition of data.
A DBMS (SQLite, MySQL, PostgreSQL) manages the database and maintains data integrity.

Practice tasks

Design a “Students” table for your class: define at least 4 fields (with name and type) and mark the primary key. Enter 3 records in the table.
Make two tables for a school library: “Books” and “Borrowed books”. Decide which field will be the foreign key and explain why the book title should not be repeated in the second table.
Explain which of the following fields could be a primary key: first name, last name, date of birth, passport number, serial number (id).

Review questions

Test yourself: think first, then reveal the answer.

1What are a field and a record in a table?Show answer

A field is a table column (a kind of data); a record is a table row (the full information about one object).

2How does a primary key differ from a foreign key?Show answer

A primary key uniquely identifies a record in its own table; a foreign key refers to the primary key of another table and links the tables.