Databases

What you'll learn

This revision guide covers all database concepts required for the CXC CSEC Information Technology examination. You will learn about database terminology, structures, management systems, normalization, and practical applications. This material aligns directly with the CXC syllabus and reflects the types of questions you will encounter in Paper 2.

Key terms and definitions

Database — An organized collection of related data stored electronically in a structured format that can be accessed, managed, and updated efficiently.

Database Management System (DBMS) — Software that allows users to create, maintain, and manipulate databases while controlling access and ensuring data integrity.

Table — A collection of related data organized in rows (records) and columns (fields) within a database.

Primary key — A field or combination of fields that uniquely identifies each record in a table and cannot contain duplicate or null values.

Foreign key — A field in one table that links to the primary key in another table, establishing a relationship between the two tables.

Normalization — The process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables.

Query — A request for specific information from a database, typically written in Structured Query Language (SQL) or created using a query builder.

Data redundancy — The unnecessary duplication of data within a database, which wastes storage space and can lead to inconsistencies.

Core concepts

Database structures and file types

Databases can be organized using different structures, but relational databases are the most common type tested at CSEC level.

Flat-file databases consist of a single table containing all data. They are simple but become problematic as data grows because they:

• Create excessive data redundancy
• Make updating information difficult and error-prone
• Waste storage space
• Cannot easily represent complex relationships

Relational databases organize data into multiple related tables. This structure:

• Eliminates redundancy by storing each piece of information only once
• Uses primary and foreign keys to link tables
• Allows complex queries across multiple tables
• Maintains data integrity more effectively

A typical Caribbean business example: A restaurant chain like Mario's Pizza across Trinidad operates a relational database with separate tables for Customers, Orders, Menu_Items, and Employees, all linked through appropriate keys.

Database Management Systems (DBMS)

A DBMS provides the interface between users and the database. Common examples include Microsoft Access, MySQL, and Oracle.

Key functions of a DBMS include:

Data definition: Creating and modifying the structure of tables, fields, and relationships

Data manipulation: Adding, modifying, deleting, and retrieving records

Data security: Controlling user access through passwords and permissions to protect sensitive information such as customer credit card details or employee salaries

Data integrity: Enforcing rules to maintain accuracy and consistency, such as validation rules that prevent a customer's date of birth from being in the future

Backup and recovery: Creating copies of data to prevent loss from hardware failure, natural disasters (hurricanes, floods), or human error

Concurrent access control: Managing multiple users accessing the database simultaneously, crucial for systems like airline reservation systems used by Caribbean Airlines where multiple agents book seats simultaneously

Tables, records, fields and data types

Understanding the components of database tables is fundamental.

Fields (columns) represent individual attributes:

• Field name: Identifies the attribute (e.g., StudentID, FirstName, CourseCode)
• Data type: Defines what kind of data can be stored
• Field size: Specifies maximum length
• Validation rules: Control what data is acceptable

Common data types:

• Text/String: Alphabetic and alphanumeric data (names, addresses, product codes)
• Number/Integer: Whole numbers for calculations (quantity, age)
• Currency: Monetary values (price in TTD, JMD, or BBD)
• Date/Time: Calendar dates and times (DateOfBirth, OrderTime)
• Boolean/Yes-No: True/false values (PaidInFull, IsActive)
• Memo/Long Text: Large text blocks (product descriptions, comments)

Records (rows) represent complete entries about a single entity. Each record in a Students table contains all information about one student.

Example table structure for a Caribbean school:

Students Table

StudentID	FirstName	LastName	DateOfBirth	FormClass	IslandOfOrigin
S001	Marcus	Williams	15/03/2008	5A	Barbados
S002	Alisha	Singh	22/07/2008	5B	Trinidad

Relationships and keys

Relationships connect tables in a relational database, eliminating redundancy while maintaining data connections.

Types of relationships:

One-to-many: The most common relationship type. One record in Table A relates to multiple records in Table B, but each record in Table B relates to only one record in Table A.

Example: One customer can place many orders, but each order belongs to only one customer. In a Grenada spice company database, one Supplier can provide many Products, but each Product comes from one Supplier.

One-to-one: One record in Table A relates to exactly one record in Table B. This is rare and used for security or organizational purposes.

Example: Each employee has one salary record containing confidential information stored in a separate table.

Many-to-many: Records in Table A relate to multiple records in Table B and vice versa. This requires a junction table to implement.

Example: Students enroll in multiple subjects, and each subject has multiple students. A junction table called Enrollment links Students and Subjects.

Keys establish these relationships:

The primary key in the "one" side table appears as a foreign key in the "many" side table. In Orders and Customers tables, CustomerID is the primary key in Customers and a foreign key in Orders.

Composite keys consist of two or more fields combined to create a unique identifier when no single field can serve as the primary key. In the Enrollment table, StudentID and SubjectCode together form the composite primary key.

Normalization

Normalization removes data redundancy and organizes data efficiently. At CSEC level, you must understand First, Second, and Third Normal Forms.

First Normal Form (1NF) requirements:

• Eliminate repeating groups
• Each field contains atomic (indivisible) values
• Each record is unique with a primary key

Unnormalized example:

OrderID	CustomerName	Products
001	John's Shop	Sorrel, Ginger Beer, Mauby

1NF version: Split into separate records, one product per row.

Second Normal Form (2NF) requirements:

• Must be in 1NF
• All non-key fields depend on the entire primary key
• Eliminates partial dependencies

This applies when you have a composite primary key. If certain fields depend on only part of the key, move them to a separate table.

Third Normal Form (3NF) requirements:

• Must be in 2NF
• No non-key field depends on another non-key field
• Eliminates transitive dependencies

Example: A table storing OrderID, ProductID, ProductName, and SupplierName violates 3NF if SupplierName depends on ProductID rather than OrderID. Move product and supplier information to separate tables.

Benefits of normalization:

• Reduces storage requirements
• Eliminates update anomalies
• Maintains data consistency
• Simplifies maintenance

Queries and data retrieval

Queries extract specific information from databases based on defined criteria. They answer questions like "Which students scored above 80% in Mathematics?" or "What products did Caribbean customers order in December?"

Query types:

Select queries: Retrieve data matching specific conditions without changing the database

Parameter queries: Prompt the user for input (e.g., "Enter the territory: [Jamaica]")

Calculated queries: Perform calculations on data (e.g., Total = Quantity × Price)

Update queries: Modify existing data in bulk

Query components:

SELECT clause: Specifies which fields to display

• SELECT FirstName, LastName, Territory

FROM clause: Indicates which table(s) to query

• FROM Customers

WHERE clause: Defines filtering conditions

• WHERE Territory = "Jamaica" AND CreditLimit > 5000

ORDER BY clause: Sorts results

• ORDER BY LastName ASC

Operators in queries:

• Comparison: = (equal), > (greater than), < (less than), >= , <=, <> (not equal)
• Logical: AND, OR, NOT
• Pattern matching: LIKE "Car%" (finds Caribbean, Carnival, etc.)
• Range: BETWEEN 100 AND 500

Example query for a bookstore in Bridgetown:

SELECT BookTitle, Author, Price
FROM Books
WHERE Category = "Caribbean Literature" AND Price < 50
ORDER BY Author ASC

This retrieves titles, authors, and prices of Caribbean literature books costing less than BBD$50, sorted alphabetically by author.

Worked examples

Example 1: Identifying primary and foreign keys

Question: The following tables are part of a library database system used in schools across St. Lucia:

Books (BookID, Title, AuthorID, ISBN, YearPublished) Authors (AuthorID, AuthorName, Nationality) Loans (LoanID, BookID, StudentID, DateBorrowed, DateDue) Students (StudentID, StudentName, FormClass, ContactNumber)

(a) Identify the primary key in each table. [4 marks] (b) Identify TWO foreign keys and state which tables they link. [4 marks]

Solution:

(a) Primary keys:

• Books table: BookID [1 mark]
• Authors table: AuthorID [1 mark]
• Loans table: LoanID [1 mark]
• Students table: StudentID [1 mark]

(b) Foreign keys:

• AuthorID in the Books table links to AuthorID in the Authors table / links Books and Authors [2 marks]
• BookID in the Loans table links to BookID in the Books table / links Loans and Books [1 mark]
• StudentID in the Loans table links to StudentID in the Students table / links Loans and Students [1 mark]

(Any two foreign keys with correct explanation for full marks)

Example 2: Normalization

Question: A craft vendor at the Castries market keeps customer orders in this format:

OrderNo	CustomerName	CustomerEmail	Items	ItemPrices
1001	Maria Charles	maria@email.com	Basket, Hat, Bag	45, 30, 60
1002	David Greene	david@email.com	Hat, Bag	30, 60

(a) Explain why this table is NOT in First Normal Form. [2 marks] (b) Normalize the table to Third Normal Form (3NF). Show the resulting tables with appropriate field names and indicate primary and foreign keys. [6 marks]

Solution:

(a) The table is not in 1NF because:

• The Items field contains repeating groups / multiple values [1 mark]
• The ItemPrices field contains multiple values / is not atomic [1 mark]

(b) Normalized tables:

Customers table

• CustomerID (PK)
• CustomerName
• CustomerEmail [1 mark for appropriate customer table]

Orders table

• OrderNo (PK)
• CustomerID (FK)
• OrderDate [1 mark for orders table with correct keys]

Items table

• ItemID (PK)
• ItemName
• ItemPrice [1 mark for items table]

OrderDetails table (junction table)

• OrderNo (FK)
• ItemID (FK)
• Quantity
• (OrderNo + ItemID form composite PK) [2 marks for junction table with composite key]

[1 mark for correctly indicating all PKs and FKs]

Example 3: Writing queries

Question: A database for a Jamaican sports club contains a Members table with the following fields: MemberID, FirstName, LastName, MembershipType, DateJoined, Parish, AnnualFee

Write a query to: (a) Display the FirstName, LastName, and Parish of all members who joined after 01/01/2023. [3 marks] (b) Calculate the total annual fees for members from Kingston. [3 marks]

Solution:

(a)

SELECT FirstName, LastName, Parish [1 mark]
FROM Members [1 mark]
WHERE DateJoined > #01/01/2023# [1 mark]

(b)

SELECT SUM(AnnualFee) [1 mark]
FROM Members [1 mark]
WHERE Parish = "Kingston" [1 mark]

Common mistakes and how to avoid them

• Confusing primary and foreign keys: Remember that the primary key uniquely identifies records in its own table, while a foreign key appears in a different table to create the link. The foreign key matches the primary key from another table.

• Incorrectly identifying relationships: Count how many records on each side. If one customer has multiple orders, it's one-to-many, not many-to-many. Many-to-many requires both sides to have multiple connections.

• Forgetting composite keys in junction tables: When resolving many-to-many relationships, the junction table typically needs a composite primary key made from both foreign keys.

• Misunderstanding normalization levels: Know the sequence: 1NF removes repeating groups, 2NF removes partial dependencies (when using composite keys), 3NF removes transitive dependencies (non-key fields depending on other non-key fields).

• Using incorrect query syntax: Pay attention to quotation marks (text values), hash symbols (dates in Access), and spelling of keywords (SELECT, FROM, WHERE). Practice writing queries exactly as required.

• Overlooking validation rules versus data types: Data type controls what category of data (text, number, date), while validation rules control specific acceptable values (age between 11 and 19).

Exam technique for "Databases"

• Command words matter: "Identify" requires you to name something specific (1 mark each). "Explain" requires you to give a reason or describe how something works (usually 2-3 marks). "Describe" requires characteristics or features. "Normalize" means show the actual tables with fields and keys.

• Table diagrams should be clear: When showing normalized tables, underline primary keys or write (PK) beside them. Indicate foreign keys with (FK). Draw lines or arrows to show relationships if the question requires it.

• Query questions require precision: Write SQL keywords in capitals (SELECT, WHERE, ORDER BY). Put text values in quotes, dates in appropriate delimiters. Even if your syllabus uses QBE (Query By Example), understand the SQL equivalent.

• Allocate time by marks: A 2-mark question needs less detail than a 6-mark question. If asked to normalize to 3NF for 6 marks, show all three tables minimum with clear labeling of keys to earn full credit.

Quick revision summary

Databases organize related data in tables with records (rows) and fields (columns). A DBMS manages data definition, manipulation, security, and integrity. Primary keys uniquely identify records; foreign keys link tables. Relational databases use one-to-many, one-to-one, or many-to-many relationships. Normalization (1NF, 2NF, 3NF) eliminates redundancy and improves data integrity. Queries retrieve specific data using SELECT, FROM, WHERE, and ORDER BY clauses with appropriate operators. Understanding these concepts and practicing with Caribbean-contextualized examples prepares you for CSEC examination success.