What Data Structure Would You Use To Implement/model A Line At Quick Service Restaurant

Main Body

Affiliate 8 The Entity Relationship Information Model

Adrienne Watt

The entity human relationship (ER) data model has existed for over 35 years. It is well suited to information modelling for use with databases because it is fairly abstract and is like shooting fish in a barrel to hash out and explicate. ER models are readily translated to relations. ER models, also called an ER schema, are represented by ER diagrams.

ER modelling is based on two concepts:

Entities, defined as tables that hold specific information (data)
Relationships, defined every bit the associations or interactions between entities

Hither is an example of how these ii concepts might exist combined in an ER data model:Prof. Ba (entity) teaches (relationship) the Database Systems class (entity).

For the rest of this chapter, we will use a sample database called the COMPANY database to illustrate the concepts of the ER model. This database contains information virtually employees, departments and projects. Important points to notation include:

There are several departments in the company. Each department has a unique identification, a proper name, location of the role and a particular employee who manages the department.
A section controls a number of projects, each of which has a unique name, a unique number and a budget.
Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to ane department simply can join in several projects. Nosotros need to record the start date of the employee in each project. We besides need to know the directly supervisor of each employee.
Nosotros want to go along track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

Entity, Entity Ready and Entity Type

An entity is an object in the real world with an contained existence that can be differentiated from other objects. An entity might exist

An object with physical existence (east.1000., a lecturer, a student, a auto)
An object with conceptual beingness (e.grand., a form, a job, a position)

Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.

That is, it cannot exist without a human relationship with another entity
Its master cardinal is derived from the primary cardinal of the parent entity
- The Spouse table, in the Company database, is a weak entity considering its primary cardinal is dependent on the Employee table. Without a corresponding employee record, the spouse tape would not exist.

An entity is considered strong if it can exist autonomously from all of its related entities.

Kernels are strong entities.
A table without a strange key or a table that contains a foreign cardinal that can contain nulls is a potent entity

Some other term to know is entity type which defines a collection of similar entities.

An entity set is a drove of entities of an entity blazon at a particular point of fourth dimension. In an entity relationship diagram (ERD), an entity blazon is represented by a proper name in a box. For example, in Figure 8.1, the entity type is EMPLOYEE.

A yellow rectangle with e1, e2 and en inside. There is an arrow from the yellow box to a blue rectangle with the work EMPLOYEE in capitals. Over the arrow are the words Represent in ER diagram. To the far left it says Entity set. — Figure 8.ane. ERD with entity blazon EMPLOYEE.

Existence dependency

An entity'southward beingness is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign cardinal (i.e., a foreign key aspect that cannot be null). For instance, in the COMPANY database, a Spouse entity is existence -dependent on the Employee entity.

Kinds of Entities

Yous should also exist familiar with different kinds of entities including independent entities, dependent entities and feature entities. These are described below.

Independent entities

Independent entities, likewise referred to as kernels, are the courage of the database. They are what other tables are based on. Kernels have the post-obit characteristics:

They are the edifice blocks of a database.
The primary key may be simple or composite.
The primary key is not a foreign key.
They do non depend on another entity for their existence.

If we refer dorsum to our COMPANY database, examples of an independent entity include the Customer table, Employee tabular array or Product table.

Dependent entities

Dependent entities, also referred to as derived entities, depend on other tables for their meaning. These entities have the following characteristics:

Dependent entities are used to connect two kernels together.
They are said to exist existence dependent on two or more tables.
Many to many relationships become associative tables with at least two foreign keys.
They may contain other attributes.
The foreign key identifies each associated table.
There are three options for the primary key:
1. Use a composite of foreign keys of associated tables if unique
2. Use a composite of foreign keys and a qualifying column
3. Create a new simple primary cardinal

Characteristic entities

Characteristic entities provide more than information near some other table. These entities have the following characteristics:

They represent multivalued attributes.
They describe other entities.
They typically have a one to many human relationship.
The foreign key is used to farther place the characterized tabular array.
Options for primary key are as follows:
1. Use a composite of foreign key plus a qualifying column
2. Create a new uncomplicated primary key. In the Visitor database, these might include:
  - Employee (EID, Name, Address, Age, Salary) – EID is the simple primary primal.
  - EmployeePhone (EID, Phone) – EID is office of a composite primary key. Here, EID is likewise a foreign key.

Attributes

Each entity is described past a set of attributes (e.chiliad., Employee = (Proper noun, Address, Birthdate (Age), Salary).

Each aspect has a name, and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD.

In the entity relationship diagram, shown in Figure 8.ii, each attribute is represented by an oval with a name inside.

One blue rectangle with the word EMPLOYEE. This is connected with a line to four separate yellow ovals. Each has a different word inside it: Name, Address, Birthdate, Salary. — Effigy eight.2. How attributes are represented in an ERD.

Types of Attributes

There are a few types of attributes you demand to be familiar with. Some of these are to be left every bit is, but some demand to be adjusted to facilitate representation in the relational model. This showtime department will discuss the types of attributes. Afterward on we will discuss fixing the attributes to fit correctly into the relational model.

Simple attributes

Simple attributes are those fatigued from the diminutive value domains; they are likewise chosen unmarried-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23}

Composite attributes

Blended attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written equally → Address = {59 + 'Meek Street' + 'Kingsford'}

Blue rectangle with the word EMPLOYEE. Under this are four yellow ovals with the words Name, Address, Birthdate, Salary. There are lines between the rectangle and yellow ovals. Under the Address oval are three white ovals with the words Number, Street, Suburb. — Figure 8.3. An example of composite attributes.

Multivalued attributes

Multivalued attributes are attributes that accept a prepare of values for each entity. An example of a multivalued aspect from the Company database, as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.

Blue rectangle with the word EMPLOYEE. A line connects this to each of five yellow ovals with these words inside the ovals: Degrees, Name, Address, Birthdate, Salary — Figure eight.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are attributes that comprise values calculated from other attributes. An example of this tin be seen in Figure viii.5. Age can be derived from the attribute Birthdate. In this state of affairs, Birthdate is called a stored attribute,which is physically saved to the database.

Blue rectangle with the word EMPLOYEE, and connected by a line to five different yellow ovals with the words: Age, Name, Address, Birthdate, Salary. — Figure 8.v. Example of a derived attribute.

Keys

An important constraint on an entity is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.

Types of Keys

In that location are several types of keys. These are described below.

Candidate cardinal

A candidate central is a elementary or composite central that is unique and minimal. It is unique considering no 2 rows in a table may have the aforementioned value at any fourth dimension. It is minimal considering every column is necessary in order to reach uniqueness.

From our COMPANY database case, if the entity is Employee(EID, First Proper name, Final Name, SIN, Accost, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:

EID, SIN
First Proper name and Last Name – bold at that place is no one else in the company with the same name
Last Name and DepartmentID – assuming ii people with the same concluding name don't work in the same section

Blended key

A composite fundamental is composed of two or more than attributes, merely information technology must exist minimal.

Using the example from the candidate cardinal section, possible composite keys are:

First Proper name and Final Proper name – assuming at that place is no i else in the company with the same name
Last Name and Department ID – bold two people with the same last proper noun don't work in the same department

Primary key

The principal fundamental is a candidate cardinal that is selected by the database designer to be used as an identifying mechanism for the whole entity set. Information technology must uniquely identify tuples in a table and not be nada. The main key is indicated in the ER model by underlining the aspect.

A candidate key is selected by the designer to uniquely place tuples in a table. It must non be null.
A key is called by the database designer to be used as an identifying mechanism for the whole entity set. This is referred to as the principal cardinal. This cardinal is indicated by underlining the attribute in the ER model.

In the following case, EID is the primary key:

Employee( EID , First Proper name, Terminal Proper name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Secondary key

A secondary key is an aspect used strictly for retrieval purposes (tin can be composite), for example: Phone and Last Proper name.

Alternate key

Alternate keys are all candidate keys not called every bit the main key.

Foreign key

A foreign fundamental (FK) is an attribute in a table that references the primary fundamental in some other table OR it tin can exist null. Both foreign and primary keys must be of the same data type.

In the COMPANY database example below, DepartmentID is the strange key:

Employee(EID, Get-go Proper name, Last Proper noun, SIN, Address, Telephone, BirthDate, Bacon, DepartmentID)

Nulls

A null is a special symbol, independent of data blazon, which means either unknown or inapplicable. It does not mean zero or bare. Features of null include:

No data entry
Not permitted in the master key
Should be avoided in other attributes
Tin correspond
- An unknown attribute value
- A known, merely missing, attribute value
- A "not applicable" condition
Can create issues when functions such every bit COUNT, AVERAGE and SUM are used
Can create logical problems when relational tables are linked

NOTE: The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is zilch (except functions that ignore nulls).

Example of how null can be used

Use the Salary tabular array (Salary_tbl) in Effigy 8.half-dozen to follow an example of how null can be used.

SalaryTable-300x132 — Effigy viii.half-dozen. Salary tabular array for null case, by A. Watt.

To begin, find all employees (emp#) in Sales (under the jobName column) whose bacon plus commission are greater than 30,000.

SELECT emp# FROM Salary_tbl
WHERE jobName = Sales AND
(committee + salary) > 30,000 –> E10 and E12

This event does not include E13 because of the null value in the commission column. To ensure that the row with the zip value is included, we need to expect at the individual fields. By adding commission and salary for employee E13, the result volition be a null value. The solution is shown below.

SELECT emp# FROM Salary_tbl
WHERE jobName = Sales AND
(commission > 30000 OR
salary > 30000 OR
(commission + bacon) > xxx,000 –>E10 and E12 and E13

Relationships

Relationships are the glue that holds the tables together. They are used to connect related information between tables.

Relationship strength is based on how the main key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary cardinal of the related entity does not contain a primary central component of the parent entity. Visitor database examples include:

Customer( CustID , CustName)
Lodge( OrderID , CustID, Engagement)

A strong, or identifying, relationship exists when the master key of the related entity contains the main key component of the parent entity. Examples include:

Course( CrsCode , DeptCode, Description)
Grade( CrsCode, Section , ClassTime…)

Types of Relationships

Below are descriptions of the diverse types of relationships.

Ane to many (1:One thousand) relationship

A 1 to many (ane:M) relationship should be the norm in whatever relational database design and is establish in all relational database environments. For example, ane department has many employees. Figure viii.7 shows the relationship of one of these employees to the department.

A light blue diamond in the middle connected on either side to a blue rectangle. The rectangle on the left says EMPLOYEE and is connected with a line to five yellow ovals with the words Birthdate, Name, Address, Salary, EID. The diamond is also connected to a blue rectangle on its right with the word DEPARTMENT and that is connected with lines to three yellow ovals with the words Name, Office, DeptID. — Effigy 8.7. Instance of a 1 to many relationship.

I to ane (1:1) relationship

A i to one (1:one) relationship is the relationship of 1 entity to merely one other entity, and vice versa. It should exist rare in any relational database design. In fact, it could indicate that two entities actually vest in the same table.

An example from the COMPANY database is one employee is associated with 1 spouse, and one spouse is associated with i employee.

Many to many (M:N) relationships

For a many to many relationship, consider the post-obit points:

Information technology cannot exist implemented as such in the relational model.
It tin be changed into 2 1:M relationships.
It tin be implemented by breaking upwardly to produce a set of one:M relationships.
Information technology involves the implementation of a composite entity.
Creates two or more ane:Yard relationships.
The composite entity table must incorporate at least the primary keys of the original tables.
The linking table contains multiple occurrences of the strange key values.
Additional attributes may be assigned every bit needed.
It tin can avoid problems inherent in an 1000:N relationship past creating a composite entity or bridge entity. For example, an employee can piece of work on many projects OR a project tin have many employees working on it, depending on the business rules. Or, a educatee tin can have many classes and a class can hold many students.

Effigy 8.8 shows some other some other aspect of the M:N relationship where an employee has different start dates for different projects. Therefore, nosotros need a JOIN table that contains the EID, Lawmaking and StartDate.

Figure 8.eight. Example where employee has different start dates for different projects.

Example of mapping an M:Due north binary relationship type

For each M:N binary human relationship, identify two relations.
A and B represent ii entity types participating in R.
Create a new relation S to correspond R.
S needs to contain the PKs of A and B. These together tin be the PK in the South table OR these together with another uncomplicated aspect in the new table R can be the PK.
The combination of the primary keys (A and B) will make the primary cardinal of S.

Unary relationship (recursive)

A unary human relationship, besides called recursive, is ane in which a relationship exists between occurrences of the aforementioned entity prepare. In this relationship, the main and foreign keys are the same, but they represent 2 entities with different roles. Run into Figure 8.ix for an example.

For some entities in a unary relationship, a separate column can be created that refers to the primary fundamental of the same entity set.

Figure 8.nine. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a human relationship blazon that involves many to many relationships between three tables.

Refer to Effigy 8.ten for an example of mapping a ternary relationship blazon. Annotationn-ary means multiple tables in a relationship. (Recall, N = many.)

For each n-ary (> 2) human relationship, create a new relation to represent the human relationship.
The primary primal of the new relation is a combination of the main keys of the participating entities that agree the North (many) side.
In well-nigh cases of an n-ary relationship, all the participating entities hold a many side.

Ternary-Mapping-Relationships-300x197 — Effigy 8.10. Case of a ternary relationship.

alternate cardinal: all candidate keys not called as the primary keycandidate key: a unproblematic or composite central that is unique (no two rows in a table may take the same value) and minimal (every column is necessary)

feature entities: entities that provide more information about another table

composite attributes: attributes that consist of a hierarchy of attributes

blended primal: composed of 2 or more attributes, merely it must be minimal

dependent entities: these entities depend on other tables for their meaning

derived attributes: attributes that contain values calculated from other attributes

derived entities: run into dependent entities

EID: employee identification (ID)

entity: a thing or object in the real earth with an independent being that can be differentiated from other objects

entity relationship (ER) data model: too called an ER schema, are represented by ER diagrams. These are well suited to data modelling for use with databases.

entity human relationship schema : run across entity relationship information model

entity set:a collection of entities of an entity type at a point of time

entity blazon: a drove of similar entities

strange key (FK): an attribute in a table that references the master key in another table OR information technology can be null

contained entity: as the building blocks of a database, these entities are what other tables are based on

kernel: encounter independent entity

central: an attribute or group of attributes whose values tin can exist used to uniquely identify an individual entity in an entity set

multivalued attributes: attributes that have a set of values for each entity

due north-ary: multiple tables in a relationship

null: a special symbol, independent of data type, which means either unknown or inapplicable; information technology does not mean nix or blank

recursive relationship: come across unary relationship

relationships: the associations or interactions between entities; used to connect related information between tables

human relationship strength: based on how the primary primal of a related entity is defined

secondary key an attribute used strictly for retrieval purposes

simple attributes: drawn from the diminutive value domains

SIN: social insurance number

unmarried-valued attributes: see elementary attributes

stored attribute: saved physically to the database

ternary relationship: a relationship blazon that involves many to many relationships between three tables.

unary relationship: 1 in which a relationship exists between occurrences of the same entity set.

Attribution

This chapter of Database Design (including images, except as otherwisse noted) is a derivative copy of Data Modeling Using Entity-Human relationship Model by Nguyen Kim Anh licensed underCreative Commons Attribution License 3.0 license

The post-obit material was written past Adrienne Watt:

Nulls department and example
Key Terms
Exercises

Source: https://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/

Posted by: poteatprody2002.blogspot.com