---

The Entity-Relationship Modeling Technique

---

• Modeling Methodlogy used in Database Design
  • There are two fundamentally different approaches to database design:

    An "inexact" approach using the Entity-Relationship (or the Extended Entity-Relationship) modeling technique The "exact" normalization approach which is based on mathematical functional dependencies between various attributes.

  • Right now, we will study the Entity-Relationship technique.

  ---

  ---

  ---

  ---

• Concepts in the Entity-Relationship (ER) model
  • The ER model is based on two fundamental concepts :

    Entity = a thing in the real world. Entities in the model = a thing that we find important (So much so that we want to store information abouth that thing) Typically, our database model will only represent a small portion of the real world, or a "micro-world". Relationship = is a mathematical concept - discussed later (you may still remember its definition from your calculus days....)

  ---

  ---

  ---

  ---

• Entity
  • Entity = an object in the real world

    To make things more concrete: you are an example of an "entity"

  • Entities have properties or attributes , e.g., name, age, sex, SSN, etc.

    ---

    ---

  • Entity type = the collection (set) of entities that have the same set of properties/characteristics
    • Example:

      Student is an entity type (Because every student has the same set of properties; e.g.: each one has a student ID, a major, a GPA, etc In contrast: a faculty member do not have a GPA)

    ---

    ---

  • Entity instance:

    Entity instance = one particular representative of an entity set Example: John is an entity instance of entity type student

    ---

    ---

  • Entity set:

    Entity set = a collection of entity instances at a given moment in time Example: {John, James, Mary} is a Entity set

    ---

    ---

  • Entity type schema = the description of an entity type

    Entity type schema consists of an entity identifier (name) and a list of properties of the entity

    • Example:

      Student(StuID, Name, Address, ...) is an entity type schema

  ---

  ---

  ---

  ---

  ---

  ---

• Attributes (of an entity)
  • Continue... Entities have attributes

  • Each attribute can take on values from a certain domain.

  • Example:
    • The entity person has the attribute Social Security Number.

    • This attribute can assume the values between 000-00-0000 and 999-99-9999

  ---

  ---

  ---

  ---

• Types of Attributes

  Attributes can come in different types....

  • Simple attributes: the attribute only takes on atomic values.
    Example simple attributes: color, age, salary

  • Compound attributes: the attribute takes on structured values.
    Example compound attributes: address (consists of street, city, zip), name (consists of first_name, middle_initial, last_name)

  • Multivalued attributes: the attribute takes on a set of values.
    Example multivalued attribute: earned_degree (can take on {BS, MS} - a set)

  • You can also nest any of the above types of attributes.

  ---

  ---

  ---

  ---

• Other properties of attributes
  • Derived attributes: values of a derived attribute can be derived from other attributes.

    Example: age - can be derived from "birth date".

    Note: never store derived attributed in the database, you introduce unnecessary redundance....

  • Key attributes: a set of (i.e., one or more) attributes for which no two different entities will have the same values for that set of attributes.

    Values of key attributes can be used to identify an entity uniquely

    Example:

    Social security number is a key attribute and it is commonly used for identifying humans in the US

  ---

  ---

  ---

  ---

• Special Attribute value: The NULL value
  • There is a special value that an attribute can take on: NULL

  • When an attribute value is NULL, its meaning can be:
    • The attribute value is not available, or
    • The attribute value is not applicable

  • IMPORTANT NOTE: When comparing whether two attribute values equal, and the two attribute values that NULL, the outcome of the compare is FALSE !!!

    I.e.: NULL == NULL result is FALSE (because NULL means "unavailable", so we don't know the outcome of the equality comparision, thus resulting is FALSE)

  ---

  ---

  ---

  ---

• Prelude to Relationship: Relation - a well-known mathematical concept
  • OK, we have covered the E (Entity) in the ER-model

    Next up: the R (Relationship) in the ER-model

    ---

    ---

  • Relationship is based on a well-known mathematical concept: relation

  • Given two sets:
    • A = {a₁, a₁, ..., a_m,}
    • B = {b₁, b₁, ..., b_n,}

  • A relation R (in Mathematics) is a subset of the cartesian product A x B of the sets A and B

  • The cartesian product A x B of the sets A = {a₁, a₂, ..., , a_m} and B = {b₁, b₂, ..., , b_n}:

    consists of all pairs of tuples { (a1,b1), (a1,b2), ... (a1,bn), (a2,b1), (a2,b2), ... (a2,bn), ... (am,b1), (am,b2), ... (am,bn)}

  ---

  ---

  ---

  ---

• Examples of Relations

  • Given several (concrete) sets:
    • Students = {John, James, Mary, ...}
    • Courses = {CS170, CS255, CS377, CS450, MATH110, MATH221, ...}
    • Major = {Math, CS, Chem, Phys, ...}

  • Examples of Relations:

    A relation between Students and Courses can be Grade Report describing which student has taken which course: Grade Report = {(John, CS170), (John, CS255), (Mary, CS170)} A relation between Students and Major can be Majoring describing which student has chosen which major: Majoring = {(John, Math), (John, Chem), (Mary, CS)} A relation between Courses and Major can be Major-Requirement describing which courses are has required which major: Major-Requirement = {(MATH221, Math), (CS170, CS), (CS255, CS)}

  ---

  ---

  ---

  ---

• Some notes on "Relation"

  • The "relation" concept in Mathematics is static

    In other words:

    The content of a Mathematical relation does not change in time

    This is the main reason why a relation is not used in ER modeling (content of relations must change in database, e.g., more kids are born to parents)...

    Instead, ER-modeling uses the concept of relationship

  • Nevertheless, but the relationship concept used in ER model is very closely related with the mathematical concept of relation

  ---

  ---

  ---

  ---

• Relationship Types, Relationship Sets and Relationship Instances
  • A relationship type R among n entity types E₁, E₂, ..., E_n is a mathematical relation among the entity types E₁, E₂, ..., E_n.
    Thus, a relationship type R defines a set of associations among the n entity types.

    So a relationship type R consists of all possible subset of the cartesian product E₁ x E₂ x ... x E_n

  • A relationship instance is one tuple from the cartesian product E₁ x E₂ x ... x E_n

  • A relationship set is the set of relationship instance at a given moment in time.

    ---

    ---

  • Example:

    Entity types: Student Course Entity sets: Student = {John, James, Mary, ...} Course = {CS170, CS255, CS377, CS450, MATH110, MATH221, ...} Example of a Relationship type: Grade Report Grade Report = Student <==> Course (Represents all possible subsets of the cartesian product of Student × Course) Example of a relationship set of the Grade Report relationship type: {(John, CS170), (John, CS255), (Mary, CS170)} (One of the possible subset of the cartesian product of Student × Course) Example of a relationship instances (John, CS170)

  ---

  ---

  ---

  ---

  ---

  ---

• ER Model
  • The Entity-Relationship model uses
    • Entity types and
    • Relationship types

    to model a part of the real world.

  • We will do a simple design to illustrate how this is done and introduce further concepts of the ER modeling technqiue.

  ---

  ---

  ---

  ---

• NOTE: When is an entity not an entity but an attribute...
  • There is a fine line on whether one would call something an entity or an attribute

    E.g., A tire compnay Good-year will call a tire an entity, because it manufacture tires and will need to maintain a lot of information on tires (e.g., how (components) to make it, where it is made, etc - these information will be needed to compute cost/earning for the company)

    On the other hand, a car manufacturer - like Honda - would most likely look at a tire as an attribute of a car : it does not care how or where the tire is made, all Honda needs to know is the make of the tire

    E.g., THIS Accord car has four Michellin 140 tires

    THAT Accord car has four Good-Year 700 tires

  • A rule of thumb is that if something is important enough for the user, and you need to maintain more than the identifying information of the object, then the object is is an entity in the ER-model....

    Otherwise, the object is modeled as an attribute