Entity-Relationship Model 3/3

In the previous two articles we have been talking about the entity-relationship model.  We have talked about entites and attributes, relationships, different types of relationships, cardinality and participation constraints etc.  In this article we are going to be learning about something we have mentioned before, which is crucial if we want to efficiently model our relational database: keys.  We are also going to give an introduction to creating entity-relationship diagrams, before giving an example diagram in the next article.

One of the most important concepts in relational database modelling is the following: there can be no multiple entities with the same values on all attributes.  This uniqueness property is regulated by keys.  Now, we are going to introduce the concept of a primary key.  A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.  A primary key's main features are that it must contain a unique value for each row of data, and it cannot contain null values.  Proper selection of primary keys is very important for the organization of the entire database, which is why this is something which should be carefully thought through.  When modelling the database, it is important for the administrator to notice certain properties of things in real life which need to be unique.  For example, if we are defining a table called Student, our primary key will probably be something like StudentID, and not, for example, the student's name, since that does not have to be unique.  A good practice is giving every table in your database an integer ID as a primary key which can be auto-incremented when a new record is entered.

Sometimes, we have a situation in which entities of a certain table are defined by a property from another table.  This is when we use foreign keys.  In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.  In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.  The purpose of the foreign key is to ensure referential integrity of the data.  In other words, only values that are supposed to appear in the database are permitted.  We are going to give examples of using primary and foreign keys when we create our entity-relationship diagrams, and for now, we will make a short introduction about the elements of the diagram.

Entity-Relationship Diagram

Like we said earlier, the entity-relationship conceptual model of a database can be represented graphically, using a diagram.  These diagrams are simple and intuitive, and their basic components are:

  • Rectangles - specifying entity types
  • Ellipses - specifying attributes
  • Romboids - specifying relationship types
  • Lines - connections between entities and attributes, or entities and relationship types.

In the picture, you can see a more detailed overview of the notation:

We have talked about all of the concepts mentioned in the picture, except for weak entities.  Sometimes, entity types don't have attributes which constitute the primary key.  Such types are called weak entity types.  This kind of entity can be identified through their relationship with a strong entity type.  This is why these strong entity types are also called identifying types, and the relationship between them is called an identifying relationship.  Another concept related to entity-relationship diagrams (which is not shown in the picture, because it is usually considered a part of the "extended" entity-relationship model) is specialization/generalization.  Specialization is the process of identifying and representing sub-groups in a certain entity type.  This is very closely related to the concept of inheritance, parent and child classes, which we learned in object-oriented programming.