Introduction to Databases

Learning Objectives

• Define a database and state why databases are used.
• Describe the parts of a database.
• Distinguish between a table, a record and a field.
• Create, sort and filter a table in a database program.

Meaning and Reason for Databases

A database is an organised collection of related data stored so that it can be searched, sorted and retrieved quickly. A file system stores data in separate files, which leads to duplication and to data that disagrees with itself. A database stores the data once in a controlled structure.

A school keeps a register of pupils. In files, each pupil would have a separate file, and a change of address would have to be made in several places. In a database there is one pupil record, so the change is made once and every report that uses it is correct.

A database is used when there is a large amount of related data that must be searched quickly or reported on in different ways. Schools, banks, hospitals, shops and government offices all use databases.

Parts of a Database

A database system has three parts. The database itself is the collection of stored data. Database software is the application used to enter, search and report on the data. The hardware is the machine and the storage that holds it.

Within the database, data is held in tables. A table holds one kind of record, such as all the pupils or all the staff. A record is one row and holds the fields of one item. A field is one column and holds one kind of information about every record, such as the surname of every pupil.

Each table has a key. The primary key is the field or combination of fields that makes every record unique, and no two records may share the same key. A pupil number makes a good primary key because it is unique and does not change, while a name does not, because two pupils may have the same name.

A table and a record are not the same. A table holds many records, and a record is one row.

A field is not a column label only. The label is the field name, while the field holds the value in each row.

A primary key must be unique, so a name is a poor choice because two people may share it.

Keys, Relationships and Design

A foreign key is a field in one table that refers to the primary key of another table, and the link is called a relationship. A pupil table holds the name and address, an marks table holds the scores, and the pupil number links them.

The design is called normalisation. Splitting the data so that each fact is stored once avoids repetition. The pupil table holds the address once and the marks table holds the scores, and neither repeats the other. This is called first normal form, and the goal is that a fact is entered once and used everywhere.

A relationship may be one to one, where each record in the first table matches exactly one in the second, or one to many, where one record matches many in the second. One pupil has many results, so that is a one to many relationship.

Validation means checking data as it is entered.

Redundancy means the same fact stored more than once.

Using a Database Program

A database program is used to create a table, define the fields and their data types, and then enter the records. The data type is set for each field, such as text, number or date, and it decides what may be entered.

Records are sorted by choosing a field and the sort order, which arranges the records alphabetically or by value. Filtering shows only the records that meet a stated condition, such as all the pupils in a given class, while the rest are hidden but not deleted.

A search finds a particular value. A report is a formatted output produced from the data, such as a list of pupils in a class or a record of attendance. Reports are generated from the stored data, so they are up to date without being typed.

Filtering hides records without deleting them, so a filtered result is not a reduced database.

Advantages and Precautions

The advantages of a database are that data is stored once, searches are fast, several users may work with the same data at once, security can control who sees what, and reports can be produced in any required format.

Precautions are needed because a database holds a great deal in one place. Access is given only to those who need it, a password protects it, a backup copy is kept separately, and the data is validated on entry so that a date of birth cannot be entered as a word.

Practical Work

1. Create a database with one table of pupils holding the fields pupil number, surname, forename, class and date of birth.
2. Set the data type of each field and set the pupil number as the primary key.
3. Enter ten records, then sort the table by surname and confirm the order.
4. Use a filter to show only the pupils in a given class and record the number shown.
5. Create a second table of results holding the pupil number and three scores, and link it to the first table using the pupil number.
6. Produce a report listing the pupils in one class with their scores.

Scenario-Based Questions

1. Explain why storing pupil records in separate files leads to errors that a database avoids.
2. A school wants to use names as the primary key of a pupil table. Give two reasons this is a poor choice.
3. Explain the difference between a field and a record, using a library catalogue as an example.
4. Describe how splitting the address from the marks table reduces redundancy.
5. Explain what a filter does and state what it does not do.
6. A database holds confidential results. Explain two precautions that should be applied.
7. Explain the difference between a one to many relationship and a many to many relationship.

Lesson objectives

By the end of the week, learners should be able to:

1. Define a database and describe its purpose in an organisation.
2. Name the main components of a database schema.
3. Distinguish relational and non-relational structures briefly.
4. Create a simple table schema with fields, types and constraints.
5. Explain why indexing speeds up queries.

Key vocabulary

• Database: a structured collection of related records.
• Schema: the design defining tables, fields and relationships.
• Table: a collection of related rows and fields.
• Field: a column holding a characteristic of each record.
• Constraint: a rule limiting what data may enter a field.
• Index: a sorted lookup structure speeding query searches.
• Query: a request returning records matching criteria.
• Relational: a tabular structure holding tables linked by keys.
• Non-relational: a document or graph structure storing loose data.
• Speeds up: makes something faster through a structural aid.

Common misconceptions

• "A database is just a big spreadsheet." Databases enforce constraints and relationships that spreadsheets ignore.
• "Tables store unrelated rows." Related rows belong together because of shared field types.
• "Indexing adds unrelated overhead." Indexing speeds queries substantially without changing stored values.

Teacher activities

1. Starter: ask where a school keeps student records if not on a teacher's desktop.
2. Define a database and describe its organisational purpose.
3. Name the main schema components one by one.
4. Briefly distinguish relational from non-relational structures.
5. Create a simple table schema on the board for a student records table.
6. Demonstrate how an index speeds a query lookup.

Student activities

1. Answer the starter and identify the right storage location.
2. Define a database in their own words and state its purpose.
3. Name the main schema components in one sentence each.
4. Distinguish relational from non-relational structures briefly.
5. Create a simple table schema with fields, types and constraints.
6. Explain why an index speeds up queries in one paragraph.
7. Answer the exit question in one sentence about databases versus spreadsheets.

Practical / learning evidence

A one-table schema worksheet with fields, types and constraints, plus an index explanation paragraph. Submit both for marking.

Resources and preparation

Printed schema templates, sample student data and index example cards. Offline route: paper templates, no device needed. Provide an index example drawn simply on paper.

Assessment for learning

Inspect the schema for correct field names, types and at least one constraint. Mark the index explanation for a plausible lookup analogy. Flag any learner who calls the database a big spreadsheet.

Inclusion and homework

Provide the schema prefilled for the field names and allow oral constraint completion. Extend confident learners with two tables linked by a foreign key and a second index example. Homework: name one advantage and one disadvantage of a relational database over a spreadsheet.