Year 10 · ICT · Spring 1

Effects of IT and ICT applications; databases · Lesson 4 of 5

Practical: a database from a spec

Practical18.1 Create a database structure

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

You will learn to

Import a .csv file into a new table, keeping the field names from the first row.
Choose the right field type and display format for each field.
Set a primary key and add validation to fields, as the specification asks.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Success criteria

You have got it when you can say:

I can import the data file and check each field has the right type.
I can explain why a phone number is stored as text, not as a number.
I can set a primary key and say why its values are unique.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

Last lesson
A report grouped by Form means…

A The records are in random orderB Only one record showsC Each form's records appear together, with a count or total per group

C. Each form's records appear together, with a count or total per group
Last unit
Applying a style instead of formatting by hand means…

A It only works in WordB It is slowerC Changing the style changes every heading at once

C. Changing the style changes every heading at once
Earlier this year
A pie chart is for…

A Change over timeB Comparing 30 categoriesC Parts of a whole

C. Parts of a whole

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Key words (1 of 4)

Database: An organised store of data that can be searched, sorted and reported on.
Table: A set of records about one kind of thing, such as members or bookings.
Record: One row of a table: all the data about one item.
Field: One column of a table: one piece of data stored for every record.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Key words (2 of 4)

Field type: The kind of data a field holds: text, numeric, date/time or Boolean.
Boolean: A field type with only two values, such as Yes/No or True/False.
Display format: How a value looks on screen or paper, without changing the stored value.
Primary key: A field whose value is different for every record, used to identify each one.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Key words (3 of 4)

Foreign key: A field in one table that holds the primary key of another table, to link them.
One-to-many relationship: A link where one record in a table matches many records in another table.
Flat-file database: A database with only one table.
Relational database: A database with two or more tables linked by keys.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Key words (4 of 4)

.csv file: A plain-text file where each line is a record and commas separate the fields.

The explanation · step 1 of 9 · part 1 of 2

Tables, records and fields

A database is an organised store of data that a computer can search and sort.

Data sits in tables. A record is one row: all the data about one thing, such as one member.

A field is one column: one piece of data about every record, such as LastName.

Through this topic we use one example: a sports centre in Madrid. It has a Members table and a Bookings table.

The explanation · step 1 of 9 · part 2 of 2

Tables, records and fields (continued)

MemberID	FirstName	LastName	Category	Phone	Discount
M01	Lucía	García	Junior	612 345 678	20%
M02	Pablo	Ruiz	Adult	655 210 987	0%
M03	Carmen	López	Senior	699 004 321	30%
M04	Diego	Martín	Adult	634 778 120	0%
M05	Sofía	Navarro	Junior	617 556 043	20%

?Why?

Splitting data into fields lets the software search one field at a time. You can ask for every Junior member without reading every row yourself.

The explanation · step 2 of 9 · part 1 of 3

Import a .csv or .txt file

In the exam you do not type the data. Cambridge gives you a source file, usually .csv (comma separated values) or .txt.

A .csv file is plain text. Each line is one record. Commas separate the fields.

When you import, tell the software that the first row contains field names. Then the headings become field names, not a record.

The explanation · step 2 of 9 · part 2 of 3

Import a .csv or .txt file (continued)

During the import you can set each field's type and choose the primary key. Check the result against the source file after importing.

BookingID,MemberID,Activity,BookDate,Hours,Rate,Paid
101,M02,Padel,06/10/2026,1.5,12.00,Yes
102,M01,Swimming,06/10/2026,1,4.50,Yes
103,M04,Tennis,07/10/2026,2,11.00,No

The explanation · step 2 of 9 · part 3 of 3

Import a .csv or .txt file (continued)

!Watch out

If you forget to tick "first row contains field names", the headings become record 1 and every field gets a name like Field1. The examiner cannot see the field names you were asked to use.

The explanation · step 3 of 9 · part 1 of 3

Field types and sub-types

A field type (data type) tells the software what kind of data a field holds. It controls how data is stored, checked and sorted.

Cambridge names four main types: text, numeric, date/time and Boolean. Numeric has sub-types: integer, decimal, currency and percentage.

The table shows each type with a field from our sports centre.

The explanation · step 3 of 9 · part 2 of 3

Field types and sub-types (continued)

Type	Sub-type	What it holds	Our field	Example
Text		Letters, symbols, or digits not used for maths	MemberID, LastName, Phone	M04, Martín, 634 778 120
Numeric	Integer	Whole numbers only	BookingID	103
Numeric	Decimal	Numbers with a fractional part	Hours	1.5
Numeric	Currency	Money, shown with a symbol and 2 decimal places	Rate	€12.00
Numeric	Percentage	A part of 100, often stored as a decimal	Discount	20% (stored as 0.2)
Date/time		A date, a time, or both	BookDate	07/10/2026
Boolean		Only two values: Yes/No or True/False	Paid	No

The explanation · step 3 of 9 · part 3 of 3

Field types and sub-types (continued)

!Watch out

Phone numbers are text, not numeric. You never add or average them. A numeric field also drops a leading zero or a "+34" and cannot store spaces.

The explanation · step 4 of 9 · part 1 of 2

Why the wrong type loses marks

The examiner checks your table design. Each wrong type usually costs a mark.

If Hours is set to integer, the value 1.5 is stored as a whole number. The cost of booking 101 is then wrong too.

If BookDate is text, sorting goes by characters, not by date. "06/11/2026" (6 November) sorts before "10/10/2026" (10 October), because 0 comes before 1.

If Paid is text, someone can type "yes", "Y" or "paid". A Boolean field only allows two values, so searches work every time.

The explanation · step 4 of 9 · part 2 of 2

Why the wrong type loses marks (continued)

?Why?

The type is a promise about the data. Keeping that promise makes searching, sorting and calculating reliable.

!Watch out

Your screenshot of the table design must show every field name in full and its type. Widen the column before you take the screenshot.

The explanation · step 5 of 9 · part 1 of 2

Display formats

A display format changes how a value looks, not the value stored.

Dates can show as 07/10/2026 or 7 October 2026. Currency shows a symbol and 2 decimal places, such as €4.50.

Decimal fields can show a set number of places: Hours as 1.0, 1.5, 2.0. A Boolean field can show as Yes/No or as a check box.

Read the task carefully. If it says "currency in euros with 2 decimal places", both parts are marked.

The explanation · step 5 of 9 · part 2 of 2

Display formats (continued)

e.g.Example

Rate stored: 4.5 Display format: currency, euro, 2 decimal places Shown on screen: €4.50 Discount stored: 0.3 Display format: percentage, 0 decimal places Shown on screen: 30%

!Watch out

Formatting to 0 decimal places only hides the decimals. The value 1.5 still shows as 2 on screen, which looks wrong. Choose the places the task asks for.

The explanation · 4 more steps

More on the lesson page

The lesson page has the rest of the explanation, step by step:

Primary keys and foreign keys
One-to-many relationships
Flat-file and relational databases
Data entry forms

Open the lesson page

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Try it: Ask the database

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Quick quiz · 1 of 3

Non-contiguous data means…

A Ranges that are not next to each other
B Numbers only
C A single column

A. Ranges that are not next to each other

Hold Ctrl to select them.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Quick quiz · 2 of 3

RSI is reduced by…

A A brighter screen
B A bigger hard disk
C Breaks and an ergonomic keyboard

C. Breaks and an ergonomic keyboard

Repetitive strain injury comes from repeated movement.

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Quick quiz · 3 of 3

Trailing cables are a…

A Security risk
B Health issue
C Safety hazard

C. Safety hazard

Safety is accidents (tripping, fire, shock). Health is the body (eyes, back, wrists).

Year 10 · ICT · Effects of IT and ICT applications; databases · Lesson 4 of 5

Exit ticket

Answer on paper before you go. Then check.

Job sharing means…

A Two people share one full-time jobB Working nightsC Working from home

A. Two people share one full-time job A working pattern made easier by IT.
A microprocessor-controlled washing machine…

A Needs someone to watch itB Frees the owner's timeC Cannot be programmed

B. Frees the owner's time Set it and leave. The trade-off is less manual skill and reliance on the device.