Unit 3: Data Modeling Using the Entity-Relationship Model – Key Concepts and Applications

1. Introduction to Data Modeling

Data modeling is the process of creating a conceptual representation of data requirements and constraints. It bridges the gap between business requirements and the physical database implementation. In DBMS, the Entity‑Relationship (ER) model is the most widely taught conceptual model because it closely mirrors human understanding of the problem domain.

1.1 Data Abstraction

• Data abstraction is the process of hiding the details of data representation and focusing on the logical structure.
• It allows users to interact with the database without needing to know how data is stored physically.

1.2 Data Model

• A data model is a formal description of the structure, semantics, and constraints of data.
• It defines entities, attributes, relationships, and integrity rules.

1.3 Schema, Instance, and Database State

The schema is static; the instance changes as data is inserted, updated, or deleted. The database state is the snapshot of the instance at a particular moment.

2. Data Independence and Three‑Schema Architecture

2.1 Data Independence

• Logical data independence: Ability to change the conceptual schema without affecting external schemas.
• Physical data independence: Ability to change the internal schema without affecting the conceptual schema.

2.2 Three‑Schema Architecture

This separation ensures that changes at one level do not ripple through the entire system, preserving data independence.

3. Relational Constraints in the ER Context

Although ER modeling is conceptual, it maps to relational constraints once translated.

3.1 Domain Integrity

• Each attribute must belong to a predefined domain (data type and permissible values).
• Example: age must be an integer between 0 and 120.

3.2 Entity Integrity

• Primary key of an entity must be unique and non‑NULL.
• Example: Customer.id must be unique and not null.

3.3 Referential Integrity

• Foreign keys must reference existing primary keys or be NULL.
• Example: Order.customer_id must reference an existing Customer.id.

These constraints are enforced by the DBMS to maintain consistency.

4. Core ER Modeling Concepts

4.1 Entities and Entity Sets

• Entity: A real‑world object with a distinct existence.
• Entity Set: Collection of similar entities.
• Example: Book entity set contains all books.

4.2 Attributes

• Properties of entities.
• Simple: indivisible (e.g., first_name).
• Composite: composed of multiple simple attributes (e.g., address = street, city, zip).
• Multivalued: can have multiple values (e.g., phone_numbers).
• Derived: computed from other attributes (e.g., age derived from birth_date).

4.3 Relationships and Relationship Sets

• Relationship: Association between two or more entity sets.
• Relationship Set: Collection of specific relationships.
• Example: Borrows relationship between Student and Book.

4.4 Cardinality and Participation Constraints

4.5 Specialization and Generalization

• Generalization: Abstracting common attributes/relationships into a super‑entity.
  • Example: Vehicle super‑entity with Car, Truck, Motorcycle subclasses.
• Specialization: Splitting an entity into more specific subclasses.
  • Example: Employee specialized into Manager and Staff.

4.5.1 Constraints in Specialization/Generalization

5. ER Diagram Construction

5.1 Diagram Symbols

5.2 Worked Example: Online Bookstore

Scenario:

• Customers place orders.
• Each order contains one or more books.
• Books have authors.

Step 1: Identify Entities

• Customer, Order, Book, Author.

Step 2: Identify Attributes

• Customer: customer_id (PK), name, email.
• Order: order_id (PK), order_date.
• Book: isbn (PK), title, price.
• Author: author_id (PK), name.

Step 3: Identify Relationships

• Places: Customer – Order (1:N).
• Contains: Order – Book (N:M).
• Writes: Author – Book (1:N).

Step 4: Add Cardinality

• Customer 1 – N Order.
• Order N – M Book (via associative entity OrderItem).
• Author 1 – N Book.

Step 5: Draw Diagram

+-----------+          +-----------+          +-----------+
| Customer  |          |   Order   |          |   Book    |
|-----------|          |-----------|          |-----------|
| customer_id PK |<---1 | order_id PK |---N>---| isbn PK   |
| name      |          | order_date |          | title     |
| email     |          +-----------+          | price     |
+-----------+                |                 +-----------+
                              |
                              | N
                              v
                        +-----------+
                        | OrderItem |
                        |-----------|
                        | order_id FK|
                        | isbn FK    |
                        | quantity   |
                        +-----------+

Explanation:

• OrderItem is an associative entity resolving the N:M relationship between Order and Book.
• OrderItem contains the foreign keys order_id and isbn, and an attribute quantity.

5.3 Library System Example

Scenario:

• Library has Books, Members, and Loans.
• Each book can be borrowed by many members over time.
• Each member can borrow multiple books.

Entities & Attributes

Relationships

• Borrows: Member – Loan (1:N).
• Contains: Loan – Book (N:M via LoanItem).

ER Diagram (ASCII)

+-----------+          +-----------+          +-----------+
|  Member   |          |   Loan    |          |   Book    |
|-----------|          |-----------|          |-----------|
| member_id PK |<---1 | loan_id PK |---N>---| isbn PK   |
| name      |          | loan_date |          | title     |
| address   |          | return_date|         | author    |
| phone     |          +-----------+          | publisher |
+-----------+                |                 | year      |
                              | N
                              v
                        +-----------+
                        | LoanItem  |
                        |-----------|
                        | loan_id FK|
                        | isbn FK   |
                        | due_date  |
                        +-----------+

6. Comparison: ER Model vs. Relational Model

7. Advantages and Disadvantages of ER Modeling

Advantages

• Visual clarity: Easy to communicate with stakeholders.
• Early error detection: Constraints and cardinalities identified before coding.
• Reusable components: Generalization/specialization promote reuse.
• Documentation: Serves as a blueprint for developers and DBAs.

Disadvantages

• Scalability issues: Very large ER diagrams become unwieldy.
• Ambiguity: Without strict notation, interpretations may vary.
• Mapping overhead: Requires careful translation to relational schema.

8. Database Users and Their Roles

Data independence ensures that changes in the schema do not disrupt these users’ interactions.

9. Advantages of DBMS over Traditional Filing Systems

10. Summary of Key Terms

11. Exam Tip

• Understand the mapping: Be able to convert an ER diagram to a relational schema (tables, primary keys, foreign keys).
• Practice cardinality: Draw the correct crows‑foot notation for 1:N, N:M, and 1:1 relationships.
• Specialization/Generalization: Know the difference between disjoint/overlapping and complete/partial constraints.
• Constraints: Be ready to write domain, entity, and referential integrity rules in natural language.
• Diagram construction: Given a scenario, sketch the ER diagram within 5–10 minutes; practice with varied examples (library, bookstore, hospital).
• Past questions: Re‑create the bookstore scenario and the library diagram; annotate each entity, attribute, and relationship with keys and cardinalities.

By mastering these concepts, you will confidently tackle both descriptive and diagrammatic questions in the unit.