Entity-Relationship Model

Intro

One of the most difficult aspects of a database design is the fact that designers, programmers, and end-users tend to view data and its use in different ways. Unfortunately, unless we gain a common understanding that reflects how the enterprise operates, the design we produce will fail to meet the users' requirements. To ensure that we get a precise understanding of the nature of the data and how it is used, we need to have a model for communication that is non-technical and free of ambiguities. The Entity-Relationship model is one such example.


ER model is a top-down approach to database design that begins by identifying the important data called entities and relationships between the data that must be represented in the model.

Entity type A group of objects with the same properties, which are identified by the enterprise as having an independent existence. (The basic concept of the ER model, which represents a group of "objects" in the "real world" with the same properties.)

Entity occurrence A uniquely identifiable objects of an entity type.
Example: Branch, Department, Staff, Skills

Relationship type A set of meaningful associations among entity types (each relationship is given a name that describes its functions).
Example: Branch Has Department

Relationship occurrence A uniquely identifiable association, which includes one occurrence from each participating entity types.

Degree of a relationship type The number of participating entity types in a relationship. (binary, ternary, quaternary) Example of a quaternary relation: A solicitor arranges a bid on behalf of a buyer supported by a financial institute.

Recursive relationship A relationship type where the same entity participates more than once in different roles. (Relationships may be given role names to indicate the purpose that each entity type plays in a relationship.)
Example:

Staff (Supervisor) Supervises Staff (Supervisee).

Attribute A property of an entity or a relationship.

Attribute domain A set of available values for one or more attributes.

Attributes can be classified as:

  • simple or composite;
  • single-valued or multi-valued;
  • or derived.

    Simple (or atomic) attribute An attribute composed of a single component with an independent existence. (Simple attribute can not be further subdivided into small components.)
    Example: Salary.

    Composite attribute An attribute composed of multiple components, each with different existence. (Can be further divided.)
    Example: Address can be subdivided into Street, City, State, ZipCode.

    Single-valued attribute An attribute that holds a single value for each occurrence of an entity type.

    Multi-valued attribute An attribute that holds multiple values for each occurrence of an entity type.
    Example: Phones (of an office), Authors (of a book), Skills (of an employee).

    Derived attribute An attribute that represents a value that is derivable from the values of a related attribute or a set of attributes, not necessarily in the same entity.

    Keys

    Candidate key The minimal set of attributes that uniquely identifies each occurrence of an entity type.

    Primary key The candidate key that is selected to uniquely identify each occurrence of an entity type.

    Composite key A candidate key that consists of more than one attribute.

    Note: Relationship also may have attributes. Example: Relationship: Parts SoldTo Client may have attributes When or/and HowMany.

    Structural constraints

    These constraints should reflect the restrictions on the relationships as perceived in the "real world". The main type of constraints on relationships is called multiplicity.

    Multiplicity The number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.
    Binary relationship are generally referred to as being:

  • one-to-one (1:1)
  • one-to-many (1:*)
  • many-to-many (*:*)
    Examples:
  • Department Has Manager (1:1)
  • Department Employes Staff (1:*)
  • Part SoldTo Client (*:*)