Check List for ER Modeling

(Revised 25-APR-01)

1) Entity Types

Each entity type should model a type of real-world object. We model an entity in order to store important information about its properties.

Kinds of real-world objects we might care about include people, places, things, organizations, events and concepts. An entity type should be important in its own right. For example, address is often not a good entity

Draw an entity type as a rectangle. Write its name inside it.

The name of an entity type should always be a noun. For example “Manager”. Entity names should be as specific and meaningful as possible.

All entity types must participate in at least one relationship type.

If there is only one of something, and this is not expected to change, then it’s not a good choice for an entity type. For example, if your system includes only one airline or only one university then there is no need to model airline or university as an entity.

2) Attributes

Each attribute is attached to a single entity type or relationship type.

Each entity in an entity type has its own unique set of attribute values. But each has the same set of attributes. For example all Managers might have Name, Rank and Salary attributes – but each individual manager will had a different name, rank and salary.

Draw an entity as an oval. Write its name inside it. The name of an attribute should be a noun – for example “Salary”. It is best if attribute names are unique for the whole ERM and not just for the individual entity type.

Connect composite attributes to the main attribute of which they are a part. For example, name decomposes to “First Name”, “Last Name” and maybe “Middle Name”, “Prefix” and “Suffix”.

Attribute domains are not marked on the ERM itself, and are not required for this exercise. They will be used for the project.

2a) Primary Keys

Make sure you have marked a primary key for each strong entity type. Do this by underlining it on the diagram. The primary key has a unique value for each entity within the entity type. (EG, if SSN is the key for the Manager entity type then no two Manager entities can have the same SSN).

(Each Entity Type must have a primary key. This means it must have at least one attribute, as the key is made up of attributes).

Some primary keys are composite – in this case the contents of a number of attributes are used in conjunction to uniquely define the entity. In this case, underline all the attributes that make up the key.

A week entity automatically “inherits” the primary key of the strong entity it is dependent on. However, if it has a 1:M relationship with the strong entity then it will need an additional partial key, to differentiate it from other weak entities dependent on the same strong entity. (Example from model answer: Flight Instance has date as a partial key, to set it aside from other instances of the same flight number).

There is no need to identity candidate keys on the ERM.

The key must always be unique. It is helpful if it is also short, easy to remember, meaningful in the real world and unlikely to change. Sometimes you have to trade off between these things.

3) Relationships

Draw a relationship type as a diamond. Write its name inside it. The name of a relationship should be a present-tense verb.

Relationship types join together one or more (usually two) entity types. EG “Supervisor manages supervisee”. Check your relationships and entity types by trying to read them this way. If they don’t make sense then change them.

Individual relationships join together two or more individual entities. EG “Jane Doe manages Fred Smith.”. All relationships are bi-directional.

Use relationship names that model real world relationships, not system processes. “Has”, “Assigns”, “Logs”, “Generates”, “Processes”, “Stores” and so on are bad relationship names if they refer to internal system processes.

Relationships can have attributes. If something is

Try and arrange entities and relationships from left to right and top to bottom, to make the diagram easier to read.

Avoid recursive relationships where possible. This can often be done by creating additional subtypes (For example, manages” could be represented as a recursive relationship from person to person, or a regular one from sub-type supervisor to sub-type supervisee).

Avoid relationships including more than two entity types where possible. (So-called N-ary relationships). These relationships can be represented by the combination of several regular (binary) relationships and a weak entity type.

Be alert for fan-traps and chasm traps. (See texbook). If two entities are related then you should be able to read across the diagram and tell unambiguously which is related to which. For example, in the assignment you will need to be able to determine for flight X on day Y, who the pilot is, which specific plane is in use, who the flight attendants are, and so on.

3a) Participation Constraints

Use a double-line to indicate a mandatory relationship. If a relationship type is mandatory for an entity type then each entity must take part in a relationship. For example, it may be mandatory for a supervisee to have a supervisor.

For a weak entity type, at least one of its relationships (with the “parent” strong entity type) will always be mandatory.

Participation constraints can be combined with cardinality by using the Mini-Max notation. If the minimum is more than 0 then participation is mandatory. For example (1,N).

3b) Relationship Cardinality

Show the cardinality of each relationship (eg 1:1, 1:N, M:N). Write cardinality between the relationship diamond and the line leading to each entity type.

Remember, the cardinality refers to the maximum number of entities of one entity type that are linked to an entity of the other entity type through the relationship type. For example, if “supervisor managers supervisee” is a 1:M relationship then this means that each supervisor can have many supervisees (the “M” next to supervisee) but that each supervisee only has one supervisor (the “1” next to supervisor). It does NOT mean that there will only ever be one supervisor in the database – just that each supervisee will only ever have one supervisor at a time.

4) Generalization and Specialization

Subclasses construct an is-a hierarchy. Every member of a subclass is also a member of the super class. It inherits all attributes and relationships of the super class.

Show whether subclasses are disjoint (d) or overlapping (o) for the super class above them.

Show a shared subclass as (u). A shared subclass has multiple inheritance.

Never draw cardinality on a subclass/super class diagram.

A subclass has the same primary key as the superclass.

Show whether membership in at least one subclass is mandatory for each member of the superclass – this is a participation constraint, like that for relationships.

If you can’t read up from each subclass to the super class above it and say “[subclass name] is a [super class name]” then you have done something wrong! (Note: This is true for regular subclasses, as shown with a (d) or an (o). A shared subclass – shown with a (u) holds characteristic that exist for several different entity types, and so the reading here is sometimes more like “has a” than “is a”).

Be very clear on the different between a relationship and a subclass or super class. Relationships tie together separate entities.(Fred manages Jim). Subclasses let us selectively specify additional information (attributes, relationships) about some entities in an overall entity type but not others. For example, by making a “manager” subclass for the person entity type we can show that some people are also managers, and that managers (and only managers) can supervise people. We can also show additional attributes for managers that other kinds of people do not have – for example their budget authorization amount. By making Fred a manager, as well as a person, we store extra information about him and his relationships. But manager is just a specialized kind of person, and there is still only one Fred. If subclasses had a cardinality it would always be 1:1 – Fred the manager and Fred the person are the same.

(therefore) If there is a 1:m or M:M relationship between a subclass and a superclass on your diagram then you got something wrong! Model it as two separate entity types and a relationship.