Week_4_Lecture_-_Tutorial_on_ER_Modelling

COM162 Database Systems

Lecture 4: Tutorial on ER Modelling

ER Modelling Recap

• Purpose of ER Modelling:

  • A tool used in database design to visually represent data relationships.

  • Helps structure information and clarify relationships.

  • Serves as a communication tool between stakeholders.

• Key ER Model Concepts:

  • Entities & Attributes:

    • Entities: Objects with independent existence (e.g., Staff, Branch).

    • Attributes: Properties of entities (e.g., StaffNo, Name).

  • Relationships:

    • Connections between entities, named with verbs (e.g., "Staff Manages Branch").

    • Types of relationships: Binary (2 entities), Ternary (3), or Quaternary (4).

Relationship Multiplicity (Structural Constraints)

• Multiplicity Types:

  • One-to-One (1:1):

    • Each entity instance in A is associated with at most one instance in B and vice versa.

  • One-to-Many (1:*):

    • One entity instance in A can relate to multiple instances in B; each instance in B relates to only one instance in A.

  • Many-to-Many (:):

    • Multiple instances in A relate to multiple instances in B.

• Entity Types:

  • Strong Entity:

    • Exists independently (e.g., Staff).

  • Weak Entity:

    • Depends on another entity; lacks a unique identifier.

1:1 Relationship - Multiplicity of Staff Manages Branch

• Diagram Representation:

  • Indicates that a member of staff can manage zero or one branch (0..1 beside Branch).

  • A branch must always have one manager (1..1 beside Staff).

1:* Relationship - Multiplicity of Staff Oversees PropertyForRent

• Diagram Representation:

  • A member of staff can oversee zero or more properties for rent (0..* beside PropertyForRent).

  • Each property for rent is overseen by zero or one member of staff (0..1 beside Staff).

: Relationship - Multiplicity of Newspaper Advertises PropertyForRent

• Diagram Representation:

  • Each newspaper can advertise one or more properties for rent (1..* beside PropertyForRent).

  • Each property for rent can be advertised by zero or more newspapers (0..* beside Newspaper).

Structural Constraints

• Cardinality:

  • Maximum number of possible relationship occurrences for an entity.

  • Examples:

    • 1:1 → Staff Manages Branch

    • 1:* → Branch Has Staff

    • : → Newspaper Advertises Property.

• Participation:

  • Determines if all or only some entity occurrences participate in relationships.

  • Mandatory: Every entity must participate (e.g., every Branch must have Staff).

  • Optional: Some entities may not participate (e.g., a Staff member may not manage a Branch).

Participation Examples in ER Diagrams

• Minimum Values for Multiplicity Ranges:

  • Participation of entities can be indicated with minimum values beside relationships.

  • Mandatory Participation: Minimum value of 1 (e.g., Student must enroll in at least one course).

  • Optional Participation: Minimum value of 0 (e.g., Student may not manage any branch).

Activity 1

1. Create ER Diagrams for the Following Descriptions:a) Each company operates four departments; each department belongs to one company.b) Each department employs one or more employees; each employee works for one department.c) Each employee may have one or more dependents; each dependent belongs to one employee.d) Each employee may have an employment history.

2. Represent all the described ER diagrams as a single ER diagram.

Activity 2

• Draw ER Diagram for the Following Description:

  • Students register for different modules.

  • Each student may register for one or more modules (max 24).

  • Each module normally has many students registered.

  • Failed modules must be retaken, with relevant information including date of registration and result.

Activity 3

• Problem Statement:

  • Develop a database system for tracking student registration and accommodation records.

  • Students can enroll in any number of courses based on their IQ.

  • The university owns several hostels, and each student is assigned a shared room after enrollment.

• Tasks:a) Identify main entity types for the project.b) Identify main relationship types and specify multiplicity for each, stating assumptions.c) Draw a single ER diagram to represent the data.