Entity Relationship Diagram

Databases are used to store structured data that are stored in tables. The structure of this data, together with other constraints, can be designed using a variety of techniques. One of which is called as entity-relationship modeling (ERM).  The basic object that the entity-relationship model represents is an entity, which is a thing in the real world with an independent existence (such as person, car, house, etc) or it may be an object with a conceptual existence (such as company, job, courses, etc).Entity is represented as table in database. Each entity has attribute that describes it (name, age, address, etc) (Elmasri & Navathe 2002). Diagram made for ERM. Process is an entity relationship diagram (ERD).

Figure 1: Entity Relationship Diagram (ERD)

Source: Powell (2006), Beginning Database Design

An ERD is a type of conceptual data model or semantic data model. Tables (entity) can have various types of relationships between them. The different types of inter-table relationships that can be formed between different tables can be best described as displayed in Entity Relationship Diagrams (ERDs). An ERD displays tables and relationships between those tables. Figure 1 shows an example ERD for tables in a schema containing published books. A table is often referred to as an entity in an ERD (Powell 2006).

Multiplicity is the type of relationship that exists between entities in database. There are three types of multiplicities (relationship) one-to-one, one-to-many and many-to-many; and they are described in details in below paragraphs.

One-to-one relationships are often created to remove frequently NULL valued fields from a table (Powell 2006). This type of relationship rarely exists in any database model. In figure 2 (a) one-to-one relationship is shown between Rank entity and Edition entity. Here this relationship implies that there is exactly one row in the rank entity for every row in Edition entity.

Figure 2: Relationship; (a) One-to-one, (b) One-to-many

Source: Powell (2006), Beginning Database Design

One-to-many relationships are extremely common in the relational database model between tables (Powell 2006). In figure 2 (b) one-to-many relationship is shown between Author entity and Publication entity. Here this relationship implies that there can be many published work for each author.

Figure 3: Relationship; (c) Many-to-many

Source: Powell (2006), Beginning Database Design

A many-to-many relationship means that for every one record in one table there are many possible records in another related table, and visa versa for both tables (Powell 2006). In figure 3 (c) Many-to-many relationship is shown between Publisher entity and Publication entity. Here this relationship implies that a publisher can publish many books and a single book can be published by many publishers, when assuming multiple editions of a single book. One another example of this type of relationship is many students enrolled in many courses at a university. Student can take many courses and similarly course has many students enrolled result in a many-to-many relationship between students and courses.

References:

Powell Gavin. Beginning Database Design. Indianapolis, Indiana. Wiley Publishing 2006.

Elmasri & Navathe. Fundamentals of Database Systems. 3rd edition. Pearson Education 2002.