Attributes (Candidate keys)

Entities

Based on the Personnel scenario you can identify some real world objects ( called "concrete" entities).

• Employee

• Assets

• Clients

Another type of entity represents a concept (called an "abstract" entity) , for example

• Timesheet (where it shows when an employee worked for a client).

Attributes

Now think about what thigs we want to know about each entity. These are called "attributes"

So for;

• Employee we would have things like EmployeeID, Name, Address, Mobile, IRDNumber, Comments

• Clients we would have things like ClientID, Name, Address, Mobile, GSTNumber, Comments

• Assets we would have AssetID, Description, PurchaseDate, Value

• Timesheet (TimesheetID, EmployeeID, ClientID, Date, Time, Description)

Key attributes

Our next issue is if we have a list of say Staff how could we make sure that they are all unique? It does happen that more than one person can have the same name, so name is not really a good way to "uniquely" identify a person.

In a database a simple way to identify each person (instance) is to give them a unique identifier (usually a number). e.g.,.

Employeee:

1. Joe Bloggs

2. Sue Smith

3. Tui Puke

So here if you wanted to reference "Sue Smith" you would use the identifier "2".

Candidate Keys

It can happen that you can choose to have other attributes that will also uniquely identify an instance. For example, if we think of an Employee attributes could be:

tblEmployee

• EmpID (a unique Number)

• EmpLastName

• EmpFirstName

• EmpAddresss

• EmpMobile

• EmpIRDNumber

(Notice I have put in a three character prefix - this really helps when you have multiple entities where some of the attributes are the same e.g., for Employees ands Clients both will have a Name attribute)

So for EmpMobile and EmpIRDNumber (the employee's mobile phone and Inland Revenue number) they are likely to be unique to the employee and can be considered a candidate key (as can the primary key). But, it is possible that an employee does not have a mobile phone or IRD number (if they want to get paid they probably need one!) so would not be a good choice for a primary key. However they are considered secondary keys.

Primary Key

This is called the Primary Key.

Some rules for a primary key.

1. They must uniquely identify one instance of an entity

2. Every instance must have one

Foreign Keys

A Foreign Key allows you to link tables together. So if you have an Employee and need to assign an Asset to them you would need to include the EmployeeID in the Asset table, as shown in the Diagram following (Figure 1). Note that an asset can ONLY be assigned to a maximum of 1 employee.

It is common to use the same identifier in both tables (EmpID). To make the ERD clearer, the line between the entities is best drawn so that it connects the two attributes (EmpID) and if possible arrange the entities so that the line is horizontal (as shown). When identifying which EmpID you are referring to you can use object notation e.g., tblEmployee.EmpID and tblAsset.EmpID.

Foreign Keys

A Foreign Key allows you to link tables together. So if you have an Employee and need to assign an Asset to them you would need to include the EmployeeID in the Asset table (Note that an asset can ONLY be assigned to a maximum of 1 employee).

It is common to use the same identifier in both tables (EmpID). When identifying which EmpID you are referring to you can use object notation e.g., tblEmployee.EmpID and tblAsset.EmpID.

Entity/Attributes notation showing keys

When we describe the entities and attributes we can also show the PKs and FK by underlining the PKs and putting a * next to the FKs.

• tblEmployee ( EmployeeID, Name, Address, Mobile, IRDNumber, Comments)

• tblClients ( ClientID, Name, Address, Mobile, GSTNumber, Comments)

• tblAssets (AssetID, ClientID*, Description, PurchaseDate, Value)

Foreign Keys in an ERD

To make the ERD clearer, the line between the entities is best drawn so that it connects the two attributes (EmpID) and if possible arrange the entities so that the line is horizontal (as shown). When identifying which EmpID you are referring to you can use object notation e.g., tblEmployee.EmpID and tblAsset.EmpID.