Unit 9: Algorithm, Programming and Logic 9.1 Verified

Databases

4 learning objectives

1. Overview

Databases are organised collections of data, essential for storing and managing information efficiently. Understanding database concepts, data types, and SQL queries is crucial for creating and interacting with databases, a fundamental skill in computer science. This topic focuses on single-table databases and basic query operations.

Key Definitions

  • Database: An organised collection of related data, typically stored in a computer system.
  • Table: A collection of related records within a database. Often visualised as rows and columns.
  • Record: A single entry in a table (a row), representing a set of related data.
  • Field: A single piece of data within a record (a column), representing a specific attribute.
  • Primary Key: A field (or combination of fields) that uniquely identifies each record in a table.
  • Data Type: Specifies the kind of data a field can hold (e.g., text, number, date).
  • SQL (Structured Query Language): A standard language for accessing and manipulating databases.
  • Query: A request for information from a database.

Core Content

A. Single-Table Database Design

  • Fields:

    • Each field needs a meaningful name (e.g., FirstName, Age, DateOfBirth).
    • A suitable data type must be chosen for each field (see section B).
    • Consider any size or format requirements (e.g., FirstName may have a maximum length of 50 characters).
    • Validation rules can ensure data accuracy (e.g., Age must be between 0 and 120).

  • Records:

    • Each record represents a single instance of the data. For example, a single student's information.

  • Validation:

    • Validation rules help prevent errors and maintain data integrity. Examples:
      • Range check: Ensures data falls within a specified range (e.g., age between 0 and 120).
      • Type check: Ensures data matches the expected data type (e.g., a number is entered into a numeric field).
      • Length check: Ensures data does not exceed a specified length (e.g., a string is not too long).
      • Presence check: Ensures a required field is not left blank.
      • Format check: Ensures data conforms to a specific format (e.g., a date is in the format DD/MM/YYYY).

Example: Students Table Structure

Field Name Data Type Validation
StudentID Integer Primary Key, Auto-increment
FirstName Text(50) Presence check
LastName Text(50) Presence check
DateOfBirth Date Format: DD/MM/YYYY
Age Integer Range: 5-100
Email Text(100) Format check (@, .)
IsMember Boolean True/False

Example Records:

StudentID FirstName LastName DateOfBirth Age Email IsMember
1 Ahmed Khan 15/03/2008 16 [email protected] True
2 Sarah Smith 22/07/2009 15 [email protected] False

Fields = columns, Records = rows

B. Data Types

  • Text/Alphanumeric (String): Used for storing sequences of characters of variable length, including letters, numbers, and symbols. Examples: Name, Address, Description.
  • Character: Used for storing a single character. Example: Gender (M/F).
  • Boolean: Used for storing logical values, representing True or False, Yes or No, or 0 or 1. Example: IsMember, HasPaid.
  • Integer: Used for storing whole numbers (numbers without decimal points). Examples: Age, Quantity, Year.
  • Real (Floating-Point): Used for storing numbers with decimal points. Examples: Price, Temperature, Height.
  • Date/Time: Used for storing dates and times. Examples: DateOfBirth, AppointmentTime.
Data Type Description Example
Text/Alphanumeric Variable length string of characters "John Doe", "123 Main St"
Character Single character "M", "F"
Boolean True/False value True, False
Integer Whole number 25, 100
Real Number with a decimal point 3.14, 99.99
Date/Time Date and/or time value 2023-10-27, 14:30:00
  • Choosing the right data type is important for:
    • Data integrity: Ensures the correct type of data is stored.
    • Storage efficiency: Using the smallest appropriate data type saves storage space.
    • Performing operations: Enables correct calculations and comparisons.

C. Primary Keys

  • A primary key uniquely identifies each record in a table.
  • Characteristics of a good primary key:
    • Unique: No two records can have the same primary key value.
    • Not Null: The primary key field cannot be empty.
    • Immutable: The primary key value should rarely, if ever, change.
  • Examples of good primary keys:
    • StudentID
    • ProductID
    • ISBN (for books)
  • Examples of bad primary keys:
    • Name (not unique - multiple people can have the same name)
    • Address (can change over time)
    • PhoneNumber (can change over time)
  • Benefits of using a primary key:
    • Ensures data integrity by preventing duplicate records.
    • Enables efficient searching and retrieval of records.
    • Used for linking tables together in relational databases (covered in more advanced topics).

D. SQL Queries

  • Basic SQL keywords:

    • SELECT: Specifies the columns you want to retrieve. SELECT * retrieves all columns.
    • FROM: Specifies the table you want to retrieve data from.
    • WHERE: Specifies the conditions that records must meet to be included in the result set.
    • ORDER BY: Specifies the column(s) to sort the results by. ASC for ascending order (default), DESC for descending order.
    • SUM(): Calculates the sum of values in a specified column.
    • COUNT(): Counts the number of records that match a specified condition.

  • Example SQL Queries:

    • Retrieve all information from the Students table:

      SELECT * FROM Students;

    • Retrieve the Name and Age of all students from the Students table:

      SELECT Name, Age FROM Students;

    • Retrieve the Name and Age of all students who are older than 16:

      SELECT Name, Age FROM Students WHERE Age > 16;

    • Retrieve the Name and Age of all students, sorted by Name in ascending order:

      SELECT Name, Age FROM Students ORDER BY Name ASC;

    • Retrieve the Name and Age of all students who are older than 16, sorted by Age in descending order:

      SELECT Name, Age FROM Students WHERE Age > 16 ORDER BY Age DESC;

    • Calculate the total age of all students:

      SELECT SUM(Age) FROM Students;

    • Count the number of students in the Students table:

      SELECT COUNT(*) FROM Students;

    • Retrieve the Name of students whose name starts with 'A':

      SELECT Name FROM Students WHERE Name LIKE 'A%';

      Note % acts as a wildcard character, matching any sequence of zero or more characters.

    • Retrieve the number of students over 18

      SELECT COUNT(*) FROM Students WHERE Age > 18;

  • Combining WHERE conditions:

    • AND: Both conditions must be true.
    • OR: At least one of the conditions must be true.
    • <>: Not equal to.

Exam Focus

  • Database Design: Be able to describe how to design a single-table database, including defining fields, data types, and validation rules, and justify your choices. Use appropriate terminology like "field", "record", "data type", and "validation".
  • Data Types: Know the different data types and when to use each one. Be able to explain why a specific data type is suitable for a given field.
  • Primary Keys: Understand the purpose of a primary key and its properties (unique, not null). Be able to identify a suitable primary key for a given table.
  • SQL Queries: Be able to read, understand, and write basic SQL queries using SELECT, FROM, WHERE, ORDER BY, SUM, and COUNT. Pay attention to syntax (e.g., capitalization of keywords, use of commas).
  • Explanation: Always explain why you are making certain choices (e.g., why you chose a particular data type or primary key).
  • Keywords: Correct and effective use of all keywords, especially SELECT, FROM, WHERE, ORDER BY, SUM and COUNT is essential.

Common Mistakes to Avoid

  • ❌ Wrong: Providing code examples that are not relevant to the question. ✓ Right: Focus on the specific requirements of the question and provide only relevant code and explanations.
  • ❌ Wrong: Selecting Name or Address as a primary key. ✓ Right: Choose a field that is guaranteed to be unique and unchanging, such as StudentID or ProductID.
  • ❌ Wrong: Not explaining the purpose of your SQL queries. ✓ Right: Briefly explain what the query is intended to do.
  • ❌ Wrong: Omitting necessary keywords in your SQL queries (e.g., forgetting the FROM clause). ✓ Right: Carefully check your syntax and make sure you have included all required keywords.
  • ❌ Wrong: Using incorrect syntax in SQL queries. ✓ Right: Pay close attention to the correct syntax, including capitalization of SQL keywords, the use of commas, and the placement of semicolons.

Exam Tips

  • Read the question carefully and understand exactly what it is asking before you start writing.
  • Plan your answer before you start writing to ensure that you cover all the key points.
  • Use clear and concise language to explain your answers.
  • Use examples to illustrate your points where appropriate.
  • Double-check your answers for accuracy and completeness before you submit your exam.

Test Your Knowledge

Ready to check what you've learned? Practice with 9 flashcards covering key definitions and concepts from Databases.

Study Flashcards