3. Entity-Relationship & EER Modeling

Why Design Comes Before Code

A database designed poorly at the start becomes progressively harder to fix as data accumulates. Adding a forgotten column to a table with ten rows is trivial. Adding it to a table with ten million rows, while keeping live applications running, is a major engineering problem. Professional database designers spend days or weeks on the ER diagram before they touch a keyboard. You will do the same.

Entities — The Things Your Database Tracks

An entity is a real-world object or concept that your database needs to store information about. It has independent existence and multiple properties worth recording.

In a university database, the entities include Student, Course, Lecturer, and Department. Each of these exists independently and has several properties. A Student has an ID, a name, a date of birth, and a department. A Course has an ID, a title, and a credit value. These are entities.

In ER diagrams, entities are drawn as rectangles.

A good test for whether something is an entity: does it have at least two properties worth storing, and does it exist independently of other things? If yes, it is an entity.

Attributes — The Properties of an Entity

An attribute is a single piece of information stored about an entity. The Student entity has attributes such as StudentID, FirstName, LastName, Email, and DateOfBirth. In ER diagrams, attributes are drawn as ovals connected to their entity by lines.

Attributes come in five distinct types. Understanding the differences directly affects how you design your tables.

Simple attributes hold exactly one indivisible value. StudentID and GPA are simple attributes. They cannot be split into parts.

Composite attributes can be divided into sub-parts. FullName can be split into FirstName and LastName. Address can be split into Street, City, Region, and PostalCode. When you convert the diagram to a table, each sub-part becomes its own column.

Multi-valued attributes can hold more than one value for the same entity. A student might have two phone numbers. A single database column cannot store a list — that violates the relational model. Multi-valued attributes always require a separate table.

Derived attributes can be calculated from other stored data. Age can be derived from DateOfBirth. You store the date of birth and compute the age when needed. Derived attributes are drawn with a dashed oval in ER diagrams.

Key attributes uniquely identify each instance of the entity. StudentID is unique to each student. Key attributes are drawn with an underline in ER diagrams.

Relationships — How Entities Connect

A relationship describes an association between two or more entities. A Student enrolls in a Course. A Lecturer teaches a Course. A Student belongs to a Department. In ER diagrams, relationships are drawn as diamonds connected to the entities they link.

The most important property of any relationship is its cardinality — how many instances of one entity can be associated with how many instances of another.

One-to-One (1:1). Each instance of entity A is associated with exactly one instance of entity B, and vice versa. Each department has exactly one Head of Department, and each lecturer is Head of at most one department. This is a 1:1 relationship.

One-to-Many (1:N). One instance of entity A is associated with many instances of entity B, but each instance of B is associated with only one instance of A. One department has many students, but each student belongs to exactly one department. This is the most common cardinality in real databases.

Many-to-Many (M:N). Many instances of entity A are associated with many instances of entity B. A student enrolls in many courses, and each course has many students enrolled. This cannot be directly stored in two tables — it requires a third junction table to resolve it, which you will learn about in Module 5.

Participation Constraints

Every relationship also has a participation constraint describing whether every entity instance must be involved in the relationship.

Total participation means every single instance must participate. Every student must belong to a department — a student with no department is not allowed. In ER diagrams, total participation is shown as a double line between the entity and the relationship diamond.

Partial participation means some instances may not participate. Not every lecturer manages a department — most are standard lecturers. Partial participation is shown as a single line.

Weak Entities

A weak entity is an entity that cannot be uniquely identified using only its own attributes. It depends on another entity — called the owner entity or identifying entity — for its identity and existence.

Consider a Dependent stored in a university HR system. A dependent (such as a staff member's child registered for a benefit) does not have a university ID. Their identity in the system is partly defined by which staff member they belong to. Without the staff member, the dependent record has no meaning.

Weak entities are drawn as double rectangles. The identifying relationship connecting them to their owner is drawn as a double diamond.

Enhanced ER (EER) — Specialisation and Generalisation

The Enhanced Entity-Relationship model extends the basic ER model with object-oriented concepts. The two most important are specialisation and generalisation.

Generalisation is a bottom-up process. You notice that Undergraduate and Postgraduate share many attributes (StudentID, Name, Email). You combine their common attributes into a higher-level entity called Student.

Specialisation is the opposite — top-down. You start with a general entity like Person and specialise it into more specific sub-entities: Staff and Student. Each sub-entity inherits all attributes of the parent and adds its own specific ones. Staff has a salary. Student has a GPA. This is the same concept as inheritance in object-oriented programming.

In EER diagrams, specialisation is shown with a circle labelled with a d (disjoint — a Person is either Staff or Student, not both) or an o (overlapping — a Person can be both simultaneously).

The SSMS Diagram Tool

SSMS includes a visual database diagram builder you will use after creating tables in Module 5. To access it, expand your database in Object Explorer, right-click Database Diagrams, and select New Database Diagram. If prompted to install diagram support objects, click Yes.

The canvas that opens lets you drag your existing tables onto it. SSMS automatically draws lines between tables wherever foreign key relationships exist. This gives you a live visual of your actual schema — not just a design, but the real structure as it exists in the database.