Information and Communication Technology (0417) | Create a database structure

🎉 Welcome to Databases: Creating the Structure!

Hello! This chapter is where we learn how to build the foundation of any great database. Think of it like being an architect: you need a solid blueprint before you can build a skyscraper!
Designing a database structure correctly from the start is absolutely crucial for storing information neatly and efficiently. Don't worry if this seems technical—we will break down every part using simple steps and real-world examples. Let's get building!

1. Understanding Database Types: Flat File vs. Relational

Before you start designing, you need to understand the two main ways databases are organised:

Flat File Databases

A Flat File Database is the simplest type. It stores all the data in a single table.

Analogy: Think of a simple spreadsheet containing all your friends' details (Name, Phone, Address, Favourite Colour). All the information is in one place.

Advantages and Disadvantages of Flat Files

Advantages:

• Simple to set up and use.
• Good for small amounts of data.

Disadvantages:

• Lots of data redundancy (the same data repeated many times). For example, if two students live at the same address, you must type the address twice.
• Inconsistent data: If you misspell the repeated address once, your data is now inaccurate.
• Difficult to update (you have to change the data everywhere it appears).

Relational Databases

A Relational Database stores data in multiple, separate tables that are linked together by common fields (keys). This is what you will focus on building.

Analogy: Instead of one huge friend list, you have a "Friends" table (Name, ID) and a separate "Addresses" table (ID, Street, City). You link them using the ID number.

Key Takeaway (Database Types)

Relational databases are complex to set up but are better for large datasets because they minimise data repetition (redundancy) and make data consistent and easy to manage.

2. Creating the Table Structure: Fields and Data Types

The first step in creating your database structure (the table) is defining what information you need to store. These pieces of information are called fields.

2.1 Importing Data (When starting from scratch is not an option)

Sometimes, the data you need already exists in other formats, like plain text files (.txt) or comma-separated values files (.csv).

You must be able to Import data from existing files (including .csv, .txt) using specified field names to create your tables. When importing, the database software will often try to guess the data type, but you must check and adjust this manually!

2.2 Setting Field Names

Field names should be descriptive and clear, for example, FirstName, DateOfBirth, or IsActiveMember.

2.3 Choosing Appropriate Data Types

The data type tells the database what kind of information will be stored in that field (e.g., words, numbers, or dates). Choosing the wrong type can lead to errors and prevent calculations.

The Four Main Data Types You Must Know:

1. Text/String:

• Used for words, names, addresses, or numbers that you will not perform calculations on (like phone numbers, postcodes, or student IDs).
• The syllabus calls this Text.

2. Numeric:

• Used for numbers you might perform calculations on (e.g., quantity, price, age).
• Sub-types of numeric data you need to set include:
    • Integer: Whole numbers only (e.g., number of items purchased: 5).
    • Decimal: Numbers with fractional parts.
    • Currency: Numbers that need a currency symbol (\$).
    • Percentage: Numbers formatted to show a percentage (e.g., 25%).
    • You must also be able to set the specific number of decimal places required.

3. Date/Time:

• Used for dates and times (e.g., Date of Birth, time of transaction).
• You must set the display format (e.g., DD/MM/YYYY or YYYY-MM-DD).

4. Boolean/Logical:

• Used for fields that can only have one of two values: True or False.
• The syllabus calls this Boolean/Logical.
• Example: Is the order shipped? (Yes/No). Is the student male? (True/False).
• You must set the display format (e.g., yes/no, true/false, or checkbox).

3. Defining Relationships: Primary and Foreign Keys

This is the most important part of building a Relational Database. Keys are how we connect tables together.

3.1 The Primary Key (PK)

A Primary Key is a field (or combination of fields) that uniquely identifies each record in a table. It's the unique ID badge for every entry.

Characteristics of a Primary Key:

• Unique: No two records can have the same primary key value.
• Present: It can never be empty (null).
• Example: A Student ID number, a Customer ID, or a Product Code.

Did you know? You might sometimes use two fields together to form a unique key, known as a composite key.

3.2 The Foreign Key (FK)

A Foreign Key is a field in one table that refers back to the Primary Key in another table. It is the connector or the "bridge."

Characteristics of a Foreign Key:

• It links two tables.
• It does not have to be unique in its own table (e.g., many students can share the same TeacherID).
• It creates the necessary relationship.

Example: In the "Student" table, the field TeacherID is a Foreign Key. It links back to the TeacherID (the Primary Key) in the "Teacher" table.

3.3 Creating Relationships Between Tables

In your database software, you will use these keys to create relationships. Most relationships in IGCSE databases are One-to-Many.

Example: One Teacher (Primary Key) teaches Many Students (Foreign Key referencing the teacher).

Setting up these relationships ensures referential integrity—meaning you can't accidentally assign a student to a teacher who doesn't exist!

Key Takeaway (Keys and Relationships)

Primary Keys ensure every record is unique. Foreign Keys link tables together, forming a relational database and preventing redundant data.

4. Designing the Data Entry Form

Once the tables and relationships are set up, you need a way for users to input the data easily and accurately. This is done using a data entry form.

4.1 Characteristics of Good Form Design

The goal of a good form is to make data entry fast, accurate, and easy for the user.

Good Form Design includes:

• Clear Layout: Logical order, grouped related fields.
• Appropriate Spacing: Enough white space so the form doesn't look cluttered.
• Appropriate Fonts and Sizes: Easy to read text.
• Titles and Labels: Clear labels for every input field.
• Using appropriate controls: Selecting the right visual element for the data type. (See section 4.2)

4.2 Choosing the Right Form Controls

Instead of just using standard text boxes for every field, you should use specialised controls to speed up input and reduce errors:

• Radio Buttons (Option Buttons): Used when the user must select only one option from a small list.
    Example: Select Gender (Male or Female).

• Check Boxes: Used for Boolean/Logical data types where the user is choosing between Yes or No, or True or False. They can be checked (True) or unchecked (False).
    Example: Is the item in stock? (Check the box if Yes).

• Drop-down Menus (Combo/List Boxes): Used when the field has a large number of acceptable inputs, or to ensure that the user enters data consistently by selecting from a list.
    Example: Selecting a Country or a predefined Department Name.

Key Takeaway (Form Design)

A well-designed form uses white space and appropriate controls (like radio buttons or drop-down menus) to guide the user, making data entry efficient and accurate.

You have now mastered the art of creating a solid database structure! You know how to define fields, assign types, link tables using keys, and build a user-friendly entry form. Excellent work!