Entity Relational Design

An entity is depicted as a box. The top is the title or name of the entity. Next is a primary key, a separator, and then the attributes that describe the entity. Bold font indicates that an attribute is required.

There are three types of relationships between entities

One-to-Many, which is the normal relationship among entities and will account for 95% of all relationships. Here is one Department has many employees.

The one side always points to the primary key, the crow's foot (the three point prong) always points to the many side

One-to-One. This is quite rare but is legal. It says for every record in one table there is exactly one matching record in the second table. One example of this is when a table is split up for security reasons. For instance, the public information about an employee could be in one table and the private in another.

The third relationship is Many-to-Many. This is legal in design, but no database can process it. Whenever you have a many to many relationship, you must resolve it into two one-to-many relationships by creating a linking table. For instance, assume an employee can be in more than one department. That means that each employee can be in many departments and each department contains many employees. A many to many relationship. To resolve this we create a linking table, here called EmployeeDepartment.

Here is the coffee shop ERD we did in class