Database Concepts – Database Design (Senior 4 ICT) | Ganzaa

Database Design

Lessons

Senior 4 · ICT

Database Design

5 lessons

Lesson 1 of 5

Database Concepts

25 min

One student, many lists

Think of everything a secondary school records about one student: her name and class in the admissions register, her fees in the bursar's book, her marks in each teacher's mark sheet, the books she has borrowed from the library, the club she belongs to. Kept in separate exercise books or spreadsheets, the same facts are written again and again. When she changes class or phone number, someone must find and correct every copy, and sooner or later one copy is missed. A database stores each fact once, in an organised way, so that every user and every program works from the same data. In Senior 1 you learned that data are raw facts and information is data that has been processed to have meaning. This unit is about designing the structure that holds the data.

Definition

Database: an organised collection of related data, stored so that it can be searched, updated and shared easily. Example: a school's records of its students, classes, marks and fees.

Database management system (DBMS): the software that creates a database, stores the data, and controls who can read and change it. Examples: SQLite, MySQL, PostgreSQL, Microsoft Access, Oracle Database, Microsoft SQL Server.

Table: data about one kind of thing, arranged in rows and columns. Record (row): all the data about one item, such as one student. Field (column): one kind of data stored for every item, such as the class.

Entity: a thing about which data is stored (a student, a book, a class). Attribute: a property of an entity (the student's first name). Each entity normally becomes a table and each attribute a field.

Query: a request to the DBMS to find, add, change or delete data. SQL (Structured Query Language) is the standard language for queries on relational databases.

Inside a table

Why not just use separate files?

Keeping data in separate, unconnected files (a flat-file approach) causes predictable problems:

Data redundancy: the same data is stored more than once, wasting space and typing time.
Data inconsistency: copies of the same fact disagree, so nobody knows which is true.
Hard to search and combine: answering "which S4B students owe fees and have overdue books?" means comparing files by hand.
Weak security: a whole file is either shared or not; you cannot let the librarian see loans but not fees.

What a DBMS does

Function	What it means for the school
Stores data once and links it	A student's details live in one place; other tables refer to them
Enforces integrity rules	It refuses a mark of 150 out of 100, or a loan for a student who does not exist
Controls access	The librarian sees loans; only the bursar changes fees
Allows many users at once	Ten teachers can enter marks at the same time without overwriting each other
Answers queries quickly	"All S4 students born in 2010" takes milliseconds, even with thousands of records
Backs up and recovers	After a power cut or a disk failure, the data can be restored

The costs are real too: a DBMS must be installed and looked after, its design takes skill, and a badly designed database spreads mistakes as efficiently as a good one prevents them. That is why design is the subject of this unit.

Data types

Every field holds one type of data. Choosing the right type lets the DBMS check entries and calculate with them.

Type (common name)	Holds	Example field
INTEGER	Whole numbers	StudentID, NumberOfCopies
DECIMAL or REAL	Numbers with a fractional part	Price, AverageMark
VARCHAR(n) or TEXT	Text of up to n characters	FirstName, Class
DATE	A calendar date	BirthDate, DateBorrowed
BOOLEAN	True or false	FeesPaid

A phone number such as 0788 100 200 is stored as text, not as a number: it starts with 0, contains spaces, and nobody adds phone numbers together. SQLite, the DBMS in these lessons, stores values in five classes (NULL, INTEGER, REAL, TEXT and BLOB) and keeps dates as text in the form YYYY-MM-DD.

Try it: a first table in SQLite

The code box below runs in SQLite, a small, free DBMS whose code is in the public domain and which, according to its developers, is the most widely deployed database engine in the world: it is built into every Android phone and iPhone and into the major web browsers. You do not need to learn SQL in this unit, but seeing a design hold real data makes it concrete. CREATE TABLE defines the structure, INSERT adds records and SELECT asks questions.

sql

-- A first table: one row for each student
CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  FirstName TEXT NOT NULL,
  LastName  TEXT NOT NULL,
  Class     TEXT,
  BirthDate TEXT
);

INSERT INTO Student VALUES (1001, 'Aline', 'Uwase',    'S4A', '2010-03-14');
INSERT INTO Student VALUES (1002, 'Eric',  'Mugisha',  'S4B', '2009-11-02');
INSERT INTO Student VALUES (1003, 'Grace', 'Ingabire', 'S4A', '2010-07-21');
INSERT INTO Student VALUES (1004, 'Jean',  'Habimana', 'S4B', '2010-01-30');

-- Every field of every record
SELECT * FROM Student;

-- Only two fields, only the records in S4A
SELECT FirstName, LastName FROM Student WHERE Class = 'S4A';

-- How many students are in each class?
SELECT Class, COUNT(*) AS Students FROM Student GROUP BY Class;

Code Explanation

Press Run. Each statement gets its own result card. The first SELECT shows all 4 records and 5 fields; the second picks two fields of the records whose Class is S4A; the third counts the students in each class. The names and dates are illustrative.

Worked example 1: from a description to a table

"For every book, the school library records its ISBN, title, author, year of publication and the number of copies it owns."

Entity: Book, so the table is called Book.
Attributes, which become fields: ISBN, Title, Author, YearPublished, Copies.
Types: ISBN as TEXT (it can contain hyphens and an X and is never used in arithmetic); Title and Author as TEXT; YearPublished and Copies as INTEGER.
One record might be: 978-0-00-000000-2, Things Fall Apart, Chinua Achebe, 1958, 4. (The ISBN here is a placeholder.)

If the library owns 350 different titles, the table has 350 records and 5 fields, whatever the number of copies.

Worked example 2: what is wrong with this spreadsheet?

Borrower	Class	Phone	Book	Date borrowed
Aline Uwase	S4A	0788 100 200	Things Fall Apart	2026-09-01
Eric Mugisha	S4B	0788 300 400	Weep Not, Child	2026-09-01
Aline Uwase	S4A	0788 100 200	Arrow of God	2026-09-08
Aline Uwase	S4B	0788 555 000	No Longer at Ease	2026-09-15

Redundancy: Aline's name, class and phone are typed three times.
Inconsistency: the last row gives her a different class and phone. Is she in S4A or S4B? The spreadsheet cannot tell.
Ambiguity: two different students called Aline Uwase could not be told apart, because nothing identifies a student uniquely.

The cure, developed in this unit, is to store students once in their own table with a unique StudentID, and let each loan refer to that ID (data is illustrative).

IBM Technology: What is a Database? Watch how data is organised in tables, what a database management system adds, and how relational databases differ from other kinds.

Warning

A spreadsheet is not a DBMS. It can hold a table, but it does not link tables, enforce rules or control many users safely.
Do not confuse the database with the DBMS. The database is the data; the DBMS is the program that manages it.
Personal data is protected by law. A database about people must be kept secure, used only for its stated purpose and not kept longer than needed.

Tip

Entity → table, attribute → field, instance → record. Say it as you design: "a Student (table) has a FirstName (field); Aline is one student (record)."

✦

Practice

Define database and DBMS, and name three DBMSs.
A clinic stores, for each patient, an ID, full name, date of birth, phone number and blood group. Name the entity and its attributes, and choose a data type for each attribute.
A table has 8 fields and 1,200 records. How many values does it hold? What does one record represent?
Give two problems that arise when a school keeps the same student data in several separate files, with an example of each.
Why is a national ID number better stored as text than as an integer?
List three functions of a DBMS that a spreadsheet does not provide.
A small shop has one spreadsheet of sales, in which every row repeats the customer's name and phone. The owner says, "It works, so why change?" Evaluate this view.

Show answersHide answers

A database is an organised collection of related data; a DBMS is the software that creates, stores, protects and gives access to it. Examples: SQLite, MySQL, PostgreSQL (also Access, Oracle, SQL Server).
Entity: Patient. PatientID (INTEGER), FullName (TEXT), BirthDate (DATE), Phone (TEXT), BloodGroup (TEXT, values such as O+).
8 × 1,200 = 9,600 values. One record is all the data about one item of the entity, for example one patient.
Redundancy: the name and class typed in the fees file and the library file. Inconsistency: a phone number corrected in one file but not the other.
It is an identifier, not a quantity: it is never used in arithmetic, may be too long for an integer type, and could have leading zeros or letters that a number would lose.
Any three of: linking tables, enforcing integrity rules, controlling who may see or change each part, safe multi-user access, backup and recovery, fast queries.
It works while the shop is small, but each customer's details are repeated in every sale, so a changed phone must be corrected in many rows and copies will disagree; it is also hard to answer questions such as total sales per customer. A Customer table and a Sale table linked by a customer ID would avoid both problems.

Quick check

Match each term to its meaning.

Tap one on the left, then its partner on the right.

Key points

A database is an organised collection of related data; a DBMS (SQLite, MySQL, PostgreSQL, Access…) is the software that manages it.
A table stores one entity; each record (row) is one item and each field (column) one attribute; every field has a data type.
Separate files cause redundancy and inconsistency; a DBMS stores each fact once, links tables, enforces rules, controls access and supports many users.
SQL is the language used to define and query relational databases.

Sources and credits

Database, Wikipedia contributors, Wikipedia, licensed under CC BY-SA 4.0. Adapted by Ganzaa.
Datatypes In SQLite and Most Widely Deployed SQL Database Engine, SQLite developers, sqlite.org, public domain.
Photo: Schlagwortkatalog, Dr. Marcus Gossler, Wikimedia Commons, CC BY-SA 3.0.
Video: What is a Database?, IBM Technology, embedded from YouTube.
Diagrams, SQL examples (all data illustrative), worked examples and exercises: written by Ganzaa.

This lesson adapts CC BY-SA material and is shared under CC BY-SA 4.0.

1One student, many lists

2Inside a table

3Why not just use separate files?

4What a DBMS does

5Data types

6Try it: a first table in SQLite