Header Ads Widget

IGCSE ICT 0417: Essential Database Concepts and Practices

Databases Notes

Table of Contents

  1. Introduction to Databases
    • 1.1 What is a Database?
    • 1.2 Types of Databases
      • 1.2.1 Flat File Databases
      • 1.2.2 Relational Databases
  2. Creating a Database Structure
    • 2.1 Setting Up Tables
      • 2.1.1 Step-by-Step Table Creation
    • 2.2 Data Types
      • 2.2.1 Common Data Types
      • 2.2.2 Examples of Fields and Data Types
    • 2.3 Keys and Relationships
      • 2.3.1 Primary and Foreign Keys
      • 2.3.2 Creating Relationships
      • 2.3.3 Types of Relationships
    • 2.4 Data Entry Forms
      • 2.4.1 Designing a Data Entry Form
  3. Manipulating Data Using Queries
    • 3.1 Retrieving Data
      • 3.1.1 Example of a SELECT Query
    • 3.2 Modifying Data
      • 3.2.1 Example of an UPDATE Query
    • 3.3 Deleting Data
      • 3.3.1 Example of a DELETE Query
    • 3.4 Displaying Data
      • 3.4.1 Example of a REPORT Query
  4. Presenting Data
    • 4.1 Producing Reports
      • 4.1.1 Designing Reports
    • 4.2 Output Layouts
      • 4.2.1 Controlling Display Layouts
  5. Exercises
    • 5.1 Practical Exercise: Create a Database
    • 5.2 Practical Exercise: Data Entry Form
    • 5.3 Practical Exercise: Querying Data
  6. GUI Solutions
    • 6.1 Screenshots of Database Creation
    • 6.2 Example of a Data Entry Form
    • 6.3 Example Reports

1. Introduction to Databases

1.1 What is a Database?

A database is an organized collection of data that can be easily accessed, managed, and updated. It allows users to store, retrieve, and manipulate data efficiently.

1.2 Types of Databases

1.2.1 Flat File Databases

  • Characteristics: Stores data in a single table, often in a text file format.
  • Uses: Suitable for small datasets, such as contact lists.
  • Advantages: Simple structure, easy to understand and implement.
  • Disadvantages: Limited functionality, redundancy issues, not ideal for complex queries.

1.2.2 Relational Databases

  • Characteristics: Uses multiple tables that are related to each other through keys.
  • Uses: Suitable for complex data storage and manipulation.
  • Advantages: Reduces redundancy, allows for complex queries and data integrity.
  • Disadvantages: More complex to set up and manage, requires knowledge of database design.

2. Creating a Database Structure

2.1 Setting Up Tables

To create a database structure, follow these steps:

2.1.1 Step-by-Step Table Creation

  1. Open Database Software: Start your database management system (DBMS).
  2. Create a New Database:
    • Go to the “File” menu and select “New Database.”
    • Name your database (e.g., "SchoolDB").
  3. Create a Table:
    • Click on “Create Table” option.
    • Enter field names (e.g., StudentID, Name, Age, Grade).
  4. Set Data Types:
    • Select each field and specify its data type (see section 2.2 for types).
  5. Save the Table:
    • Name your table (e.g., "Students") and save it.

2.2 Data Types

2.2.1 Common Data Types

  • Text: For alphanumeric characters (e.g., names, addresses).
  • Numeric: For numbers, including:
    • Integer: Whole numbers (e.g., 10, 25).
    • Decimal: Numbers with decimal points (e.g., 10.5, 0.75).
    • Currency: Special format for monetary values (e.g., $10.00).
  • Date/Time: For dates and times (e.g., 2024-10-30).
  • Boolean/Logical: For true/false values (e.g., Yes/No, True/False).

2.2.2 Examples of Fields and Data Types

  • StudentID: Integer
  • Name: Text
  • Age: Integer
  • DateOfBirth: Date/Time
  • Grade: Text
  • IsActive: Boolean (True/False)

2.3 Keys and Relationships

2.3.1 Primary and Foreign Keys

  • Primary Key: A unique identifier for each record in a table. For example, StudentID can be a primary key in the "Students" table.
  • Foreign Key: A field in one table that links to the primary key of another table, establishing a relationship. For example, ClassID in a "Classes" table that relates to ClassID in a "Students" table.

2.3.2 Creating Relationships

  1. Open the Relationship Tool: In your DBMS, find the option to manage relationships.
  2. Add Tables: Select the tables you want to relate (e.g., "Students" and "Classes").
  3. Define Relationships:
    • Drag the primary key from one table to the foreign key in another.
    • Set the relationship type (see section 2.3.3).
  4. Save Relationships: Confirm and save the relationships.

2.3.3 Types of Relationships

  • One-to-One: One record in Table A is linked to one record in Table B (e.g., each student has one unique ID).
  • One-to-Many: One record in Table A can link to multiple records in Table B (e.g., one class can have many students).
  • Many-to-Many: Records in Table A can relate to multiple records in Table B and vice versa (e.g., students can enroll in multiple classes).

2.4 Data Entry Forms

2.4.1 Designing a Data Entry Form

  1. Select the Form Tool: In your DBMS, choose the option to create a form.
  2. Choose the Table: Select the table (e.g., "Students") for which you want to create the form.
  3. Add Fields: Drag the desired fields into the form (e.g., StudentID, Name, Age).
  4. Customize Layout:
    • Use appropriate font styles and sizes.
    • Ensure proper spacing between fields.
    • Add radio buttons for gender and drop-down menus for grade selection.
  5. Save the Form: Name your form (e.g., "Student Entry Form") and save it.

3. Manipulating Data Using Queries

Queries are used to retrieve, modify, delete, and display data in a database. They allow users to interact with the data effectively.

3.1 Retrieving Data

3.1.1 Example of a SELECT Query

To retrieve data from a table, you can use the SELECT statement.

SQL Syntax:

sql
SELECT field1, field2 FROM table_name WHERE condition;

Example: Retrieve all student names and ages from the "Students" table:

sql
SELECT Name, Age FROM Students;

3.2 Modifying Data

3.2.1 Example of an UPDATE Query

To modify existing data, use the UPDATE statement.

SQL Syntax:

sql
UPDATE table_name SET field1 = value1, field2 = value2 WHERE condition;

Example: Update the age of a student with a specific StudentID:

sql
UPDATE Students SET Age = 20 WHERE StudentID = 1;

3.3 Deleting Data

3.3.1 Example of a DELETE Query

To delete records from a table, use the DELETE statement.

SQL Syntax:

sql
DELETE FROM table_name WHERE condition;

Example: Delete a student record with a specific StudentID:

sql
DELETE FROM Students WHERE StudentID = 1;

3.4 Displaying Data

3.4.1 Example of a REPORT Query

To create a report that displays aggregated or detailed data, you can use a combination of SELECT statements with functions.

SQL Syntax:

sql
SELECT field1, COUNT(*) FROM table_name GROUP BY field1;

Example: Count the number of students in each grade:

sql
SELECT Grade, COUNT(*) AS StudentCount FROM Students GROUP BY Grade;

4. Presenting Data

4.1 Producing Reports

4.1.1 Designing Reports

  1. Select the Report Tool: In your DBMS, choose the option to create a report.
  2. Choose the Query: Select the query that pulls the data you want to display.
  3. Add Fields: Include necessary fields in the report (e.g., StudentID, Name, AverageScore).
  4. Format the Report:
    • Add headers and footers for clarity.
    • Align data appropriately for readability.
  5. Save the Report: Name it (e.g., "Student Report") and save it.

4.2 Output Layouts

4.2.1 Controlling Display Layouts

  • Tabular Layout: Arrange data in rows and columns for clarity.
  • Columnar Layout: Stack fields in columns for a detailed view.
  • Alignment: Ensure numerical data is right-aligned and use decimal alignment for currency.

5. Exercises

5.1 Practical Exercise: Create a Database

  • Task: Import a .csv file containing student information and create a table with appropriate data types.
    • Steps:
      1. Open the DBMS.
      2. Use the "Import" feature to load the .csv file.
      3. Create fields such as:
        • StudentID (Integer)
        • Name (Text)
        • Age (Integer)
        • DateOfBirth (Date/Time)
        • Grade (Text).

5.2 Practical Exercise: Data Entry Form

  • Task: Design a data entry form for the student table.
    • Steps:
      1. Select "Create Form."
      2. Add fields for StudentID, Name, Age, DateOfBirth, Grade.
      3. Use radio buttons for gender and checkboxes for extracurricular activities.
      4. Save the form as "Student Entry Form."

5.3 Practical Exercise: Querying Data

  • Task: Create a query to find students above a certain age and display their names.
    • Steps:
      1. Open the query tool in your DBMS.
      2. Write the SQL query:
        sql
        SELECT Name FROM Students WHERE Age > 18;
      3. Run the query and review the results.

6. GUI Solutions

6.1 Screenshots of Database Creation

  • [Insert Screenshots]: Show steps for creating a table and setting data types.

6.2 Example of a Data Entry Form

  • [Insert Form Example]: Screenshot of a completed data entry form.

6.3 Example Reports

  • [Insert Report Example]: Screenshot of a generated report with aligned data and appropriate headers.

 

Post a Comment

0 Comments