8 min readPathway sheetOften a 5-mark answer

Creating a database

Creating a database means planning what to store, designing tables, fields and keys, setting relationships, then entering, testing and using the data.

First stepDecide the purpose and data needed

Each field needsName, data type, size, validation

Common data typesText, Number, Currency, Date/Time, Yes/No

NormalisationSplitting tables to remove repeated data

Steps to create a database

1★
Decide the purpose
what questions must it answer? e.g. which customers owe money
2
Identify the data
list every fact that must be stored
3
Design the tables
one table per subject: Customers, Products, Orders
4
Choose fields and data types
set size, format and validation rules
5★
Set a primary key in each table
6
Create relationships
link tables with foreign keys; enforce referential integrity
7
Enter and test sample data
use forms; check results
8
Build queries, forms and reports
for everyday use

★ one-way, controls the pace

Common data types (Microsoft Access)

Data type	Use for	Example
Short Text	Names, addresses, codes, phone numbers	Nimal Perera
Number	Quantities used in calculations	25
Currency	Money values	Rs. 1,250.00
Date/Time	Dates and times	2026-03-02
Yes/No	Only two possible values	Paid? Yes
AutoNumber	An automatic unique number for each new record	1, 2, 3 …

Keeping the data correct

Control	What it does	Example
Validation rule	Rejects values outside allowed limits	Quantity must be greater than 0
Input mask	Forces a fixed pattern of characters	A date entered as DD/MM/YYYY
Required field	Cannot be left blank	Customer name
Referential integrity	Stops links to records that do not exist	No order for a customer who is not in the Customer table

Why phone numbers are stored as text

A phone number such as 077… is stored as Short Text, not Number. It may start with 0, which a number field would drop; it is never added up; and it may contain spaces or a + sign.

In Sri Lankan business

A tea exporter could design tables for Estates, Grades, Buyers and Shipments, make ShipmentID the primary key, and add validation rules (for example, weight must be more than 0) so clerks cannot enter impossible values.

Exam angle

Short answer: “What is a validation rule? Give an example.” A rule that checks data as it is entered and rejects values outside limits, e.g. quantity must be greater than 0.

Structured question: “Explain the steps in creating a database.” Purpose, identify data, design tables, fields and data types, primary keys, relationships, test with data, then queries, forms and reports.

Short answer: “What is referential integrity?” A rule that a foreign key value must match an existing primary key, e.g. no order for a customer who does not exist.

Remember it

Purpose → Data → Tables → Fields → Keys → Links → Test → Use.