This blog post will look at ERD’s and hopefully will give the reader (and writer!) a better understanding of ERD’s – what they are and why they’re used.
Database systems are often modeled using an ERD. The ERD acts as the ‘blueprint’ from which the actual data is stored. The ERD is the visual component and output of the design phase.
- ER model allows the user to sketch database designs
- ERD is a graphical tool for modeling data
- ERD is widely used in database design
- ERD is a graphical representation of the logical structure of a database
- ERD is a model that identifies the concepts or entities that exist in a system and the relationships between those entities
Purposes of ERD
An ERD serves several purposes:
- The database designer gets a better understanding of the information to be contained in the database through the process of constructing the ERD
- The ERD serves as a documentation tool
- The ERD is used to communicate the logical structure of the database to users. The ERD effectively communicates the logic of the database to users
Typically an ERD consists of three different graphical components:
- Entity – The entity is a person, object, place or event for which data is collected. In a business example the entities are customers, suppliers, orders etc.
- Relationship – The relationship is the interaction between the entities. In a business example, the customer places an order, the word “places” defines the relationship between that instance of a customer and the order / orders placed.
- Cardinality – The cardinality defines the relationship between the entities in terms of numbers i.e. “Cardinality” expresses the maximum number of relationships. Cardinality may be optional e.g. a sales rep could have no customers or could have one or many customers. In terms of cardinality being mandatory e.g. there must be at least one product listed in an order.
A visual representation of these components typically looks like this:
Primary Keys & Foreign Keys
Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity in database tables. These are important database objects.
Primary Key – A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column.
When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.
Foreign Key – A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.
The role of the Primary Key & Foreign Key in a Many to Many Relationship
To resolve a many to many relationship a new entity is created and the foreign key from each table on the one side of the relationship forms a composite primary key in a new table. Other appropriate attributes are also added to this new table.
Based on the above ERD a new table called Sales Order Details is created.
It’s primary key is a composite of the two foreign keys from one side of the relationship. In the below example “Sales Order Details” has two other attributes, quantity_ordered and Order_price.
Simple ERD Example
Below is a simple ERD example based on a college situation. After reading the aforementioned text, can you understand the ERD?
( * = Primary Key – FK = Foreign Key)
If the answer is no, read the blog post again. If the answer is yes, then it’s time to attempt your first ERD. Take a look at the steps for creating your first ERD below. What are you waiting for?
Steps to creating your first ERD
- Identify the entity
- Identify the entity’s attributes
- Identify the relation between entities
- Identify the cardinality constraint
- Draw the ERD
- Check the ERD
SlideShare. 2010. Entity relationship diagram (erd). [ONLINE] Available at: https://www.slideshare.net/tameemyousaf/entity-relationship-diagram-erd. [Accessed 19 March 2017].
Microsoft. 2016. Primary and Foreign Key Constraints. [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/ms179610.aspx. [Accessed 19 March 2017].