Entity-Relationship (E/R) Model

Entity-Relationship (E/R) Model

• Peter Chen proposed the widely used conceptual level data model in the 1970s.
• The data model describes the database system at the requirements collection stage.
• It provides a high-level description that is easy for enterprise managers to understand, yet rigorous enough for system building.

Core Concepts of the Model

• Entities and their attributes are central.
• Relationships between entities are defined.
• Uses diagrammatic notation for representation.

Entities

• Entities are distinguishable things (animate or inanimate) with independent physical or conceptual existence.
• Examples include a student, faculty member, classroom, or course in a university database.

Entity Sets or Types

• An entity set or type is a collection of entities sharing the same properties.
• Examples include a student entity set (all students) and a course entity set (all courses).

Attributes

• Each entity has attributes/properties with associated values describing it.
• Student entities can have: StudName (name), RollNumber, and Sex (gender).
• All entities within an entity set/type have the same set of attributes.
• The chosen set of attributes determines the level of detail in modeling.

Types of Attributes

• Simple attributes have atomic or indivisible values (e.g., Dept as a string, PhoneNumber as a 10-digit number).
• Composite attributes have several components in their value's example: Qualification with components "DegreeName, Year, UniversityName".
• Derived attributes' values depend on some other attribute for example: Age depending on DateOfBirth.

Attribute Characteristics

• Single-valued attributes have only one value (e.g., PlaceOfBirth as a single string).
• Multi-valued attributes have a set of values (e.g., CoursesEnrolled, EmailAddress, PreviousDegree).
• Attributes can be simple single-valued, simple multi-valued, composite single-valued, or composite multi-valued.

Diagrammatic Notation

• Rectangles represent entities.
• Ellipses connected to rectangles represent attributes.
• Double ellipses represent multi-valued attributes.
• Composite attributes have ellipses connected to other ellipses.
• Dashed ellipses represent derived attributes.

Attribute Domains

• Each attribute draws values from its domain.
• A studentAge domain might be {17, 18, ..., 55}.
• A HomeAddress domain might use character strings of length 35.
• Composite attributes' domains are cross products of their components’ domains.
• Multi-valued attributes' domains consist of sets of subsets from a basic domain.

Entity Sets and Key Attributes

• A key is an attribute, or attribute set, whose value(s) uniquely identify entities within the entity set.
• Examples include: RollNumber for students, EmpID for faculty, and HostelName/RoomNo for student residences.
• Entity sets can have multiple keys.
• Meaning of attributes in the entity type determines keys which are set by designers.

Relationships Explained

• Association of two or more entities is a relationship instance.
• The "enrolls" relationship links Student and Course entity sets.
• (s,c) ∈ enrolls implies Student 's' enrolled in Course 'c'.
• Tuples in enrolls are relationship instances.
• "enrolls" is a relationship Type/Set.

Relationship Degree

• Relationship degree indicates the number of participating entities.
• Degree 2 = binary, Degree 3 = ternary, Degree n = n-ary.
• Binary relationships are widely used.

Diagrammatic Notation for Relationships

• Diamonds represent Relationships.
• Lines connect participating entities' rectangles to the diamond.
• Relationship name is written inside the diamond.

Binary Relationships and Cardinality Ratio

• The maximum number of entities from E2 that an entity from E1 can be associated through R determines the cardinality ratio of R.
• The four possibilities specified are one-to-one (1:1), one-to-many (1:N), many-to-one (N:1), and many-to-many (M:N).

Cardinality Ratio Details

• One-to-one: An E1 entity is associated with at most one E2 entity and vice versa.
• One-to-many: An E1 entity is associated with many E2 entities, but an E2 entity is associated with at most one E1 entity.
• Many-to-one: An E2 entity is associated with many E1 entities, but an E1 entity is associated with at most one E2 entity.
• Many-to-many: Many E1 entities can be associated with a single E2 entity, and a single E1 entity can be associated with many E2 entities.

Participation Constraints

• An entity set may participate in a relation either totally or partially.
• In total participation, every entity in the set is included in some relationship tuple.
• In partial participation, not all entities in the set need be involved.

Participation Constraints Notation

• A total participation from E1 to R is indicated by a double line.
• A partial participation is indicated by a single line.

Structural Constraints Defined

• Structural Constraints combines Cardinality Ratio and Participation Constraints.
• They are called constraints because the data must satisfy them to be consistent with the requirements.
• Min-Max notation: pairs of numbers (m,n) are marked on the line joining an entity to the relationship.
• m denotes the minimum times an entity must appear in relationship tuples ie 0 (partial) ≥ 1 (total).
• n indicates the maximum times an entity can appear in relationship tuples.

Comparing Notations

• An E1-R-E2 relationship with N:1 cardinality is equivalent to E1 relates to E2 with one-to-one mapping, where E1 is "many-to-one" to E2.

Attributes for Relationship Types

• Relationship types include attributes that describe the association of entities.
• For example, "Grade" in the Student enrolls Course relationship is neither a Student nor Course attribute.

Recursive Relationships and Role Names

• Recursive relationships arise when an entity set relates to itself.
• The "prereqOf" relationship on the "Course" entity is an example.
• Role names specify the precise role an entity plays in a relationship, used in recursive relationships, optionally specified otherwise.

Weak Entity Sets

• A weak entity set depends on an entity in a strong entity set for existence.
• Weak entities lack independent existence and are linked to an owner entity via a special relationship.
• Weak entity sets lack the key attribute.
• The identifying relationship may not always be binary.

Key Properties of Weak Entities

• Double wall box denotes the entity.
• Always total indicates owner entity.

Complete Example: Educational Institute Specifications

• Each student belongs to one, of an educational institute’s several departments.
• Departments have a unique number, name, location, phone number, and a professor as the head.
• Professors are tracked with unique employee ID, name, and phone number; each professor works for exactly one department.
• For students, the name, unique roll number, sex, phone number, birth date, age and one or more email addresses are noted.
• Students have a local address consisting of hostel name and room number, and a home address (number, street, city, PIN); all students reside in hostels.
• A section is a course is taught in a semester; the same course can have multiple sections identified by a section number.
• Each section is taught by a professor, with own timings/room.
• Students enroll in several sections per semester.

Core Details

• The department that offers a course, the course name, and credits of each course are captured.
• A course can list other courses as pre-requisites (required to be completed before enrollment).
• Research projects undertaken by professors, sponsored by funding agencies, has a start/end date, and allocated money are tracked.
• Multiple professors can be on a project, a professor can be on several projects, and projects have unique IDs.

E/R Diagram: Design Choices

• Determine if offering department should be an attribute of a course, or a relationship with the Department entity.
• A Course-Dept relationship ("offers") is better if the Department entity exists.
• Decide whether class room is an attribute of Section, or create a ClassRoom entity and "meetsIn" relationship.
• Making classRoom a Section attribute is suitable if a class room entity isn't necessary.

Weak Entities vs. Composite Multi-Valued

• Model a section as a composite multi-valued attribute of the Course entity.
• If a section participates in relationships (e.g., enrolls), capture it as a weak entity.
• Weak entity is best captured if it participates in relationships even without independent existence.
• Composite multi-valued attribute is sufficient otherwise.

Ternary Relationships Essence

• Describes instances where relationship (c, p, j) = company c supplies component p used by project j.

Important Note

• Relationships are not necessarily implied
• (c,p) canSupply, (j,p) in users, (c,j) can not implies that (c,p,j) = Supply
• the opposite is true