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 simple to discuss and explain. ER models space readily translated to relations. ER models, also called period schema, are stood for by ER diagrams.

You are watching: A collection of fields all related to a single object, activity, or individual is a(n) _____.

ER modelling is based on two concepts:

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

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

For the remainder of this chapter, we will use a sample database referred to as the firm database to show the principles of the ER model. This database contains information about employees, departments and also projects. Essential points to note include:

There are numerous departments in the company. Each department has a distinct identification, a name, place of the office and also a specific employee that manages the department.A room controls a variety of projects, every of which has a distinctive name, a distinctive number and also a budget.Each employee has actually a name, to know number, address, salary and birthdate. An employee is assigned to one department but can join in number of projects. We need to record the start date of the employee in every project. We also need to know the direct supervisor of each employee.We desire to keep track the the dependents for each employee. Each dependent has a name, birthdate and also relationship v the employee.

Entity, Entity collection and entity Type

An entity is things in the real world with one independent existence that can be differentiated from other objects. An entity could be

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

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

That is, it cannot exist there is no a partnership with one more entityIts primary key is obtained from the primary key of the parental entityThe Spouse table, in the firm database, is a weak entity since its primary key is dependent on the Employee table. There is no a matching employee record, the spouse record would not exist.

An entity is considered strong if it can exist personally from every one of its associated entities.

Kernels are strong entities.A table there is no a foreign an essential or a table that consists of a foreign vital that can save nulls is a strong entity

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

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

\"*\"
Figure 8.1. ERD v entity form EMPLOYEE.

Existence dependency

An entity’s existence is dependency on the visibility of the connected entity. The is existence-dependent if it has a mandatory international key (i.e., a foreign vital attribute the cannot be null). For example, in the agency database, a Spouse entity is visibility -dependent top top the Employee entity.

Kinds the Entities

You should likewise be acquainted with different kinds that entities including independent entities, dependent entities and characteristic entities. This are explained below.

Independent entities

Independent entities, also referred to as kernels, are the backbone the the database. Lock are what other tables room based on. Kernels have the complying with characteristics:

They space the structure blocks that a database.The primary key may be an easy or composite.The primary vital is no a foreign key.They do not rely on another entity for your existence.

If us refer back to our company database, instances of an live independence entity incorporate the client table, Employee table or Product table.

Dependent entities

Dependent entities, also referred to together derived entities, rely on various other tables for your meaning. These entities have actually the complying with characteristics:

Dependent entities are supplied to connect two kernels together.They are stated to be visibility dependent on two or an ext tables.Many to many relationships come to be associative tables v at least two international keys.They may contain various other attributes.The foreign an essential identifies each connected table.There space three alternatives for the major key:Use a composite of foreign keys of associated tables if uniqueUse a composite of international keys and a qualifying columnCreate a brand-new simple main key

Characteristic entities

Characteristic entities provide more information about another table. These entities have the complying with characteristics:

They represent multivalued attributes.They explain other entities.They generally have a one to numerous relationship.The foreign crucial is supplied to further identify the characterized table.Options because that primary an essential are together follows:Use a composite that foreign key plus a qualifying columnCreate a brand-new simple major key. In the agency database, these might include:Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.EmployeePhone (EID, Phone) – EID is part of a composite main key. Here, EID is likewise a foreign key.

Attributes

Each reality is described 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 a domain of legit values. However, the information about attribute domain is no presented ~ above the ERD.

In the entity partnership diagram, presented in figure 8.2, every attribute is represented by an oval with a surname inside.

\"*\"
Figure 8.2. How qualities are represented in one ERD.

Types the Attributes

There room a couple of types of attributes you have to be acquainted with. Some of these space to be left as is, yet some should be changed to facilitate depiction in the relational model. This an initial section will discuss the types of attributes. Afterwards we will talk about fixing the features to right correctly into the relational model.

Simple attributes

Simple attributes space those drawn from the atomic worth domains; castle are likewise called single-valued attributes. In the agency database, an instance of this would be: Name = John ; period = 23

Composite attributes

Composite attributes are those that consist that a pecking order of attributes. Utilizing our database example, and also shown in number 8.3, attend to may consist of Number, Street and Suburb. For this reason this would be created 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 set of worths for every entity. An instance of a multivalued attribute native the firm database, as seen in figure 8.4, space the degrees of one employee: BSc, MIT, PhD.

\"*\"
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are characteristics that contain worths calculated from various other attributes. An instance of this can be seen in figure 8.5. Age can be obtained from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically conserved to the database.

\"*\"
Figure 8.5. Instance of a obtained attribute.

Keys

An important constraint on one entity is the key. The key is an attribute or a team of attributes whose values have the right to be offered to uniquely identify an individual entity in an reality set.

Types of Keys

There are several varieties of keys. These are described below.

Candidate key

A candidate key is a simple or composite key that is unique and also minimal. It is unique since no 2 rows in a table may have actually the exact same value at any kind of time. The is minimal due to the fact that every shaft is crucial in order to attain uniqueness.

From our company database example, if the reality is Employee(EID, an initial Name, critical Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:

EID, SINFirst Name and Last surname – assuming there is nobody else in the company with the exact same nameLast Name and DepartmentID – suspect two human being with the exact same last surname don’t occupational in the exact same department

Composite key

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

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

First Name and Last surname – assuming over there is no one else in the agency with the exact same nameLast Name and also Department i would – assuming two human being with the same last name don’t occupational in the very same department

Primary key

The primary an essential is a candidate crucial that is selected through the database designer to be used as an identifying mechanism for the entirety entity set. It must uniquely determine tuples in a table and not it is in null. The primary key is shown in the ER version by underlining the attribute.

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

In the following example, EID is the primary key:

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

Secondary key

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

Alternate key

Alternate keys are every candidate keys not liked as the primary key.

Foreign key

A foreign an essential (FK) is one attribute in a table that referrals the primary crucial in one more table OR it deserve to be null. Both foreign and also primary keys must be of the exact same data type.

In the firm database instance 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. That does not average zero or blank. Attributes 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 produce problems when functions such as COUNT, AVERAGE and also SUM are usedCan create logical problems when relational tables space linked

NOTE: The result of a comparison operation is null when either debate is null. The an outcome of one arithmetic procedure is null once either debate is null (except features that ignore nulls).

Example of just how null have the right to be used

Use the value table (Salary_tbl) in number 8.6 to follow an example of just how null can be used.

\"*\"
Figure 8.6. Value table for null example, through A. Watt.

To begin, discover all employees (emp#) in Sales (under the jobName column) whose salary to add commission are better than 30,000.

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

This result does not encompass E13 due to the fact that of the null worth in the board of directors column. Come ensure that the row through the null value is included, we should look at the separation, personal, instance fields. By including commission and also salary because that employee E13, the result will it is in a null value. The solution is shown below.

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

Relationships

Relationships space the glue the holds the tables together. They are provided to affix related information in between tables.

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

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

A strong, or identifying, connection exists as soon as the primary key of the related entity has the primary key component of the parental entity. Instances include:

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

Types of Relationships

Below are descriptions of the various species of relationships.

One to many (1:M) relationship

A one to numerous (1:M) relationship need to be the share in any relational database design and also is discovered in all relational database environments. Because that example, one department has numerous employees. Figure 8.7 mirrors the partnership of among these employee to the department.

\"*\"
Figure 8.7. Instance of a one to numerous relationship.

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 need to be rare in any type of relational database design. In fact, it can indicate that two entities in reality belong in the exact same table.

An example from the company database is one employee is linked with one spouse, and also one spouse is connected with one employee.

Many to many (M:N) relationships

For a plenty of to numerous relationship, consider the adhering to points:

It can not be implemented therefore in the relational model.It can be readjusted into 2 1:M relationships.It have the right to be imposed by breaking up to develop a collection of 1:M relationships.It entails the implementation the a composite entity.Creates two or more 1:M relationships.The composite reality table have to contain at the very least the primary tricks of the original tables.The linking table contains multiple occurrences of the foreign crucial values.Additional characteristics may it is in assigned together needed.It have the right to avoid difficulties inherent in an M:N partnership by producing a composite reality or bridge entity. Because that example, one employee deserve to work on countless projects OR a project have the right to have numerous employees functioning on it, depending upon the company rules. Or, a student can have plenty of classes and also a class can hold many students.

Figure 8.8 shows an additional another facet of the M:N connection where an employee has different start days for different projects. Therefore, we need a join table that contains the EID, Code and also StartDate.

\"*\"
Figure 8.8. Instance where employee has different start days for different projects.

Example of mapping an M:N binary connection type

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

Unary partnership (recursive)

A unary relationship, also called recursive, is one in which a connection exists in between occurrences the the same entity set. In this relationship, the primary and also foreign secrets are the same, however they stand for two entities with different roles. See figure 8.9 because that an example.

For part entities in a unary relationship, a separate column can be developed that refers to the primary key of the exact same entity set.

\"*\"
Figure 8.9. Instance of a unary relationship.

Ternary Relationships

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

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

For every n-ary (> 2) relationship, create a new relation to stand for the relationship.The primary an essential of the brand-new relation is a mix of the primary tricks of the participating reality that host the N (many) side.In most situations of one n-ary relationship, all the participating entities organize a many side.

See more: 9) This Is A Suggested, But Not Stated, Definition. It Is An ___ Definition.

\"*\"
Figure 8.10. Instance of a ternary relationship.