UNIT 2: Data Modeling using the Entity-Relationship Model (ER Model)

Hello Everyone, Welcome to Rajasthan Polytechnic Blogs.

Subject - Introduction to DBMS CS 4002(Same as CI/IT 4002)
Branch - Computer Science Engineering
Semester - 4th Semester

📍⚡ Important 

▶ Video PDF - Update Soon

👉 WhatsApp Group - Join Now

👉 Telegram Channel - Join Now

📄 Notes in Hindi - Click Here

📄 Notes in English - Click Here

🔥 4th Semester All Subjects Notes - Click Here

Please take a moment to share your Review in Comment Section.

UNIT 2: Data Modeling using the Entity-Relationship Model (ER Model)

This chapter covers the concept of data modeling and introduces the Entity-Relationship (ER) Model, which is used to visually represent the structure of a database.

---

2.1. Data Modeling using the Entity-Relationship (ER) Model 🧑‍💻

Data Modeling is the process of creating a visual representation of a database's structure. It helps to design a database that will efficiently store data and support various operations like retrieval, update, and deletion.

The Entity-Relationship (ER) Model is a popular tool for database design. It uses entities, attributes, and relationships to represent data and its interconnections.

Key Concepts:

• Entity: An entity is a real-world object or concept that can have data stored about it. For example, a Student, Employee, or Product can be considered entities.
• Attribute: An attribute is a property or characteristic of an entity. For instance, a Student might have attributes like Name, ID, and Age.
• Relationship: A relationship represents the interaction between two or more entities. For example, a Student "enrolls in" a Course.

Example:

• Entity: Student
• Attributes: Student_ID, Name, Date_of_Birth
• Relationship: Student “enrolls in” Course

---

2.2. Notations of ER Diagram ✏️

An ER Diagram is a graphical representation of the entities, attributes, and relationships in a database. The notations used in ER diagrams include:

1. Rectangle (Entity): Represents an entity set.

   • Example: A rectangle labeled Student represents the entity set of students.

2. Ellipses (Attributes): Represent attributes of entities.

   • Example: An ellipse labeled Name connected to the Student rectangle represents the student's name.

3. Diamond (Relationship): Represents a relationship between two or more entities.

   • Example: A diamond labeled Enrolls shows the relationship between Student and Course.

4. Lines (Connecting Entities and Relationships): Connect entities and relationships, indicating how they are related.

5. Double Ellipse (Multi-valued Attribute): Represents attributes that can have multiple values.

   • Example: A double ellipse labeled Phone Numbers connected to Student means a student can have multiple phone numbers.

6. Key Attribute (Double Rectangle): Represents an attribute that uniquely identifies an entity instance.

   • Example: A double rectangle labeled Student_ID shows it is the primary key of the Student entity.

Example ER Diagram:

+-------------------+              +---------------+
|   Student         |              |   Course      |
|-------------------|              |---------------|
| Student_ID (PK)   |--------------| Course_Code   |
| Name              |              | Course_Name   |
| Date_of_Birth     |              +---------------+
+-------------------+
         |
         | Enrolls
         |
   +-------------+
   | Enrollment  |
   +-------------+

---

2.3. Mapping Constraints 🔄

Mapping constraints are rules that define the number of instances of one entity that can be associated with instances of another entity in a relationship.

There are three main types of mapping constraints:

1. One-to-One (1:1): Each instance of Entity A can be associated with at most one instance of Entity B, and vice versa.

   • Example: A Student has one Student_ID.

2. One-to-Many (1:N): One instance of Entity A can be associated with multiple instances of Entity B, but each instance of Entity B can only be associated with one instance of Entity A.

   • Example: A Department can have many Employees, but each Employee works in only one Department.

3. Many-to-Many (M:N): Instances of both entities can be associated with many instances of the other entity.

   • Example: A Student can enroll in many Courses, and each Course can have many Students enrolled.

Diagram Examples:

• One-to-One:

  +------------+         +------------+
  | Student    |         | Student_ID |
  +------------+         +------------+
  

• One-to-Many:

  +-------------+         +--------------+
  | Department  |<------->| Employee     |
  +-------------+         +--------------+
  

• Many-to-Many:

  +------------+        +------------+       +-------------+
  | Student    |<----->| Enrollment |<----->| Course      |
  +------------+        +------------+       +-------------+
  

---

2.4. Keys 🔑

A key is an attribute or a set of attributes that uniquely identifies an entity or a relationship. Keys are critical for database integrity and retrieval.

1. Primary Key (PK): An attribute or set of attributes that uniquely identifies a record in a table. No two rows can have the same value for the primary key.

   • Example: Student_ID in the Student entity.

2. Foreign Key (FK): An attribute in one table that links to the primary key in another table, establishing a relationship between the two.

   • Example: Department_ID in the Employee entity might be a foreign key referencing the Department entity.

3. Candidate Key: An attribute or set of attributes that can uniquely identify a record, but is not chosen as the primary key. There can be multiple candidate keys in an entity.

   • Example: Email and Student_ID could both be candidate keys for the Student entity.

4. Composite Key: A primary key that consists of two or more attributes.

   • Example: In a Course Enrollment table, a combination of Student_ID and Course_Code could be a composite key.

5. Unique Key: An attribute or set of attributes that uniquely identifies a record, but unlike the primary key, it can accept null values.

   • Example: Email Address might be a unique key in the Student entity.

---

2.5. The Enhanced Entity-Relationship (EER) Model 🌟

The Enhanced Entity-Relationship (EER) Model is an extension of the basic ER model, incorporating additional concepts to deal with complex database designs.

Key extensions in EER models include:

1. Generalization: The process of identifying a higher-level entity set that encompasses lower-level entity sets.

   • Example: A Person can be a Student or an Employee, and we generalize these into the entity Person.

2. Specialization: The process of dividing an entity set into sub-sets based on certain characteristics.

   • Example: A Vehicle entity might be specialized into Car, Truck, and Motorcycle based on type.

3. Aggregation: An abstraction that is used when we need to model relationships between relationships.

   • Example: If a Teacher teaches many Courses, and a Course has many Students, we can aggregate the teaching relationship.

4. Participation Constraints: These specify whether all or some entities participate in a relationship.

   • Total Participation: Every instance of an entity set must participate in the relationship.
   • Partial Participation: Some instances of an entity set may not participate in the relationship.

---

Conclusion 🌐

• The ER Model provides a powerful way to visually represent and design databases.
• It uses entities, attributes, and relationships to define the structure.
• EER is an advanced version of the ER model that helps model more complex relationships like generalization, specialization, and aggregation.

Understanding and applying the ER and EER models is crucial for effective database design and management.