Overview

This chapter will look at how we view the enterprise and how we go about modeling that enterprise in order to create a database to support it.

E/R diagram

The E/R (Entity-relationship) diagram is the way in which we model the enterprise with it's various business functions and entities into a model that can be used to create the actual database.  The E/R model is a diagram using various icons to represent entities,  attributes, relationships and cardinality for the business.  These will all be defined below.

E/R Notation

Entity - drawn as a rectangle (it includes the ENTITY name and ATTRIBUTES - what the entity is made up of);  it also highlights the IDENTIFIER (or primary key) Attribute and any PARTIAL IDENTIFIER (foreign key)

Relationship - how entities are related to each other

     a.  Unary relationship - an entity is related to itself

              examples:   An EMPLOYEE supervises an EMPLOYEE

                               A PERSON is married to a PERSON

     b.  Binary relationship - an entity is related to another different entity

             examples:   A CUSTOMER purchases a PRODUCT

                              A VENDOR supplies a PRODUCT

     c.  Ternary relationship - three entities are related by one relationship

             examples:  A VENDOR, PART and WAREHOUSE are all related by

                              something being supplied

             Ternary relationships are actually a PROBLEM - we NEED to get them

                             changed to BINARY relationships!!!!!

Attributes - an attribute is a descriptor of what the entity contains:  such as CUSTOMER entity contains such things as Customer Identification Number, Customer Last Name, Customer First Name, Customer Street, Customer City, Customer State, Customer Zip, Customer Home phone,  ......

Cardinality - this is the DEGREE to which Entities are related to each other.  The following statement is used when deciding what a cardinality should be:

"Given one instance of Entity X, how many of Entity Z can we have?"

a.  The cardinality symbol "|" is used to indicate that you MUST have one of Z if you have X

b.  The cardinality symbol "0" is used to indicate that you MAY (OPTIONAL) have one of Z if you have X

c.  The cardinality symbol "open arrow" is used to indicate that you have MANY of Z's if you have X

d.  The cardinality symbol "1" is used to indicate that you only have ONE Z if you have X

The possible combinations are:  mandatory 1; mandatory many, optional 1, optional many

The process for creating your E/R diagram is as follows:

1.  Take a business function and draw the first entity that is listed with it (draw the entity as a rectangle)

      example:   

EMPLOYEE

 

2.  Take the next entity in the business function and draw it as a rectangle (if no more entities in that business function - go to the next business function to get the next entity)

example:

CUSTOMER

3.  See if the entity from #2 above relates to any of the other already drawn entities (rectangles).  If the entity relate to another entity - then DRAW a LINE connecting the two entities and  LIST the relationship (verb) that they have.

      Example:

EMPLOYEE

                 |  serves

CUSTOMER

4.  Repeat steps 2 through 4 until NO MORE ENTITIES from ALL business functions - you will have a diagram with many rectangles and relationships (this takes time - don't skip any entities);**if you have already drawn an entity (such as CUSTOMER) that appears in more than one business function, just skip that one and go to the next entity in the list.

5.  Now start anywhere on your diagram and add the Cardinalities:  remember to use the statement above to decide on how to draw the cardinality (this will take some time so DON'T rush!!)

Now you have a High-level E/R diagram - one that contains Entities, Relationships and Cardinality

6.  Now go and add all attributes to EACH entity

example:

CUSTOMER

Cust_identifier

Cust_last_name

Cust_first_name

Cust_street

Cust_city

Cust_state

Cust_zip  .......

7.  Add the IDENTIFIER (primary key attribute) by underlying the Attribute that would make an instance of that attribute unique - such as Cust_identifer for the CUSTOMER entity.

You now have a low-level E/R diagram - one with Entities, Relationships, Cardinality and Attributes.

Ternary Relationship Problems

As mentioned previously - ternary relationships are a PROBLEM!!!  You need to convert your ternary relationships (if you even have any) to Binary relationships.  This is done by changing the relationship to a NEW entity and adding new relationships AND cardinality between the original 3 entities and the new 4th entity!!

Multi-valued Attribute

It is possible that a given Attribute may actually have more than one value for a given entity.  For example, if "skill" is listed as an attribute for employee, any given employee may actually have more than one skill.  This is called a multi-valued attribute

Composite Attribute

This is one where one might write "Address" under CUSTOMER entity instead of breaking up the individual units in address - it's NOT a good idea to do this - be sure to list the INDIVIDUAL attributes instead

Derived Attribute

It is possible to have some calculated or derived attributes - such as if the company needs to know how many years an employee has worked for the company - they can get that information from the Date_Employed attribute and today's date.  We indicate the derived attribute in our attribute list by putting square brackets around the attribute name.