The entity relationship (ER) data model has existed because that over 35 years. It is fine suited to data modelling for use v databases because it is relatively abstract and also is easy to discuss and explain. ER models are readily translated to relations. ER models, likewise called an age schema, are stood for by ER diagrams.

You are watching: The associative entity is also known as a ____ entity.

ER modelling is based upon two concepts:

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

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

For the rest of this chapter, we will usage a sample database called the company database to show the principles of the ER model. This database consists of information around employees, departments and also projects. Necessary points to keep in mind include:

There are number of departments in the company. Every department has a unique identification, a name, place of the office and a certain employee who manages the department.A room controls a number of projects, every of which has actually 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 but can sign up with in numerous projects. We need to record the start day of the employee in each project. We also need to recognize the direct supervisor of each employee.We desire to store track that the dependents because that each employee. Each dependent has actually a name, birthdate and relationship through the employee.

Entity, Entity collection and entity Type

An entity is things in the real civilization with one independent presence that can be differentiated from various other objects. One entity can be

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

Entities have the right to be classified based on their strength. An entity is taken into consideration weak if that is tables are visibility dependent.

That is, it cannot exist without a relationship with one more entityIts primary vital is acquired from the primary vital of the parent entityThe Spouse table, in the firm database, is a weak entity since its primary crucial is dependency on the Employee table. There is no a equivalent employee record, the spouse document would not exist.

An reality is considered strong if it deserve to exist apart from all of its connected entities.

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

Another ax to recognize is entity type which specifies a collection of similar entities.

An entity set is a arsenal of entities of one entity type at a particular point the time. In one entity relationship diagram (ERD), an entity form is represented by a name in a box. Because that example, in figure 8.1, the entity type is EMPLOYEE.

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

Existence dependency

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

Kinds the Entities

You should also be familiar with different kinds of entities consisting of independent entities, dependent entities and characteristic entities. This are defined below.

Independent entities

Independent entities, also referred to together kernels, room the backbone the the database. They are what various other tables are based on. Kernels have actually the adhering to characteristics:

They space the building blocks the a database.The primary crucial may be basic or composite.The primary key is no a foreign key.They carry out not rely on another entity for their existence.

If us refer ago to our agency database, examples of an live independence entity encompass the customer table, Employee table or Product table.

Dependent entities

Dependent entities, additionally referred to together derived entities, rely on other tables for your meaning. These entities have actually the adhering to characteristics:

Dependent reality are supplied to affix two kernels together.They are stated to be existence dependent on 2 or more tables.Many to plenty of relationships end up being associative tables through at least two foreign keys.They might contain other attributes.The foreign vital identifies each connected table.There space three options 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 major key

Characteristic entities

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

They represent multivalued attributes.They describe other entities.They generally have a one to numerous relationship.The foreign an essential is supplied to further identify the identified table.Options because that primary vital are as follows:Use a composite that foreign crucial plus a qualifying columnCreate a brand-new simple primary key. In the firm database, these can include:Employee (EID, Name, Address, Age, Salary) – EID is the an easy primary key.EmployeePhone (EID, Phone) – EID is component of a composite primary key. Here, EID is likewise a international key.

Attributes

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

Each attribute has actually a name, and also is associated with one entity and a domain of legitimate values. However, the information about attribute domain is not presented ~ above the ERD.

In the entity connection diagram, presented in number 8.2, every attribute is represented by one oval through a name inside.

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

Types the Attributes

There space a few types of features you should be familiar with. Some of these room to it is in left together is, yet some have to be changed to facilitate depiction in the relational model. This very first section will talk about the types of attributes. Later on we will comment on fixing the features to fit correctly right into the relational model.

Simple attributes

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

Composite attributes

Composite attributes space those that consist the a hierarchy of attributes. Utilizing our database example, and also shown in number 8.3, resolve may covers Number, Street and Suburb. So this would certainly be composed as → attend to = 59 + ‘Meek Street’ + ‘Kingsford’

\"*\"
Figure 8.3. An instance of composite attributes.

Multivalued attributes

Multivalued attributes are features that have actually a collection of worths for each entity. An instance of a multivalued attribute indigenous the firm database, as watched in number 8.4, space the levels 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 instance of this deserve to be checked out in figure 8.5. Age can be derived 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 deserve to be offered to uniquely determine an individual reality in an reality set.

Types of Keys

There room several types of keys. This are described below.

Candidate key

A candidate key is a basic or composite an essential that is unique and minimal. It is unique due to the fact that no two rows in a table may have actually the same value at any type of time. It is minimal because every shaft is vital in order to acquire uniqueness.

From our firm database example, if the reality is Employee(EID, first Name, last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate secrets are:

EID, SINFirst Name and Last name – assuming over there is no one else in the agency with the same nameLast Name and also DepartmentID – suspect two civilization with the same last name don’t work-related in the same department

Composite key

A composite key is written of two or an ext attributes, but it need to be minimal.

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

First Name and Last surname – assuming there is no one else in the firm with the same nameLast Name and also Department i would – assuming two world with the very same last name don’t job-related in the same department

Primary key

The primary crucial is a candidate an essential that is selected through the database designer come be supplied as an identifying mechanism for the totality entity set. It need to uniquely recognize tuples in a table and not it is in null. The primary an essential is suggested in the ER model by underlining the attribute.

A candidate crucial is selected by the designer to uniquely identify tuples in a table. It need to not it is in null.A an essential is favored by the database designer to be offered as one identifying device for the totality entity set. This is referred to as the main key. This key is suggested by underlining the attribute in the ER model.

In the adhering to 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 supplied strictly for retrieval purposes (can be composite), because that example: Phone and also Last Name.

Alternate key

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

Foreign key

A foreign vital (FK) is an attribute in a table that recommendations the primary vital in one more table OR it can be null. Both foreign and also primary secrets must be of the exact same data type.

In the agency database instance below, DepartmentID is the foreign key:

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

Nulls

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

No data entryNot allowed in the major keyShould be avoided in other attributesCan representAn unknown attribute valueA known, yet missing, attribute valueA “not applicable” conditionCan produce problems when features such together COUNT, AVERAGE and SUM are usedCan develop logical difficulties when relational tables room linked

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

Example of how null have the right to be used

Use the salary table (Salary_tbl) in number 8.6 come follow an instance 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) who salary plus commission are better than 30,000.

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

This an outcome does not encompass E13 since of the null worth in the board of directors column. Come ensure that the row v the null value is included, we must look at the separation, personal, instance fields. By including commission and also salary because that employee E13, the result will be a null value. The equipment is displayed below.

SELECT emp# indigenous 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. Lock are used to affix related information between tables.

Relationship strength is based on how the primary vital of a connected entity is defined. A weak, or non-identifying, relationship exists if the primary vital of the associated entity does no contain a primary vital component of the parental entity. Agency database instances include:

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

A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component that the parent entity. Examples include:

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

Types of Relationships

Below space descriptions of the various types of relationships.

One to many (1:M) relationship

A one to many (1:M) relationship should be the norm in any relational database design and also is found in all relational database environments. For example, one department has numerous employees. Number 8.7 mirrors the relationship of among these employees to the department.

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

One to one (1:1) relationship

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

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

Many to many (M:N) relationships

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

It can not be implemented as such in the relational model.It can be changed into two 1:M relationships.It deserve to be enforced by break up to produce a collection of 1:M relationships.It entails the implementation the a composite entity.Creates 2 or more 1:M relationships.The composite reality table need to contain at least the primary keys of the original tables.The linking table includes multiple cases of the foreign key values.Additional attributes may be assigned as needed.It deserve to avoid troubles inherent in an M:N partnership by creating a composite reality or bridge entity. Because that example, one employee can work on many projects OR a project have the right to have many employees functioning on it, depending upon the business rules. Or, a student can have numerous classes and also a class can hold countless students.

Figure 8.8 shows an additional another element of the M:N relationship where one employee has various start dates for various projects. Therefore, we require a join table that consists of the EID, Code and also StartDate.

\"*\"
Figure 8.8. Example where employee has different start dates for different projects.

Example that mapping an M:N binary relationship type

For each M:N binary relationship, determine two relations.A and B stand for two entity species participating in R.Create a new relation S to stand for R.S requirements to save on computer the PKs of A and also B. This together deserve to be the PK in the S table OR these together with another an easy attribute in the new table R have the right to be the PK. The mix of the primary keys (A and also B) will make the primary vital of S.

Unary connection (recursive)

A unary relationship, also dubbed recursive, is one in i beg your pardon a relationship exists between occurrences of the exact same entity set. In this relationship, the primary and foreign secrets are the same, but they represent 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 created that refers to the primary an essential of the same entity set.

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

Ternary Relationships

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

Refer to figure 8.10 for an example of mapping a ternary partnership type. Note n-ary way 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 vital of the new relation is a mix of the primary keys of the participating reality that host the N (many) side.In most situations of one n-ary relationship, all the participating entities host a many side.

See more: M I Miss Your Arms Around Me Quotes, Miss Your Arms Around Me Lyrics

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