ER (Entity-Relationship) Diagrams

Visual Representations of a Relational Database


star




What is an ER Diagram?



As we read in Oppel and Sheldon (2009), an entity is "[a] person, place, thing, event, or concept about which data is collected, such as a recording artist, a book, or a sales transaction."

We also noted in Database Principles (in Welling and Thomson, 2001), that a relationship is a "link between tables; can be one-to-one, one-to-many, or many-to-many, 'according to the number of things on each side of the relationship.'" Strictly speaking, a many-to-many relationship is not permitted in an ER diagram or in any database schema.

"In a many-to-many relationship between Table A and Table B, each row in Table A is linked to 0, 1 or many rows in Table B and vice versa. A 3rd table called a mapping table [or, bridging table] is required in order to implement such a relationship. (www.databaseprimer.com)

Let us now take a look at an ER diagram of a relational database (that is, one with multiple tables and relationships between them) and see what we can tell about it upon closer examination.


Bond ER Diagram

Figure 17: ER diagram from our James Bond database (November 2016)

Notes:

  1. This table pertains to our James Bond database, which was our second Assessment for our databases unit.
  2. There are six tables in this schema.
  3. The main table, to which all the others refer or link in some way, is tblMovie.
  4. To prevent a many-to-many relationship between the Cars table and the Movies table, there is a bridging table connecting them, called tblMovieCar.
  5. The database has separate tables for Movies, Girls, Villains, Cars and Songs, as well as our Movie-Car bridging table.
  6. There is a one-to-one relationship between movies and songs (to simplify things, we had to imagine that this is actually the case with James Bond movies).
  7. Visit our James Bond website to get a better idea of what data we have been working with.
  8. We will be linking a database with the search bar in our website in our IT Certificate IV, such as to allow full web interactivity.
  9. Note the relationships between the tables. These symbols stand for:

ER diagram info

Figure 18: Information Engineering style of how to depict cardinality.
From http://younetaddict.blogspot.com.au/2012/03/how-to-make-erd.html




© 2016-2020 Leo Coroneos
Certificate IV in Information Technology
South Regional TAFE, Albany WA Australia