Introduction to ER Diagrams and Relational Schemas

Before a single table is created, before a column is defined, and before any data is stored, database designers rely on a conceptual map of the information landscape they are trying to capture. That map is called an Entity-Relationship (ER) diagram, and together with its companion artifact, the relational schema, it forms the foundation of every well-designed relational database. This topic introduces both artifacts from the ground up, establishes the precise vocabulary needed to work with them, and explains why investing time in this conceptual stage pays enormous dividends later in the development process.

An ER diagram is a graphical model that describes what data an organization needs to store and how the various pieces of data relate to one another — entirely independently of any specific database management system or physical storage technology. It answers questions like: What are the things we care about? What facts do we record about each thing? How are those things connected? The answers are expressed through three fundamental building blocks: entities, attributes, and relationships.

Entities represent real-world objects or concepts about which data must be stored. An entity could be a tangible, physical thing — a Student, an Employee, a Product — or it could be a more abstract concept, such as a Course, an Account, or an Invoice. What makes something worth modeling as an entity is that the organization needs to record multiple distinct facts about many instances of it. If you only need to record a single fact about something (say, a color name as a simple label), it is probably just an attribute rather than an entity in its own right.

Attributes describe the properties or characteristics of an entity. Consider a Student entity: the database needs to track each student's unique identifier, their name, and their date of birth. These become the attributes StudentID, Name, and DateOfBirth of the Student entity. Attributes are the individual data items that will eventually become columns in a table. Some attributes are simple (atomic, single-valued, like a date of birth), while others are composite (made up of sub-parts, like a full name broken into first, middle, and last), multi-valued (capable of holding several values at once, like a set of phone numbers), or derived (computable from other stored data, like an age derived from a date of birth).

Relationships capture the meaningful associations between two or more entities. A Student enrolls in a Course; an Employee works for a Department; an Order contains one or more Products. Relationships are not just decorative lines — they encode business rules about how entities interact, and those rules directly determine what additional tables or foreign-key constraints will appear in the final relational schema.

One of the most important practical roles of an ER diagram is that it serves as a communication tool between database designers and non-technical stakeholders. A manager, a business analyst, or a subject-matter expert can review an ER diagram and confirm that the model correctly reflects reality — without needing to understand SQL or database internals. This shared understanding is invaluable: catching a missing entity or a misunderstood relationship at the diagram stage takes minutes to fix, whereas discovering the same error after thousands of rows have been loaded into production tables can require costly data migrations and application rewrites.

The design process follows a well-established progression. It begins with requirements gathering — interviews, document analysis, and observation to understand what data the system must manage. Those requirements are then translated into an ER diagram, which is reviewed and refined with stakeholders. Once approved, the ER diagram is converted into a relational schema following a set of systematic rules. Finally, the relational schema guides the physical implementation: writing the CREATE TABLE statements, defining indexes, setting up user permissions, and populating the database. Every stage depends on the quality of the stage before it, so the conceptual work done in the ER diagram directly determines the integrity of the final system.

Understanding the notation used in ER diagrams is essential before attempting to read or draw them. The most widely taught notation is Chen notation, named after Peter Chen who introduced ER modeling in 1976. The following table summarizes the core symbols:

With notation established, we can look more closely at how entities and relationships behave at scale. An entity set is the complete collection of all entity instances of a particular type. The Student entity set contains every student the university knows about. A single entity is one row in that collection — one specific student with her own StudentID, name, and date of birth. This distinction mirrors the later distinction between a table (the entity set) and a row (the individual entity), making it easier to reason about how the conceptual model maps to physical storage.

A relationship set groups all instances of a particular association. If 500 students are each enrolled in some number of courses, the Enrolls In relationship set contains every individual enrollment pair (or tuple, if the relationship involves more than two entities). Each instance in that relationship set links exactly one student entity to exactly one course entity and may carry its own attributes — for example, an EnrollmentDate or a Grade that belongs to the enrollment itself rather than to the student or the course alone.

Cardinality constraints are among the most important pieces of information encoded in an ER diagram because they determine which conversion rules to apply and which foreign keys end up in which tables. Cardinality describes how many instances of one entity can be associated with how many instances of another through a given relationship:

• One-to-One (1:1): Each instance of entity A is associated with at most one instance of entity B, and vice versa. For example, each Country has exactly one Capital City, and each capital city belongs to exactly one country.
• One-to-Many (1:N): Each instance of entity A can be associated with many instances of entity B, but each instance of entity B is associated with at most one instance of entity A. For example, one Department employs many Employees, but each employee belongs to only one department.
• Many-to-Many (M:N): Each instance of entity A can be associated with many instances of entity B, and each instance of entity B can be associated with many instances of entity A. For example, a Student enrolls in many Courses, and each course has many students enrolled in it.

Alongside cardinality, participation constraints specify whether every entity in a set must participate in at least one instance of a relationship, or whether participation is optional. Total participation (sometimes called mandatory participation) means that every entity instance must appear in the relationship — it is drawn as a double line connecting the entity rectangle to the relationship diamond. For example, if every employee must be assigned to a department, then Employee has total participation in the Works For relationship. Partial participation (optional participation) means that some entity instances may not participate in any relationship instance at all — drawn as a single line. For example, not every employee manages a department, so Employee has partial participation in a Manages relationship.

Now consider relational schemas. While an ER diagram is a conceptual tool, a relational schema is the logical specification that describes, in precise terms, the structure of the database in the relational model. A relational schema is a set of relation schemas, each of which corresponds to a table. The notation for a single relation schema lists the relation name, then its attributes in parentheses:

Student(StudentID, Name, DateOfBirth)
Course(CourseID, Title, Credits)
Department(DeptCode, DeptName, Building)

By convention, primary key attributes are underlined in the schema notation. A primary key is the attribute (or minimal set of attributes) whose values uniquely identify every row in the relation. No two rows in the Student relation may share the same StudentID, and no StudentID may be null. In printed or typeset material you would see the underline literally; in plain text it is sometimes shown with an asterisk or all-caps:

Student(StudentID, Name, DateOfBirth)
Course(CourseID, Title, Credits)

Foreign keys are the mechanism by which relational schemas encode the associations that ER diagrams express as relationship sets. A foreign key is one or more attributes in a relation whose values are required to match the primary key of some (possibly the same) relation. For example, after converting the one-to-many relationship between Department and Employee, the Employee relation will contain a DeptCode attribute that references the primary key of Department:

Employee(EmployeeID, Name, Salary, DeptCode)
    DeptCode references Department(DeptCode)

This referential integrity constraint means you cannot insert an employee whose DeptCode value does not already exist in the Department table, and you cannot delete a department row if employees still reference it (without cascading or nullifying those references). Foreign keys are the relational model's way of preserving the meaningful associations that the ER diagram described at a conceptual level.

One of the more nuanced concepts in ER modeling is the weak entity. Most entities have enough attributes of their own to form a unique identifier — they are called strong entities. A weak entity, however, does not have sufficient attributes on its own to be uniquely identified across the entire database; it depends on a related strong entity for part of its identity. The classic example is a Dependent (the family member covered under an employee's benefits). A dependent might have a name and a date of birth, but two different employees could each have a dependent named "Emma" born on the same date. The dependent is only uniquely identifiable in the context of a specific employee.

In Chen notation, weak entities are drawn with a double-bordered rectangle, and the identifying relationship that links them to their owning strong entity is drawn with a double-bordered diamond. The weak entity's partial key — the attribute that distinguishes one weak entity instance from another within the scope of the same strong entity — is shown with a dashed underline rather than a solid one.

The full key of a weak entity is always a composite formed by combining the primary key of the strong entity with the partial key of the weak entity. So if Employee has primary key EmployeeID, and Dependent has partial key DependentName, then the full key of Dependent is the pair (EmployeeID, DependentName). Recognizing weak entities in an ER diagram matters enormously for conversion, because the relational schema for a weak entity must always include the owning strong entity's primary key as a foreign key — and that foreign key becomes part of the weak entity table's own primary key.

To bring all of these concepts together, consider a small university database scenario. The diagram would include:

• A Student entity set with key attribute StudentID and additional attributes Name and DateOfBirth.
• A Course entity set with key attribute CourseID and additional attributes Title and Credits.
• A Department entity set with key attribute DeptCode and additional attribute DeptName.
• An Enrolls In many-to-many relationship between Student and Course, carrying a Grade relationship attribute.
• A Offered By many-to-one relationship from Course to Department (each course is offered by one department; a department offers many courses).
• A weak entity Section (a specific scheduled offering of a course in a given semester and room) with partial key SectionNumber, dependent on Course through an identifying relationship Has Section.

The resulting relational schemas would begin to look like:

Student(StudentID, Name, DateOfBirth)
Department(DeptCode, DeptName)
Course(CourseID, Title, Credits, DeptCode)
    DeptCode references Department(DeptCode)
Section(CourseID, SectionNumber, Semester, Room)
    CourseID references Course(CourseID)
Enrollment(StudentID, CourseID, Grade)
    StudentID references Student(StudentID)
    CourseID references Course(CourseID)

Every design decision visible in those schemas traces back to a specific shape in the ER diagram: the double rectangle that became a composite primary key, the diamond that became a new table, the single line that became a foreign key column. That direct traceability is what makes ER diagrams so powerful as a design tool — they are not just documentation produced after the fact, but the actual blueprint from which the database is built. Mastering the vocabulary and notation introduced in this topic is therefore not an academic exercise; it is the prerequisite for every subsequent step in the database design process.