ER modelling

ER modelling is one of the most common techniques used in database design. The main objective being to help in understanding the nature and relationships that exist within the data of the system.

The ER diagrams can then be used by the designer to derive a logical table schema. The first stage before any ER diagram is drawn is to understand the problem domain. The problem domain for the payroll system is as follows:
Managers submit payroll queries. Payroll personnel view the payroll queries, each viewing being recorded. A query can have comments. Payroll personnel may add comments. Each query will eventually have a reply, a reply is sent by one of the payroll personnel.

Entities are any real thing or abstract notion that we wish to recognise as a separate object within the domain. Taken from the problem domain the entities for the system are as follows: Manager, Payroll Personnel, Query, Reply, Comment.

A weak entity is one which cannot exist without the existence of some other entity. Taken from the problem domain the weak entities for the system are as follows:
Reply: as a reply is to a query, a reply can not exist without a query.
Comment: as a comment is on a query, a comment can not exist without a query.
Relationships are associations that exist between these entities. A relationship has a cardinality that specifies, for one member of first entity set, the possible number of members it can be related to in the second entity set. The three possible types of cardinality are listed below along with the relationships that have a cardinality of that type. A relationship also has optionality. A relationship can be; optional, part optional or mandatory. The optionality of a relationship shown below in parenthesis determines whether or not a column attribute can be null affecting the table design.

One to One relationships
There are no relationships in this problem domain with a one to one cardinality.
One to Many relationships:

• Managers submit payroll queries (part optional).
  One manager may submit many payroll queries (optional).
  One payroll query can only be submitted by one manager (mandatory).
• A query can have comments (part optional).
  One query can have many comments (optional).
  One comment can only belong to one query (mandatory).
• Payroll personnel may add comments (part optional).
  One payroll personnel can add many comments (optional).
  One comment can only be added by one payroll personnel (mandatory).
• A reply is sent by one of payroll personnel (part optional).
  One reply can only be sent by one payroll personnel (mandatory).
  One payroll personnel may send many replies (optional).
• Each query will eventually have a reply (part optional).
  One query can have many replies (optional).
  One reply can only belong to one query (mandatory).
  

Many to Many relationships

• Payroll personnel view the payroll queries (part optional).
  One query can be viewed by many payroll personnel (mandatory).
  One payroll personnel may view many queries (optional).

All entities have attributes that describe them. The values of the attributes describe the properties of an instance of the entity. The attributes of the entities in the problem domain are as follows:
Manager: username, password, f name, l name.
Payroll Personnel: username, password, f name, l name.
Query: ref, fname, lname, payroll no, query, date time.
Reply: reply, date time.
Comment: comment, date time.