Conceptual data models and entity relationship modelling

Welcome to Database Design!

Ever wondered how massive websites like Amazon or Netflix keep track of millions of users, products, and movies without getting everything in a muddle? It all starts with a Conceptual Data Model. Think of this as the "architect's blueprint" for a database. Before we touch a computer, we need to plan out what data we need and how it all fits together.

Don't worry if this seems a bit abstract at first—by the end of these notes, you'll be able to turn any real-world scenario into a professional database plan!

1. The Building Blocks: Entities and Attributes

To build a data model, we need to identify the "things" we are storing data about and the "details" of those things.

What is an Entity?

An Entity is a category of object, person, event, or thing that is of interest to an organisation and about which data is to be recorded.

Analogy: If your database was a filing cabinet, each Entity would be a drawer.
Example: In a school database, entities might include Student, Teacher, and Subject.

What is an Attribute?

An Attribute is a specific piece of information (a characteristic) about an entity.

Memory Aid: Think of Entities as Nouns (the things) and Attributes as Adjectives (the descriptions).
Example: For the entity Student, the attributes might be FirstName, Surname, and DateOfBirth.

The Entity Identifier

Every entity needs a way to uniquely identify each record. This is called the Entity Identifier (which you will later know as a Primary Key). In your notes and exams, you must underline the identifier.

Quick Review:
• Entity: Student
• Attributes: StudentID, FirstName, Surname
(Notice how StudentID is underlined because it is unique to every person!)

Key Takeaway

Entities are the "big things" we care about, and attributes are the "small details" that describe them. One attribute must always be a unique identifier.

2. Entity Descriptions

The AQA syllabus requires you to be able to write out your data model using a specific text format called an Entity Description. It looks like this:

\(EntityName(Identifier, Attribute2, Attribute3, ...)\)

Example: If we are designing a database for a Car Showroom:
Car(RegistrationNumber, Make, Model, Colour, Price)

Common Mistake to Avoid: Forgetting to underline the identifier! If you don't underline it in an exam, you might lose the mark for that description.

3. Entity Relationship (ER) Modelling

Data doesn't exist in a vacuum; entities are usually related to each other. We use Entity Relationship Diagrams (ERDs) to show these links visually.

Types of Relationships

There are three main ways two entities can relate to one another:

1. One-to-One (1:1)
Each instance of Entity A is linked to exactly one instance of Entity B.
Example: A Husband has one Wife; a Wife has one Husband. (In a traditional database sense!)

2. One-to-Many (1:M)
One instance of Entity A can be linked to many instances of Entity B, but Entity B is linked to only one of A.
Example: One Mother can have many Children, but each Child has only one biological Mother.
Tip: This is the most common relationship in databases!

3. Many-to-Many (M:M)
Many instances of Entity A can be linked to many instances of Entity B.
Example: A Student studies many Subjects, and a Subject is studied by many Students.

Drawing the Diagram

In your exam, you draw entities as Rectangles and relationships as Lines connecting them.

To show the "Many" side of a relationship, we use a "Crow's Foot" (three small lines branching out).
• A straight line with no branches means "One".
• A line ending in a "Crow's Foot" means "Many".

Key Takeaway

ER Diagrams use rectangles for entities and special line endings (like the Crow's Foot) to show how many of one thing connect to another.

4. Step-by-Step: Creating a Data Model

When you are given a scenario in an exam, follow these steps:

Step 1: Identify the Entities
Look for the main "nouns" in the text. (e.g., Customer, Order, Product).

Step 2: Determine the Relationships
Ask yourself: "Can one Customer have many Orders?" (Yes). "Can one Order belong to many Customers?" (No). This is a One-to-Many relationship.

Step 3: Draw the ERD
Draw your rectangles and connect them with the correct lines.

Step 4: Write the Entity Descriptions
List the attributes for each entity and underline the identifier.

Did you know?
The process of creating these models is called Conceptual Modelling because it focuses on the logic of the data, not the physical software or hardware used to store it. You could implement the same conceptual model in SQL, a spreadsheet, or even a paper filing system!

5. Final Quick Review

Check your understanding:
• Can you define an Entity and an Attribute?
• Do you remember to underline the identifier in a description?
• Can you draw a Crow's Foot to represent a "Many" relationship?

Keep practicing with different scenarios, like a Library (Books, Members, Loans) or a Hospital (Doctors, Patients, Appointments). The more you practice, the easier it becomes to spot the patterns!