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

You are watching: If an entity can exist apart from one or more related entities, it is said to be ____-independent.

ER modelling is based upon two concepts:

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

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

For the rest of this chapter, us will usage a sample database dubbed the agency database to illustrate the principles of the ER model. This database includes information around employees, departments and projects. Crucial points to note include:

There are numerous departments in the company. Each department has actually a distinctive identification, a name, ar of the office and also a specific employee who manages the department.A room controls a number of projects, each of which has a distinct name, a distinctive number and a budget.Each employee has actually a name, identification number, address, salary and also birthdate. An employee is assigned come one department however can sign up with in numerous projects. We must record the start date of the employee in each project. We likewise need to recognize the straight supervisor of every employee.We desire to store track the the dependents for each employee. Each dependent has actually 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 one independent presence that can be distinguished from other objects. One entity can be

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

Entities have the right to be classified based on their strength. An entity is thought about weak if that is tables are presence dependent.

That is, it cannot exist without a relationship with another entityIts primary crucial is acquired from the primary an essential of the parent entityThe Spouse table, in the agency database, is a weak entity because its primary vital is dependent on the Employee table. There is no a equivalent employee record, the spouse record would no exist.

An reality is considered strong if it have the right to exist apart from every one of its associated entities.

Kernels are solid entities.A table there is no a foreign crucial or a table that has a foreign crucial that can contain nulls is a strong entity

Another ax to recognize is entity type which specifies a arsenal of comparable entities.

An entity set is a collection of reality of an entity kind at a details point that time. In an entity connection diagram (ERD), one entity form is represented by a surname in a box. Because that example, in number 8.1, the entity kind is EMPLOYEE.

*
Figure 8.1. ERD through entity kind EMPLOYEE.

Existence dependency

An entity’s existence is dependent on the presence of the connected entity. That is existence-dependent if it has actually a mandatory foreign key (i.e., a foreign key attribute that cannot it is in null). For example, in the agency database, a Spouse reality is visibility -dependent on the Employee entity.

Kinds that Entities

You should likewise be acquainted with various kinds the entities consisting of independent entities, dependency entities and also characteristic entities. These are defined below.

Independent entities

Independent entities, also referred to as kernels, room the backbone that the database. Castle are what other tables room based on. Kernels have the following characteristics:

They are the structure blocks that a database.The primary vital may be basic or composite.The primary crucial is no a foreign key.They execute not depend on another entity for their existence.

If us refer back to our firm database, instances of an independent entity include the customer table, Employee table or Product table.

Dependent entities

Dependent entities, likewise referred to together derived entities, depend on other tables for your meaning. These entities have the complying with characteristics:

Dependent reality are provided to attach two kernels together.They are said to be presence dependent on two or an ext tables.Many to many relationships become associative tables with at the very least two international keys.They may contain various other attributes.The foreign an essential 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 international keys and a qualifying columnCreate a new simple major key

Characteristic entities

Characteristic entities provide more information around another table. These entities have the adhering to characteristics:

They represent multivalued attributes.They describe other entities.They commonly have a one to numerous relationship.The foreign vital is used to additional identify the defined table.Options for primary crucial are as follows:Use a composite of foreign an essential plus a qualifying columnCreate a new simple main key. In the company database, these could include:Employee (EID, Name, Address, Age, Salary) – EID is the an easy primary key.EmployeePhone (EID, Phone) – EID is component of a composite main key. Here, EID is likewise a foreign key.

Attributes

Each entity is defined by a collection of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary).

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

In the entity relationship diagram, presented in number 8.2, every attribute is stood for by one oval through a surname inside.

*
Figure 8.2. How characteristics are represented in an ERD.

Types that Attributes

There space a few types of features you need to be familiar with. Several of these room to it is in left as is, however some have to be readjusted to facilitate representation in the relational model. This an initial section will discuss the varieties of attributes. After that we will discuss fixing the characteristics to right correctly right into the relational model.

Simple attributes

Simple attributes space those attracted from the atomic value domains; they are additionally called single-valued attributes. In the company database, an instance of this would be: Name = John ; period = 23

Composite attributes

Composite attributes room those the consist of a pecking order of attributes. Making use of our database example, and shown in figure 8.3, deal with may consist of Number, Street and also Suburb. So this would certainly be composed as → deal with = 59 + ‘Meek Street’ + ‘Kingsford’

*
Figure 8.3. An example of composite attributes.

Multivalued attributes

Multivalued attributes are attributes that have actually a collection of worths for each entity. An instance of a multivalued attribute from the company database, as checked out in number 8.4, space the degrees of an employee: BSc, MIT, PhD.

*
Figure 8.4. Instance of a multivalued attribute.

Derived attributes

Derived attributes are features that contain worths calculated from other attributes. An example of this can be checked out in figure 8.5. Period can be obtained from the attribute Birthdate. In this situation, Birthdate is dubbed a stored attribute, which is physically conserved to the database.

*
Figure 8.5. Instance of a derived attribute.

Keys

An crucial constraint on an entity is the key. The key is one attribute or a team of characteristics whose values can be used to uniquely identify an individual entity in an reality set.

Types that Keys

There room several varieties of keys. These are defined below.

Candidate key

A candidate key is a an easy or composite an essential that is unique and minimal. The is unique since no 2 rows in a table may have actually the exact same value at any time. That is minimal since every obelisk is important in bespeak to obtain uniqueness.

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

EID, SINFirst Name and Last name – assuming there is no one else in the firm with the exact same nameLast Name and DepartmentID – assuming two world with the same last name don’t occupational in the exact same department

Composite key

A composite key is written of two or more attributes, yet it have to be minimal.

Using the instance from the candidate an essential section, possible composite keys are:

First Name and also Last surname – assuming over there is no one else in the firm with the same nameLast Name and also Department identifier – suspect two world with the exact same last name don’t work-related in the very same department

Primary key

The primary vital is a candidate an essential that is selected by the database designer come be offered as an identifying device for the totality entity set. It have to uniquely identify tuples in a table and not be null. The primary crucial is indicated in the ER model by underlining the attribute.

A candidate vital is selected by the designer to uniquely recognize tuples in a table. It have to not it is in null.A vital is liked by the database designer to be used as one identifying system for the entirety entity set. This is described as the primary key. This an essential is indicated by underlining the attribute in the ER model.

In the following example, EID is the primary key:

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

Secondary key

A secondary key is an attribute provided strictly because that retrieval functions (can it is in composite), for example: Phone and also Last Name.

Alternate key

Alternate keys are all candidate keys not liked as the main key.

Foreign key

A foreign an essential (FK) is an attribute in a table that references the primary vital in another table OR it can be null. Both foreign and primary keys must it is in of the same data type.

In the agency database example below, DepartmentID is the international key:

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

Nulls

A null is a distinct symbol, independent of data type, which way either unknown or inapplicable. It does not median zero or blank. Functions of null include:

No data entryNot allowed in the primary keyShould be avoided in various other attributesCan representAn unknown attribute valueA known, but missing, attribute valueA “not applicable” conditionCan create problems when functions such as COUNT, AVERAGE and SUM are usedCan produce logical troubles when relational tables are linked

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

Example of just how null have the right to be used

Use the salary table (Salary_tbl) in figure 8.6 come follow an instance of just how null can be used.

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

To begin, find all employee (emp#) in Sales (under the jobName column) whose salary plus commission are better than 30,000.

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

This result does not include E13 due to the fact that of the null value in the board of directors column. Come ensure that the row through the null worth is included, we must look at the individual fields. By including commission and also salary because that employee E13, the an outcome will be a null value. The systems is displayed below.

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

Relationships

Relationships room the glue that holds the tables together. They are offered to affix related information between tables.

Relationship strength is based on how the primary an essential of a associated entity is defined. A weak, or non-identifying, connection exists if the primary an essential of the connected entity does no contain a primary crucial component that the parental entity. Firm database instances include:

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

A strong, or identifying, relationship exists once the primary an essential of the associated entity contains the primary an essential component the the parental entity. Examples include:

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

Types that Relationships

Below are descriptions that the various species of relationships.

One to countless (1:M) relationship

A one to countless (1:M) relationship need to be the norm in any relational database design and is found in every relational database environments. Because that example, one department has plenty of employees. Number 8.7 mirrors the connection of among these employees to the department.

*
Figure 8.7. Instance of a one to many relationship.

One come one (1:1) relationship

A one to one (1:1) partnership is the connection of one reality to only one other entity, and also vice versa. It have to be rarely in any relational database design. In fact, it could indicate that 2 entities in reality belong in the very same table.

An instance from the firm database is one employee is associated with one spouse, and also one spouse is connected with one employee.

Many to many (M:N) relationships

For a numerous to plenty of relationship, take into consideration the adhering to points:

It cannot be implemented because of this in the relational model.It have the right to be changed into two 1:M relationships.It can be enforced by breaking up to produce a set of 1:M relationships.It requires the implementation the a composite entity.Creates 2 or more 1:M relationships.The composite reality table need to contain at the very least the primary tricks of the original tables.The linking table includes multiple events of the foreign an essential values.Additional attributes may it is in assigned together needed.It have the right to avoid problems inherent in an M:N connection by creating a composite entity or leg entity. For example, an employee can work on many projects OR a project can have many employees functioning on it, depending on the business rules. Or, a student have the right to have plenty of classes and also a class can hold countless students.

Figure 8.8 shows one more another aspect of the M:N relationship where one employee has different start days for various projects. Therefore, we need a join table that has the EID, Code and StartDate.

*
Figure 8.8. Instance where employee has different start dates for various projects.

Example that mapping an M:N binary relationship type

For each M:N binary relationship, determine two relations.A and also B stand for two entity species participating in R.Create a brand-new relation S to represent R.S demands to contain the PKs that A and B. These together can be the PK in the S table OR these along with another basic attribute in the new table R deserve to be the PK. The mix of the primary keys (A and B) will make the primary key of S.

Unary relationship (recursive)

A unary relationship, also referred to as recursive, is one in i beg your pardon a partnership exists in between occurrences the the exact same entity set. In this relationship, the primary and foreign secrets are the same, however they stand for two reality with different roles. See figure 8.9 for an example.

For part entities in a unary relationship, a separate column have the right to be produced that describes the primary key 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 plenty of to plenty of 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 each n-ary (> 2) relationship, produce a brand-new relation to represent the relationship.The primary an essential of the brand-new relation is a mix of the primary secrets of the participating reality that hold the N (many) side.In most cases of one n-ary relationship, every the participating entities hold a many side.

See more: Bojack Horseman Love And/Or Marriage (Tv Episode 2016), Bojack Horseman

*
Figure 8.10. Example of a ternary relationship.