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

You are watching: The uml notation ____ identifies a zero or many relation.

ER modelling is based upon two concepts:

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

Here is an example of how these two ideas might be linked 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 called the firm database to illustrate the principles of the ER model. This database includes information around employees, departments and projects. Essential points to note include:

There are number of departments in the company. Each department has actually a distinct identification, a name, ar of the office and a details employee who manages the department.A department controls a variety of projects, each of which has actually a distinct name, a distinct number and also a budget.Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to one department yet can sign up with in number of projects. We have to record the start date of the employee in every project. We likewise need to recognize the straight supervisor of each employee.We want to store track the the dependents because that each employee. Every dependent has a name, birthdate and also relationship through the employee.

Entity, Entity set and entity Type

An entity is things in the real human being with one independent presence that can be identified from various other objects. An entity could be

An object through 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 upon their strength. An entity is considered weak if that tables are presence dependent.

That is, it cannot exist there is no a partnership with another entityIts primary crucial is acquired from the primary crucial of the parental entityThe Spouse table, in the firm database, is a weak entity due to the fact that its primary key is dependent on the Employee table. There is no a corresponding employee record, the spouse record would no exist.

An entity is considered strong if it have the right to exist personal from every one of its related entities.

Kernels are solid entities.A table without a foreign key or a table that consists of a foreign crucial that can contain nulls is a solid entity

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

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

*
Figure 8.1. ERD with entity form EMPLOYEE.

Existence dependency

An entity’s existence is dependent on the presence of the associated entity. That is existence-dependent if it has a mandatory foreign key (i.e., a foreign an essential attribute that cannot be null). For example, in the company database, a Spouse entity is presence -dependent top top the Employee entity.

Kinds that Entities

You should also be acquainted with various kinds the entities including independent entities, dependence entities and characteristic entities. This are explained below.

Independent entities

Independent entities, additionally referred to as kernels, are the backbone of the database. Castle are what other tables space based on. Kernels have actually the adhering to characteristics:

They room the structure blocks the a database.The primary crucial may be an easy or composite.The primary key is no a international key.They do not count on an additional entity for their existence.

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

Dependent entities

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

Dependent reality are used to attach two kernels together.They are said to be visibility dependent on two or more tables.Many to plenty of relationships become associative tables with at the very least two international keys.They might contain other attributes.The foreign vital identifies each linked table.There room three options for the major key:Use a composite of foreign keys of linked tables if uniqueUse a composite of international keys and also a qualifying columnCreate a new simple primary key

Characteristic entities

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

They stand for multivalued attributes.They define other entities.They frequently have a one to many relationship.The foreign an essential is offered to additional identify the characterized table.Options for primary an essential are together follows:Use a composite the foreign crucial 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 straightforward primary key.EmployeePhone (EID, Phone) – EID is part of a composite primary key. Here, EID is additionally a international key.

Attributes

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

Each attribute has a name, and is connected with an entity and also a domain of legal values. However, the information around attribute domain is not presented on the ERD.

In the entity connection diagram, shown in number 8.2, each attribute is represented by an oval through a surname inside.

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

Types of Attributes

There are a couple of types of attributes you need to be familiar with. Few of these room to be left as is, however some have to be changed to facilitate representation in the relational model. This very first section will discuss the varieties of attributes. Afterwards we will talk about fixing the characteristics to to the right correctly right into the relational model.

Simple attributes

Simple attributes space those attracted from the atomic value domains; lock are additionally called single-valued attributes. In the company database, an instance of this would be: Name = John ; period = 23

Composite attributes

Composite attributes room those the consist the a hierarchy of attributes. Utilizing our database example, and shown in number 8.3, address may consist of Number, Street and also Suburb. For this reason this would be written 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 example of a multivalued attribute indigenous the firm database, as checked out in number 8.4, room the levels of one employee: BSc, MIT, PhD.

*
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are attributes that contain values calculated from various other attributes. An instance of this have the right to be checked out in figure 8.5. Period can be acquired 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 acquired attribute.

Keys

An crucial constraint on an entity is the key. The key is one attribute or a team of features whose values have the right to be used to uniquely identify an individual entity in an entity set.

Types that Keys

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

Candidate key

A candidate key is a an easy or composite an essential that is unique and also minimal. It is unique due to the fact that no two rows in a table may have actually the exact same value at any type of time. It is minimal due to the fact that every column is important in bespeak to obtain uniqueness.

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

EID, SINFirst Name and Last name – assuming there is nobody else in the firm with the same nameLast Name and also DepartmentID – assuming two world with the same last surname don’t work-related in the very same department

Composite key

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

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

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

Primary key

The primary vital is a candidate an essential that is selected by the database designer to be provided as an identifying device for the entirety entity set. It have to uniquely determine tuples in a table and not be null. The primary crucial is indicated in the ER design by underlining the attribute.

A candidate an essential is selected by the designer come uniquely determine tuples in a table. It have to not be null.A crucial is preferred by the database designer to be provided as an identifying mechanism for the whole entity set. This is described as the primary key. This key is suggested by underlining the attribute in the ER model.

In the following example, EID is the major key:

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

Secondary key

A secondary key is an attribute provided strictly for retrieval purposes (can it is in composite), for example: Phone and also Last Name.

Alternate key

Alternate keys are all candidate tricks not favored as the primary key.

Foreign key

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

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

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

Nulls

A null is a special symbol, live independence of data type, which method either unknown or inapplicable. That does not median zero or blank. Attributes of null include:

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

NOTE: The result of a comparison operation is null as soon as either discussion is null. The result of one arithmetic procedure is null as soon as either argument is null (except features that ignore nulls).

Example of how null have the right to be used

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

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

To begin, find all employees (emp#) in Sales (under the jobName column) who 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 an outcome does not incorporate E13 because of the null worth in the commission column. To ensure that the row through the null worth is included, we have to look at the individual fields. By adding commission and also salary because that employee E13, the an outcome will it is in a null value. The solution is shown below.

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

Relationships

Relationships room the glue that holds the tables together. Castle are supplied to attach related information between tables.

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

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

A strong, or identifying, relationship exists as soon as the primary vital of the associated entity consists of the primary key component of the parental entity. Instances include:

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

Types the Relationships

Below room descriptions that the various types of relationships.

One to many (1:M) relationship

A one to plenty of (1:M) relationship must be the norm in any relational database design and is discovered in all relational database environments. For example, one department has many employees. Figure 8.7 reflects the connection of one of these employee to the department.

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

One come one (1:1) relationship

A one come one (1:1) relationship is the relationship of one entity to only one various other entity, and vice versa. It must be rare in any kind of relational database design. In fact, it can indicate that two entities actually belong in the same table.

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

Many to many (M:N) relationships

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

It can not be implemented as such in the relational model.It can be changed into 2 1:M relationships.It can be implemented by breaking up to create a set of 1:M relationships.It involves the implementation of a composite entity.Creates two or an ext 1:M relationships.The composite reality table need to contain at least the primary tricks of the original tables.The linking table contains multiple incidents of the foreign key values.Additional qualities may be assigned as needed.It can avoid problems inherent in an M:N relationship by producing a composite entity or leg entity. Because that example, one employee can work on countless projects OR a project can have plenty of employees functioning on it, relying on the service rules. Or, a student can have many classes and a course can hold many students.

Figure 8.8 shows one more another facet of the M:N partnership where one employee has various start days for different projects. Therefore, we need a join table that consists of the EID, Code and StartDate.

*
Figure 8.8. Example where employee has various start dates for various projects.

Example of mapping an M:N binary relationship type

For each M:N binary relationship, identify two relations.A and B represent two entity types participating in R.Create a brand-new relation S to stand for R.S requirements to contain the PKs that A and also B. These together deserve to be the PK in the S table OR these along with another simple attribute in the brand-new table R deserve to be the PK. The combination of the primary secrets (A and also B) will make the primary crucial of S.

Unary relationship (recursive)

A unary relationship, also referred to as recursive, is one in i beg your pardon a partnership exists between occurrences of the very same entity set. In this relationship, the primary and also foreign secrets are the same, but they represent two entities with different roles. See number 8.9 for an example.

For some entities in a unary relationship, a different column deserve to be produced that describes the primary an essential of the exact same entity set.

*
Figure 8.9. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a relationship kind that involves countless to countless relationships in between three tables. 

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

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

See more: Are Ethernet Networks Are Always Connected Using Twisted-Pair Cable Technology

*
Figure 8.10. Instance of a ternary relationship.