Design

In the development of a complex database system, entity-relationship modelling is used to plan the structure of the database. In entity-relationship modelling, specialist terminology is used to define each component of the model. This terminology includes:

Entity

• An entity is a person, place, thing, event or concept of interest to the business or organisation about which data is to be stored. For example, in a school, possible entities might be Student, Teacher, Class and Subject.

Entity occurrence

• A specific example of an entity is called an instance or entity occurrence. For example, John Smith, Mary McLeod and Omar Shaheed are all entity occurrences found in the Student entity; English, Computing and Chemistry are all entity occurrences within the Subject entity.

Attribute

• An entity is described by its attributes. Each attribute is a characteristic of the entity. For example, attributes of the Student entity would include studentID, firstname, surname and dateOfBirth.

Primary key

• An attribute or combination of attributes that uniquely identifies one, and only one record is called a primary key. For example, the primary key of the Student entity would be studentID. A primary key is signified by underlining in an entity-relationship diagram.

Foreign key

• An attribute in one table that uniquely identifies a row of another table  (in which it is a primary key). The foreign key creates a link between two tables. A foreign key is signified by an asterisk (*) in the entity-relationship diagram.

Compound key

• A compound key is a primary key that comprises two or more attributes. Each attribute that makes up a compound key is a primary key in its own right. For example, the primary key of a Class entity would be the compound key formed by combining subjectCode + teacherRef columnID. In this example, subjectCode would be the primary key of the Subject entity, teacherRef would be the primary key of the Teacher entity .

Relationship

• A relationship is a natural association between one or more entities. For example:

  • Students learn Subjects.  

  • Teachers educate Students.

Entity-occurrence diagram

• An entity-occurrence diagram illustrates the relationships between the entity occurrences of one entity, with the entity occurrences within a related entity. The creation of an entity occurrence diagram helps to identify the cardinality of the relationship that exists between the two entities.

Entity-relationship diagram

• An entity-relationship diagram is a graphical representation of the entities in a system. It is used to summarise the relationship that exists between two or more entities.

The cardinality of a relationship defines the number of participants in the relationship. It states the number of entity occurrences in one entity that are associated with one occurrence of the related entity. Cardinality can be:

• 1. One-to-one

  2. One-to-many

  3. Many-to-many

Each entity is associated with only one entity occurrence within a related entity.

For example:

• A School is managed by only one Headteacher and a Headteacher manages only one School.

Each entity can be associated with one or more entity occurrences within a related entity.

For example:

• A School employs many Teachers and a Teacher is employed by only one School.

Several occurrences of an entity can be associated with multiple entity occurrences within a related entity.

For example:

• Students study many Subjects and a Subject is studied by many Students.

Whilst many-to-many relationships are fine between two entities during the modelling stage this cannot be implemented by a relational database system.

To overcome this problem, many-to-many relationships can be resolved to form 2 one-to-many relationships. You will notice that in the example below we have introduced a new intermediate/linking table. This would hold usually hold the compound key which would consist of the primary key of the Student table and the primary key of the Subject table.