The entity relationship (ER) data model has actually existed for over 35 years. It is well suited to information modelling for usage through databases bereason it is reasonably abstract and is straightforward to talk about and also describe. ER models are readily interpreted to relations. ER models, also referred to as an ER schema, are represented by ER diagrams.

You are watching: What kind of attribute can be calculated using the value of another attribute?

ER modelling is based upon 2 concepts:

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

Here is an example of exactly how these 2 concepts can be combined in an ER data model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity).

For the remainder of this chapter, we will certainly use a sample database referred to as the COMPANY database to illustrate the ideas of the ER design. This database includes indevelopment about employees, departments and also tasks. Important points to note include:

There are a number of departments in the firm. Each department has actually a unique identification, a name, place of the office and also a certain employee who manperiods the department.A department controls a variety of tasks, each of which has a distinctive name, a distinctive number and a budgain.Each employee has a name, identification number, attend to, salary and birthday. An employee is assigned to one department however can join in several projects. We have to document the begin day of the employee in each task. We also must know the direct supervisor of each employee.We desire to keep track of the dependents for each employee. Each dependent has actually a name, birthday and connection via the employee.

Entity, Entity Set and Entity Type

An entity is a things in the actual world through an independent visibility that can be identified from various other objects. An entity could be

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

Entities have the right to be classified based upon their toughness. An entity is thought about weak if its tables are presence dependent.

That is, it cannot exist without a relationship through an additional entityIts primary key is acquired from the major essential of the parent entityThe Spouse table, in the COMPANY database, is a weak entity bereason its main essential is dependent on the Employee table. Without a matching employee document, the spousage record would certainly not exist.

An entity is thought about solid if it can exist apart from all of its associated entities.

Kernels are solid entities.A table without a international crucial or a table that consists of a international crucial that deserve to contain nulls is a strong entity

Anvarious other term to recognize is entity type which defines a arsenal of comparable entities.

An entity set is a repertoire of entities of an entity form at a particular suggest of time. In an entity partnership diagram (ERD), an entity form is represented by a name in a box. For example, in Figure 8.1, the entity kind is EMPLOYEE.

*
Figure 8.1. ERD with entity type 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 international key (i.e., a foreign crucial attribute that cannot be null). For instance, in the COMPANY database, a Spousage entity is existence -dependent on the Employee entity.

Kinds of Entities

You should likewise be familiar through different kinds of entities consisting of independent entities, dependent entities and characteristic entities. These are defined below.

Independent entities

Independent entities, likewise referred to as kernels, are the backbamong the database. They are what other tables are based on. Kernels have actually the following characteristics:

They are the building blocks of a database.The main crucial may be basic or compowebsite.The main essential is not a foreign vital.They do not depfinish on another entity for their visibility.

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

Dependent entities

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

Dependent entities are used to connect two kernels together.They are sassist to be existence dependent on 2 or more tables.Many kind of to many kind of relationships come to be associative tables through at leastern two international secrets.They might contain various other qualities.The foreign vital identifies each linked table.There are 3 choices for the main key:Use a compowebsite of international keys of associated tables if uniqueUse a composite of foreign keys and a qualifying columnCreate a brand-new straightforward primary key

Characteristic entities

Characteristic entities provide even more information around an additional table. These entities have the adhering to characteristics:

They recurrent multivalued attributes.They define various other entities.They generally have actually a one to many kind of relationship.The international key is offered to better identify the identified table.Options for primary crucial are as follows:Use a composite of international vital plus a qualifying columnCreate a brand-new basic major key. In the COMPANY database, these could include:Employee (EID, Name, Address, Era, Salary) – EID is the straightforward major vital.EmployeePhone (EID, Phone) – EID is part of a compowebsite main crucial. Here, EID is additionally a foreign essential.

Attributes

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

Each attribute has actually a name, and is linked via an entity and also a domain of legal values. However, the indevelopment about attribute domajor is not presented on the ERD.

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

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

Types of Attributes

Tright here are a few kinds of features you have to be familiar through. Several of these are to be left as is, but some need to be adjusted to facilitate depiction in the relational version. This first area will discuss the forms of characteristics. Afterwards we will certainly talk about addressing the qualities to fit correctly into the relational model.

Simple attributes

Simple attributes are those drawn from the atomic worth domains; they are also called single-valued attributes. In the COMPANY database, an example of this would certainly be: Name = John ; Age = 23

Compowebsite attributes

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

*
Figure 8.3. An instance of composite characteristics.

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 watched 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 features that contain values calculated from other attributes. An example of this have the right to be checked out in Figure 8.5. Era deserve to be acquired from the attribute Birthday. In this instance, Birthdate is referred to as a stored attribute, which is physically saved to the database.

*
Figure 8.5. Example of a obtained attribute.

Keys

An essential constraint on an entity is the key. The key is an attribute or a team of attributes whose values have the right to be provided to uniquely recognize an individual entity in an entity set.

Types of Keys

Tbelow are a number of types of secrets. These are described below.

Candidate key

A candidate key is a simple or composite vital that is distinctive and minimal. It is distinctive bereason no two rows in a table may have the exact same value at any type of time. It is minimal bereason every column is crucial in order to attain uniqueness.

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

EID, SINFirst Name and also Last Name – assuming tright here is no one else in the firm via the very same nameLast Name and also DepartmentID – assuming 2 civilization through the very same last name don’t work in the very same department

Compowebsite key

A composite key is composed of 2 or even more attributes, however it need to be minimal.

Using the instance from the candidate vital area, feasible composite secrets are:

First Name and Last Name – assuming tright here is no one else in the agency via the exact same nameLast Name and Department ID – assuming two world through the same last name don’t occupational in the very same department

Primary key

The major crucial is a candidate vital that is selected by the database designer to be provided as an identifying device for the whole entity set. It have to uniquely identify tuples in a table and not be null. The major key is suggested in the ER model by underlining the attribute.

A candiday essential is schosen by the designer to uniquely determine tuples in a table. It should not be null.A essential is preferred by the database designer to be provided as an identifying mechanism for the whole entity collection. This is referred to as the major crucial. This crucial is shown by underlining the attribute in the ER version.

In the complying with instance, 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 used strictly for retrieval purposes (have the right to be composite), for example: Phone and Last Name.

Alternating key

Alternative keys are all candiday secrets not preferred as the primary key.

Foreign key

A foreign key (FK) is an attribute in a table that referrals the major essential in an additional table OR it deserve to be null. Both international and primary keys must be of the very same data type.

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 special symbol, independent of data type, which suggests either unknown or inapplicable. It does not suppose zero or blank. Features of null include:

No information entryNot allowed in the main keyShould be avoided in various other attributesCan representAn unwell-known attribute valueA recognized, yet lacking, attribute valueA “not applicable” conditionCan produce troubles when attributes such as COUNT, AVERAGE and also SUM are usedCan produce logical troubles once relational tables are linked

NOTE: The result of a comparikid procedure is null when either dispute is null. The outcome of an arithmetic operation is null as soon as either dispute is null (except features that ignore nulls).

Example of just how null can be used

Use the Salary table (Salary_tbl) in Figure 8.6 to follow an example of just how null have the right to be used.

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

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

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

This result does not include E13 bereason of the null worth in the commission column. To encertain that the row through the null worth is included, we must look at the individual areas. By adding commission and also salary for employee E13, the outcome will 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 E12 and also E13

Relationships

Relationships are the glue that holds the tables together. They are supplied to affix connected indevelopment between tables.

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

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

A solid, or identifying, relationship exists when the major key of the related entity consists of the main essential component of the parent entity. Examples include:

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

Types of Relationships

Below are descriptions of the miscellaneous types of relationships.

One to many (1:M) relationship

A one to many type of (1:M) connection need to be the norm in any type of relational database architecture and is uncovered in all relational database atmospheres. For example, one department has many employees. Figure 8.7 shows the relationship of one of these employees to the department.

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

One to one (1:1) relationship

A one to one (1:1) partnership is the connection of one entity to only one various other entity, and vice versa. It have to be rare in any type of relational database style. In truth, it might indicate that 2 entities actually belengthy in the exact same table.

An instance from the COMPANY database is one employee is associated via one spouse, and one spousage is linked with one employee.

Many kind of to many (M:N) relationships

For a many to many kind of partnership, think about the complying with points:

It cannot be applied as such in the relational model.It deserve to be changed right into two 1:M relationships.It have the right to be implemented by breaking up to produce a collection of 1:M relationships.It requires the implementation of a compowebsite entity.Creates two or even more 1:M relationships.The composite entity table must contain at least the major tricks of the original tables.The linking table has multiple occurrences of the international key values.Further attributes may be assigned as required.It have the right to stop problems natural in an M:N relationship by creating a compowebsite entity or bridge entity. For instance, an employee have the right to work-related on many kind of tasks OR a project have the right to have many employees functioning on it, depending on the organization rules. Or, a student deserve to have actually many classes and also a class can organize many students.

Figure 8.8 shows one more another element of the M:N relationship wbelow an employee has actually various begin days for various jobs. As such, we need a JOIN table that includes the EID, Code and also StartDate.

*
Figure 8.8. Example wbelow employee has various begin days for different projects.

Example of mapping an M:N binary relationship type

For each M:N binary relationship, recognize 2 relationships.A and B represent two entity types participating in R.Create a brand-new relation S to recurrent R.S requirements to contain the PKs of A and B. These together deserve to be the PK in the S table OR these together with another basic attribute in the new table R have the right to be the PK. The combination of the main tricks (A and also B) will make the main vital of S.

Unary relationship (recursive)

A unary connection, also referred to as recursive, is one in which a relationship exists in between events of the very same entity set. In this relationship, the primary and also international secrets are the very same, but they reexisting two entities with different functions. See Figure 8.9 for an example.

For some entities in a unary relationship, a separate column deserve to be created that describes the primary vital of the exact same entity collection.

*
Figure 8.9. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a partnership type that involves many type of to many relationships between three tables. 

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

For each n-ary (> 2) connection, create a brand-new relation to recurrent the connection.The main vital of the brand-new relation is a mix of the main keys of the participating entities that organize the N (many) side.In the majority of cases of an n-ary partnership, all the participating entities hold a many side.

See more: Why Do Rats Not Have A Gallbladder In A Rat? Gallbladder

*
Figure 8.10. Example of a ternary partnership.