Databases: models, DBMS and related tables — Computer Science

In this lesson you will learn

Tell apart a database, a DBMS and the DBMS objects (tables, queries, forms, reports)
Recognise hierarchical, network and relational models from an example
Find the fields, records and key field of a table and decide the type of a relationship (1:1, 1:N, M:N)
Solve DİM-style questions on related tables step by step

A school library has a thousand books and hundreds of readers. The librarian answers “who took which book this month?” in a few seconds, because the information is kept not in a notebook but in a database. An electronic school register, bank accounts and an online shop catalogue work the same way.

In the informatics part of the entrance exam, every 2025–2026 paper had 2 database tasks: one on a query and one on related tables. In this lesson you learn the concepts and a method for related-table questions; the next lesson, “Database queries, searching and sorting”, covers queries.

Databases and DBMS

Definition

Database

A collection of data on one subject, organised by rules planned in advance and stored in a computer. Thanks to the rules, the information you need can be found, added and changed quickly.

Definition

Database management system (DBMS)

A program for creating a database, filling and editing it, searching and sorting in it and protecting the data. Examples: Microsoft Access, LibreOffice Base, MySQL, PostgreSQL, SQLite, Oracle Database, Microsoft SQL Server.

Remember the difference: a database is the data itself (the library catalogue), and a DBMS is the tool that works with it (the librarian who keeps the catalogue). In the classification of software, a DBMS is application software. In one DİM matching task Yukon was also given as a DBMS: it is the code name of Microsoft SQL Server 2005. A DBMS has four main objects:

Object	What it is for	Example
Table	stores the data in rows and columns; the core of the database	“Readers”, “Books”
Query	selects records that meet a condition, sorts them, calculates	readers who live on Nizami Street
Form	a window for convenient data entry and viewing	registration form for a new reader
Report	presents data as a grouped document with totals, ready to print	list and number of books lent during a month

Coded task: matching

Match the items.
1. Database management system
2. Raster graphics editor
3. Word processor
a. Microsoft Access b. GIMP c. LibreOffice Writer d. PostgreSQL e. Adobe Photoshop

Show solution

Access and PostgreSQL manage databases; GIMP and Photoshop edit images made of pixels (raster); LibreOffice Writer is a word processor. One item may match several letters.
Answer: 1 – a, d; 2 – b, e; 3 – c.

Data models

A data model is the structure that shows how data and the links between them are organised. The programme names three classic models:

Model	Structure	Example
Hierarchical	a tree: one root at the top, every element has exactly one “parent”	disk → folder → subfolder → file; school → classes → pupils
Network	a graph: an element may be linked to many elements and have several “parents”	teachers and classes: each teacher in several classes, each class with several teachers
Relational	a set of two-dimensional tables linked by common fields	the tables “Readers”, “Books”, “Loans”

Today the most widespread model is the relational one: all the DBMSs named above work with tables. Trees and graphs themselves are covered in detail in the lessons “Tree information models” and “Graph information models: adjacency matrices and counting paths”.

A table: fields, records and the key field

Definition

Field

A column of a table. It stores one property of an object (Reader, Street). Every field has a name and a type.

Definition

Record

A row of a table: all the information about one object (one reader, one book). The header row with the field names is not a record.

Definition

Key field

A field whose value never repeats in two records, so it identifies a record uniquely (the primary key): Card_No, Book_ID. A field in another table that stores the values of this key is called a foreign key.

Field type	What it stores	Example
Text	letters, digits, symbols	name, street, phone number
Number	numbers used in calculations	number of pages, score
Date/time	dates and times	15.09.2025
Currency	amounts of money	12.50 ₼
AutoNumber (counter)	a growing number given automatically to each new record	1, 2, 3, …
Yes/No (logical)	one of two values: yes / no	book returned?

Fields, records and the key

The header row of the “Pupils” table has 5 field names: ID, First name, Last name, Class, Date of birth; below it 7 rows are filled in. 1) How many fields and records does the table have? 2) Which field can be the key? 3) What is the type of the “Date of birth” field?

Show solution

1) Field = column: 5 fields. Record = row below the header: 7 records (the header row does not count).
2) First name, last name, class and even the date of birth can be the same for two pupils. The ID given to each pupil never repeats, so the key field is ID.
3) Date/time.

Relationships between tables

If we put everything into one table, Aysel’s street would be written again every time she borrows a book: this wastes space and invites mistakes, because a change of address would have to be fixed in dozens of rows. So each kind of object is kept in its own table, and the tables are linked through key fields. There are three kinds of relationship:

One-to-one (1:1): one record of the first table matches at most one record of the second — a citizen and their ID card.
One-to-many (1:N): one record of the first table matches many records of the second, but not the other way round — a class and its pupils.
Many-to-many (M:N): many on both sides — readers and books. In a relational database it is built with a third, link table: it holds the keys of both tables as foreign keys, so M:N turns into two 1:N relationships.

The M:N relationship between readers and books is split by the “Loans” table into two 1:N relationships. Key fields are shown in violet.

Every row of the “Loans” table is one event: “this reader took this book”. The same reader or book may appear in several rows — these are not duplicates but separate loans.

Interactive

Loading simulation…

Check yourself: drop each pair into the right group and press “Check”.

DİM-style questions on related tables

The task gives 2–3 related tables and asks: how many times, how many people, the sum of IDs, the total of days. A DBMS does it with one query; in the exam you do it by hand. The safest way is to write the keys down as sets.

1
Analyse the question
What is asked (a count, a sum, a name) and what are the conditions (street, author, class)?
2
Turn the conditions into keys
For each condition pick the matching keys in its own table and write them as a set, e.g. {A1, A3, A5}.
3
Filter the link table
Mark only the rows that match both sets.
4
Do the operation
Count the rows (how many times), count different people (how many people) or add up the needed column.
5
Check
Were repeated rows counted separately? Was the question “how many times” or “how many people”?

Card_No	Reader	Street
A1	Aysel	Nizami Street
A2	Murad	Fuzuli Street
A3	Leyla	Nizami Street
A4	Elvin	Samad Vurgun Street
A5	Nigar	Nizami Street
A6	Rashad	Fuzuli Street

Table 1. Readers

Book_ID	Author	Pages
11	Nizami Ganjavi	320
12	Jules Verne	280
13	Nizami Ganjavi	240
14	Mirza Fatali Akhundov	150
15	Jules Verne	410

Table 2. Books

№	Card_No	Book_ID
1	A1	12
2	A2	11
3	A3	15
4	A1	15
5	A4	12
6	A5	13
7	A3	12
8	A6	15
9	A1	11
10	A5	12
11	A3	15
12	A2	14

Table 3. Loans

DİM-style task: how many times?

According to Tables 1–3, how many times in total did the residents of Nizami Street borrow books by Jules Verne?
A) 4 B) 5 C) 6 D) 3 E) 7

Show solution

1) Table 1: Nizami Street → {A1, A3, A5}.
2) Table 2: Jules Verne → {12, 15}.
3) Rows of Table 3 that meet both conditions: 1 (A1, 12), 3 (A3, 15), 4 (A1, 15), 7 (A3, 12), 10 (A5, 12), 11 (A3, 15).
4) The question is “how many times”, so we count rows: 6. (Leyla took book 15 twice — two separate loans.)
Correct answer: C) 6.

A sum: the number of pages

According to Tables 1–3, if Leyla read every book she borrowed, how many pages did she read in total? Each loan counts separately.

Show solution

1) Table 1: Leyla → A3.
2) Table 3: rows with A3 — 3 (book 15), 7 (book 12), 11 (book 15).
3) Table 2: 15 → 410 pages, 12 → 280 pages.
4) 410 + 280 + 410 = 1100 pages.
Trap: counting book 15 only once gives 690, which is wrong.

Python· Edit the code and run it again

readers = {'A1': 'Nizami', 'A2': 'Fuzuli', 'A3': 'Nizami',
           'A4': 'Vurgun', 'A5': 'Nizami', 'A6': 'Fuzuli'}
books = {11: ('Nizami Ganjavi', 320), 12: ('Jules Verne', 280),
         13: ('Nizami Ganjavi', 240), 14: ('Mirza Fatali Akhundov', 150),
         15: ('Jules Verne', 410)}
loans = [('A1', 12), ('A2', 11), ('A3', 15), ('A1', 15), ('A4', 12), ('A5', 13),
         ('A3', 12), ('A6', 15), ('A1', 11), ('A5', 12), ('A3', 15), ('A2', 14)]

nizami = {card for card, st in readers.items() if st == 'Nizami'}
verne = {book for book, (author, pages) in books.items() if author == 'Jules Verne'}
print('Readers:', sorted(nizami), 'Books:', sorted(verne))

times = sum(1 for card, book in loans if card in nizami and book in verne)
print('Loans of Verne books by Nizami St. readers:', times)

pages = sum(books[book][1] for card, book in loans if card == 'A3')
print('Pages read by A3 (Leyla):', pages)

▸ Expected output

Readers: ['A1', 'A3', 'A5'] Books: [12, 15]
Loans of Verne books by Nizami St. readers: 6
Pages read by A3 (Leyla): 1100

The same problem in Python: the conditions become sets, then the rows of the link table are filtered. Change a value and press Run. In SQL, JOIN does this — see “JOIN: combining tables”.

Trap: “grade 10 classes”

Classes (Class_ID — class name): 1 — 9a, 2 — 10a, 3 — 10b, 4 — 11a, 5 — 10c.
Pupils (name — Class_ID — height): Kamran — 2 — 172 cm, Fidan — 4 — 168 cm, Tural — 3 — 181 cm, Sabina — 5 — 165 cm, Orkhan — 1 — 175 cm, Gunay — 3 — 169 cm, Kanan — 5 — 177 cm.
How many pupils in the grade 10 classes are taller than 170 cm?

Show solution

1) “Grade 10 classes” are 10a, 10b and 10c, i.e. Class_ID ∈ {2, 3, 5}. Taking only 10a is the most common mistake.
2) Pupils of these classes: Kamran (172), Tural (181), Sabina (165), Gunay (169), Kanan (177).
3) Taller than 170 cm: Kamran, Tural, Kanan.
Answer: 3. (Only 10a would give 1; forgetting the class condition gives 4.)

Key points

A database is the data itself; a DBMS is the program that works with it (Access, MySQL, PostgreSQL, SQLite); a DBMS is application software.
DBMS objects: a table stores, a query selects, a form helps to enter data, a report presents it for printing.
Models: hierarchical — a tree, network — a graph, relational — linked tables.
Field = column, record = row (the header does not count), a key field never repeats.
An M:N relationship is split by a link table into two 1:N relationships; each of its rows is one event and counts separately.
Related-table task: conditions → sets of keys → filter the link table → count or sum.

Check yourself

12 questions. Every correct answer earns XP.

1 / 12

What is a DBMS?

№	Card_No	Book_ID
1	A1	12
2	A2	11
3	A3	15
4	A1	15
5	A4	12
6	A5	13
7	A3	12
8	A6	15
9	A1	11
10	A5	12
11	A3	15
12	A2	14

№	Card_No	Book_ID
1	A1	12
2	A2	11
3	A3	15
4	A1	15
5	A4	12
6	A5	13
7	A3	12
8	A6	15
9	A1	11
10	A5	12
11	A3	15
12	A2	14

№	Card_No	Book_ID
1	A1	12
2	A2	11
3	A3	15
4	A1	15
5	A4	12
6	A5	13
7	A3	12
8	A6	15
9	A1	11
10	A5	12
11	A3	15
12	A2	14