adam's notes

Binary Relationships

A binary relationship is a relationship between two entity types.

  • simplest kind of relationship

Cardinality

Cardinality represents the maximum number of entities that can be involved in a particular relationship.

Binary Relationships with Cardinality

One-to-One Binary Relationship

A one-to-one (1-1) binary relationship means that a single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa.

  • e.g.,
    • A particular salesperson is assigned to one office
    • a particular office has just one salesperson assigned to it
  • the “bar” or “one” symbol on either end of the relationship in the diagram indicates the maximum one cardinality
  • how to read these diagrams:
    • start at one entity,
    • read the relationship on the connecting line,
    • pick up the cardinality on the other side of the line near the second entity,
    • and then finally reach the other entity

One-to-Many Binary Relationship

  • Figure (b) shows a one-to-many (1-M) binary relationship
  • “crow’s foot” device attached to the customer entity box represents the multiple association
  • many can mean any number
    • not restricted to exactly one
  • Reading from left to right, the diagram indicates that a salesperson sells to many customers
  • Reading from right to left, says that a customer buys from only one salesperson

Many-to-Many Binary Relationship

  • Figure (c) shows a many-to-many (M-M) binary relationship
    • A salesperson is authorized to sell many products
    • a product can be sold by many salespersons
  • “many” can be either:
    • an exact number
    • or have a known maximum value

Modality

Modality is the minimum number of entity occurrences that can be involved in a relationship.

Binary Relationships with Cardinalities (maximums) and modalities (minimums)

  • figure (a)
    • every salesperson must be assigned to an office
    • a given office might be empty or it might be in use by exactly one salesperson
    • the “inner” symbol, which can be a zero or a one, represents the modality
    • the “outer” symbol, which can be a one or a crow’s foot, represents the cardinality
    • reading left to right tells us that a salesperson works in a minimum of one and a maximum of one office,
    • Reading right to left, an office may be occupied by or assigned to a minimum of no salespersons (i.e. the office is empty) or a maximum of one salesperson
  • figure (b)
    • indicates that a salesperson may have no customers or many customers
    • a customer is always assigned to exactly one salesperson
  • figure (c)
    • says that each salesperson is authorized to sell at least one or many of our products
    • each product can be sold by at least one or many of our salespersons

Intersection Data

Intersection data describes the combination of 2 particular entities.

  • e.g., sales person selling products
    • when adding a quantity attribute for the number of a particular product sold by a particular salesperson
    • would not make sense to add the attribute to either entity
    • it describes the combination of a particular sales person and product
    • it falls at the intersection

Many-to-Many Binary Relationship with Intersection data

  • the intersection data describes the relationship between the two entities
  • quantity is an attribute of this occurrence of that relationship

Associative Entity

An associative entity indicates the relationship between two entities and includes any intersection data that describes this relationship

Associative Entity with Intersection Data

  • shows the many-to-many relationship Sells converted into the associative entity SALES
  • indicates a relationship between a salesperson and a product
  • note the reversal of the cardinalities and modalities when the many-to-many relationship is converted to an associative entity
  • a single occurrence of the new SALES entity type records the fact that a particular salesperson has been involved in selling a particular product
  • A single occurrence of SALES relates to a single occurrence of SALESPERSON and to a single occurrence of PRODUCT
    • which is why the diagram indicates that a sales occurrence involves exactly one salesperson and exactly one product

Info

  • When you have many-to-many relationships between two entities, you use a junction table to handle this intersection data

A junction table is a special table that connects the two entities by storing their relationships.

A transitive dependency occurs in a database when one attribute depends on another, and that second attribute, in turn, determines a third attribute.

  • it’s when an indirect relationship exists between two attributes through a third attribute
  • To avoid transitive dependencies,
    • break down the relationship into separate tables and use junction tables to connect them

Unique Identifier in Many-to-Many Relationships

The unique identifier of the many-to-many relationship of the associative entity is the combination of the unique identifiers of the two entities in the many-to-many relationship.

  • Sometimes, an additional attribute or attributes must be added to this combination to produce uniqueness
    • often involves a time element
    • if we wanted to keep track of the sales on an annual basis, we would have to include a year attribute and the unique identifier would be Salesperson Number, Product Number, and Year
  • The third and last possibility occurs when,
    • the nature of the associative entity is such that it has its own unique identifier
    • e.g.,
      • company might specify a unique serial number for each sales record

Graph View

Backlinks