Analysis, ER Diagrams, Data Dictionaries, Advanced SQL, Testing & Evaluation.

Analysis

End-User Requirements

The tasks the users need to perform.

e.g., "The receptionist needs to search for a guest by surname."
Functional Requirements

What the database system must do.

  • Must store Customer and Booking details.
  • Must allow querying of bookings by date.
  • Must calculate total costs.

Entity Relationships

ER Diagram (ERD)

Visual map of entities (tables) and relationships.

Customer
———<
Order

One-to-Many Relationship (Crow's Foot)

Entity Occurrence Diagram

Maps specific instances of data.

  • John —
  • Ali —
  • — Order 1
  • — Order 2

Structures

Compound Key

A Primary Key made up of 2 or more attributes. Used to uniquely identify a record when one field isn't enough (often in link entities).

Data Dictionary
Attribute Type Key Validation
cust_ID Int PK Required
name Text Max 50
Query Design

Plan: Fields, Tables, Search Criteria, Sort Order.

Modification

-- INSERT (Add new row)
INSERT INTO Customers (name, age)
VALUES ('Bob', 24);

-- UPDATE (Change data)
UPDATE Customers
SET age = 25
WHERE name = 'Bob';

-- DELETE (Remove row)
DELETE FROM Customers
WHERE name = 'Bob';

Wildcards

Used with LIKE for pattern matching.

%

Many characters

'Sm%' = Smith, Small
_

One character

'H_t' = Hat, Hot
SELECT * FROM Users
WHERE surname LIKE 'Mc%';

Aggregates

Functions that perform a calculation on a set of values.

SUM() Total of column
AVG() Average value
MAX() Highest value
MIN() Lowest value
COUNT() Number of rows
SELECT AVG(price) FROM Products;

Calc & Alias

Computed Values

Performing math on columns (+ - * /).

Alias (AS)

Giving a temporary name to a column/calculation.

SELECT item, price * 0.2 AS vat_amount
FROM Products;

Group & Subquery

GROUP BY

Used with aggregates to group results by a category.

SELECT category, COUNT(*)
FROM Items
GROUP BY category;
Subqueries

Using the result of one query inside another.

SELECT * FROM Items
WHERE price > (
  SELECT AVG(price) FROM Items
);

Test & Eval

Testing

Comparing Actual Results (what the query output) against Expected Results (what you calculated manually).

Evaluation
  • Fitness for Purpose:
    Does the database meet the Functional Requirements? (Can it store/query what was asked?)
  • Accuracy of Output:
    Is the data correct? Are calculations (sums/averages) precise and correct?