Entity-Relationship Modelling and Relational Databases

Given a description of a real-world problem, you must identify the entities, their attributes, and the relationships between them.

Process:

1. Identify nouns in the requirements as candidate entities (things the system needs to store data about)
2. Identify properties of each entity as attributes
3. Identify verbs/actions connecting entities as relationships
4. Determine the cardinality of each relationship (1:1, 1:many, many:many)

Example requirements: "A library stores books. Each book can be borrowed by multiple members. Each member can borrow multiple books at different times. A book has a title, ISBN, and author. A member has a member ID, name, and email."

Entities: Book, Member, Loan (the act of borrowing)

An ER diagram visually represents entities, their attributes, and their relationships.

Notation:

• Rectangles represent entities
• Ovals (or listed columns) represent attributes; underlined = key attribute
• Lines between entities represent relationships; labels describe the relationship
• Crow's foot or numbers indicate cardinality

Relationship cardinalities:

Type	Notation	Meaning	Example
One-to-one (1:1)	1 ── 1	Each entity instance relates to exactly one of the other	Person ─── Passport
One-to-many (1:N)	1 ──< N	One instance relates to many of the other	Customer ─── Orders
Many-to-many (M:N)	M >──< N	Many instances relate to many of the other	Student ─── Courses

Many-to-many relationships cannot be directly implemented in a relational database. They must be resolved into two one-to-many relationships via a junction (linking) table.

The AQA specification uses a text notation for relational schema:

Entity(<u>PrimaryKey</u>, Attribute1, Attribute2, ForeignKey*)

• <u>PrimaryKey</u> — the primary key is underlined
• * suffix on an attribute indicates it is a foreign key
• Attributes are separated by commas

Library example:

Book(<u>ISBN</u>, Title, AuthorID*)
Author(<u>AuthorID</u>, FirstName, LastName)
Member(<u>MemberID</u>, Name, Email)
Loan(<u>LoanID</u>, ISBN*, MemberID*, LoanDate, ReturnDate)

Loan is the junction table that resolves the many-to-many relationship between Book and Member.

A relational database organises data into tables (relations). Each table stores data about one entity.

Term	Definition	Example
Table (relation)	A collection of rows about one entity type	Member table
Attribute	A column; a property of the entity	Email, Name
Row (tuple/record)	One instance of the entity	A specific member
Primary key	An attribute (or set of attributes) that uniquely identifies each row	MemberID
Composite primary key	Two or more attributes together form the primary key	(ISBN, MemberID) in a Loan table
Foreign key	An attribute that references the primary key of another table	MemberID in Loan references MemberID in Member

Referential integrity: a foreign key value must either match an existing primary key in the referenced table, or be NULL. A database cannot have a Loan record pointing to a MemberID that does not exist in the Member table.

Foreign keys are how relational databases implement relationships between tables.

One-to-many (Customer → Orders):

Customer(<u>CustomerID</u>, Name, Email)
Order(<u>OrderID</u>, CustomerID*, Date, Total)

Each Order row has a CustomerID foreign key referencing the Customer table. One customer may appear in many Order rows — one-to-many.

Many-to-many resolved (Student ↔ Course):

Student(<u>StudentID</u>, Name)
Course(<u>CourseCode</u>, Title)
Enrolment(<u>StudentID*</u>, <u>CourseCode*</u>, EnrolmentDate)

Enrolment is a junction table with a composite primary key of (StudentID, CourseCode). Each student-course pair is unique. One student may appear in many rows; one course may appear in many rows — many-to-many resolved via two one-to-many relationships.

1. Placing attributes in the wrong entity

Attributes belong to the entity they directly describe. A customer's email belongs in Customer, not Order. A product's price belongs in Product, not OrderLine (unless you need to record the price at time of sale — then it belongs in both).

2. Forgetting to resolve many-to-many relationships

Relational databases cannot directly represent many-to-many. If your ER diagram has a many-to-many between A and B, you need a junction table C with foreign keys to both A and B.

3. Confusing primary key and unique key

A primary key uniquely identifies rows and cannot be NULL. A table can have only one primary key. Other attributes may be unique (e.g. email must be unique) but are not the primary key unless designated.

4. Omitting foreign keys from the entity notation

When writing entity notation, every foreign key must be marked (with * or underlined as FK). Omitting the foreign key notation loses marks, as it is how the relationship between tables is declared.