The entity relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for use v databases since it is reasonably abstract and is easy to discuss and explain. ER models space readily translated to relations. ER models, likewise called an er schema, are stood for by ER diagrams.

You are watching: A natural key is also called a(n) ____ key.

ER modelling is based on two concepts:

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

Here is an example of how these two principles might be an unified in period data model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity).

For the remainder of this chapter, us will use a sample database referred to as the agency database to show the ideas of the ER model. This database has information around employees, departments and projects. Important points to keep in mind 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 that manages the department.A room controls a variety of projects, every of which has a distinctive name, a unique number and also a budget.Each employee has a name, identification number, address, salary and also birthdate. An employee is assigned come one department yet can join in numerous projects. We should record the start date of the employee in every project. We also need to recognize the straight supervisor of each employee.We want to store track that the dependents because that each employee. Every dependent has a name, birthdate and relationship v the employee.

Entity, Entity set and reality Type

An entity is an item in the real world with an independent existence that can be distinguished from various other objects. An entity can be

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

Entities can be classified based on their strength. An reality is taken into consideration weak if that tables are presence dependent.

That is, it can not exist without a partnership with one more entityIts primary vital is acquired from the primary vital of the parental entityThe Spouse table, in the company database, is a weak entity due to the fact that its primary crucial is dependent on the Employee table. Without a equivalent employee record, the spouse document would no exist.

An reality is considered solid if it have the right to exist personal from all of its related entities.

Kernels are solid entities.A table without a foreign key or a table that consists of a foreign an essential that can save nulls is a strong entity

Another ax to understand is entity type which specifies a repertoire of similar entities.

An entity set is a collection of reality of an entity form at a details point of time. In an entity relationship diagram (ERD), an entity form is stood for by a name in a box. For example, in figure 8.1, the entity type is EMPLOYEE.

*
Figure 8.1. ERD v entity form EMPLOYEE.

Existence dependency

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

Kinds that Entities

You should additionally be acquainted with different kinds of entities including independent entities, dependency entities and also characteristic entities. These are explained below.

Independent entities

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

They are the building blocks of a database.The primary crucial may be simple or composite.The primary vital is no a international key.They perform not rely on an additional entity for your existence.

If we refer back to our agency database, instances of an independent entity incorporate the customer table, Employee table or Product table.

Dependent entities

Dependent entities, likewise referred to as derived entities, rely on other tables for their meaning. These entities have the adhering to characteristics:

Dependent entities are supplied to affix two kernels together.They are said to be existence dependent on 2 or more tables.Many to numerous relationships become associative tables v at the very least two foreign keys.They may contain various other attributes.The foreign an essential identifies each linked table.There room three choices for the main key:Use a composite of international keys of connected tables if uniqueUse a composite of international keys and also a qualifying columnCreate a brand-new simple main key

Characteristic entities

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

They represent multivalued attributes.They explain other entities.They generally have a one to countless relationship.The foreign vital is used to more identify the identified table.Options because that primary an essential are together follows:Use a composite that foreign vital plus a qualifying columnCreate a new simple primary key. In the company database, these might include:Employee (EID, Name, Address, Age, Salary) – EID is the basic primary key.EmployeePhone (EID, Phone) – EID is component of a composite primary key. Here, EID is likewise a foreign key.

Attributes

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

Each attribute has a name, and also is linked with one entity and a domain of legit values. However, the information about attribute domain is no presented on the ERD.

In the entity connection diagram, displayed in figure 8.2, every attribute is represented by an oval through a surname inside.

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

Types the Attributes

There space a couple of types of attributes you have to be acquainted with. Several of these space to it is in left as is, yet some have to be changed to facilitate depiction in the relational model. This first section will talk about the species of attributes. Later on we will discuss fixing the features to right correctly right into the relational model.

Simple attributes

Simple attributes space those attracted from the atomic worth domains; they are additionally called single-valued attributes. In the firm database, an example of this would be: Name = John ; age = 23

Composite attributes

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

*
Figure 8.3. An example of composite attributes.

Multivalued attributes

Multivalued attributes are features that have actually a collection of values for each entity. An example of a multivalued attribute indigenous the agency database, as watched in figure 8.4, room the levels of one employee: BSc, MIT, PhD.

*
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are characteristics that contain values calculated from various other attributes. An instance of this deserve to be viewed in figure 8.5. Age can be derived from the attribute Birthdate. In this situation, Birthdate is referred to as a stored attribute, which is physically conserved to the database.

*
Figure 8.5. Example of a derived attribute.

Keys

An vital constraint on an entity is the key. The key is an attribute or a team of characteristics whose values have the right to be provided to uniquely identify an individual reality in an entity set.

Types of Keys

There room several species of keys. This are defined below.

Candidate key

A candidate key is a simple or composite an essential that is unique and minimal. The is unique because no 2 rows in a table may have actually the exact same value at any type of time. It is minimal since every pillar is important in stimulate to acquire uniqueness.

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

EID, SINFirst Name and also Last surname – assuming over there is nobody else in the company with the very same nameLast Name and DepartmentID – assuming two people with the very same last name don’t work in the exact same department

Composite key

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

Using the example from the candidate key section, possible composite keys are:

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

Primary key

The primary key is a candidate crucial that is selected by the database designer to be provided as one identifying mechanism for the totality entity set. It need to uniquely identify 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 vital is selected by the designer to uniquely identify tuples in a table. It must not be null.A vital is liked by the database designer to be offered as an identifying system for the entirety entity set. This is referred to as the main key. This key is indicated by underlining the attribute in the ER model.

In the complying with 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 because that retrieval purposes (can it is in composite), because that example: Phone and Last Name.

Alternate key

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

Foreign key

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

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

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

Nulls

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

No data entryNot allowed in the main keyShould be avoided in various other attributesCan representAn unknown attribute valueA known, however missing, attribute valueA “not applicable” conditionCan create problems when attributes such together COUNT, AVERAGE and SUM are usedCan produce logical difficulties when relational tables are linked

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

Example of just how null deserve to be used

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

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

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

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

This an outcome does not encompass E13 because of the null value in the board of directors column. To ensure that the row with the null worth is included, we have to look at the individual fields. By adding commission and also salary because that employee E13, the result will be a null value. The systems is shown below.

SELECT emp# from 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. Lock are offered to attach related information between tables.

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

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

A strong, or identifying, partnership exists as soon as the primary crucial of the connected entity contains the primary vital component of the parental entity. Instances include:

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

Types the Relationships

Below room descriptions that the various species of relationships.

One to plenty of (1:M) relationship

A one to plenty of (1:M) relationship must be the share in any relational database design and is found in all relational database environments. Because that example, one department has plenty of employees. Figure 8.7 shows the relationship of one of these employees to the department.

*
Figure 8.7. Example of a one to countless relationship.

One to one (1:1) relationship

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

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

Many to many (M:N) relationships

For a numerous to many relationship, think about the complying with points:

It cannot be implemented thus in the relational model.It can be adjusted into 2 1:M relationships.It have the right to be applied by break up to develop a set of 1:M relationships.It entails the implementation that a composite entity.Creates two or much more 1:M relationships.The composite entity table have to contain at least the primary keys of the initial tables.The linking table consists of multiple cases of the foreign vital values.Additional features may be assigned as needed.It can avoid troubles inherent in an M:N connection by producing a composite reality or leg entity. For example, one employee have the right to work on plenty of projects OR a project have the right to have plenty of employees working on it, depending upon the service rules. Or, a student can have many classes and also a class can hold plenty of students.

Figure 8.8 shows one more another element of the M:N partnership where one employee has different start dates for different projects. Therefore, we require a sign up with table that includes the EID, Code and StartDate.

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

Example that mapping an M:N binary partnership type

For each M:N binary relationship, determine two relations.A and also B stand for two entity types participating in R.Create a new relation S to stand for R.S requirements to contain the PKs of A and B. This together have the right to be the PK in the S table OR these in addition to another straightforward attribute in the new table R have the right to be the PK. The combination of the primary secrets (A and also B) will make the primary an essential of S.

Unary partnership (recursive)

A unary relationship, also referred to as recursive, is one in i m sorry a connection exists in between occurrences of the exact same entity set. In this relationship, the primary and foreign tricks are the same, however they represent two reality with different roles. See number 8.9 because that an example.

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

*
Figure 8.9. Instance of a unary relationship.

Ternary Relationships

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

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

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

See more: Dirty Night Before Christmas 2, 'Twas The Night Before Christmas…

*
Figure 8.10. Example of a ternary relationship.