The entity relationship (ER) data model has existed because that over 35 years. It is fine suited to data modelling for use through databases due to the fact that it is fairly abstract and also is simple to discuss and explain. ER models are readily translated to relations. ER models, likewise called period schema, are stood for by ER diagrams.

You are watching: An erm is dependent on the database type.

ER modelling is based on two concepts:

Entities, defined as tables that hold details information (data)Relationships, defined as the associations or interactions in between entities

Here is an instance of how these two principles might be merged in an er data model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity).

For the remainder of this chapter, we will use a sample database dubbed the company database to highlight the concepts of the ER model. This database has information around employees, departments and projects. Vital points to keep in mind include:

There are numerous departments in the company. Every department has a distinct identification, a name, ar of the office and also a particular employee that manages the department.A room controls a number of projects, every of which has a distinctive name, a distinctive number and also a budget.Each employee has a name, identification number, address, salary and also birthdate. One employee is assigned to one department yet can sign up with in several projects. We must record the start date of the employee in each project. We likewise need to recognize the direct supervisor of every employee.We want to keep track the the dependents for each employee. Every dependent has a name, birthdate and also relationship with the employee.

Entity, Entity collection and reality Type

An entity is an item in the real world with an independent visibility that can be distinguished from various other objects. One entity can be

An object v physical existence (e.g., a lecturer, a student, a car)An thing with conceptual existence (e.g., a course, a job, a position)

Entities deserve to be classified based on their strength. An entity is considered weak if its tables are presence dependent.

That is, it can not exist there is no a connection with another entityIts primary key is derived from the primary an essential of the parental entityThe Spouse table, in the firm database, is a weak entity due to the fact that its primary vital is dependency on the Employee table. Without a equivalent employee record, the spouse record would not exist.

An reality is considered strong if it can exist apart from all of its related entities.

Kernels are strong entities.A table there is no a foreign crucial or a table that includes a foreign crucial that can save on computer nulls is a solid entity

Another hatchet to recognize is entity type which defines a collection of comparable entities.

An entity set is a repertoire of entities of one entity kind at a details point of time. In one entity relationship diagram (ERD), an entity form is represented by a surname in a box. For example, in number 8.1, the entity kind is EMPLOYEE.

*
Figure 8.1. ERD through entity type EMPLOYEE.

Existence dependency

An entity’s visibility is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory international key (i.e., a foreign key attribute that cannot it is in null). Because that example, in the company database, a Spouse reality is visibility -dependent on the Employee entity.

Kinds of Entities

You should also be acquainted with different kinds the entities consisting of independent entities, dependent entities and also characteristic entities. These are described below.

Independent entities

Independent entities, additionally referred to together kernels, space the backbone the the database. Lock are what other tables room based on. Kernels have the adhering to characteristics:

They space the structure blocks the a database.The primary key may be basic or composite.The primary vital is no a foreign key.They perform not count on one more entity for their existence.

If we refer back to our company database, instances of an independent entity include the client table, Employee table or Product table.

Dependent entities

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

Dependent reality are supplied to attach two kernels together.They are said to be visibility dependent on 2 or much more tables.Many to plenty of relationships become associative tables v at least two international keys.They may contain other attributes.The foreign key identifies each associated table.There are three alternatives for the major key:Use a composite of international keys of connected tables if uniqueUse a composite of foreign keys and a qualifying columnCreate a new simple major key

Characteristic entities

Characteristic entities provide more information around another table. These entities have actually the following characteristics:

They represent multivalued attributes.They describe other entities.They commonly have a one to countless relationship.The foreign key is supplied to further identify the identified table.Options for primary vital are together follows:Use a composite the foreign an essential plus a qualifying columnCreate a new simple major key. In the company database, these could include:Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.EmployeePhone (EID, Phone) – EID is component of a composite main key. Here, EID is additionally a international key.

Attributes

Each reality is explained by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary).

Each attribute has actually a name, and also is linked with an entity and also a domain of legitimate values. However, the information about attribute domain is not presented on the ERD.

In the entity relationship diagram, displayed in number 8.2, each attribute is represented by one oval through a name inside.

*
Figure 8.2. How features are stood for in one ERD.

Types the Attributes

There are a few types of features you must be familiar with. Several of these room to it is in left as is, but some should be readjusted to facilitate representation in the relational model. This an initial section will discuss the varieties of attributes. Later on we will comment on fixing the characteristics to to the right correctly right into the relational model.

Simple attributes

Simple attributes are those attracted from the atomic value domains; lock are also called single-valued attributes. In the firm database, an example of this would certainly be: Name = John ; age = 23

Composite attributes

Composite attributes room those the consist that a power structure of attributes. Using our database example, and shown in figure 8.3, deal with may consist of Number, Street and also Suburb. For this reason this would certainly be created as → attend to = 59 + ‘Meek Street’ + ‘Kingsford’

*
Figure 8.3. An instance of composite attributes.

Multivalued attributes

Multivalued attributes are qualities that have actually a set of worths for each entity. An instance of a multivalued attribute from the agency database, as checked out in number 8.4, space the levels of one employee: BSc, MIT, PhD.

*
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are qualities that contain values calculated from other attributes. An example of this can be watched in number 8.5. Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database.

*
Figure 8.5. Example of a acquired attribute.

Keys

An essential constraint on an entity is the key. The key is one attribute or a team of features whose values can be supplied to uniquely determine an individual reality in an reality set.

Types that Keys

There space several species of keys. This are defined below.

Candidate key

A candidate key is a an easy or composite key that is unique and also minimal. That is unique due to the fact that no two rows in a table may have the very same value at any type of time. The is minimal since every tower is necessary in stimulate to attain uniqueness.

From our agency database example, if the reality is Employee(EID, very first Name, critical Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), feasible candidate tricks are:

EID, SINFirst Name and Last name – assuming over there is nobody else in the agency with the very same nameLast Name and also DepartmentID – assuming two world with the exact same last surname don’t work in the same department

Composite key

A composite key is created of two or more attributes, yet it should be minimal.

Using the example from the candidate an essential section, feasible composite keys are:

First Name and Last surname – assuming there is no one else in the agency with the exact same nameLast Name and Department i would – assuming two civilization with the very same last name don’t work-related in the very same department

Primary key

The primary an essential is a candidate an essential that is selected through the database designer to be used as an identifying device for the whole entity set. It should uniquely recognize tuples in a table and not it is in null. The primary crucial is shown in the ER model by underlining the attribute.

A candidate vital is selected by the designer to uniquely recognize tuples in a table. It should not be null.A an essential is liked by the database designer come be supplied as one identifying system for the whole entity set. This is referred to as the major key. This vital is shown by underlining the attribute in the ER model.

In the following example, EID is the major key:

Employee(EID, an initial Name, critical Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Secondary key

A secondary key is one attribute offered strictly for retrieval objectives (can be composite), because that example: Phone and also Last Name.

Alternate key

Alternate keys are all candidate tricks not preferred as the major key.

Foreign key

A foreign crucial (FK) is an attribute in a table that recommendations the primary crucial in another table OR it have the right to be null. Both foreign and primary keys must it is in of the same data type.

In the company database example below, DepartmentID is the foreign key:

Employee(EID, an initial Name, last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Nulls

A null is a one-of-a-kind symbol, independent of data type, which method either unknown or inapplicable. It does not typical zero or blank. Features of null include:

No data entryNot permitted in the main keyShould be avoided in various other attributesCan representAn unknown attribute valueA known, however missing, attribute valueA “not applicable” conditionCan develop problems when features such as COUNT, AVERAGE and SUM room usedCan develop logical problems when relational tables are linked

NOTE: The an outcome of a comparison procedure is null once either dispute is null. The an outcome of one arithmetic procedure is null as soon as either discussion is null (except functions that ignore nulls).

Example of exactly how null deserve to be used

Use the value table (Salary_tbl) in number 8.6 come follow an instance of just how null have the right to be used.

*
Figure 8.6. Value table because that null example, by A. Watt.

To begin, uncover all employees (emp#) in Sales (under the jobName column) who salary plus commission are greater than 30,000.

SELECT emp# from Salary_tblWHERE jobName = Sales AND(commission + salary) > 30,000 –> E10 and E12

This an outcome does not include E13 since of the null value in the commission column. Come ensure the the row with the null value is included, we need to look in ~ the individual fields. By including commission and also salary for employee E13, the an outcome will it is in a null value. The equipment is presented below.

SELECT emp# indigenous Salary_tblWHERE jobName = Sales AND(commission > 30000 ORsalary > 30000 OR(commission + salary) > 30,000 –>E10 and E12 and also E13

Relationships

Relationships space the glue the holds the tables together. Castle are supplied to attach related information in between tables.

Relationship strength is based upon how the primary key of a associated entity is defined. A weak, or non-identifying, relationship exists if the primary vital of the related entity does no contain a primary crucial component the the parent entity. Company database instances include:

Customer(CustID, CustName)Order(OrderID, CustID, Date)

A strong, or identifying, partnership exists when the primary key of the connected entity consists of the primary an essential component the the parent entity. Instances include:

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

Types of Relationships

Below are descriptions of the various types of relationships.

One to many (1:M) relationship

A one to many (1:M) relationship have to be the norm in any kind of relational database design and also is uncovered in all relational database environments. Because that example, one room has countless employees. Number 8.7 mirrors the connection of one of these employee to the department.

*
Figure 8.7. Example of a one to numerous relationship.

One to one (1:1) relationship

A one come one (1:1) relationship is the partnership of one reality to only one various other entity, and also vice versa. It have to be rare in any kind of relational database design. In fact, it can indicate that 2 entities in reality belong in the exact same table.

An instance from the agency database is one employee is linked with one spouse, and one spouse is linked with one employee.

Many to many (M:N) relationships

For a numerous to countless relationship, consider the adhering to points:

It cannot be implemented as such in the relational model.It can be readjusted into 2 1:M relationships.It deserve to be enforced by break up to create a collection of 1:M relationships.It requires the implementation of a composite entity.Creates 2 or an ext 1:M relationships.The composite entity table need to contain at least the primary tricks of the initial tables.The linking table has multiple incidents of the foreign crucial values.Additional qualities may it is in assigned together needed.It have the right to avoid problems inherent in an M:N connection by producing a composite entity or leg entity. For example, an employee have the right to work on many projects OR a project have the right to have many employees functioning on it, depending upon the organization rules. Or, a student have the right to have plenty of classes and a class can hold many students.

Figure 8.8 shows an additional another element of the M:N partnership where an employee has various start days for different projects. Therefore, we need a sign up with table that contains the EID, Code and StartDate.

*
Figure 8.8. Example where employee has various start days for various projects.

Example of mapping an M:N binary relationship type

For each M:N binary relationship, identify two relations.A and B stand for two entity types participating in R.Create a new relation S to stand for R.S demands to save on computer the PKs the A and B. These together have the right to be the PK in the S table OR these together with another an easy attribute in the brand-new table R deserve to be the PK. The combination of the primary secrets (A and also B) will make the primary an essential of S.

Unary relationship (recursive)

A unary relationship, also called recursive, is one in i m sorry a partnership exists in between occurrences of the same entity set. In this relationship, the primary and foreign tricks are the same, however they stand for two reality with different roles. See figure 8.9 for an example.

For some entities in a unary relationship, a separate column deserve to be developed that describes the primary crucial of the very same entity set.

*
Figure 8.9. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a relationship kind that involves numerous to countless relationships between three tables. 

Refer to number 8.10 for an instance of mapping a ternary connection type. Note n-ary method multiple tables in a relationship. (Remember, N = many.)

For every n-ary (> 2) relationship, develop a brand-new relation to represent the relationship.The primary an essential of the brand-new relation is a mix of the primary keys of the participating reality that organize the N (many) side.In most instances of one n-ary relationship, every the participating entities hold a many side.

See more: Red Hot Chili Peppers Co. Had The Following Activity In Its Most Recent Year Of Operations.

*
Figure 8.10. Instance of a ternary relationship.