Data Modelling — CCEA A-Level Digital Skills & IT Revision
Entity Relationship Modelling is used to design databases by identifying entities, attributes, and relationships. It involves constructing ER diagrams to r
Topic Synopsis
Entity Relationship Modelling is used to design databases by identifying entities, attributes, and relationships. It involves constructing ER diagrams to represent data structures clearly.
Key Concepts & Core Principles
- Entities and Attributes: An entity is a real-world object or concept (e.g., Customer, Order). Attributes are properties that describe an entity (e.g., CustomerID, Name, Email). Each entity must have a primary key (unique identifier).
- Relationships and Cardinality: Relationships link entities (e.g., a Customer places an Order). Cardinality defines the number of instances involved — one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). Many-to-many relationships must be resolved using a linking table.
- Normalisation: The process of organising data to reduce redundancy and dependency. You need to understand First (1NF), Second (2NF), and Third (3NF) normal forms. For CCEA, you must be able to normalise a set of attributes to 3NF, identifying functional dependencies and removing transitive dependencies.
- Entity-Relationship Diagrams (ERDs): A graphical tool to represent entities as rectangles, attributes as ovals, and relationships as diamonds. You must be able to draw and interpret ERDs using standard notation (e.g., Chen or crow's foot).
- Primary and Foreign Keys: A primary key uniquely identifies each record in a table. A foreign key is a field in one table that matches the primary key of another table, creating a link between them. Foreign keys enforce referential integrity.
Exam Tips & Revision Strategies
- Practice drawing ER diagrams from scenarios.
- Label all relationships clearly.
- Check for many-to-many relationships and resolve them.
- When normalising, show step-by-step transformations: list the original table, identify anomalies, decompose to 1NF, then 2NF, then 3NF, clearly indicating keys and functional dependencies at each stage.
- In explanation questions, use concrete examples to illustrate anomalies before and after normalisation; this demonstrates high-level application knowledge and earns top marks.
- Use correct technical vocabulary (candidate key, primary key, foreign key, functional dependency, transitive dependency) and avoid vague terms to meet A-Level standard.
Common Misconceptions & Mistakes to Avoid
- Confusing entities with attributes.
- Omitting primary keys or foreign keys.
- Incorrect cardinality notation.
- Mistaking repeating groups for separate tables; incorrectly creating a new table for every multi-valued attribute without identifying the group as a whole.
- Overlooking partial dependencies when a composite primary key is present, leading to 2NF violations; for example, failing to separate attributes dependent on only part of the key.
- Misapplying transitive dependencies, such as treating any indirect relationship as transitive, or leaving derived attributes that would cause update anomalies.
Examiner Marking Points
- Correctly identify entities and attributes.
- Define relationships with appropriate cardinality.
- Use correct notation for ER diagrams.
- Ensure diagram is complete and consistent.
- Award credit for explaining that normalisation reduces data redundancy and prevents update, insertion, and deletion anomalies, thus maintaining data integrity.
- Credit identification and removal of repeating groups to achieve 1NF, ensuring all attributes contain atomic values and a primary key is defined.
- Credit demonstration of removing partial dependencies by splitting tables so that non-key attributes depend on the entire primary key (2NF), and eliminating transitive dependencies so that all non-key attributes depend solely on the primary key (3NF).