The entity relationship (ER) data model has actually existed for over 35 years. It is well suited come data modelling because that use v databases since it is reasonably abstract and is straightforward to discuss and explain. ER models are readily analyzed to relations. ER models, also called an er schema, are represented by ER diagrams.

You are watching: The network model has structural level dependence

ER modelling is based on two concepts:

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

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

For the remainder of this chapter, we will usage a sample database dubbed the agency database to highlight the ideas of the ER model. This database consists of information about employees, departments and also projects. Necessary points to note include:

There are several departments in the company. Each department has a distinct identification, a name, ar of the office and a specific employee who manages the department.A department controls a variety of projects, every of which has a distinct name, a distinct number and a budget.Each employee has a name, to know number, address, salary and also birthdate. One employee is assigned come one department but can sign up with in numerous projects. We should record the start day of the employee in every project. We likewise need to understand the straight supervisor of every employee.We desire to store track that the dependents because that 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 civilization with an independent presence that can be identified from various other objects. An entity might be

An object with 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 upon their strength. An reality is taken into consideration weak if that is tables are visibility dependent.

That is, it cannot exist there is no a connection with one more entityIts primary an essential is obtained from the primary crucial of the parental entityThe Spouse table, in the agency database, is a weak entity due to the fact that its primary vital is dependency on the Employee table. There is no a corresponding employee record, the spouse document would not exist.

An entity is considered solid if it have the right to exist apart from every one of its connected entities.

Kernels are solid entities.A table there is no a foreign an essential or a table that has a foreign crucial that can save nulls is a strong entity

Another ax to understand is entity type which defines a collection of comparable entities.

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

*
Figure 8.1. ERD with entity form EMPLOYEE.

Existence dependency

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

Kinds that Entities

You should additionally be familiar with various kinds that entities including independent entities, dependent entities and also characteristic entities. These are defined below.

Independent entities

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

They space the structure blocks that a database.The primary key may be basic or composite.The primary an essential is not a foreign key.They carry out not count on an additional entity for your existence.

If we refer earlier to our agency database, instances of an elevation entity encompass the customer table, Employee table or Product table.

Dependent entities

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

Dependent reality are offered to attach two kernels together.They are claimed to be presence dependent on 2 or more tables.Many to plenty of relationships become associative tables v at the very least two foreign keys.They may contain various other attributes.The foreign key identifies each linked table.There are three alternatives for the primary key:Use a composite of international keys of associated tables if uniqueUse a composite of foreign keys and also a qualifying columnCreate a brand-new simple primary key

Characteristic entities

Characteristic entities provide an ext information about another table. This entities have the complying with characteristics:

They represent multivalued attributes.They describe other entities.They typically have a one to many relationship.The foreign key is supplied to additional identify the defined table.Options for primary an essential are together follows:Use a composite that foreign an essential plus a qualifying columnCreate a new simple primary key. In the agency 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 also a foreign 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 a domain of legitimate values. However, the information about attribute domain is no presented ~ above the ERD.

In the entity partnership diagram, shown in number 8.2, each attribute is represented by one oval v a surname inside.

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

Types the Attributes

There are a couple of types of features you have to be acquainted with. Several of these space to be left as is, however some have to be readjusted to facilitate representation in the relational model. This very first section will comment on the types of attributes. After that 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; lock 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 room those the consist that a pecking order of attributes. Utilizing our database example, and also shown in figure 8.3, attend to may covers Number, Street and also Suburb. For this reason this would certainly be created as → attend to = 59 + ‘Meek Street’ + ‘Kingsford’

*
Figure 8.3. An instance of composite attributes.

Multivalued attributes

Multivalued attributes are features that have a set of worths for each entity. An instance of a multivalued attribute native the company database, as viewed in figure 8.4, are the degrees 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 example of this can be viewed in number 8.5. Period 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. Instance of a obtained attribute.

Keys

An crucial constraint on one entity is the key. The key is one attribute or a team of features whose values deserve to be offered to uniquely determine an individual reality in an reality set.

Types that Keys

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

Candidate key

A candidate key is a straightforward or composite vital that is unique and also minimal. That is unique because no two rows in a table may have actually the exact same value at any type of time. It is minimal since every obelisk is crucial in stimulate to achieve uniqueness.

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

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

Composite key

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

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

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

Primary key

The primary vital is a candidate key that is selected through the database designer to be offered as one identifying device for the totality entity set. It have to uniquely determine tuples in a table and not it is in null. The primary an essential is suggested 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 need to not be null.A crucial is chosen by the database designer come be offered as one identifying mechanism for the whole entity set. This is referred to as the main key. This an essential is indicated by underlining the attribute in the ER model.

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

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

Secondary key

A secondary key is one attribute supplied strictly because that retrieval purposes (can be composite), for example: Phone and Last Name.

Alternate key

Alternate keys are every candidate keys not chosen as the main key.

Foreign key

A foreign an essential (FK) is an attribute in a table that referrals the primary an essential in another 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, last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)

Nulls

A null is a one-of-a-kind symbol, elevation of data type, which means either unknown or inapplicable. It does not typical zero or blank. Attributes of null include:

No data entryNot allowed in the primary keyShould it is in avoided in other attributesCan representAn unknown attribute valueA known, yet missing, attribute valueA “not applicable” conditionCan produce problems when attributes such together COUNT, AVERAGE and SUM space usedCan create logical difficulties when relational tables room linked

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

Example of exactly how null have the right to be used

Use the salary table (Salary_tbl) in number 8.6 to follow an example of exactly how null have the right to be used.

*
Figure 8.6. Value 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 higher than 30,000.

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

This result does not incorporate E13 due to the fact that of the null worth in the commission column. Come ensure the the row with the null value is included, we need to look in ~ the individual fields. By including commission and salary for employee E13, the an outcome will it is in a null value. The systems 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 the holds the tables together. Castle are supplied to connect related information in between tables.

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

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

A strong, or identifying, connection exists once the primary crucial of the associated entity has the primary key component that the parental entity. Examples include:

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

Types the Relationships

Below room descriptions that the various species of relationships.

One to many (1:M) relationship

A one to many (1:M) relationship should be the share in any type of relational database design and also is discovered in all relational database environments. For example, one room has numerous employees. Number 8.7 shows the partnership of among these employee to the department.

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

One to one (1:1) relationship

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

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

Many to many (M:N) relationships

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

It can not be implemented therefore in the relational model.It deserve to be readjusted into 2 1:M relationships.It can be enforced by breaking up to develop a set of 1:M relationships.It involves the implementation the a composite entity.Creates two or an ext 1:M relationships.The composite entity table need to contain at least the primary secrets of the original tables.The linking table contains multiple incidents of the foreign crucial values.Additional features may be assigned as needed.It deserve to avoid difficulties inherent in an M:N relationship by creating a composite reality or bridge entity. For example, one employee deserve to work on many projects OR a project have the right to have countless employees functioning on it, depending upon the organization rules. Or, a student can have plenty of classes and a course can hold numerous students.

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

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

Example the mapping an M:N binary partnership type

For each M:N binary relationship, identify two relations.A and also B represent two entity species participating in R.Create a brand-new relation S to stand for R.S demands to contain the PKs that A and also B. This together deserve to be the PK in the S table OR these together with another simple attribute in the new table R can be the PK. The combination of the primary keys (A and B) will certainly make the primary an essential of S.

Unary connection (recursive)

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

For some entities in a unary relationship, a separate column have the right to be created that describes the primary crucial of the exact same entity set.

*
Figure 8.9. Example of a unary relationship.

Ternary Relationships

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

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

For every n-ary (> 2) relationship, create 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 host the N (many) side.In most instances of an n-ary relationship, all the participating entities hold a many side.

See more: 5 Letter Words Starting With No, Words That Start With No

*
Figure 8.10. Instance of a ternary relationship.