Relational databases and structured query language (SQL)
This topic introduces the fundamental concepts of relational databases, focusing on how data is structured and managed. Students learn to identify key components such as tables, records, fields, and keys, and understand how relational structures help eliminate data inconsistency and redundancy.
Subtopics in this area
Topic Overview
Relational databases are the backbone of modern data storage, organising information into tables with rows and columns. In this topic, you'll learn how to design efficient databases using primary keys, foreign keys, and relationships (one-to-many, many-to-many). Understanding these principles is crucial because real-world applications—from school systems to social media—rely on well-structured databases to avoid data duplication and ensure consistency.
Structured Query Language (SQL) is the standard language for interacting with relational databases. You'll master commands like SELECT, INSERT, UPDATE, and DELETE, as well as filtering with WHERE, sorting with ORDER BY, and joining tables with JOIN. SQL is a practical skill that lets you retrieve exactly the data you need, making it essential for any data-driven career.
This topic fits into the wider Computer Science curriculum by linking data representation, algorithms, and problem-solving. You'll apply logical thinking to design normalised databases and write efficient queries—skills that are tested in both written exams and programming tasks. Mastering databases and SQL gives you a solid foundation for further study in data science, software engineering, and information systems.
Key Concepts
Core ideas you must understand for this topic
- →Primary key: a unique identifier for each record in a table (e.g., StudentID).
- →Foreign key: a field in one table that links to the primary key of another table, creating relationships.
- →Normalisation: organising data to reduce redundancy (e.g., splitting a table into two to avoid repeating data).
- →SQL SELECT statement: used to retrieve data; can include WHERE (filter), ORDER BY (sort), and JOIN (combine tables).
- →Data types: choosing appropriate types (e.g., INTEGER, VARCHAR, DATE) ensures data integrity and efficient storage.
What You Need to Demonstrate
Key skills and knowledge for this topic
- Definition of a database
- Definition of a relational database
- Identification of table, record, field, and data type
- Explanation of primary key and foreign key
- Understanding the role of relational databases in reducing data inconsistency and redundancy
Marking Points
Key points examiners look for in your answers
- Definition of a database
- Definition of a relational database
- Identification of table, record, field, and data type
- Explanation of primary key and foreign key
- Understanding the role of relational databases in reducing data inconsistency and redundancy
Examiner Tips
Expert advice for maximising your marks
- 💡Ensure you can clearly define the difference between a primary key and a foreign key.
- 💡Be prepared to explain why relational databases are preferred over flat-file databases in terms of data integrity.
- 💡Remember that the terms table, record, and field are used for both abstract models and actual implementations.
- 💡Always state the primary key and foreign key when describing a table relationship. This shows you understand how tables link together.
- 💡When writing SQL queries, use single quotes for string values (e.g., WHERE Name = 'Smith') and remember to end statements with a semicolon.
- 💡For normalisation questions, look for repeated data—if you see the same information in multiple rows, it's a sign you need to split the table.
Common Mistakes
Pitfalls to avoid in your exam answers
- Confusing the terms entity and attribute with table and field
- Failing to explain the purpose of a foreign key in linking tables
- Misunderstanding the difference between data inconsistency and data redundancy
- Misconception: 'A primary key can be NULL.' Correction: Primary keys must be unique and NOT NULL—every record must have a value.
- Misconception: 'JOIN always combines all rows from both tables.' Correction: INNER JOIN only returns rows where there is a match; LEFT JOIN returns all rows from the left table even if no match exists.
- Misconception: 'You can use SELECT * in an exam and get full marks.' Correction: While SELECT * works, examiners prefer you to specify column names to show you understand the structure.
Frequently Asked Questions
Common questions students ask about this topic
Before You Start
Prior knowledge that will help with this topic
- •Basic understanding of data types (integer, string, boolean).
- •Familiarity with tables and how data is organised in rows and columns.
- •Logical reasoning skills to design relationships and write precise queries.
Key Terminology
Essential terms to know
- Relational Database Design (Entities, Attributes, Relationships)
- Data Integrity and Normalization (1NF, 2NF, 3NF)
- Structured Query Language (SQL) for DDL and DML
- Database Management Systems (DBMS) functionality and security
Likely Command Words
How questions on this topic are typically asked
Ready to test yourself?
Practice questions tailored to this topic