Relational Databases & ER Modelling | OCR H446

Learning Objectives

By the end of this topic you will be able to:

Explain relational database concepts: entity, attribute, tuple, relation

Define primary key, foreign key and candidate key

Draw and interpret entity-relationship (ER) diagrams

Identify and describe 1:1, 1:M and M:M relationships and how M:M is resolved

Database Concepts

Key Relational Database Terms

Structure Terms

Relation (Table): a named set of rows and columns storing data about one entity type

Attribute (Field/Column): a single piece of information stored per entity (e.g. StudentName)

Tuple (Row/Record): one complete set of attribute values — one row in a table

Entity: a real-world object or concept represented in the database (e.g. Student, Course)

Example Relation

StudentID	Name	DOB
1001	Ali	2006-03-12
1002	Beth	2007-01-04
1003	Cara	2006-09-28

3 tuples · 3 attributes · StudentID is primary key

Keys

Primary Keys, Foreign Keys and Candidate Keys

Primary key: a unique identifier for each tuple in a relation. No two rows can have the same primary key value; it cannot be NULL. The chosen primary key for the table (e.g. StudentID).

Candidate key: any attribute (or combination of attributes) that could uniquely identify each tuple. The primary key is selected from the set of candidate keys. Remaining candidate keys become alternate keys.

Foreign key: an attribute in one relation that is the primary key of another relation. Used to link tables and enforce referential integrity. E.g. CourseID in the Enrolment table references CourseID in the Course table.

Referential integrity: the DBMS ensures that a foreign key value always matches an existing primary key value in the referenced table — you cannot enrol a student on a non-existent course.

ER Diagrams

Entity-Relationship (ER) Diagrams

An ER diagram models the relationships between entities in a database before it is implemented. It uses standard notation to show entities, their attributes, and how they relate.

ER Notation

Rectangle: Entity (e.g. Student, Course)

Ellipse/oval: Attribute (linked to entity)

Diamond: Relationship (e.g. Enrols)

Lines + crow's foot / 1 / M / *: cardinality

Cardinality

1:1 — one entity relates to exactly one of another (e.g. Person : Passport)

1:M — one entity relates to many (e.g. Teacher : Classes)

M:M — many relate to many (e.g. Student : Course)

M:M Relationships

Resolving Many-to-Many Relationships

A M:M relationship cannot be directly implemented in a relational database. It must be resolved by introducing a link (junction) table that breaks it into two 1:M relationships.

Before (M:M — invalid)

Student ←M:M→ Course

A student can enrol on many courses; a course can have many students. Cannot directly store this without data duplication.

After (two 1:M — correct)

Student 1:M← Enrolment →M:1 Course

Enrolment table: (StudentID [FK], CourseID [FK], DateEnrolled). Composite primary key: (StudentID, CourseID).

Exam Practice

OCR H446 Style · 4 marks

A hospital database stores information about Doctors and Patients. A doctor can treat many patients, and a patient can be treated by many doctors. Describe how you would model this relationship in a relational database.

[4 marks]

This is a many-to-many (M:M) relationship — one doctor treats many patients, and one patient sees many doctors.

A M:M relationship cannot be directly implemented in a relational database; it must be resolved with a link table.

Create a link table, e.g. Treatment(DoctorID, PatientID, Date), with DoctorID as a foreign key referencing Doctor, and PatientID as a foreign key referencing Patient.

This creates two 1:M relationships: Doctor 1:M Treatment and Patient 1:M Treatment. The composite primary key (DoctorID, PatientID, Date) uniquely identifies each treatment record.

Common Mistakes

Don’t Lose Marks

Confusing tuple with attribute — a tuple is a row (one complete record); an attribute is a column (one type of data). Mixing these two terms is heavily penalised.

Saying a primary key is any unique field — a primary key is the chosen unique identifier for the table (selected from candidate keys). Every field could be unique, but only one is designated the primary key.

Leaving a M:M relationship unresolved in a database design — M:M relationships must always be broken into two 1:M relationships via a link table. Directly implementing M:M causes data redundancy and anomalies.

Relational Databases& ER Modelling