Mapping ERDs to Relational Schemas
Objective. Students will convert a completed ERD into a relational schema by applying the standard mapping rules for all relationship types, including resolution of many-to-many relationships into junction tables.
This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.
VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.
From ERD to Relational Schema
An entity-relationship diagram (ERD) is a planning tool. A relational schema is the blueprint that a database engine can actually implement. Mapping means translating every entity, attribute, and relationship from your ERD into tables, columns, and keys using a consistent set of rules. Mastering this translation is one of the most tested skills on the DAMA CDMP Associate, Oracle DBA CA, PostgreSQL CE, and Microsoft DP-300 exams.
Rule 1 — One Entity Becomes One Table
Every strong (regular) entity in your ERD becomes a table. The entity name becomes the table name. Each single-valued attribute becomes a column. The entity's primary key (PK) attribute becomes the primary key of the table.
Example: An entity Book with attributes `book_id`, `title`, and `publication_year` maps directly to:
```
Book(book_id PK, title, publication_year)
```
Weak entities also become tables, but their primary key is a composite of their own partial key plus the primary key of the identifying (owner) entity, which becomes a foreign key.
Rule 2 — One-to-Many Relationships Use a Foreign Key
In a one-to-many (1:M) relationship, the table on the many side receives a new column: a foreign key (FK) that references the primary key of the one side. No separate relationship table is needed.
Example: One Branch has many Members. The `branch_id` FK is added to the Member table:
```
Branch(branch_id PK, branch_name, address)
Member(member_id PK, full_name, branch_id FK → Branch.branch_id)
```
The foreign key enforces referential integrity — you cannot insert a member with a `branch_id` that does not exist in Branch.
Rule 3 — Many-to-Many Relationships Require a Junction Table
A many-to-many (M:N) relationship cannot be stored directly in either participating table without violating normalization rules. The solution is a junction table (also called an associative table, bridge table, or linking table). The junction table holds at minimum the primary keys of both sides as foreign keys, and those two columns together form the composite primary key of the junction table. Additional attributes that belong to the relationship itself are also placed here.
Example: A Member can borrow many Books, and each Book can be borrowed by many Members. The relationship Loan carries a `loan_date` attribute:
```
Loan(member_id PK/FK → Member.member_id,
book_id PK/FK → Book.book_id,
loan_date)
```
This junction table eliminates the M:N relationship and replaces it with two 1:M relationships.
Rule 4 — One-to-One Relationships
A one-to-one (1:1) relationship is handled by placing the FK in either table, but it is usually placed in the table that represents the optional or less-frequent participant. A `UNIQUE` constraint on the FK column enforces the one-to-one nature.
Rule 5 — Multivalued Attributes Become Separate Tables
If an entity has a multivalued attribute (for example, a Member who may have several phone numbers), that attribute becomes a new table containing the owning entity's PK as a FK, and the attribute itself as a column. The two columns together typically form the composite PK.
```
MemberPhone(member_id PK/FK → Member.member_id,
phone_number PK)
```
Standard SQL-Like Schema Notation
When you write up a schema, use the notation your team or exam expects. A widely accepted convention is:
```
table_name(col1 PK, col2, col3 FK → other_table.col_name)
```
- PK appears after the primary key column(s).
- FK → target.column identifies every foreign key and where it points.
- Underline or highlight PK columns on paper worksheets.
- List FK columns close to their table's PK for readability.
Maintaining Data Integrity Through the Mapping
Every mapping decision you make has a direct effect on data integrity:
- A missing FK means the database cannot enforce referential integrity automatically, creating orphaned rows.
- Placing a multivalued attribute as a comma-separated string in a single column breaks First Normal Form (1NF) and prevents reliable querying.
- Forgetting to carry relationship attributes into a junction table loses information permanently.
Always cross-check your finished schema against the original ERD. Every relationship arrow in the ERD must appear as a FK or junction table in the schema, and every entity must appear as a table.
Accuracy and Version Control
In a professional or classroom setting, treat your schema diagram as a controlled document. Date it, name it, and store it alongside the ERD it was derived from. When the ERD changes — and it will — the schema must be updated and both documents kept in sync. This habit is fundamental to database administration practice and is tested conceptually on all four credentials targeted by this program.
Key terms8
Materials: Whiteboard, mapping rules handout, structured mapping worksheet, retail-store ERD from Lesson 2, SQL notation reference
Check: Instructor checks mapping worksheet and notation; all relationship types from the ERD must be correctly represented in the schema. Pass: correct mapping of at least 9 of 10 relationships on a schema-mapping quiz.
Practice check8
Answer each question, then check it. Nothing is recorded; this is your own practice.
