Mapping Business Rules

To create a database from this, we need to translate requirements into ERD components:

• Nouns → Entities
• Verbs → Relationships
• Adjectives / identifiers → Attributes
• Quantifiers (one, many, must, may) → Cardinalities & Participation

Steps to Map Business Rules to ERD

1. Extract Entities from Business Rules

• Look for nouns in the rules → these usually represent entities.
• Each entity will become a rectangle in the ER diagram.

Example rule:

• “A university has students and courses.”
• Entities: Student, Course

2. Identify Relationships

• Look for verbs or action phrases → these usually represent relationships.
• Relationships are shown as diamonds in the ERD.

Example rule:

• “Students enroll in courses.”
• Relationship: Enrolls (between Student and Course).

3. Determine Cardinality and Participation

• Business rules often describe how many instances of one entity relate to another.
• Cardinality can be:
  • One-to-One (1:1)
  • One-to-Many (1:N)
  • Many-to-Many (M:N)
• Participation may be mandatory (total) or optional (partial).

Example rule:

• “Each course must be taught by one instructor, but an instructor can teach many courses.”
• Relationship: Teaches (Instructor–Course)
• Cardinality: 1 Instructor → Many Courses (1:N)

4. Define Attributes

• Business rules may also specify important details (adjectives, descriptors, identifiers).
• These become attributes of entities or sometimes of relationships.

Example rule:

• “Each student has a student ID, name, and date of birth.”
• Attributes of Student: student_id (PK), Name, DOB.

5. Apply Constraints

• Some business rules describe constraints (conditions that must always be true).
• These map to keys, total participation, or unique attributes in the ERD.

Example rule:

• “Every course must have at least one student enrolled.”
• Constraint: Total participation of Course in Enrolls relationship.

Example of Mapping Business Rules into an ERD

1. A university has students, courses, and instructors.
2. Each student can enroll in many courses, and each course can have many students.
3. Each course is taught by one instructor, but an instructor can teach many courses.
4. Each student has a student_id, Name, and DOB.
5. Each course has a course_id, Title, and Credits.
6. Each instructor has an Instructor_ID, Name, and Salary.

Mapping Process

• Entities: Student, Course, Instructor.
• Relationships:
  • Enrolls (Student–Course, M:N).
  • Teaches (Instructor–Course, 1:N).
• Attributes:
  • Student → student_id (PK), Name, DOB.
  • Course → course_id (PK), Title, Credits.
  • Instructor → Instructor_ID (PK), Name, Salary.
• Constraints:
  • Each course must be taught by one instructor (total participation of Course in Teaches).
  • A student may or may not enroll in a course (optional participation).

 Student (student_id, Name, DOB)
        |M:N
        |
     Enrolls
        |
        |M:N
 Course (course_id, Title, Credits)
        |
        |1:N
     Teaches
        |
 Instructor (Instructor_ID, Name, Salary)

Common Mistakes

1. Treating Attributes as Entities
2. Ignoring Cardinality and Participation
3. Overusing Many-to-Many Relationships
4. Not Defining Primary Keys
5. Mixing Up Entities and Relationships
6. Not Handling Weak Entities Properly
7. Missing Attributes on Relationships

Mapping entities to tables

1. Strong Entities → Tables

• Each strong entity becomes a separate table.
• Attributes of the entity become columns.
• The primary key (PK) of the entity becomes the table’s primary key.

2. Weak Entities → Tables (with Identifying Relationship)

• A weak entity cannot exist without a strong entity.
• Its table includes:
  • Its own attributes.
  • The primary key of the owner entity (as a foreign key).
  • A partial key to uniquely identify instances.
• Together, they form the composite primary key.

3.Relationships → Tables

1:1 Relationship

• Add the primary key of one entity as a foreign key in the other.
• Sometimes, merge both entities into one table if they always participate together.

Example:

Relationship: Each student has one student_card.

STUDENT(
   student_id PRIMARY KEY,
   Name,
   DOB
);

STUDENT_CARD(
   Card_ID PRIMARY KEY,
   Issue_Date,
   student_id UNIQUE,   -- FK referencing STUDENT
   FOREIGN KEY(student_id) REFERENCES STUDENT(student_id)
);

1:N Relationship

Add the primary key of the "1" side as a foreign key in the "N" side.

Example:

Relationship: Each course is taught by one instructor, but an instructor can teach many courses.

INSTRUCTOR(
   Instructor_ID PRIMARY KEY,
   Name,
   Salary
);

COURSE(
   course_id PRIMARY KEY,
   Title,
   Credits,
   Instructor_ID,   -- FK referencing INSTRUCTOR
   FOREIGN KEY(Instructor_ID) REFERENCES INSTRUCTOR(Instructor_ID)
);

M:N Relationship

• Create a new table (associative/bridge entity).
• Include the primary keys of both entities as foreign keys.
• Add relationship attributes (if any).

Example:

Relationship: Students enroll in many courses, and each course has many students. Enrollment has a Date.

STUDENT(
   student_id PRIMARY KEY,
   Name,
   DOB,
   Email
);

COURSE(
   course_id PRIMARY KEY,
   Title,
   Credits
);

ENROLLMENT(
   student_id,   -- FK referencing STUDENT
   course_id,    -- FK referencing COURSE
   Enroll_Date,
   PRIMARY KEY (student_id, course_id),
   FOREIGN KEY(student_id) REFERENCES STUDENT(student_id),
   FOREIGN KEY(course_id) REFERENCES COURSE(course_id)
);

4. Multi-Valued Attributes → Separate Tables

• If an attribute can have multiple values, create a new table.
• The table contains:
  • The primary key of the original entity.
  • The multivalued attribute.
• The composite key ensures uniqueness.

Example:

Entity: Student has multiple Phone_Numbers.

STUDENT(
   student_id PRIMARY KEY,
   Name,
   DOB
);

STUDENT_PHONE(
   student_id,   -- FK referencing STUDENT
   Phone_Number,
   PRIMARY KEY (student_id, Phone_Number)
);

5. Specialization / Generalization → Tables

Two common approaches:

One Table for Each Subclass (Disjoint strategy)

• Superclass attributes → in the superclass table.
• Subclass-specific attributes → in separate subclass tables.
• Subclass table’s PK = Superclass PK (and FK).

Example:

Entity: Employee specialized into FullTime_Employee and PartTime_Employee.

EMPLOYEE(
   Emp_ID PRIMARY KEY,
   Name,
   Salary
);

FULLTIME_EMPLOYEE(
   Emp_ID PRIMARY KEY,   -- FK referencing EMPLOYEE
   Benefits,
   FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID)
);

PARTTIME_EMPLOYEE(
   Emp_ID PRIMARY KEY,   -- FK referencing EMPLOYEE
   Hourly_Rate,
   FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID)
);

Single Table with Type Attribute

• All attributes in one table.
• A type discriminator column specifies which subclass applies.

EMPLOYEE(
   Emp_ID PRIMARY KEY,
   Name,
   Salary,
   Emp_Type,        -- 'F' = Fulltime, 'P' = Parttime
   Benefits,        -- Null if not Fulltime
   Hourly_Rate      -- Null if not Parttime
);

Summary

• Strong entities → independent tables.
• Weak entities → dependent tables with composite PK.
• Relationships → FKs (1:1, 1:N) or new tables (M:N).
• Multivalued attributes → separate tables.
• Specialization/Generalization → multiple strategies (separate subclass tables or single table with type attribute).

Mapping relationships to tables

One-to-One (1:1)

• Each entity instance is related to exactly one instance of the other entity.
• Implementation options:
  • Add the primary key of one table as a foreign key in the other.
  • If both always participate, merge them into one table.

Example:

• Business Rule: Each student has one student_card.

ERD: Student (student_id, Name) —(Has)— Student_Card (Card_ID, Issue_Date)

Tables:

STUDENT(
   student_id PRIMARY KEY,
   Name
);

STUDENT_CARD(
   Card_ID PRIMARY KEY,
   Issue_Date,
   student_id UNIQUE,   -- ensures 1:1 mapping
   FOREIGN KEY(student_id) REFERENCES STUDENT(student_id)
);

Here, student_id in STUDENT_CARD ensures each student has at most one card.

One-to-Many (1:N)

• One entity instance relates to many instances of another entity.
• Implementation:
  • Place the primary key of the "1" side as a foreign key in the "N" side.

Example:

• Business Rule: One instructor teaches many courses, but each course has only one instructor.

ERD: Instructor (Instructor_ID, Name) —(Teaches)— Course (course_id, Title)

Tables:

INSTRUCTOR(
   Instructor_ID PRIMARY KEY,
   Name
);

COURSE(
   course_id PRIMARY KEY,
   Title,
   Instructor_ID,   -- FK from INSTRUCTOR
   FOREIGN KEY(Instructor_ID) REFERENCES INSTRUCTOR(Instructor_ID)
);

Each course points to one instructor, but an instructor can appear in many rows.

Many-to-Many (M:N)

• One entity instance can relate to many instances of another, and vice versa.
• Implementation:
  • Create a new table (associative/bridge table).
  • Include the primary keys of both entities as foreign keys.
  • If the relationship has attributes, they also go into this new table.

Example:

• Business Rule: Students enroll in many courses, and courses have many students. Enrollment has a Date.

ERD: Student —(Enrolls)— Course with attribute Enroll_Date

Tables:

STUDENT(
   student_id PRIMARY KEY,
   Name,
   DOB
);

COURSE(
   course_id PRIMARY KEY,
   Title,
   Credits
);

ENROLLMENT(
   student_id,   -- FK from STUDENT
   course_id,    -- FK from COURSE
   Enroll_Date,
   PRIMARY KEY(student_id, course_id),
   FOREIGN KEY(student_id) REFERENCES STUDENT(student_id),
   FOREIGN KEY(course_id) REFERENCES COURSE(course_id)
);

ENROLLMENT resolves the M:N relationship, and stores Enroll_Date as an attribute.

Relationships with Attributes

• If a relationship itself has attributes, it must be represented as a separate table, even if it’s 1:1 or 1:N.

Example:

• Business Rule: Employee works on Project, with Hours as an attribute.

ERD: Employee —(Works_On[Hours])— Project

Tables:

EMPLOYEE(
   Emp_ID PRIMARY KEY,
   Name
);

PROJECT(
   Proj_ID PRIMARY KEY,
   Title
);

WORKS_ON(
   Emp_ID,   -- FK from EMPLOYEE
   Proj_ID,  -- FK from PROJECT
   Hours,
   PRIMARY KEY(Emp_ID, Proj_ID),
   FOREIGN KEY(Emp_ID) REFERENCES EMPLOYEE(Emp_ID),
   FOREIGN KEY(Proj_ID) REFERENCES PROJECT(Proj_ID)
);

Relationship attributes always force a new table.

Mapping Rules Recap

Relationship Type	How to Map to Tables
1:1	Add FK to one side (or merge if total participation)
1:N	Add FK from “1” side into “N” side
M:N	Create new associative table with PKs as FKs
With Attributes	Always create new table (even for 1:1, 1:N)

Handling weak entities composite keys

• A weak entity is an entity that cannot be uniquely identified by its own attributes alone.
• It depends on a strong (owner) entity for its identification.
• Always has:
  • A partial key (attribute that only distinguishes instances relative to the owner).
  • An identifying relationship (double diamond in ERD) with its owner entity.

Example (ERD):

• Order (strong entity) has Order_ID.
• Order_Item (weak entity) has Item_No, Quantity.
• Item_No alone doesn’t uniquely identify an order item (two different orders may both have Item_No = 1).
• The combination of Order_ID + Item_No is needed.

Mapping Weak Entities to Relational Model

When converting a weak entity to a relational table:

Rule:

• Create a table for the weak entity.
• Include:
  1. The primary key of the owner entity (as a foreign key).
  2. The partial key of the weak entity.
  3. Other attributes of the weak entity.
• Together, (owner’s PK + weak entity’s partial key) = composite primary key.

Composite Keys

• A composite key is a primary key that consists of two or more attributes.
• Needed when no single attribute uniquely identifies a record.
• Common in:
  • Weak entities.
  • Associative entities (resolving M:N relationships).

Example:

• Order_Item → Composite Key = (Order_ID, Item_No)
• Enrollment (for Student–Course M:N relationship) → Composite Key = (student_id, course_id)

Weak Entity Conversion

1. A customer places many orders. Each order has an Order_ID.
2. Each order contains multiple items. Each item has an Item_No (per order), product name, and quantity.
3. Order_Item is a weak entity, identified by (Order_ID + Item_No).

ERD:

Customer (Customer_ID, Name)
      |
      | places
      |
   Order (Order_ID, Date)
      |
      | contains
      |
   Order_Item (Item_No, Product, Quantity)   <-- Weak Entity

CUSTOMER(
   Customer_ID PRIMARY KEY,
   Name
);

ORDER(
   Order_ID PRIMARY KEY,
   Order_Date,
   Customer_ID,
   FOREIGN KEY(Customer_ID) REFERENCES CUSTOMER(Customer_ID)
);

ORDER_ITEM(
   Order_ID,   -- FK referencing ORDER
   Item_No,    -- Partial key
   Product,
   Quantity,
   PRIMARY KEY(Order_ID, Item_No),
   FOREIGN KEY(Order_ID) REFERENCES ORDER(Order_ID)
);

Key Differences of Weak Entity and Composite Key

Concept	Weak Entity	Composite Key
Definition	Cannot exist without owner entity	A PK made of ≥ 2 attributes
Why Needed?	Depends on strong entity for identity	No single attribute uniquely identifies a row
ERD Symbol	Double rectangle, double diamond	Not a symbol, but implied in schema
Example	Order_Item depends on Order	Enrollment(student_id, course_id)

Resolving M:N relationships

• In an ERD, we can easily draw a many-to-many (M:N) relationship between two entities.
• But in a relational database, we cannot directly implement M:N relationships because:
  • A relational table must have a primary key.
  • If we just try to add foreign keys on both sides, data redundancy and anomalies occur.

Solution → Introduce a new table (associative/bridge/junction table) to represent the M:N relationship.

General Rule for Mapping

If Entity A and Entity B have an M:N relationship:

1. Create separate tables for both entities.
2. Create a new relationship table (associative entity).
3. In the new table:

• Include the primary keys of both entities (as foreign keys).
• The combination of these foreign keys becomes the composite primary key.
• If the relationship has attributes, store them in this table.

Example

Business Rule:

• A student can enroll in many courses.
• A course can have many students.
• Enrollment has an attribute: Enroll_Date.

ERD

STUDENT -------- (Enrolls) -------- COURSE
                    |
                Enroll_Date

Relational Tables:

STUDENT(
   student_id PRIMARY KEY,
   Name,
   DOB
);

COURSE(
   course_id PRIMARY KEY,
   Title,
   Credits
);

ENROLLMENT(                     -- Bridge table
   student_id,                  -- FK from STUDENT
   course_id,                   -- FK from COURSE
   Enroll_Date,                 -- Relationship attribute
   PRIMARY KEY(student_id, course_id),
   FOREIGN KEY(student_id) REFERENCES STUDENT(student_id),
   FOREIGN KEY(course_id) REFERENCES COURSE(course_id)
);

ENROLLMENT resolves the M:N relationship and holds the attribute Enroll_Date.

Step by Step Recap

Step	Action
1	Identify M:N relationships in ERD
2	Create tables for both entities
3	Create a new bridge table
4	Add both entities’ PKs as FKs in bridge table
5	Make composite key (PK1 + PK2) as the new table’s PK
6	Include any relationship attributes in bridge table