Databases (Copy)

1. Defining a Single-Table Database

• Database:
  • An organised collection of data stored electronically so that it can be easily accessed, managed, and updated.
  • Can consist of one or more tables; here, focus is on a single-table database.
• Single-table database:
  • A structure containing rows (records) and columns (fields) storing related data.
  • All data relates to the same entity type (e.g., customers, products, employees).
  • Each field stores one type of data (e.g., name, date of birth, price).
• Fields (columns):
  • Named attributes of the entity.
  • Example for a Student table: StudentID, Name, DateOfBirth, Grade, FeesPaid.
• Records (rows):
  • Each record represents one complete set of data for an instance of the entity.
  • Example: One student’s details in a Student table.
• Validation:
  • Methods used to ensure entered data is reasonable, correct, and useful before storage.
  • Examples:
    • Range check – ensures a numeric value is within a set range.
    • Presence check – ensures a required field is not empty.
    • Format check – ensures data follows a pattern (e.g., date in DD/MM/YYYY).
    • Type check – ensures the correct data type is entered.

2. Suggesting Suitable Basic Data Types

When designing a database, each field must be given an appropriate data type.

• Text / Alphanumeric:
  • Stores letters, numbers, and symbols not used for calculations.
  • Example: Name, Address, Postcode.
• Character:
  • Stores a single character only (letter, number, or symbol).
  • Example: Gender field storing M or F.
• Boolean:
  • Stores only two possible values: TRUE/FALSE, Yes/No, or 1/0.
  • Example: FeesPaid in a student database.
• Integer:
  • Whole numbers only, no decimal points.
  • Example: NumberOfUnits, Age.
• Real:
  • Numbers that may include decimal points.
  • Example: Price, Temperature, Weight.
• Date/Time:
  • Stores calendar dates, times, or both.
  • Example: DateOfBirth, HireDate, OrderTime.

3. Primary Key

• Definition:
  • A field (or a combination of fields) whose value uniquely identifies each record in the table.
  • No two records can have the same primary key value.
• Purpose:
  • Ensures each record is unique.
  • Allows efficient retrieval, updating, or deletion of records.
  • Prevents duplication of data.
• Suitable primary key examples:
  • StudentID in a student table.
  • ISBN in a books table.
  • EmployeeNumber in an employee table.

4. SQL (Structured Query Language) Basics

SQL is used to manage and retrieve data from databases.
Here, limited to basic commands for a single-table database.

SELECT

• Used to choose which fields to display in the output.
• Example:

  SELECT Name, Age
  FROM Students;
  

  Displays only the Name and Age columns from the Students table.

FROM

• Specifies the table from which data will be retrieved.
• Always follows SELECT.

WHERE

• Filters records according to a condition.
• Example:

  SELECT Name
  FROM Students
  WHERE Age > 18;
  

  Shows only students older than 18.

ORDER BY

• Sorts the output in ascending (ASC) or descending (DESC) order.
• Example:

  SELECT Name, Age
  FROM Students
  ORDER BY Age DESC;
  

  Lists students from oldest to youngest.

SUM

• Calculates the total of numeric values in a field.
• Example:

  SELECT SUM(Marks)
  FROM Students;
  

  Returns the sum of all Marks values.

COUNT

• Returns the number of records meeting a given condition.
• Example:

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

  Counts students older than 18.

5. Understanding SQL Output

When given a table and an SQL statement, you must determine the exact result it will produce.

Example Table – Products

Example SQL:

SELECT Name
FROM Products
WHERE Price > 5
ORDER BY Price DESC;

Output:

Explanation:

• Only products with price > 5 are selected.
• Ordered from highest to lowest price.

6. Example – End-to-End Database Design Task

Scenario: A school wants a single-table database to store club members’ details:

• Full Name
• Age
• Gender
• Membership Fee Paid?
• Date Joined

Step 1: Decide fields and data types

Step 2: Identify primary key

• MemberID – unique for every member.

Step 3: Example SQL Queries

1. List all members:

   SELECT * FROM Members;
   

2. List names of members who have paid fees:

   SELECT FullName
   FROM Members
   WHERE FeePaid = TRUE;
   

3. Count number of members aged over 18:

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

4. List all members in order of DateJoined (earliest first):

   SELECT FullName, DateJoined
   FROM Members
   ORDER BY DateJoined ASC;