Identify the Function Family to Which F Belongs

Main Torso

Chapter 8 The Entity Relationship Data Model

Adrienne Watt

The entity relationship (ER) data model has existed for over 35 years. Information technology is well suited to data modelling for use with databases because information technology is fairly abstract and is piece of cake to talk over and explicate. ER models are readily translated to relations. ER models, besides called an ER schema, are represented past ER diagrams.

ER modelling is based on two concepts:

• Entities, defined every bit tables that hold specific information (data)
• Relationships, divers as the associations or interactions between entities

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

For the residual of this chapter, we will use a sample database called the Visitor database to illustrate the concepts of the ER model. This database contains information about employees, departments and projects. Important points to note include:

• There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department.
• A department controls a number of projects, each of which has a unique name, a unique number and a budget.
• Each employee has a proper noun, identification number, accost, salary and birthdate. An employee is assigned to 1 section merely can join in several projects. Nosotros demand to record the beginning appointment of the employee in each project. Nosotros as well need to know the straight supervisor of each employee.
• Nosotros want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

Entity, Entity Set and Entity Type

An entity is an object in the real world with an independent being that tin can be differentiated from other objects. An entity might be

• An object with concrete existence (due east.g., a lecturer, a student, a car)
• An object with conceptual beingness (e.chiliad., a course, a task, a position)

Entities tin exist classified based on their forcefulness. An entity is considered weak if its tables are being dependent.

• That is, information technology cannot exist without a human relationship with another entity
• Its master cardinal is derived from the main key of the parent entity
  • The Spouse table, in the Visitor database, is a weak entity because its primary primal is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.

An entity is considered strong if information technology tin can exist apart from all of its related entities.

• Kernels are strong entities.
• A tabular array without a foreign primal or a table that contains a foreign key that can contain nulls is a strong entity

Another term to know is entity type which defines a collection of similar entities.

An entity fix is a collection of entities of an entity type at a particular betoken of fourth dimension. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure viii.one, the entity type is EMPLOYEE.

Figure 8.ane. ERD with entity blazon EMPLOYEE.

Existence dependency

An entity's existence is dependent on the being of the related entity. It is existence-dependent if information technology has a mandatory foreign key (i.e., a foreign key attribute that cannot exist cypher). For instance, in the COMPANY database, a Spouse entity is being -dependent on the Employee entity.

Kinds of Entities

Y'all should likewise be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.

Independent entities

Contained entities, also referred to equally kernels, are the courage of the database. They are what other tables are based on. Kernels have the post-obit characteristics:

• They are the building blocks of a database.
• The principal primal may be simple or blended.
• The principal key is not a foreign cardinal.
• They exercise not depend on another entity for their existence.

If we refer dorsum to our COMPANY database, examples of an independent entity include the Customer tabular array, Employee table or Product table.

Dependent entities

Dependent entities, besides referred to as derived entities, depend on other tables for their pregnant. These entities have the following characteristics:

• Dependent entities are used to connect two kernels together.
• They are said to be existence dependent on two or more tables.
• Many to many relationships become associative tables with at to the lowest degree two foreign keys.
• They may comprise other attributes.
• The foreign cardinal identifies each associated table.
• In that location are three options for the main key:
  1. Use a composite of foreign keys of associated tables if unique
  2. Use a composite of foreign keys and a qualifying cavalcade
  3. Create a new uncomplicated master cardinal

Characteristic entities

Characteristic entities provide more than data about some other table. These entities have the following characteristics:

• They represent multivalued attributes.
• They draw other entities.
• They typically have a one to many human relationship.
• The strange key is used to further identify the characterized table.
• Options for primary primal are as follows:
  1. Use a blended of foreign cardinal plus a qualifying column
  2. Create a new simple primary key. In the Visitor database, these might include:
     • Employee (EID, Name, Address, Historic period, Salary) – EID is the elementary primary key.
     • EmployeePhone (EID, Telephone) – EID is function of a blended primary fundamental. Here, EID is also a foreign fundamental.

Attributes

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

Each aspect has a proper name, and is associated with an entity and a domain of legal values. Nevertheless, the data most attribute domain is non presented on the ERD.

In the entity relationship diagram, shown in Figure eight.2, each aspect is represented past an oval with a name inside.

Figure eight.2. How attributes are represented in an ERD.

Types of Attributes

At that place are a few types of attributes you need to exist familiar with. Some of these are to be left as is, but some need to exist adjusted to facilitate representation in the relational model. This first department will talk over the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model.

Simple attributes

Elementary attributes are those drawn from the atomic value domains; they are likewise called single-valued attributes. In the COMPANY database, an instance of this would exist: Proper noun = {John} ; Age = {23}

Composite attributes

Composite attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. And then this would exist written as → Accost = {59 + 'Meek Street' + 'Kingsford'}

Figure 8.iii. An example of composite attributes.

Multivalued attributes

Multivalued attributes are attributes that have a set of values for each entity. An case of a multivalued attribute from the COMPANY database, as seen in Figure 8.four, are the degrees of an employee: BSc, MIT, PhD.

Figure 8.four. Case of a multivalued attribute.

Derived attributes

Derived attributes are attributes that contain values calculated from other attributes. An example of this can be seen in Figure eight.v.  Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute,which is physically saved to the database.

Figure 8.5. Example of a derived attribute.

Keys

An important constraint on an entity is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.

Types of Keys

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

Candidate key

A candidate key is a simple or blended key that is unique and minimal.  It is unique because no 2 rows in a table may have the same value at whatsoever fourth dimension. It is minimal because every column is necessary in order to attain uniqueness.

From our Visitor database example, if the entity is Employee(EID, First Name, Terminal Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:

• EID, SIN
• Beginning Name and Terminal Name – assuming in that location is no one else in the company with the aforementioned name
• Final Proper name and DepartmentID – assuming two people with the aforementioned final name don't work in the same department

Blended central

A composite key is composed of two or more than attributes, merely information technology must be minimal.

Using the example from the candidate central department, possible composite keys are:

• First Name and Terminal Name – bold there is no i else in the visitor with the same name
• Final Proper name and Department ID – bold two people with the aforementioned last name don't work in the same department

Primary cardinal

The primary cardinal is a candidate key that is selected by the database designer to be used as an identifying machinery for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary primal is indicated in the ER model past underlining the attribute.

• A candidate key is selected past the designer to uniquely identify tuples in a table. Information technology must not be null.
• A key is chosen by the database designer to be used as an identifying machinery for the whole entity set.  This is referred to as the main key. This key is indicated past underlining the attribute in the ER model.

In the post-obit example, EID is the chief key:

Employee( EID , First Proper noun, Last Name, SIN, Address, Telephone, BirthDate, Bacon, DepartmentID)

Secondary key

A secondary central is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name.

Alternate key

Alternating keys are all candidate keys not chosen as the primary key.

Foreign key

A strange key (FK) is an attribute in a table that references the primary key in another table OR it tin can be null. Both strange and primary keys must be of the aforementioned data type.

In the Visitor database case below, DepartmentID is the foreign key:

Employee(EID, First Proper name, Final Name, SIN, Address, Telephone, BirthDate, Salary, DepartmentID)

Nulls

A null is a special symbol, independent of information type, which ways either unknown or extraneous. It does not hateful naught or blank. Features of null include:

• No data entry
• Not permitted in the primary key
• Should exist avoided in other attributes
• Can represent
  • An unknown attribute value
  • A known, but missing, attribute value
  • A "not applicable" condition
• Can create problems when functions such as COUNT, AVERAGE and SUM are used
• Tin can create logical problems when relational tables are linked

NOTE: The event of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is cypher (except functions that ignore nulls).

Example of how zero can be used

Utilize the Salary table (Salary_tbl) in Figure 8.6 to follow an example of how nil can be used.

Figure 8.6. Salary table for zippo example, by A. Watt.

To begin, detect all employees (emp#) in Sales (nether the jobName column) whose salary plus commission are greater than 30,000.

• SELECT emp# FROM Salary_tbl
• WHERE  jobName = Sales AND
• (commission + bacon) > 30,000  –> E10 and E12

This result does non include E13 because of the null value in  the commission cavalcade. To ensure that the row with the null value is included, nosotros demand to await at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below.

• SELECT emp# FROM Salary_tbl
• WHERE jobName = Sales AND
• (commission > 30000 OR
• salary > 30000 OR
• (committee + salary) > 30,000  –>E10 and E12 and E13

Relationships

Relationships are the mucilage that holds the tables together. They are used to connect related information between tables.

Relationship strength is based on how the primary key of a related entity is divers. A weak, or non-identifying, relationship exists if the chief central of the related entity does non contain a master key component of the parent entity. Visitor database examples include:

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

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

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

Types of Relationships

Below are descriptions of the various types of relationships.

One to many (ane:M) relationship

A i to many (ane:M) relationship should be the norm in whatsoever relational database design and is found in all relational database environments. For instance, ane department has many employees. Figure 8.7 shows the relationship of one of these employees to the section.

Figure viii.7. Example of a ane to many relationship.

One to i (1:one) relationship

A one to one (1:one) relationship is the relationship of 1 entity to simply one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually vest in the aforementioned table.

An example from the COMPANY database is one employee is associated with i spouse, and one spouse is associated with one employee.

Many to many (One thousand:N) relationships

For a many to many relationship, consider the following points:

• Information technology cannot exist implemented as such in the relational model.
• It can be changed into ii 1:M relationships.
• It can be implemented past breaking up to produce a gear up of 1:K relationships.
• It involves the implementation of a composite entity.
• Creates ii or more than 1:M relationships.
• The composite entity table must comprise at to the lowest degree the primary keys of the original tables.
• The linking table contains multiple occurrences of the strange key values.
• Additional attributes may be assigned every bit needed.
• Information technology tin avoid problems inherent in an Thou:N human relationship by creating a composite entity or bridge entity. For case, an employee can piece of work on many projects OR a projection tin can have many employees working on it, depending on the business rules. Or, a pupil can have many classes and a grade can hold many students.

Effigy 8.8 shows some other another aspect of the M:N relationship where an employee has dissimilar showtime dates for different projects. Therefore, we demand a JOIN table that contains the EID, Code and StartDate.

Figure eight.8. Case where employee has different outset dates for different projects.

Instance of mapping an M:N binary relationship blazon

• For each M:Due north binary relationship, identify two relations.
• A and B represent two entity types participating in R.
• Create a new relation S to represent R.
• S needs to contain the PKs of A and B. These together can 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 make the primary central of Southward.

Unary relationship (recursive)

A unary relationship, also called recursive, is one in which a human relationship exists between occurrences of the same entity set. In this human relationship, the primary and foreign keys are the aforementioned, only they represent 2 entities with different roles. See Effigy 8.9 for an example.

For some entities in a unary relationship, a separate cavalcade tin can be created that refers to the master key of the aforementioned entity set.

Figure 8.nine. Example of a unary relationship.

Ternary Relationships

A ternary relationship is a relationship type that involves many to many relationships betwixt iii tables.

Refer to Effigy eight.x for an example of mapping a ternary relationship type. Notationdue north-ary means multiple tables in a human relationship. (Remember, N = many.)

• For each n-ary (> 2) relationship, create a new relation to represent the human relationship.
• The primary cardinal of the new relation is a combination of the primary keys of the participating entities that agree the Due north (many) side.
• In most cases of an northward-ary relationship, all the participating entities agree a many side.

Figure viii.10. Example of a ternary human relationship.

alternate key: all candidate keys not chosen as the primary keycandidate key: a simple or blended primal that is unique (no two rows in a table may have the same value) and minimal (every column is necessary)

characteristic entities: entities that provide more information about some other table

composite attributes: attributes that consist of a hierarchy of attributes

composite primal: composed of two or more attributes, but it must be minimal

dependent entities: these entities depend on other tables for their pregnant

derived attributes: attributes that contain values calculated from other attributes

derived entities: run into dependent entities

EID: employee identification (ID)

entity: a thing or object in the existent world with an independent beingness that tin can be differentiated from other objects

entity human relationship (ER) data model: too called an ER schema, are represented past ER diagrams. These are well suited to data modelling for employ with databases.

entity relationship schema : see entity relationship information model

entity set up:a collection of entities of an entity blazon at a point of time

entity type: a drove of similar entities

foreign key (FK): an aspect in a table that references the primary key in another table OR information technology can be null

contained entity: as the building blocks of a database, these entities are what other tables are based on

kernel: see contained entity

key: an attribute or group of attributes whose values can exist used to uniquely identify an individual entity in an entity set

multivalued attributes: attributes that have a set of values for each entity

n-ary: multiple tables in a relationship

null: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean cipher or blank

recursive relationship: meet unary relationship

relationships: the associations or interactions between entities; used to connect related data between tables

relationship strength:  based on how the primary key of a related entity is defined

secondary key an aspect used strictly for retrieval purposes

uncomplicated attributes: drawn from the atomic value domains

SIN: social insurance number

single-valued attributes: see simple attributes

stored attribute: saved physically to the database

ternary relationship: a relationship type that involves many to many relationships between three tables.

unary human relationship: 1 in which a relationship exists between occurrences of the same entity set.

Attribution

This chapter of Database Blueprint (including images, except equally otherwisse noted) is a derivative copy of Data Modeling Using Entity-Human relationship Model  by  Nguyen Kim Anh  licensed underCreative Commons Attribution License 3.0 license

The following cloth was written past Adrienne Watt:

1. Nulls department and example
2. Central Terms
3. Exercises

gordonbossubjectis.blogspot.com

Source: https://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/

Share this post