Use an entity-relationship (E-R) diagram to document a database design

8.1 Database Concepts – Using an Entity‑Relationship (E‑R) Diagram to Document a Database Design

1. Why an E‑R diagram?

An E‑R diagram provides a visual, high‑level representation of the data requirements of a system. It helps to:

• Identify the main entities and their attributes.
• Show how entities are related.
• Clarify cardinalities (one‑to‑one, one‑to‑many, many‑to‑many).
• Serve as a blueprint for converting the design into a relational schema.

2. Core components of an E‑R diagram

The diagram consists of four fundamental elements:

1. Entity – represented by a rectangle; an object of interest (e.g., Student).
2. Attribute – an oval attached to an entity; describes a property (e.g., StudentID, Name).
3. Relationship – a diamond (or labelled line) linking two or more entities (e.g., Enrols).
4. Cardinality – notation that indicates the number of instances that can participate in a relationship.

3. Cardinality notation

The most common notation uses the symbols (1) and (N) where N denotes “many”. The following table summarises the three basic types.

4. Steps to construct an E‑R diagram

1. Gather requirements – list all real‑world objects that the system must store.
2. Identify entities – convert each object into an entity rectangle.
3. Determine attributes – for each entity, list its key attribute(s) (underlined) and non‑key attributes.
4. Define relationships – connect entities with labelled diamonds or lines.
5. Specify cardinalities – add \$(1)\$ or \$(N)\$ near each end of a relationship line.
6. Validate – check for redundancy, missing attributes, and logical consistency.

5. Example scenario – University Course Management

Consider a system that records students, courses, and the enrolment of students in courses.

• Entities: Student, Course, Enrolment.
• Key attributes: StudentID (Student), CourseCode (Course), EnrolmentID (Enrolment).
• Relationships:

  • Student enrols in Course – many‑to‑many, resolved by the associative entity Enrolment.

6. Translating the E‑R diagram into a relational schema

Each entity becomes a table; each relationship becomes a foreign key or a separate table for many‑to‑many relationships.

7. Common pitfalls to avoid

• Omitting the primary key attribute – leads to ambiguous records.
• Using many‑to‑many relationships without an associative entity – cannot be directly implemented in a relational database.
• Over‑normalising – creates excessive tables and unnecessary joins.
• Forgetting to indicate optional participation (zero vs. one) in cardinalities.

8. Summary

An E‑R diagram is a powerful tool for documenting the logical structure of a database before implementation. By following a systematic approach—identifying entities, attributes, relationships, and cardinalities—students can produce clear designs that translate cleanly into relational schemas.

9. Practice questions

1. Identify the entities, key attributes, and relationships for a library system that tracks Books, Members, and Loans.
2. Draw an E‑R diagram (use the suggested diagram placeholder) for the scenario in Question 1, indicating cardinalities.
3. Convert your E‑R diagram from Question 2 into a set of relational tables, specifying primary and foreign keys.
4. Explain why a many‑to‑many relationship must be resolved with an associative entity before creating a relational schema.