Agenda 02 06 2014
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Agenda: 02/06/2014 PowerPoint PPT Presentation


  • 33 Views
  • Uploaded on
  • Presentation posted in: General

Agenda: 02/06/2014. Review concepts of design relationships. Learn more about relationships. Heuristics of a good ERD. Demonstrate MS Visio. Practice creating more ERD’s (if time…). Relationship review. We are purposely dividing data into entities to have less data redundancy.

Download Presentation

Agenda: 02/06/2014

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Agenda 02 06 2014

Agenda: 02/06/2014

  • Review concepts of design relationships.

  • Learn more about relationships.

  • Heuristics of a good ERD.

  • Demonstrate MS Visio.

  • Practice creating more ERD’s (if time…)


Relationship review

Relationship review

  • We are purposely dividing data into entities to have less data redundancy.

    • Entities do not usually exist in isolation.

    • Entities are related because the data are related.

  • A relationship is a business association between two or more entities.

    • A relationship is a business rule.

    • An ERD is a simple modeling tool – it is not possible to depict every “real life” relationship with an ERD.


Agenda 02 06 2014

Example: Binary M:N Relationship


Agenda 02 06 2014

Example: 1:M Binary Relationships with Intersection Entity


Agenda 02 06 2014

Example: 1:M Binary Relationships with Intersection Entity

Depicts a Ternary Relationship


Agenda 02 06 2014

Example: 1:1 Binary Relationship


Agenda 02 06 2014

Example: 1:M Unary Relationship


Agenda 02 06 2014

Examples of Unary Relationships


Agenda 02 06 2014

A many-to-many (M:N) unary relationship must also be intersected


Another type of relationship generalization enhanced erd

Another type of relationship: Generalization (Enhanced ERD)

  • Generalization is the concept that some entities are very similar and have some attributes in common, but other attributes that are distinct.

  • An entity “supertype” contains those attributes that are in common.

  • An entity “subtype” contains those attributes that are distinct. An entity “subtype” adds other attributes to those inherited from the supertype.

  • Generalization allows an analyst to understand more fully the relationships between entities.


Agenda 02 06 2014

Let’s go back to the consulting ERD…


Agenda 02 06 2014

Depict the generalization hierarchy


Generalization constraints

Generalization constraints

  • Constraints are limitations depicted on a data model.

  • The most common types of constraints are:

    • Completeness. Addresses the question of whether an instance of a supertype must also be a member of at least one subtype.

      • Result is either required or not required.

      • Indicated by a double line in Visio.

    • Disjointness. Addresses the question of whether an instance of a supertype may simultaneously be a member of two or more subtypes.

      • Result is specified as disjoint or overlap.

      • Indicated with either an “o” or a “d”.


Heuristics redundant data

Heuristics: Redundant Data

  • Avoid redundant data that is composed of long alphanumeric data types.

    • Examples are names, addresses, comments, notes.

    • Standardize any “descriptive” attributes such as categories or types.

  • Put sample data in a few rows of each entity so that you can determine whether or not the data will be redundant.

    • If you don’t know what data will be stored, ask your client.


Heuristics relationships

Heuristics: Relationships

  • Do not include M:N relationships.

    • M:N relationships always produce data redundancy.

    • Divide M:N relationships with an intersection entity that will create at least two 1:M relationships.

  • A single intersection entity can be used to intersect more than two strong entities.

  • In a 1:M relationship, the foreign key is placed in the entity on the “many” side of the relationship.


Heuristics for 1 1 relationships

Heuristics for 1:1 Relationships

  • Examine all 1:1 relationships. Determine whether the attributes could all be placed in just one of the two entities eliminating the need for the relationship.

  • In a 1:1 relationship, the foreign key can be placed in either entity. Put the foreign key in the entity that seems most “reasonable” and also will result in the fewest number of null values.


Heuristics primary key

Heuristics: Primary Key

  • A “natural” primary key is composed of attributes that are already part of the application specification.

  • A “surrogate” primary key is created by the database designer for the explicit purpose of being a primary key.

  • A surrogate primary key should NEVER be concatenated.

  • For logical database design, try and find a natural primary key.


Heuristics more primary key

Heuristics: More Primary Key

  • Never add more attributes than are absolutely necessary to create a primary key value unique.

  • Use the primary key of the related strong entity as part of the primary key for the associative entity, if the relationship with the associative entity is mandatory.

  • Use the primary keys of the strong entities as part or all of the primary key for the related intersection entity.


  • Login