Be able to create relationships between tables

Topic 18 – Databases

1. Syllabus‑Map (Cambridge IGCSE ICT)

Section	Topic covered in these notes	What still needs a quick reminder
1‑7  Computer systems, input/output, storage, networks, effects of IT, ICT applications, systems life‑cycle	Context boxes link databases to storage media, networks and the systems life‑cycle.	None – brief links provided.
8  Safety & security	Section 10 (Security & data protection).	None.
9  Audience & copyright	Section 11 (Audience & copyright).	None.
10  Communication (email, internet use)	Section 12 (Communication & sharing results).	None.
11‑16  File management, images, layout, styles, proof‑reading, graphs/charts	Section 13 (Integration with other ICT topics).	None.
17  Document production	Section 13 (Integration – reports as documents).	None.
18  Databases	All sections 2‑9, 14‑15.	None – core content fully covered.
19  Presentations	Section 13 (Using database data in presentations).	None.
20  Spreadsheets	Section 13 (Spreadsheet vs. database comparison).	None.
21  Website authoring	Section 13 (Publishing database data on a simple web page).	None.

2. Relational databases – key ideas

• Table: a set of records (rows) each composed of fields (columns).
• Flat‑file (non‑relational) table: a single worksheet or text file that stores all data in one place – leads to duplication, update anomalies and slow queries.
• Relational database: data are split into several related tables, removing redundancy and enabling powerful queries.

3. Choosing field types and properties

Cambridge expects you to select the correct data‑type, size and validation for every field.

Data‑type (MS Access / MySQL style)	Typical use	Size / format example	Common validation rule
AutoNumber / INTEGER AUTO_INCREMENT	System‑generated identifiers (e.g., StudentID)	Whole numbers, no decimals	Unique → Primary Key
Short Text / VARCHAR(n)	Names, codes, addresses	1‑255 characters (e.g., VARCHAR(50) for Name)	Maximum length, Required if mandatory
Long Text / TEXT	Notes, comments	Up to 65 535 characters	Usually none
Number (Decimal) / DECIMAL(p,s)	Prices, marks, credits	Total digits p and decimal places s (e.g., DECIMAL(5,2))	Range check (e.g., 0 – 100)
Date/Time	Birth dates, enrolment dates	dd/mm/yyyy (or mm/dd/yyyy depending on locale)	Must be a valid calendar date
Yes/No (Boolean)	Checkboxes – e.g., “Paid?”	True / False (or 0 / 1)	None required

Field properties you will usually set

• Data type (as above)
• Field size / precision
• Default value (e.g., 0 for a numeric field)
• Required – cannot be left blank
• Indexed – for primary keys or fast searching
• Validation rule & validation text (friendly error message)

4. Keys – linking the tables

• Primary Key (PK): uniquely identifies each record; must be unique, not null and never changed.
• Foreign Key (FK): a field in a child table that references the PK of a parent table.
• Candidate key: any field that could serve as a PK.
• Composite key: PK made up of two or more fields (rare at IGCSE level).

5. Types of relationships

1. One‑to‑One (1:1) – each record in Table A matches exactly one record in Table B.
2. One‑to‑Many (1:M) – one record in Table A relates to many records in Table B, but each record in Table B relates to only one record in Table A.
3. Many‑to‑Many (M:N) – records in Table A can relate to many records in Table B and vice‑versa. Implemented with a junction (link) table that holds two foreign keys.

Example scenario – School database

Table	Key fields (PK / FK)	Sample fields (type)
Students	StudentID – Integer – PK – AutoNumber	Name – VARCHAR(50), DateOfBirth – DATE, YearGroup – VARCHAR(10)
Courses	CourseID – Integer – PK – AutoNumber	CourseName – VARCHAR(100), Credits – INTEGER
Enrollments	EnrollmentID – Integer – PK – AutoNumber StudentID – FK CourseID – FK	EnrolDate – DATE

• Students ↔ Enrollments – 1 : M (one student can have many enrolments).
• Courses ↔ Enrollments – 1 : M (one course can have many enrolments).
• Students ↔ Courses – M : N, realised through the Enrollments junction table.

6. Steps to create a relationship (generic DBMS – e.g., MS Access, MySQL Workbench)

1. Identify the PK in each table.
2. Decide which table will hold the FK (the “child” table).
3. Open the Relationships or Diagram View window.
4. Drag the PK field from the parent table onto the FK field in the child table.
5. In the dialog box choose:
   • Relationship type (1:1, 1:M, M:N)
   • Check Enforce Referential Integrity
   • Optional actions – Cascade Update, Cascade Delete or Restrict Delete
6. Save the design; the DBMS now automatically checks the rules.

7. Enforcing referential integrity

When active, the DBMS will prevent:

• Inserting a FK value that does not exist in the related PK table.
• Deleting a PK record that is still referenced by a FK (unless cascade delete is chosen).
• Updating a PK value without automatically updating the related FK values (if cascade update is selected).

8. Designing data‑entry forms (Cambridge requirement)

Forms make data entry fast, accurate and user‑friendly.

• Place a clear label next to each control.
• Use the most appropriate control:
  • Text box for Short Text fields.
  • Combo box or list box for a limited set of values (e.g., YearGroup).
  • Check box for Yes/No fields.
  • Date picker for Date fields.
• Set the Tab Order so the cursor moves logically.
• Mark required fields (e.g., StudentID, Name) and display a helpful message if left blank.
• Include command buttons: Save, Delete, Find, Close.
• Apply a consistent style (font, colour, alignment) – this links to Section 15 (styles).

9. Importing external data

IGCSE exams may ask you to import a CSV or TXT file into a table.

1. Prepare the file so the first line contains field names that match the destination table.
2. In the DBMS choose External Data → Import → Text File (or the equivalent wizard).
3. Select the delimiter (comma, tab, semicolon) and confirm the data‑type detection.
4. Map source fields to target fields, set any primary key, and finish the wizard.

10. Security & data protection (Section 8)

• Password‑protect the database file.
• Set user‑level permissions (read‑only vs. read‑write) where the DBMS allows it.
• Back‑up the database regularly – at least weekly for a school system.
• Consider encryption for sensitive personal data (e.g., dates of birth).
• Follow basic data‑privacy principles: collect only what is needed, keep data accurate, and delete records that are no longer required.

11. Audience, copyright and ethical use (Section 9)

• Identify who will use the system (teachers, students, administrators) and tailor the form layout and access rights accordingly.
• When using third‑party data (e.g., a list of course codes), ensure you have permission and give proper credit.
• Never copy a database design word‑for‑word from a commercial product without acknowledgement.

12. Communication – sharing results (Section 10)

• Export query results to .csv or .xlsx for emailing to stakeholders.
• Use the DBMS’s “Send to Mail” feature to attach a report directly to an email.
• Publish a read‑only version of a report on the school intranet – a simple HTML table or PDF works well.

13. Integration with other ICT topics (Sections 11‑21)

• File management (Section 11): store the database in a clearly named folder, use version numbers (e.g., SchoolDB_v2.accdb).
• Images (Section 12): embed a logo on a form or report header.
• Layout & styles (Sections 13‑14): apply a consistent heading style to all reports; use page numbers and footers.
• Proof‑reading (Section 15): run a “Find Duplicates” query to spot data‑entry errors before finalising a report.
• Graphs & charts (Section 16): export a query to Excel and create a bar chart of enrolments per course; then copy the chart back into a database report.
• Document production (Section 17): treat a database report as a document – include a title page, table of contents (generated automatically) and a bibliography if external data were used.
• Presentations (Section 19): import a chart or table from a report into PowerPoint; use “Link Data” so updates in the database refresh the slide.
• Spreadsheets (Section 20) – quick comparison:

  Feature	Spreadsheet	Relational Database
  Data size	Good for small‑to‑medium sets	Handles very large, structured data
  Redundancy	Often duplicated data	Eliminated by normalization
  Multi‑user access	Limited	Designed for concurrent users
  Complex queries	Basic filtering	Powerful SELECT, JOIN, GROUP BY
  Security	File‑level password only	Granular user permissions

• Website authoring (Section 21): a simple HTML page can display data from a database using:

  <table>
      <tr><th>Student</th><th>Course</th></tr>
      <?php
          // pseudo‑code – connect, run query, loop rows
      ?>
  </table>

  (Only a basic illustration is required for the IGCSE exam – you do not need to write full PHP/ASP code.)

14. Manipulating data – queries and functions

14.1 Basic SELECT query

SELECT StudentID, Name, YearGroup
FROM Students
ORDER BY Name ASC;

14.2 Using a WHERE clause (searching)

SELECT Name, DateOfBirth
FROM Students
WHERE YearGroup = 'Year 10';

14.3 Calculations and aggregate functions (1 M relationship)

SELECT CourseID, COUNT(*) AS EnrolledStudents
FROM Enrollments
GROUP BY CourseID
HAVING COUNT(*) > 5
ORDER BY EnrolledStudents DESC;

• COUNT(), SUM(), AVG(), MIN(), MAX() are the standard IGCSE aggregate functions.
• Use HAVING to filter groups after aggregation.

14.4 Updating data while respecting constraints

UPDATE Students
SET YearGroup = 'Year 11'
WHERE StudentID = 1023;

14.5 Deleting with referential integrity

DELETE FROM Students
WHERE StudentID = 2001;   -- fails if enrolments exist unless cascade delete is set

15. Practice activity

1. Identify the primary key in each of the three tables shown in the example.
2. Write the SQL statement that adds a foreign key from Enrollments.StudentID to Students.StudentID and enforces cascade delete.
3. Explain what happens if you try to delete a student who already has enrolment records when referential integrity is active but cascade delete is **not** selected.
4. Design a simple data‑entry form for the Students table – list at least five form‑design considerations you would apply.
5. Import a CSV file called courses.csv (fields: CourseID,CourseName,Credits) into the Courses table. Outline the three main steps you would follow in the DBMS.

16. Sample SQL statements – complete creation of the school database

CREATE TABLE Students (
    StudentID   INT PRIMARY KEY AUTOINCREMENT,
    Name        VARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    YearGroup   VARCHAR(10) NOT NULL
);

CREATE TABLE Courses (
    CourseID    INT PRIMARY KEY AUTOINCREMENT,
    CourseName  VARCHAR(100) NOT NULL,
    Credits     INT CHECK (Credits > 0)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY AUTOINCREMENT,
    StudentID    INT NOT NULL,
    CourseID     INT NOT NULL,
    EnrolDate    DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (CourseID)  REFERENCES Courses(CourseID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

17. Summary

• Relational databases split data into tables linked by primary and foreign keys.
• Select the correct data‑type, size and validation for every field.
• One‑to‑many is the most common relationship; many‑to‑many needs a junction table.
• Enforce referential integrity to avoid orphan records and maintain consistency.
• Design clear, user‑friendly data‑entry forms and remember the required field/validation rules.
• Import CSV/TXT files using the DBMS wizard; export query results for sharing.
• Use SELECT, WHERE, ORDER BY, GROUP BY and aggregate functions to retrieve and analyse data.
• Apply security, backup, audience analysis and copyright considerations – all part of the wider ICT syllabus.
• Remember how databases interact with other ICT topics: spreadsheets, charts, presentations, and simple web pages.