Understand database relationships (one-to-one, one-to-many)

Topic 10 – Database and File Concepts

Learning Objectives (Cambridge IGCSE/AS & A‑Level IT 9626)

• Identify appropriate data types, field sizes and key fields (primary, compound/composite, foreign).
• Explain and enforce referential integrity (ON DELETE/UPDATE actions).
• Compare flat‑file and relational database structures.
• Describe the three relationship types – one‑to‑one, one‑to‑many, many‑to‑many – and represent them in conceptual, logical and physical ER diagrams.
• Create tables with validation (CHECK, NOT NULL) and verification (DEFAULT) rules; design forms, reports and switchboards.
• Construct and use the full range of query types (static, dynamic, simple, complex, nested, summary/aggregate, cross‑tab) and perform searching, sorting, duplicate removal and calculations.

---

1. Data Types, Field Sizes & Key Fields

1.1 Common data types (Cambridge 9626)

Data type	Typical use	Field‑size limits
INTEGER	Whole numbers	‑2 147 483 648 to 2 147 483 647 (4 bytes)
SMALLINT	Small whole numbers	‑32 768 to 32 767 (2 bytes)
DECIMAL(p,s)	Fixed‑point numbers (e.g., prices)	p = total digits (max 38), s = digits after decimal (0 ≤ s ≤ p)
CHAR(n)	Fixed‑length text	n = 1‑255 characters (padded with spaces)
VARCHAR(n)	Variable‑length text	n = 1‑65 535 characters (actual length stored)
DATE	Calendar dates	‘YYYY‑MM‑DD’ (range 1000‑01‑01 to 9999‑12‑31)
TIME	Time of day	‘HH:MM:SS’ (00:00:00‑23:59:59)
BOOLEAN	True / False values	0 = FALSE, 1 = TRUE

1.2 Key‑field definitions

• Primary key (PK) – uniquely identifies each record; must be NOT NULL and unique.
• Compound / Composite key – a PK made up of two or more fields when a single field cannot guarantee uniqueness.
• Foreign key (FK) – a field (or group of fields) that references the PK of another table, establishing a relationship.
• Composite foreign key – an FK consisting of multiple columns, used when the referenced PK is composite.

1.3 Validation vs. Verification

Aspect	Validation (rules that *prevent* bad data)	Verification (rules that *supply* default/derived data)
NOT NULL	Disallows missing values	—
CHECK (condition)	Ensures values meet a condition (e.g., CHECK (Credits BETWEEN 1 AND 10))	—
UNIQUE	Prevents duplicate values in a column	—
DEFAULT value	—	Provides a value when none is entered (e.g., StockQty INT DEFAULT 0)

1.4 Example – Defining keys and constraints

CREATE TABLE Course (
    CourseID   CHAR(6) PRIMARY KEY,
    Title      VARCHAR(100) NOT NULL,
    Credits    SMALLINT CHECK (Credits BETWEEN 1 AND 10)
);

CREATE TABLE StudentCourse (
    StudentID  CHAR(8),
    CourseID   CHAR(6),
    Semester   CHAR(6),
    PRIMARY KEY (StudentID, CourseID),          -- compound PK
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
);

---

2. Flat‑File vs. Relational Database

Aspect	Flat‑file	Relational DB
Structure	Single table (CSV, TXT, spreadsheet)	Multiple related tables (normalised)
Data redundancy	High – same data repeated in many rows	Low – PK/FK links minimise duplication
Integrity control	Manual checks only	Built‑in constraints (PK, FK, UNIQUE, CHECK)
Query capability	Simple filters (e.g., Excel auto‑filter)	Complex queries, joins, sub‑queries, aggregates
Concurrency	Single‑user or read‑only sharing	Multi‑user, transaction support
Typical use	Very small data sets, log files, data exchange	Any application needing reliable storage, reporting, security

---

3. Database Relationships & ER Diagrams

3.1 Cardinality overview

• One‑to‑One (1:1) – each record in Table A matches **exactly one** record in Table B.
• One‑to‑Many (1:N) – a record in Table A can be linked to **many** records in Table B, but each record in Table B links to **only one** record in Table A.
• Many‑to‑Many (M:N) – records in Table A can relate to many records in Table B and vice‑versa; implemented via a **junction (associative) table**.

3.2 ER‑Diagram levels (Cambridge terminology)

• Conceptual ERD – high‑level entities & relationships, no attributes; used for stakeholder discussion.
• Logical ERD – entities, attributes, primary keys and cardinalities; still DBMS‑independent.
• Physical ERD – concrete table structures, data types, field sizes, indexes and constraints; ready for implementation.

Transition tip: Start with a conceptual diagram, add attributes to obtain the logical diagram, then translate each entity into a table (adding PK/FK, data types, constraints) to produce the physical diagram.

3.3 One‑to‑One (1:1)

• Typical uses: optional or sensitive data, splitting a very wide table, modelling a subclass.
• Implementation: place a UNIQUE foreign key in either table; the table holding the optional data usually contains the FK.

Example – Student & StudentPassport

CREATE TABLE Student (
    StudentID CHAR(8) PRIMARY KEY,
    Name      VARCHAR(100) NOT NULL
);

CREATE TABLE StudentPassport (
    PassportID CHAR(9) PRIMARY KEY,
    StudentID  CHAR(8) UNIQUE,               -- enforces 1:1
    PassportNo VARCHAR(20) NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

3.4 One‑to‑Many (1:N)

• Most common relationship – “parent” (one) side and “child” (many) side.
• Implementation: the child table contains a foreign key referencing the parent’s primary key.

Example – Department & Employee

CREATE TABLE Department (
    DeptID   SMALLINT PRIMARY KEY,
    DeptName VARCHAR(100) NOT NULL
);

CREATE TABLE Employee (
    EmpID    INT PRIMARY KEY,
    DeptID   SMALLINT,
    EmpName  VARCHAR(100) NOT NULL,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

3.5 Many‑to‑Many (M:N)

• Direct M:N is not allowed in a relational DB; it is resolved with a junction table.
• The junction table’s PK is usually a composite of the two foreign keys.

Example – Students enrol in Courses

CREATE TABLE Student (
    StudentID CHAR(8) PRIMARY KEY,
    Name      VARCHAR(100) NOT NULL
);

CREATE TABLE Course (
    CourseID CHAR(6) PRIMARY KEY,
    Title    VARCHAR(100) NOT NULL
);

CREATE TABLE Enrolment (                     -- junction table
    StudentID CHAR(8),
    CourseID  CHAR(6),
    EnrolDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
);

3.6 Visual snippets of ER diagrams

Below are simplified ASCII sketches – replace with proper drawing tools (e.g., Lucidchart, draw.io) for exam work.

Conceptual (entities only)
--------------------------
[Student]      [Course]
    |               |
    +-------+-------+
            |
        [Enrolment]

Logical (attributes & PK/FK)
----------------------------
[Student]                [Course]
PK StudentID             PK CourseID
Name                     Title

[Enrolment]
PK (StudentID,CourseID)
FK StudentID → Student
FK CourseID  → Course
EnrolDate

Physical diagrams would add data types, field sizes, and constraints exactly as shown in the CREATE TABLE statements above.

3.7 Referential integrity

• Ensures that every foreign‑key value matches an existing primary‑key value.
• Actions that can be defined for ON DELETE or ON UPDATE:
  • CASCADE – propagate the change to child rows.
  • SET NULL – replace the foreign key with NULL.
  • RESTRICT (or NO ACTION) – block the change if dependent rows exist.

SQL example – cascade delete from Department to Employee

ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Department
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
ON DELETE CASCADE
ON UPDATE RESTRICT;

---

4. Designing & Creating a Relational Database

1. Analyse requirements – list entities, attributes, and decide primary keys.
2. Choose data types & field sizes – use the limits in Section 1.1.
3. Normalise to 3NF – eliminate repeating groups, partial and transitive dependencies.
4. Define relationships – decide 1:1, 1:N or M:N; draw a logical ERD.
5. Write CREATE TABLE statements – include:
   • PK and FK definitions
   • UNIQUE, NOT NULL, CHECK (validation)
   • DEFAULT values (verification)
   • Indexes for performance (optional but recommended)
6. Populate tables – INSERT statements or import from CSV/flat files.
7. Test integrity – attempt to insert invalid rows; confirm that constraints fire.
8. Build user‑interface objects (Access/LibreOffice Base):
   • Forms – layout, labels, text boxes, combo boxes, input masks, validation rules.
   • Reports – grouping, totals, headers/footers, conditional formatting.
   • Switchboard (navigation menu) – buttons linking to forms, queries, reports.

4.1 Validation & verification examples

CREATE TABLE Product (
    ProductID   INT PRIMARY KEY,
    ProductName VARCHAR(80) NOT NULL,
    Price       DECIMAL(8,2) CHECK (Price >= 0),
    StockQty    INT DEFAULT 0 CHECK (StockQty >= 0)
);

4.2 Simple checklist for an Access form

1. Set the form’s **Record Source** to the appropriate table or query.
2. Add controls (text boxes, combo boxes) for each field you want to edit.
3. Define **Input Masks** (e.g., dates, phone numbers) on the control’s property sheet.
4. Enter **Validation Rule** and **Validation Text** for field‑level checks.
5. Use the **Form Footer** for navigation buttons (Next, Previous, New, Delete).
6. Test the form by entering valid and invalid data; ensure errors appear as expected.

---

5. Query Types (Cambridge 9626 terminology)

Query type	Purpose	Typical SQL construct
Static query	Returns a fixed result set (no parameters)	SELECT … FROM … WHERE …;
Dynamic query	Prompts the user for criteria at run‑time	SELECT … FROM … WHERE Field = [Enter value];
Simple query	One table, no aggregation or joins	SELECT Name, Salary FROM Employee;
Complex query	Multiple tables, joins, calculations, sub‑queries	SELECT d.DeptName, COUNT(e.EmpID) AS NoOfStaff FROM Department d LEFT JOIN Employee e ON d.DeptID = e.DeptID GROUP BY d.DeptName;
Nested query	Query inside another query (sub‑select)	SELECT * FROM Employee WHERE DeptID IN (SELECT DeptID FROM Department WHERE DeptName='HR');
Summary (aggregate) query	Uses aggregate functions (SUM, AVG, MIN, MAX, COUNT)	SELECT AVG(Price) AS AvgPrice FROM Product;
Cross‑tab (pivot) query	Displays aggregated data in a matrix format	In Access: Query Design → Crosstab or SQL Server PIVOT syntax

When to use each type

• Static – routine reports (e.g., end‑of‑month sales).
• Dynamic – searches where the user supplies a filter (e.g., find a customer by name).
• Simple – quick look‑ups on a single table.
• Complex – reports requiring joins, calculations or grouping.
• Nested – when a condition depends on a separate query result.
• Summary – totals, averages, counts, min/max.
• Cross‑tab – matrix views such as “sales per month per region”.

---

6. Data Manipulation, Searching, Sorting & Cleaning

6.1 Searching & performance tips

• Use WHERE to filter rows; it is evaluated before GROUP BY or HAVING.
• Indexes on columns used in WHERE, JOIN, or ORDER BY dramatically improve speed.
• Prefer WHERE over HAVING when the condition does not involve aggregates.

6.2 Sorting

SELECT * FROM Employee
ORDER BY DeptID ASC, Salary DESC;

6.3 Duplicate‑record detection & removal

-- Find duplicates based on Name and DOB
SELECT Name, DOB, COUNT(*) AS DupCount
FROM Student
GROUP BY Name, DOB
HAVING COUNT(*) > 1;

-- Delete duplicates, keeping the earliest StudentID
DELETE s1
FROM Student s1
JOIN Student s2
  ON s1.Name = s2.Name
 AND s1.DOB  = s2.DOB
 AND s1.StudentID > s2.StudentID;

6.4 Calculations in queries

• Arithmetic: Price * Quantity AS LineTotal
• Conditional logic: CASE WHEN Qty > 0 THEN 'In stock' ELSE 'Backorder' END AS StockStatus
• String concatenation (SQL Server): FirstName + ' ' + LastName AS FullName

---

7. Summary Checklist for the Exam

• Can you list the main data types and their field‑size limits?
• Do you know how to write CREATE TABLE statements with PK, FK, UNIQUE, NOT NULL, CHECK and DEFAULT?
• Can you explain referential integrity and the three ON DELETE/UPDATE actions?
• Are you able to draw conceptual, logical and physical ER diagrams and convert one level to the next?
• Do you understand when to use 1:1, 1:N and M:N relationships and how to implement each?
• Can you design a simple form, report and switchboard in Access (or equivalent) and apply input masks/validation rules?
• Can you write each of the seven query types and know when each is appropriate?
• Do you know how to remove duplicates, sort data efficiently and use indexes to improve performance?