The entity relationship (ER) data model has actually existed for over 35 years. It is fine suited to data modelling for use with databases because it is fairly abstract and is straightforward to discuss and explain. ER models are readily translated to relations. ER models, also called period schema, are represented by ER diagrams.

You are watching: What is composed of rows and columns that represent an entity?

ER modelling is based upon two concepts:

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

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

For the rest of this chapter, we will use a sample database referred to as the firm database to show the concepts of the ER model. This database consists of information about employees, departments and projects. Vital points to keep in mind include:

There are several departments in the company. Every department has a distinct identification, a name, ar of the office and a certain employee that manages the department.A room controls a variety of projects, every of which has actually a unique name, a distinctive number and a budget.Each employee has actually a name, to know number, address, salary and birthdate. One employee is assigned come one department yet can join in several projects. We have to record the start date of the employee in each project. We likewise need to understand the straight supervisor of each employee.We desire to keep track the the dependents for each employee. Each dependent has actually a name, birthdate and also relationship v the employee.

Entity, Entity collection and entity Type

An entity is an item in the real civilization with an independent visibility that can be distinguished from various other objects. An entity might be

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

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

That is, it can not exist without a relationship with one more entityIts primary an essential is derived from the primary key of the parental entityThe Spouse table, in the company database, is a weak entity due to the fact that its primary key is dependency on the Employee table. Without a corresponding employee record, the spouse record would not exist.

An entity is considered strong if it deserve to exist personal from every one of its associated entities.

Kernels are solid entities.A table without a foreign an essential or a table that contains a foreign vital that can contain nulls is a solid entity

Another ax to know is entity type which specifies a arsenal of similar entities.

An entity set is a collection of entities of an entity kind at a specific point that time. In one entity connection diagram (ERD), an entity kind is stood for by a surname in a box. For example, in figure 8.1, the entity kind is EMPLOYEE.

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

Existence dependency

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

Kinds the Entities

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

Independent entities

Independent entities, likewise referred to together kernels, space the backbone the the database. They are what various other tables are based on. Kernels have actually the complying with characteristics:

They are the structure blocks the a database.The primary vital may be straightforward or composite.The primary crucial is not a foreign key.They execute not rely on one more entity for your existence.

If we refer back to our company database, examples of an independent entity include the client table, Employee table or Product table.

Dependent entities

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

Dependent entities are supplied to connect two kernels together.They are stated to be visibility dependent on 2 or much more tables.Many to plenty of relationships end up being associative tables through at the very least two foreign keys.They might contain other attributes.The foreign crucial identifies each linked table.There room three alternatives for the major key:Use a composite of foreign keys of connected 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 stand for multivalued attributes.They describe other entities.They frequently have a one to plenty of relationship.The foreign crucial is supplied to more identify the identified table.Options because that primary vital are as follows:Use a composite that foreign vital plus a qualifying columnCreate a brand-new simple main key. In the agency 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 main key. Here, EID is likewise a foreign key.

Attributes

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

Each attribute has actually a name, and also is linked with an entity and also a domain of legitimate values. However, the information about attribute domain is no presented top top the ERD.

In the entity relationship diagram, displayed in number 8.2, every attribute is stood for by one oval v a surname inside.

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

Types that Attributes

There room a couple of types of features you need to be acquainted with. Few of these room to be left as is, however some have to be readjusted to facilitate representation in the relational model. This an initial section will talk about the types of attributes. After that we will comment on fixing the features to right correctly right into the relational model.

Simple attributes

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

Composite attributes

Composite attributes room those that consist the a hierarchy of attributes. Making use of our database example, and also shown in figure 8.3, attend to may consists Number, Street and Suburb. Therefore this would be composed as → attend to = 59 + ‘Meek Street’ + ‘Kingsford’

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

Multivalued attributes

Multivalued attributes are qualities that have actually a set of values for every entity. An example of a multivalued attribute native the agency database, as seen in number 8.4, room the levels of an employee: BSc, MIT, PhD.

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

Derived attributes

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

Keys

An vital constraint on one entity is the key. The key is one attribute or a team of qualities whose values deserve to be used to uniquely identify an individual entity in an entity set.

Types the Keys

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

Candidate key

A candidate key is a an easy or composite crucial that is unique and minimal. The is unique because no 2 rows in a table may have the very same value at any type of time. The is minimal since every obelisk is vital in bespeak to acquire uniqueness.

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

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

Composite key

A composite key is composed of two or much more attributes, however it should be minimal.

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

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

Primary key

The primary vital is a candidate vital that is selected by the database designer come be offered as one identifying mechanism for the totality entity set. It have to uniquely recognize tuples in a table and not be null. The primary vital is indicated in the ER design by underlining the attribute.

A candidate crucial is selected by the designer come uniquely recognize tuples in a table. It have to not it is in null.A crucial is chosen by the database designer come be provided as one identifying system for the totality entity set. This is referred to as the main key. This vital is shown by underlining the attribute in the ER model.

In the complying with example, EID is the major key:

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

Secondary key

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

Alternate key

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

Foreign key

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

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

Employee(EID, very 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. It does not typical 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, however missing, attribute valueA “not applicable” conditionCan produce problems when features such as COUNT, AVERAGE and SUM space usedCan develop logical troubles when relational tables are linked

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

Example of exactly how null deserve to be used

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

\"*\"
Figure 8.6. Value table because that null example, by A. Watt.

To begin, uncover all employees (emp#) in Sales (under the jobName column) whose salary to add commission are greater 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 due to the fact that of the null worth in the board of directors column. Come ensure that the row through the null worth is included, we have to look in ~ the individual fields. By including commission and salary because that employee E13, the an outcome will it is in a null value. The solution is displayed below.

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

Relationships

Relationships are the glue that holds the tables together. Castle are offered to affix related information in between tables.

Relationship strength is based on how the primary an essential 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 the the parent entity. Agency database examples include:

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

A strong, or identifying, connection exists once the primary an essential of the associated entity contains the primary an essential component that the parent entity. Examples include:

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

Types that Relationships

Below room descriptions of the various varieties of relationships.

One to plenty of (1:M) relationship

A one to countless (1:M) relationship must be the share in any kind of relational database design and is discovered in all relational database environments. Because that example, one room has plenty of employees. Figure 8.7 mirrors 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) connection is the connection of one reality to just one various other entity, and also vice versa. It have to be rarely in any kind of relational database design. In fact, it might indicate that two entities actually belong in the same table.

An example from the agency database is one employee is connected with one spouse, and one spouse is connected with one employee.

Many to many (M:N) relationships

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

It cannot be implemented because of this in the relational model.It deserve to be changed into two 1:M relationships.It can be implemented by breaking up to create a collection of 1:M relationships.It involves the implementation of a composite entity.Creates 2 or more 1:M relationships.The composite entity table must contain at least the primary secrets of the initial tables.The linking table contains multiple incidents of the foreign key values.Additional features may be assigned together needed.It deserve to avoid troubles inherent in an M:N connection by creating a composite reality or bridge entity. Because that example, one employee have the right to work on countless projects OR a project can have numerous employees functioning on it, relying on the service rules. Or, a student have the right to have plenty of classes and a course can hold many students.

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

\"*\"
Figure 8.8. Instance where employee has various start dates for various projects.

Example the mapping an M:N binary partnership type

For every M:N binary relationship, identify two relations.A and B stand for two entity species participating in R.Create a new relation S to stand for R.S needs to contain the PKs of A and B. These together can be the PK in the S table OR these along with another an easy attribute in the new table R can be the PK. The mix of the primary secrets (A and B) will certainly make the primary crucial of S.

Unary partnership (recursive)

A unary relationship, also dubbed recursive, is one in i beg your pardon a relationship exists in between occurrences that the exact same entity set. In this relationship, the primary and foreign secrets are the same, however they stand for two reality with different roles. See figure 8.9 because that an example.

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

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

Ternary Relationships

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

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

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

See more: I Don'T Need No Doctor By Ray Charles I Don T Need No Doctor

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