Unit 2: Entity Relationship Model

Comprehensive study of the Entity-Relationship Model including basic concepts, ER diagrams, weak entity sets, Enhanced ER Model, and converting ER diagrams to relational databases.

Unit 2: Entity Relationship Model

The Entity-Relationship (ER) model is a high-level conceptual data model developed by Peter Chen in 1976. It facilitates database design by allowing the specification of an enterprise schema, which represents the logical structure of a database. The ER model is extensively used in the conceptual design phase of database application development.

2.1 Basic concepts of E-R

The ER model is based on three fundamental concepts: Entities, Attributes, and Relationships.

2.1.1 Entity

An entity is a "thing" or "object" in the real world that is distinguishable from all other objects. For example, a specific person, a company, an event, or a plant are entities. An entity can be concrete (e.g., a person or a book) or abstract (e.g., a bank account or a course).

Entity Set: An entity set is a collection of entities of the same type that share the same properties or attributes. For example, the set of all persons who are customers at a given bank can be defined as the entity set Customer.

2.1.2 Attributes

Entities are represented by means of their properties, called attributes. For example, a Student entity might have attributes such as Roll_No, Name, Age, and Email.

Attributes can be classified into several types:

Simple vs. Composite Attributes:
- Simple Attributes: Cannot be divided into smaller subparts. (e.g., Age).
- Composite Attributes: Can be divided into smaller subparts, which represent more basic attributes with independent meanings. (e.g., Name can be divided into First_Name, Middle_Name, and Last_Name; Address into Street, City, State, Zip).
Single-valued vs. Multivalued Attributes:
- Single-valued Attributes: Have a single value for a particular entity. (e.g., Date_of_Birth).
- Multivalued Attributes: Can have a set of values for a specific entity. (e.g., Phone_Number - a person can have multiple phone numbers). They are usually represented by double ovals in ER diagrams.
Stored vs. Derived Attributes:
- Stored Attribute: Two or more attributes are related; the value of one attribute is stored, and the value of another can be derived from it. The stored attribute is the base. (e.g., Date_of_Birth).
- Derived Attribute: An attribute whose value is calculated from a stored attribute or related entities. (e.g., Age can be derived from Date_of_Birth). Represented by a dashed oval.
Key Attribute: An attribute or a combination of attributes that uniquely identifies an entity within an entity set. (e.g., Roll_No for a Student). Represented by underlining the attribute name.

2.1.3 Relationship

A relationship is an association among two or more entities. For example, a relationship Enrolled_In associates a Student entity with a Course entity.

Relationship Set: A collection of relationships of the same type. If $E_1, E_2, \ldots, E_n$ are entity sets, then a relationship set $R$ is a subset of the Cartesian product $E_1 \times E_2 \times \ldots \times E_n$ .

2.1.3.1 Participation

Participation constraints specify whether the existence of an entity depends on its being related to another entity via the relationship type.

Total Participation (Existence Dependency): Every entity in the entity set must participate in at least one relationship in the relationship set. For example, if every Employee must belong to a Department, then the participation of Employee in the Works_For relationship is total. Represented by a double line connecting the entity set to the relationship.
Partial Participation: Only some entities in the entity set may participate in the relationship. For example, an Employee may or may not manage a Department. The participation of Employee in the Manages relationship is partial. Represented by a single line.

2.1.3.2 Recursive relationships

A recursive relationship occurs when an entity set is related to itself. The same entity set participates in a relationship type in different roles. For example, in a Supervises relationship, an Employee entity can act as a "Supervisor" and another Employee entity can act as a "Supervisee". The roles indicate how the entity participates.

2.1.3.3 Degree of relationship set

The degree of a relationship type is the number of participating entity sets.

Unary (Degree 1): A relationship involving a single entity set (Recursive Relationship). Example: Person married to Person.
Binary (Degree 2): A relationship involving two entity sets. This is the most common degree. Example: Student enrolled in Course.
Ternary (Degree 3): A relationship involving three entity sets. Example: Supplier supplies Part to Project.
N-ary (Degree N): A relationship involving $n$ entity sets.

2.2 Mapping Cardinality

Mapping cardinalities (or cardinality ratios) express the number of entities to which another entity can be associated via a relationship set. This is most useful in describing binary relationship sets.

For a binary relationship set $R$ between entity sets $A$ and $B$ , the mapping cardinality must be one of the following:

One-to-One (1:1): An entity in $A$ $A$ is associated with at most one entity in $B$ $B$ , and an entity in $B$ $B$ is associated with at most one entity in $A$ $A$ .
- Example: An Employee manages one Department, and a Department has one Manager.
One-to-Many (1:N): An entity in $A$ $A$ is associated with any number (zero or more) of entities in $B$ $B$ . An entity in $B$ $B$ , however, can be associated with at most one entity in $A$ $A$ .
- Example: A Department has many Employees, but an Employee belongs to only one Department.
Many-to-One (N:1): An entity in $A$ $A$ is associated with at most one entity in $B$ $B$ . An entity in $B$ $B$ , however, can be associated with any number (zero or more) of entities in $A$ $A$ .
- Example: Many Students enroll in one Course (viewed from Student to Course).
Many-to-Many (M:N): An entity in $A$ $A$ is associated with any number (zero or more) of entities in $B$ $B$ , and an entity in $B$ $B$ is associated with any number (zero or more) of entities in $A$ $A$ .
- Example: A Student can enroll in many Courses, and a Course can have many Students.

2.3 ER Diagrams

An ER Diagram (Entity-Relationship Diagram) is a graphical representation of the logical structure of a database, based on the ER model.

Standard Notations:

Rectangles: Represent Entity Sets.
Ellipses/Ovals: Represent Attributes.
- Underlined: Key attribute.
- Dashed: Derived attribute.
- Double Ellipse: Multivalued attribute.
Diamonds: Represent Relationship Sets.
Lines: Link attributes to entity sets and entity sets to relationship sets.
Double Lines: Indicate total participation of an entity set in a relationship set.
Double Rectangles: Represent Weak Entity Sets.
Double Diamonds: Represent Identifying Relationship Sets (associated with weak entity sets).

Example Scenario: A university database with Student (RollNo, Name), Course (CourseID, Title), and a relationship Enrolls (M:N).

2.4 Weak Entity Sets

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set.

Characteristics:
1. A weak entity set must be associated with another entity set, called the identifying or owner entity set.
2. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship.
3. The weak entity set is existence-dependent on the identifying entity set. (Total participation).
4. The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set's discriminator (or partial key).
Example: Consider a Dependent entity set related to an Employee entity set. A Dependent (e.g., child or spouse) is identified only by their relation to an Employee. The partial key might be First_Name. The primary key of Dependent would be (Employee_ID, First_Name).
Notation: Weak entity sets are shown as double rectangles. The identifying relationship is a double diamond. The discriminator attribute is underlined with a dashed line.

2.5 Enhanced ER Model

The Enhanced ER (EER) model incorporates all concepts of the original ER model and adds semantic data modeling concepts such as subclasses, superclasses, specialization, generalization, and aggregation.

2.5.1 Subclass & Super Class

An entity type may have additional meaningful subgroupings of its entities.

Superclass: A generic entity type that has a relationship with one or more subtypes. (e.g., Employee).
Subclass: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings. (e.g., Manager, Engineer, Technician are subclasses of Employee).

Properties:

Attribute Inheritance: An entity that is a member of a subclass inherits all the attributes of the entity as a member of the superclass. It also inherits all relationships in which the superclass participates.

2.5.2 Generalization

Generalization is the process of extracting common properties from a set of entities and creating a generalized entity from it. It is a bottom-up approach.

Example: Car and Truck can be generalized into Vehicle. We identify common attributes like Vehicle_ID, Price, and License_Plate, place them in the Vehicle superclass, and leave specific attributes like No_of_Doors for Car and Tonnage for Truck in the subclasses.

2.5.3 Specialization

Specialization is the process of defining a set of subclasses of an entity type. It is a top-down approach.

Example: The entity set Employee may be specialized into Secretary, Engineer, and Technician based on the job role.
Specialization and generalization are conceptual inverses of each other. ER diagrams typically use a triangle symbol (often labeled 'ISA', meaning "is a") connecting the superclass to its subclasses.

Constraints on Specialization/Generalization:

Disjointness Constraint: Specifies whether subclasses are disjoint.
- Disjoint: An entity can be a member of at most one subclass.
- Overlapping: An entity may be a member of more than one subclass.
Completeness Constraint: Specifies whether every entity in the superclass must be a member of at least one subclass.
- Total: Every entity in the superclass must belong to a subclass.
- Partial: An entity in the superclass may not belong to any subclass.

2.5.4 Aggregation

One limitation of the E-R model is that it cannot express relationships among relationships. Aggregation is an abstraction through which relationships are treated as higher-level entities.

Example: Consider a ternary relationship Works_On between Employee, Branch, and Job. Suppose we want to record the Manager who manages the specific (Employee, Branch, Job) combination. Since ER models do not allow relationships to relate to other relationships, we use aggregation. We treat the Works_On relationship set and its participating entity sets as a single, higher-level abstract entity called Works_On_Aggregate. Then we create a binary relationship Manages between the Works_On_Aggregate and the Manager entity set.

2.6 Converting ER Diagrams to database

To implement an ER model in a relational database, the ER diagram must be converted into a relational schema (a set of tables).

Rules for Conversion:

Strong Entity Sets:
- Create a table for each strong entity set.
- The columns of the table are the simple attributes of the entity set.
- For composite attributes, include only the simple component attributes.
- The primary key of the table is the primary key of the entity set.
- Example: Student(RollNo, Name) $\rightarrow$ Table Student with columns RollNo (PK) and Name.
Weak Entity Sets:
- Create a table for the weak entity set.
- Columns include the simple attributes of the weak entity set PLUS the primary key attribute(s) of the identifying strong entity set.
- The primary key of the table is the combination of the primary key of the strong entity set and the partial key of the weak entity set.
- The primary key of the strong entity set becomes a foreign key referencing the strong entity set's table.
- Example: Dependent(EmpID, DependentName, Age) where EmpID is a FK to Employee and (EmpID, DependentName) is the PK.
Multivalued Attributes:
- Create a new table for the multivalued attribute.
- Columns include the primary key of the entity set to which the attribute belongs and the multivalued attribute itself.
- The primary key of this new table is the combination of both columns.
- Example: For entity Employee(EmpID, Name, {Phone_Numbers}), create table Emp_Phone(EmpID, Phone_Number) where PK is (EmpID, Phone_Number) and EmpID is a FK to Employee.
Binary Relationships:
- 1:1 Relationship: Add the primary key of one side as a foreign key to the table of the other side. Typically, place the foreign key in the table representing the entity with total participation.
- 1:N Relationship: The primary key of the '1' side is added as a foreign key to the table of the 'N' side. (e.g., DeptID in the Employee table).
- M:N Relationship: Create a new table for the relationship. The columns of this table are the primary keys of the participating entity sets, which act as foreign keys. The primary key of the new table is the combination of these foreign keys. Any descriptive attributes of the relationship are also added to this table.
  - Example: For M:N relationship Enrolled_In between Student and Course, create table Enrolled_In(RollNo, CourseID) where PK is (RollNo, CourseID).
N-ary Relationships:
- Create a new table.
- Include the primary keys of all participating entity sets as foreign keys.
- The primary key of the new table is usually the combination of all these foreign keys (unless cardinality constraints dictate otherwise).
Specialization/Generalization:
- Method 1 (Superclass/Subclass Tables): Create a table for the superclass and tables for each subclass. Subclass tables contain their specific attributes plus the primary key of the superclass (which serves as both PK and FK).
- Method 2 (Only Subclass Tables): Create tables only for subclasses. Push superclass attributes down into each subclass table. Only works if the specialization is total and disjoint.
- Method 3 (Single Table): Create a single table containing all attributes of the superclass and all subclasses, plus a "type" attribute to distinguish them. Columns for subclasses that a particular row does not belong to will have NULL values.