ADUVIE

ADUVIE INTERNATIONAL SCHOOL | HIGH SCHOOL ICT

Dual-Board Mastery: Cambridge IGCSE 0417 & WASSCE Digital Technology

0% Complete
WEEK 04 CURRICULAR SUITE

Relational Databases I: Tables, Data Types & Imports

Syllabus Alignment: Cambridge 0417 Unit 18; WASSCE Computer Studies (Database Management)
Mission Objective: Master relational database architecture, field data typing, primary and foreign key assignment, and error-free CSV data importation into Microsoft Access.

🎥 Video Lessons 💻 Practical Work ⚙️ Practice Tool 🧠 Notes 🃏 Key Words 🕵️ Common Mistakes 📝 Quiz 🎯 Self-Check

🎥 Video Lessons

Cambridge IGCSE · WAEC · Core Theory
Topic: Cambridge 0417 Paper 2: Access Database Import, Data Types & Keys  |  Duration: 15:20
What to look out for: Importing CSV data into Microsoft Access, configuring data types, assigning primary keys, and establishing 1-to-many relationships.

💻 Practical Work — Exam Task & Source Files

Cambridge IGCSE 0417/21 February/March 2022 (Task 3 - Database Imports)
★ Required Practical Source Files (1-Click Google Classroom CDN Download):
📁 M2221MAKERS.csv Download CDN 📁 M2221DRIVES.csv Download CDN 📁 M2221EVIDENCE.rtf Download CDN

Step-by-Step Task Checklist (tick each step as you complete it):

⚙️ Hands-On Practice Tool

Interactive

Relational Database Schema Linker (Primary Key -> Foreign Key)

Schema Visualizer

Connect tables tblMakers and tblDrives from Cambridge 0417/21 March 2022. Click the matching fields to establish a 1-to-many relationship with referential integrity.

tblMakers (Parent Table)
  • 🔑 Maker_Code [Short Text]
  • Maker [Short Text]
  • City [Short Text]
  • Country [Short Text]
tblDrives (Child Table)
  • 🔑 Drive_Code [Short Text]
  • Model_Code [Short Text]
  • Model [Short Text]
  • 🔗 Maker_Code [Foreign Key]
  • Price [Currency]

🧠 Notes & Key Concepts

Theory Summary
🔹
• Flat-file vs Relational: Flat-file databases store all data in a single table, causing massive data duplication/redundancy and update anomalies. Relational databases separate data into related tables linked by common keys.
🔹
• Keys: Primary Key (unique identifier for each record in a table, e.g. `Student_ID`), Foreign Key (a primary key from one table placed into another table to establish a link), Compound Key (two or more fields combined to create a unique identifier).
🔹
• Relationships: One-to-Many (1:M) is standard. One customer has many orders; one doctor has many patients. Enforcing Referential Integrity prevents orphaned foreign keys.
🔹
• Field Data Types: Short Text (alphanumeric up to 255 chars), Long Text, Number (Integer, Long Integer, Double), Date/Time, Currency, Yes/No (Boolean).
★ Cambridge / WAEC Model Scenario:

Identify the appropriate data types for a table named 'tblBooks': `Book_ID` (e.g. BK091), `Title`, `Date_Published`, `Price`, `In_Stock`.

💡 Worked Solution & Rationale:

• Book_ID: Short Text (contains alphanumeric letters 'BK')
• Title: Short Text
• Date_Published: Date/Time (formatted as DD/MM/YYYY)
• Price: Currency (formatted to 2 decimal places)
• In_Stock: Yes/No (Boolean)

🃏 Key Words — Click a Card to Reveal the Definition

6 Terms
Card 01

Primary Key

👆 Click / Tap to Reveal Definition

Definition 01

A unique identifier assigned to each record in a database table (e.g. Student_ID, Drive_Code). Cannot be null.

Card 02

Foreign Key

👆 Click / Tap to Reveal Definition

Definition 02

A field in one table that links to the primary key of another table to establish a relational link.

Card 03

Referential Integrity

👆 Click / Tap to Reveal Definition

Definition 03

Database constraint ensuring foreign key values must match a valid primary key in the parent table.

Card 04

Flat-File Database

👆 Click / Tap to Reveal Definition

Definition 04

A database that stores all data in a single table, resulting in data redundancy and update anomalies.

Card 05

Data Redundancy

👆 Click / Tap to Reveal Definition

Definition 05

Unnecessary duplication of data across multiple records, consuming excess storage and causing inconsistencies.

Card 06

Compound Key

👆 Click / Tap to Reveal Definition

Definition 06

A composite primary key constructed by combining two or more distinct fields to achieve uniqueness.

🕵️ Common Exam Mistakes — Can You Spot the Error?

Examiner Trap
⚠️ Candidate Error Trap:

A student chose 'Number' as the data type for a `Telephone_Number` field because phone numbers consist of digits.

🎯 Your Detective Mission:

Explain why 'Number' is an incorrect data type for telephone numbers and state the correct type.

📝 Quiz — Test Yourself (10 Questions)

Instant Marking & Explanation
1. Which database field uniquely identifies each record in a table?
A) Foreign Key
B) Primary Key
C) Index Key
D) Candidate Key
Mark Scheme Rationale: A primary key is a unique record identifier.
2. What problem is caused by storing all company data in a single flat-file table?
A) File size is too small
B) Data redundancy and update anomalies
C) Relational integrity errors
D) Faster queries
Mark Scheme Rationale: Flat files lead to duplicate records and data redundancy.
3. Which data type is most appropriate for a `Passport_Number` field containing 'A1290847'?
A) Number
B) Short Text
C) Autonumber
D) Currency
Mark Scheme Rationale: Alphanumeric text containing letters and digits requires Short Text.
4. What is the foreign key in a relational database?
A) An encrypted key
B) A primary key of one table placed into another table to link them
C) A backup key
D) An index field
Mark Scheme Rationale: Foreign keys link related records across tables.
5. What does enforcing 'Referential Integrity' prevent?
A) High file sizes
B) Orphaned records in related child tables
C) Duplicate primary keys
D) Text formatting errors
Mark Scheme Rationale: Referential integrity ensures foreign keys always match an existing parent primary key.
6. Which data type should be selected for an `Annual_Salary` field?
A) Integer
B) Short Text
C) Currency
D) Date/Time
Mark Scheme Rationale: Currency data type ensures financial symbols and precise decimal rounding.
7. What type of relationship exists between 'Classes' and 'Students' in a school?
A) One-to-One
B) One-to-Many
C) Many-to-Many
D) None
Mark Scheme Rationale: One class contains many students.
8. When importing a CSV file into Access, what must be done if the first row contains field labels?
A) Delete the first row in Notepad
B) Check 'First Row Contains Field Names'
C) Change data type to Boolean
D) Skip import
Mark Scheme Rationale: Checking this option assigns header labels automatically.
9. Which data type is best suited to record whether a student has paid their fees?
A) Number
B) Yes/No (Boolean)
C) Short Text
D) Memo
Mark Scheme Rationale: Boolean/Yes-No represents two-state flags.
10. What error occurs if duplicate values are entered into a Primary Key field?
A) Syntax Error
B) Primary Key Violation / Duplicate Key Error
C) Overflow Error
D) Divide by Zero
Mark Scheme Rationale: Primary keys strictly forbid duplicate values.

Your Score: 0 / 10 (0%)

Answer the questions above to see your score.

🎯 How Did You Find This Week?

Self-Check

1. Rate your understanding of Week 04 (Relational Databases I: Tables, Data Types & Imports):

🏆 Got it — Exam Ready
⚖️ Mostly — Needs More Practice
🚨 Not Yet — Need Help

2. Next step: Open your YEAR 12 ICT & DIGITAL TECHNOLOGY — LIVING EXAM TRACKER.xlsx on Google Classroom and update your score for this week.