English:Databases and Data Organisation
Databases and Data Organisation
Introduction
Every day, schools, shops, hospitals, games, websites, and apps work with data. A school library may store book titles, student IDs, loan dates, and return information. A sports app may store players, teams, scores, and fixtures. A database is an organised collection of data that can be stored, searched, updated, and connected efficiently.
In this aiMOOC you will learn how databases organise information, why tables have fields and records, how keys identify and connect data, and how queries help you find useful answers. The level is designed for Grades 7–8. You do not need previous experience with database software.
Learning Goals
By the end of this course, you should be able to explain the difference between data and information, describe the structure of a database table, choose sensible data types, identify a useful primary key, explain a simple table relationship, use sorting and filtering ideas, design a small relational database, and discuss data quality, privacy, and security.
You will also practise data literacy by asking whether stored data is accurate, relevant, well organised, and suitable for the task.
From Data to Information
Data consists of recorded facts or values. Examples include a name, a temperature, a date, a product code, or a score. By itself, one value may have little meaning. When data is organised and interpreted in context, it can become information.
For example, the values "13", "14", and "12" are only data until you know that they are the numbers of books borrowed by three classes. Once they are labelled and compared, they can provide information about borrowing activity.
Good data organisation makes it easier to answer questions. Poor organisation can produce duplicates, missing values, inconsistent spellings, and confusing results.
Structured Data
Structured data follows a planned pattern. In a table, each column has a meaning and each row follows the same structure. A class contact list might contain the fields StudentID, Name, Class, and SchoolEmail. Because the fields are predictable, the data can be searched, sorted, filtered, and checked.
A photo collection or a folder of free-form essays can also contain valuable data, but it is not organised into the same simple row-and-column structure. Databases can handle many kinds of data, yet this course focuses mainly on structured data in relational tables.
What Is a Database?
A database stores related data in an organised form. A database management system, often shortened to DBMS, is software that helps users create, store, retrieve, update, and control access to database data.
A database is more than a pile of facts. Its structure is designed so that information can be found and changed without rewriting everything. In a well-designed school library database, a student's details can be stored once and then connected to many loan records.
Databases and Spreadsheets
A spreadsheet and a database can both display information in rows and columns, but they are usually designed for different jobs. A spreadsheet is especially useful for calculations, charts, and smaller data sets that people work with directly. A database is especially useful when many records must be stored consistently, searched repeatedly, related across tables, or used by several parts of an application.
The important question is not "Which tool is always better?" but "Which tool fits this data task?"
Tables, Fields, and Records
A relational database organises data into tables. A table describes one type of thing, such as students, books, pets, or products.
A field is a column that stores one type of data about the things in the table. A record is a row containing the values for one item.
For example:
| StudentID | Name | Class | House |
|---|---|---|---|
| S1042 | Maya Chen | 8B | Oak |
| S1043 | Leo Grant | 7A | River |
| S1044 | Amira Khan | 8B | Hill |
In this table, StudentID and Name are fields. The row containing S1043, Leo Grant, 7A, and River is one record.
Field Names and Clear Structure
Useful field names are short but meaningful. "LoanDate" is clearer than "Date1". Each field should have a specific purpose. If one field contains "blue, 14, football", the values mix unrelated ideas and are difficult to search.
It is also helpful to store data at a sensible level of detail. A single "FullAddress" field may be enough for a simple class project, but separate fields such as Town and Postcode can make filtering easier if those details need to be searched independently.
Data Types
A data type tells the database what kind of value a field is expected to store. Choosing suitable data types helps the database process and check data.
| Data type | Example | Suitable use |
|---|---|---|
| Text | Blue | Names, labels, codes, and descriptions |
| Integer | 27 | Whole-number counts |
| Decimal | 12.75 | Measurements or prices |
| Date | 2026-08-12 | Dates such as a loan date |
| Boolean | True | Yes or no conditions |
A value that looks numeric is not always best stored as a number. A telephone number or student code is often treated as text because you normally do not calculate with it, and leading zeros may matter.
Required Fields and Validation
A database can use rules to improve data quality. A field may be required, meaning that a value must be entered. A validation rule may check that a score is within an allowed range or that a date has a sensible form.
Validation can reject values that break a rule, but validation does not prove that a value is true. If a learner enters 18 instead of 17 in a field that allows ages from 10 to 20, the value can pass the rule even though it may be wrong.
Keys: Identifying Records
A primary key is a field, or sometimes a combination of fields, whose value uniquely identifies each record in a table. In a student table, StudentID is a better primary key than first name because several students can have the same first name.
A good primary key must not accidentally point to two different records. Database systems often use generated ID values because they are short, stable, and unique.
Why Names Are Usually Poor Keys
Names can repeat, change, or be spelled differently. Imagine two students named Jordan Lee. If "Jordan Lee" were used as the only identifier, a loan record might be connected to the wrong person. A unique StudentID avoids that ambiguity.
The key is not valuable because it is memorable. It is valuable because it identifies one record reliably.
Relationships Between Tables
A relational database can connect records stored in different tables. This reduces unnecessary repetition and makes the structure clearer.
Suppose a library database has three tables:
| Table | Example fields | Purpose |
|---|---|---|
| Students | StudentID, Name, Class | Stores one record per student |
| Books | BookID, Title, Category | Stores one record per book |
| Loans | LoanID, StudentID, BookID, LoanDate, Returned | Stores each borrowing event |
The Loans table contains StudentID and BookID values that point to records in the other tables. When a field in one table refers to the primary key of another table, it is called a foreign key.
One-to-Many Relationships
A common relationship is one-to-many. One student can have many loan records over time, but each individual loan record belongs to one student. One book can also appear in many loan records over its lifetime, while each loan record refers to one book.
The Loans table acts as a bridge between students and books. This is better than placing a long list of borrowed books inside each student record.
Entity-Relationship Diagrams
An entity-relationship diagram, often called an ER diagram, is a visual plan of the main entities in a database and the relationships between them. At this level, you can think of an entity as a type of thing the database stores, such as Student, Book, or Loan.
When you design a database, drawing the tables and their links before entering hundreds of records can reveal missing keys, repeated data, or unclear relationships.
Reducing Repetition
Imagine storing the student's full name, class, and email again inside every loan record. The same details could be repeated many times. Repetition wastes space and creates the risk of inconsistency. If the class changes, one copy might be updated while another is forgotten.
A relational design stores the student's main details once in the Students table and uses StudentID in the Loans table. This idea is an introduction to the broader topic of database normalisation.
For Grades 7–8, the main design rule is simple: store each fact in a sensible place and avoid unnecessary duplicates.
Queries: Asking Questions About Data
A query is a request for specific data or for an operation on data. You might ask:
Which books are currently on loan? Which students are in Class 8B? Which books are in the Science category? Which loans are overdue?
A query can use one condition or several conditions. The exact query language depends on the system. Many relational databases use Structured Query Language, but you can understand the logic of queries before learning SQL syntax.
Searching, Sorting, and Filtering
Searching tries to find data that matches a value or pattern. Sorting changes the order in which results are shown, such as arranging book titles from A to Z. Filtering displays only records that meet a condition, such as books with Category equal to History.
These operations do not have to change the stored records. They can simply change which results you see and in what order.
Flat Files, CSV, and Relational Data
A flat file stores data in a single structure rather than splitting it across related tables. A CSV file is a common text format for table-like data. Each line usually represents a row, and separators divide values into columns.
CSV files are useful for exchanging data between programs, but a CSV file by itself does not provide all the features of a database management system, such as relationships, permissions, or built-in rules for keeping related records consistent.
Data Quality
A database is useful only when its data is useful. Important aspects of data quality include accuracy, completeness, consistency, relevance, and being up to date.
Consider a school club database. If one learner is entered as "Samira Patel" and another record says "S. Patel" for the same person, the database may treat them as different people. Clear entry rules and unique IDs can reduce this problem.
Checking data can include required fields, validation rules, duplicate checks, consistent formats, and careful review. The best method depends on the purpose and risk of the data.
Privacy, Security, and Responsible Use
Some databases contain personal or sensitive information. You should collect only data that is needed for a clear purpose and protect it from people who should not have access.
Access control can give different users different permissions. For example, a student may be able to view available library books, while a librarian may also be allowed to create loan records and correct book details.
Good security can include strong authentication, appropriate permissions, backups, software updates, and careful handling of exported files. Security is not only a technical issue: people must also avoid sharing passwords or copying private data into unsafe places.
When you create a school project, use invented or properly authorised data instead of collecting unnecessary real personal information.
Designing a Small Database
A good database starts with questions, not software. First decide what the database should help users do. Then identify the main entities, choose fields, choose primary keys, and decide how tables connect.
For a classroom equipment-loan database, you might need Equipment, Borrowers, and Loans. EquipmentID can identify each item, BorrowerID can identify each borrower, and a Loans table can record who borrowed what and when.
Before entering many records, test your design with a few sample cases. Ask whether each record can be identified, whether repeated facts are stored only where needed, and whether the database can answer the questions you care about.
A Simple Design Checklist
| Question | Why it matters |
|---|---|
| What is the purpose? | The design should support a clear task. |
| What are the main entities? | Each important type of thing may need its own table. |
| Which fields are needed? | Fields should store useful facts without unnecessary clutter. |
| What is the primary key? | Every record needs a reliable unique identity. |
| Which tables are related? | Relationships show how records connect. |
| What checks are needed? | Validation and review can improve data quality. |
| Who should have access? | Permissions help protect information. |
Common Mistakes and Better Choices
Mistake: using a person's first name as the only key. Better choice: use a unique ID.
Mistake: putting several different facts into one field. Better choice: separate facts when they need to be searched or checked independently.
Mistake: copying the same details into many tables. Better choice: store the details once and connect records with keys.
Mistake: assuming validation means the data must be correct. Better choice: remember that a valid-looking value can still be wrong.
Mistake: collecting data "just in case". Better choice: collect only what the project genuinely needs.
Interactive Tasks
Quiz: Test Your Knowledge
What is a record in a database table? (A row containing data about one item) (!A column heading only) (!A password for the database) (!A chart made from the data)
What is the main purpose of a primary key? (To uniquely identify each record) (!To make every field contain text) (!To sort every table alphabetically) (!To hide all records from users)
Which field is the best primary key for a student table? (StudentID) (!FirstName) (!FavouriteColour) (!ClassName)
What does a foreign key do? (It refers to a key in another table) (!It deletes duplicate tables) (!It turns text into numbers) (!It encrypts every record)
Which operation shows only records that meet a condition? (Filtering) (!Printing) (!Duplicating) (!Renaming)
Which data type is most suitable for a true or false value? (Boolean) (!Image) (!Paragraph) (!Currency)
Why can repeated data cause problems? (Copies can become inconsistent) (!It always makes searches impossible) (!It removes all primary keys) (!It automatically changes data types)
What is an entity-relationship diagram used for? (To plan entities and their relationships) (!To measure internet speed) (!To design a password) (!To compress a video)
What can validation rules help a database do? (Reject values that break defined rules) (!Guarantee that every value is true) (!Remove the need for primary keys) (!Decide who owns the data)
Why should a school project avoid unnecessary personal data? (To reduce privacy and security risks) (!To make every table have one column) (!To prevent all sorting) (!To replace database relationships)
Memory Game
| Database | An organised collection of related data |
| Field | A column that stores one kind of value |
| Record | A row containing values for one item |
| Primary key | A value that uniquely identifies a record |
| Foreign key | A field that refers to a key in another table |
| Query | A request for selected data or an operation |
| Validation | A rule-based check on entered data |
| Relationship | A defined connection between tables |
Drag and Drop
| Match the correct terms. | Topic |
|---|---|
| Primary key | Unique record identifier |
| Foreign key | Reference to another table |
| Field | Table column |
| Record | Table row |
| Query | Request for selected data |
Match each database term with the description that belongs to it. Then explain one of your matches to a partner.
Crossword Puzzle
| Database | What organised collection stores related data? |
| Record | What is one complete row in a table called? |
| Field | What is a column for one kind of data called? |
| Query | What request is used to retrieve selected data? |
| Identifier | What kind of value can distinguish one record from another? |
| Relationship | What connection links records in different tables? |
LearningApps
Cloze Text
Open-Ended Tasks
Easy
- Table Hunt: Find three examples of tables used in everyday life, such as a timetable or sports results table, and label what would count as fields and records.
- Classroom Collection: Create a paper table for ten invented classroom objects with an ID, name, category, location, and condition.
- Data Type Detective: Choose eight possible database fields and decide whether each should store text, an integer, a decimal, a date, or a Boolean value; justify two choices.
- Filter Challenge: Using a small table made by your teacher or group, write five questions that could be answered by filtering the records.
Standard
- Library Database Design: Design Students, Books, and Loans tables for an imaginary school library, including a primary key for every table and the foreign keys needed in Loans.
- Database Interview: Interview a teacher, librarian, coach, or office worker about a database they use; ask what is stored, how they search it, and how access is controlled without requesting private data.
- ER Diagram Poster: Draw an entity-relationship diagram for a club, pet-care centre, game inventory, or school equipment system and annotate each relationship in clear English.
- Data Quality Audit: Create a deliberately messy data set with duplicates, missing values, and inconsistent formats, then produce a corrected version and explain every change.
Advanced
- Database Prototype: Build a small database in suitable software using invented data, create at least two related tables, and demonstrate a useful search, sort, or filter.
- Query Storyboard: Make a short video or slide sequence showing how a user question becomes a database query and then a result, using a school-safe imaginary data set.
- Privacy Review: Examine the design of an imaginary school app and propose which data should be collected, which should not be collected, and which user roles should receive different permissions.
- Database Field Study: Visit or virtually explore a library, museum, sports club, or other organisation and create a report explaining what entities, keys, relationships, and data-quality checks its information system might need.
Learning Assessment
- Design Reasoning: Given a single table that repeats student names and class details for every library loan, redesign it as related tables and explain how your design reduces repetition.
- Key Choice: Compare Name, DateOfBirth, Email, and StudentID as possible primary keys for a school table and justify which one is safest for unique identification in the scenario.
- Query Planning: A librarian wants to find all unreturned science books borrowed by Class 8B; identify which fields and relationships the database would need to answer the question.
- Validation Evaluation: Create one sensible validation rule for an age field and explain why a value can pass the rule yet still be factually wrong.
- Privacy Transfer: Apply the principles from this course to a sports-club database and recommend which information should be visible to members, coaches, and administrators.
- Error Diagnosis: Analyse a database design that stores several book IDs inside one student field, explain the problems this can cause, and propose a relational alternative.
Evidence of Learning
Evidence of learning can include your ability to use the terms database, table, field, record, key, relationship, query, filter, validation, and data type accurately in context. You should be able to interpret a small table, identify its fields and records, and explain what its structure allows a user to do.
Strong evidence also includes a product such as a database plan, ER diagram, cleaned data set, prototype, poster, report, or short video. The product should show that you can choose sensible fields, use a unique identifier, connect related tables, and test whether the design answers real questions.
Your reasoning matters as much as the finished product. You should be able to explain why you separated or combined data, why a key is suitable, how you would check data quality, and how privacy or access requirements influence the design.
Transfer is shown when you can apply the same ideas to a new context, such as a sports club, game inventory, museum collection, equipment store, or science investigation, without simply copying the school-library example.
OERs on the Topic
Linked Learning Areas