Create a Database Structure - Cambridge (CIE) IGCSE ICT

Types of Databases

• Database: A structured, persistent collection of information that allows for easy storage, retrieval, and management.

• Flat File Database: Stores all data in a single table. While simple, it often results in data redundancy, inefficient storage, and difficulty in maintaining data consistency.

• Relational Database: Organizes data into multiple tables connected via keys. This structure reduces redundancy, uses storage efficiently, and prevents data inconsistency.

Database Terminology and Structure

• Table: A complete set of records regarding the same subject or topic (e.g., a table named cars).

• Record (Row): A complete set of fields belonging to a single entity (e.g., a specific car's details).

• Field (Column): A single piece of data within a record (e.g., make, model, or price).

• Data Types: Defined during table design to determine the kind of data held in a field:

  • Numeric: Includes whole numbers or decimals ($24950$, $17995$, $21450$, $19500$).

  • Character/String: Text data (e.g., Peugeot, Mazda, Citroen, Ford).

  • Date/Time: Specialized data format (e.g., $2024-01-19$).

  • Boolean: Represents true/false values.

Primary and Foreign Keys

• Primary Key: A unique field used to identify a specific record in a table (e.g., order_id, customer_id, or MemberID).

• Foreign Key: A field in one table that refers to a primary key in another table. It is used to link tables and establish relationships (e.g., customer_id in an orders table linking to a customers table).

Calculations and Aggregate Functions

• Calculated Fields: Fields that perform arithmetic operations using other numeric fields (stockvalue = \text{unit_price} \times \text{stock}).

• Calculated Controls: Objects on forms or reports that display results of expressions without storing them in the database ($TotalSale = QuantitySold \times UnitPrice$; $FinalCost = TotalCost - Discount$).

• Aggregate Functions: Used to calculate statistical information across a set of records:

  • SUM: Adds all numbers in a column.

  • AVERAGE: Computes the mean value.

  • MAX / MIN: Finds the highest or lowest value in a column.

  • COUNT: Counts the number of rows in a column.

Sorting and Searching Data

• Sorting: Organizing data based on single or multiple criteria.

  • Ascending Order: Smallest to largest ($A \rightarrow Z$ or $1 \rightarrow 100$).

  • Descending Order: Largest to smallest ($Z \rightarrow A$ or $100 \rightarrow 1$).

• Searching: Process of using queries to find specific subsets of data.

  • Operators: AND, OR, NOT, LIKE (pattern matching), and comparison operators (>, <, =, >=, <=, <>).

  • Wildcards: Used with the LIKE operator to search for patterns:

    • % or *: Represents zero, one, or multiple characters (e.g., L* find names starting with L).

    • _: Represents a single character.

Form Design Principles

• Characteristics of Good Design: Simplicity (clean, uncluttered), ease of use, intuitive layout (logical grouping and sequence), clear labels, and appropriate spacing/white space.

• Control Elements:

  • Radio Buttons: For two or more mutually exclusive options.

  • Check Boxes: For selecting multiple options from a list.

  • Drop-down Menus: For providing many options while conserving screen space.

  • Navigation: Forward/backward buttons and submit/save buttons.

Questions & Discussion

Worked Example: Dance Club Database

• Table Members: MemberID (1, 2, 3, 4), FirstName (Zarmeen, Fyn, George, Ella), LastName (Hussain, Ball, Johnson, Franks), DateJoined ($2024-01-19$, $2024-02-01$, $2024-02-25$, $2024-03-04$).

• Table Styles: StyleID (1, 2, 3, 4), MemberID (1, 1, 3, 2), Style (Hip Hop, Ballroom, Contemporary, Street), DateStarted ($2024-01-22$, $2024-02-13$, $2024-03-01$, $2024-03-07$).

• Q: State one benefit of using relational databases.

• A: Reduces data redundancy and data inconsistency.

• Q: What field in the Members table is most suitable as the primary key?

• A: MemberID.

• Q: What field in the Styles table is a foreign key?

• A: MemberID.

Worked Example: School Administration Form Design

• Task: Design a layout for the headteacher to search and view attendance records.

• Required Input: Student_ID and Semester (Autumn, Spring, Summer).

• Required Display fields: Student_name, Days_present, Number_of_lates, Number_absences, Parents_phone_number, Tutor_group.

• Design Solution: Use appropriate spacing, forward/backward navigation buttons, a submit/search button, and a drop-down or radio button for selecting the Semester.