The entity relationship (ER) data model has actually existed for over 35 years. It is well suited to information modelling for use with databases bereason it is reasonably abstract and also is easy to talk about and describe. ER models are readily analyzed to relations. ER models, also called an ER schema, are represented by ER diagrams.

You are watching: What term best describes the state entity?

ER modelling is based on two concepts:

Entities, identified as tables that host certain indevelopment (data)Relationships, identified as the associations or interactions in between entities

Here is an example of just how these two principles could be linked in an ER information model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity).

For the rest of this chapter, we will usage a sample database dubbed the COMPANY database to illustrate the ideas of the ER model. This database contains information about employees, departments and also tasks. Important points to note include:

Tbelow are numerous departments in the firm. Each department has actually a distinctive identification, a name, location of the office and a certain employee who manperiods the department.A department controls a number of projects, each of which has a unique name, a unique number and also a budobtain.Each employee has a name, identification number, attend to, salary and birthday. An employee is assigned to one department yet have the right to sign up with in numerous jobs. We must document the begin date of the employee in each project. We also should recognize the direct supervisor of each employee.We desire to save track of the dependents for each employee. Each dependent has a name, birthday and also relationship via the employee.

Entity, Entity Set and Entity Type

An entity is an object in the actual human being with an independent existence that can be distinguished from various other objects. An entity could be

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

Entities deserve to be classified based upon their strength. An entity is taken into consideration weak if its tables are existence dependent.

That is, it cannot exist without a relationship with another entityIts main crucial is derived from the main vital of the parent entityThe Spousage table, in the COMPANY database, is a weak entity because its main vital is dependent on the Employee table. Without a equivalent employee record, the spousage record would certainly not exist.

An entity is considered strong if it deserve to exist acomponent from every one of its connected entities.

Kernels are strong entities.A table without a international essential or a table that has a international crucial that have the right to contain nulls is a solid entity

Anvarious other term to understand is entity type which specifies a repertoire of comparable entities.

An entity set is a arsenal of entities of an entity form at a details suggest of time. In an entity relationship diagram (ERD), an entity type is stood for by a name in a box. For example, in Figure 8.1, the entity kind is EMPLOYEE.

*
Figure 8.1. ERD via entity form EMPLOYEE.

Existence dependency

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

Kinds of Entities

You should also be familiar with various kinds of entities including independent entities, dependent entities and characteristic entities. These are described listed below.

Independent entities

Independent entities, likewise described as kernels, are the backbone of the database. They are what various other tables are based on. Kernels have actually the adhering to characteristics:

They are the structure blocks of a database.The major key may be easy or compowebsite.The major essential is not a foreign key.They carry out not depfinish on another entity for their presence.

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

Dependent entities

Dependent entities, also described as obtained entities, depfinish on other tables for their meaning. These entities have actually the adhering to characteristics:

Dependent entities are provided to affix 2 kernels together.They are said to be presence dependent on 2 or even more tables.Many type of to many relationships become associative tables through at least two foreign secrets.They might contain other attributes.The international key identifies each linked table.Tbelow are three options for the major key:Use a composite of foreign tricks of linked tables if uniqueUse a compowebsite of international secrets and also a qualifying columnCreate a new straightforward major key

Characteristic entities

Characteristic entities administer even more indevelopment about one more table. These entities have the adhering to characteristics:

They recurrent multivalued features.They explain various other entities.They frequently have actually a one to many type of partnership.The international vital is offered to additionally determine the identified table.Options for primary vital are as follows:Use a composite of foreign vital plus a qualifying columnCreate a brand-new basic main crucial. In the COMPANY database, these can include:Employee (EID, Name, Address, Age, Salary) – EID is the basic main crucial.EmployeePhone (EID, Phone) – EID is part of a compowebsite major crucial. Here, EID is additionally a international key.

Attributes

Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthday (Age), Salary).

Each attribute has a name, and also is linked through an entity and also a domain of legal worths. However before, the information about attribute domajor is not presented on the ERD.

In the entity partnership diagram, displayed in Figure 8.2, each attribute is stood for by an oval through a name inside.

*
Figure 8.2. How characteristics are stood for in an ERD.

Types of Attributes

Tbelow are a few kinds of characteristics you have to be acquainted with. Some of these are to be left as is, but some have to be readjusted to facilitate depiction in the relational design. This initially section will certainly comment on the kinds of qualities. After that we will discuss addressing the characteristics to fit properly into the relational version.

Simple attributes

Simple attributes are those attracted from the atomic value domains; they are also referred to as single-valued attributes. In the COMPANY database, an example of this would be: Name = John ; Era = 23

Compowebsite attributes

Compowebsite attributes are those that consist of a pecking order of characteristics. Using our database example, and displayed in Figure 8.3, Address might consist of Number, Street and Suburb. So this would certainly be composed as → Address = 59 + ‘Meek Street’ + ‘Kingsford’

*
Figure 8.3. An instance of compowebsite attributes.

Multivalued attributes

Multivalued attributes are characteristics that have actually a set of values for each entity. An instance of a multivalued attribute from the COMPANY database, as viewed in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.

*
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are qualities that contain values calculated from various other characteristics. An example of this can be watched in Figure 8.5. Age deserve to be derived from the attribute Birthday. In this case, Birthdate is referred to as a stored attribute, which is physically saved to the database.

*
Figure 8.5. Example of a acquired attribute.

Keys

An necessary constraint on an entity is the essential. The key is an attribute or a group of characteristics whose worths can be provided to uniquely recognize an individual entity in an entity set.

Types of Keys

Tbelow are several forms of secrets. These are described below.

Candidate key

A candidate key is a straightforward or composite crucial that is unique and also minimal. It is unique bereason no 2 rows in a table may have the very same worth at any type of time. It is minimal because eincredibly column is crucial in order to achieve uniqueness.

From our COMPANY database instance, if the entity is Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), feasible candidate tricks are:

EID, SINFirst Name and Last Name – assuming tbelow is no one else in the agency through the same nameLast Name and also DepartmentID – assuming 2 people via the very same last name don’t occupational in the same department

Composite key

A compowebsite key is composed of 2 or even more features, yet it must be minimal.

Using the example from the candidate vital area, feasible composite keys are:

First Name and Last Name – assuming tbelow is no one else in the company via the exact same nameLast Name and also Department ID – assuming two human being via the exact same last name don’t work in the very same department

Primary key

The primary crucial is a candiday vital that is schosen by the database designer to be supplied as an identifying system for the whole entity set. It have to uniquely determine tuples in a table and also not be null. The primary key is shown in the ER design by underlining the attribute.

A candidate vital is selected by the designer to uniquely identify tuples in a table. It should not be null.A vital is chosen by the database designer to be provided as an identifying mechanism for the whole entity collection. This is referred to as the primary crucial. This crucial is suggested by underlining the attribute in the ER model.

In the adhering to example, EID is the main key:

Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Secondary key

A second key is an attribute offered strictly for retrieval objectives (deserve to be composite), for example: Phone and also Last Name.

Alternate key

Alternative keys are all candidate tricks not liked as the primary crucial.

Fopower key

A foreign key (FK) is an attribute in a table that references the primary vital in an additional table OR it deserve to be null. Both international and also major keys should be of the very same data kind.

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

Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Nulls

A null is a unique symbol, independent of data kind, which means either unknown or inapplicable. It does not suppose zero or empty. Features of null include:

No data entryNot permitted in the major keyShould be avoided in other attributesCan representAn unrecognized attribute valueA recognized, however missing, attribute valueA “not applicable” conditionCan create problems once features such as COUNT, AVERAGE and also SUM are usedCan create logical problems once relational tables are linked

NOTE: The result of a comparikid procedure is null when either debate is null. The outcome of an arithmetic procedure is null when either debate is null (other than features that disregard nulls).

Example of how null deserve to be used

Use the Salary table (Salary_tbl) in Figure 8.6 to follow an instance of exactly how null deserve to be supplied.

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

To start, discover all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000.

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

This outcome does not encompass E13 bereason of the null value in the commission column. To encertain that the row through the null worth is had, we must look at the individual fields. By including commission and also salary for employee E13, the outcome will certainly be a null worth. The solution is displayed listed 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 are the glue that holds the tables together. They are offered to affix related information between tables.

Relationship strength is based on exactly how the major vital of a connected entity is identified. A weak, or non-identifying, relationship exists if the primary crucial of the connected entity does not contain a major key component of the parent entity. Company kind of database examples include:

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

A solid, or identifying, connection exists when the primary vital of the related entity contains the primary key component of the parent entity. Instances include:

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

Types of Relationships

Below are descriptions of the assorted forms of relationships.

One to many type of (1:M) relationship

A one to many (1:M) relationship need to be the norm in any kind of relational database architecture and also is discovered in all relational database environments. For example, one department has many kind of employees. Figure 8.7 mirrors the relationship of one of these employees to the department.

*
Figure 8.7. Example of a one to many connection.

One to one (1:1) relationship

A one to one (1:1) connection is the partnership of one entity to only one other entity, and vice versa. It must be rare in any relational database style. In truth, it could show that two entities actually belengthy in the exact same table.

An example from the COMPANY database is one employee is associated with one spousage, and also one spousage is associated through one employee.

Many to many (M:N) relationships

For a many to many relationship, take into consideration the adhering to points:

It cannot be enforced as such in the relational design.It can be adjusted into two 1:M relationships.It have the right to be enforced by breaking up to create a set of 1:M relationships.It requires the implementation of a composite entity.Creates two or more 1:M relationships.The compowebsite entity table need to contain at least the main secrets of the original tables.The linking table contains multiple events of the foreign essential values.Further attributes may be assigned as required.It deserve to avoid difficulties natural in an M:N partnership by creating a composite entity or bridge entity. For example, an employee have the right to work-related on many type of tasks OR a task have the right to have actually many employees functioning on it, depending upon the business rules. Or, a student deserve to have actually many type of classes and also a course have the right to hold many students.

Figure 8.8 shows one more an additional aspect of the M:N connection wright here an employee has actually different begin days for various jobs. Thus, we require a JOIN table that consists of the EID, Code and also StartDate.

*
Figure 8.8. Example wbelow employee has actually various start days for different tasks.

Example of mapping an M:N binary partnership type

For each M:N binary partnership, recognize two connections.A and also B represent two entity types participating in R.Create a new relation S to represent R.S requirements to contain the PKs of A and also B. These together deserve to be the PK in the S table OR these along with one more straightforward attribute in the brand-new table R can be the PK. The combination of the primary keys (A and also B) will certainly make the major vital of S.

Unary partnership (recursive)

A unary partnership, additionally referred to as recursive, is one in which a connection exists in between occurrences of the same entity set. In this partnership, the main and foreign tricks are the very same, yet they recurrent two entities with various duties. See Figure 8.9 for an example.

For some entities in a unary connection, a separate column can be developed that refers to the main essential of the exact same entity collection.

*
Figure 8.9. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a connection type that requires many kind of to many relationships between 3 tables. 

Refer to Figure 8.10 for an instance of mapping a ternary partnership type. Note n-ary suggests multiple tables in a relationship. (Remember, N = many kind of.)

For each n-ary (> 2) partnership, create a brand-new relation to represent the relationship.The major essential of the brand-new relation is a combination of the main tricks of the participating entities that hold the N (many) side.In the majority of cases of an n-ary relationship, all the participating entities hold a many side.

See more: Who Died In The Westchester Incident " In 'Logan', Westchester Incident

*
Figure 8.10. Example of a ternary connection.