You Have Been Hired to Design a Family Friendly Seesaw Derive an Expression

Main Body

Affiliate 8 The Entity Relationship Information Model

Adrienne Watt

The entity relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for utilise with databases because information technology is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations. ER models, as well called an ER schema, are represented by ER diagrams.

ER modelling is based on ii concepts:

• Entities, defined equally tables that hold specific information (information)
• Relationships, defined equally the associations or interactions betwixt entities

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

For the remainder of this chapter, we will use a sample database called the COMPANY 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 visitor. Each section has a unique identification, a proper noun, 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 proper noun, a unique number and a upkeep.
• Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to 1 department only tin join in several projects. Nosotros need to tape the start engagement of the employee in each projection. We also need to know the straight supervisor of each employee.
• We want to proceed track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.

Entity, Entity Prepare and Entity Type

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

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

Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.

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

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

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

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

An entity set up is a collection of entities of an entity blazon at a particular point of fourth dimension. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Effigy 8.1, the entity type is EMPLOYEE.

Figure 8.one. ERD with entity type EMPLOYEE.

Beingness dependency

An entity's existence is dependent on the being of the related entity. Information technology is being-dependent if it has a mandatory foreign central (i.eastward., a foreign fundamental attribute that cannot exist null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee entity.

Kinds of Entities

You should as well be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.

Contained entities

Independent entities, also referred to as kernels, are the backbone of the database. They are what other tables are based on. Kernels have the following characteristics:

• They are the edifice blocks of a database.
• The primary key may be simple or blended.
• The chief cardinal is not a foreign key.
• They do not depend on another entity for their existence.

If nosotros refer back to our Company database, examples of an independent entity include the Customer tabular array, Employee table or Product table.

Dependent entities

Dependent entities, also referred to as derived entities, depend on other tables for their meaning. These entities take the following characteristics:

• Dependent entities are used to connect two kernels together.
• They are said to exist existence dependent on two or more tables.
• Many to many relationships become associative tables with at least two strange keys.
• They may contain other attributes.
• The foreign key identifies each associated table.
• At that place are three options for the chief key:
  1. Use a blended of strange keys of associated tables if unique
  2. Use a composite of foreign keys and a qualifying column
  3. Create a new elementary primary key

Characteristic entities

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

• They represent multivalued attributes.
• They describe other entities.
• They typically have a one to many human relationship.
• The foreign key is used to further identify the characterized table.
• Options for primary key are as follows:
  1. Use a composite of strange key plus a qualifying column
  2. Create a new unproblematic primary key. In the COMPANY database, these might include:
     • Employee (EID, Name, Accost, Age, Salary) – EID is the elementary master cardinal.
     • EmployeePhone (EID, Phone) – EID is function of a composite primary key. Hither, EID is also a foreign key.

Attributes

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

Each attribute has a name, and is associated with an entity and a domain of legal values. Notwithstanding, the information about attribute domain is non presented on the ERD.

In the entity human relationship diagram, shown in Figure 8.two, each attribute is represented past an oval with a name inside.

Figure 8.ii. How attributes are represented in an ERD.

Types of Attributes

There are a few types of attributes you demand to be familiar with. Some of these are to be left as is, simply some demand to exist adjusted to facilitate representation in the relational model. This commencement section will discuss the types of attributes. Later on on we will discuss fixing the attributes to fit correctly into the relational model.

Simple attributes

Simple attributes are those drawn from the diminutive value domains; they are as well called single-valued attributes. In the Visitor database, an example of this would be: Name = {John} ; Historic period = {23}

Composite attributes

Blended attributes are those that consist of a bureaucracy of attributes. Using our database example, and shown in Figure 8.three, Address may consist of Number, Street and Suburb. So this would be written every bit → Accost = {59 + 'Meek Street' + 'Kingsford'}

Figure 8.3. An case of composite attributes.

Multivalued attributes

Multivalued attributes are attributes that accept a set of values for each entity. An case of a multivalued attribute from the COMPANY database, every bit seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.

Figure eight.4. Example of a multivalued attribute.

Derived attributes

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

Figure eight.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 central is a simple or composite key that is unique and minimal.  It is unique because no two rows in a tabular array may have the same value at whatsoever time. Information technology is minimal because every column is necessary in order to reach uniqueness.

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

• EID, SIN
• First Proper noun and Concluding Proper name – bold in that location is no 1 else in the company with the same name
• Final Proper noun and DepartmentID – assuming two people with the same concluding name don't piece of work in the aforementioned department

Composite primal

A composite key is equanimous of 2 or more than attributes, but it must be minimal.

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

• First Name and Last Name – assuming at that place is no one else in the company with the same proper name
• Last Name and Section ID – assuming two people with the aforementioned last name don't piece of work in the same section

Primary fundamental

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

• A candidate key is selected by the designer to uniquely place tuples in a table. Information technology must not exist zippo.
• A central is called by the database designer to exist used as an identifying mechanism for the whole entity set.  This is referred to as the master key. This key is indicated by underlining the attribute in the ER model.

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

Employee( EID , Offset Name, Last Name, SIN, Address, Phone, BirthDate, Bacon, DepartmentID)

Secondary key

A secondary key is an aspect used strictly for retrieval purposes (can exist composite), for case: Phone and Last Name.

Alternate primal

Alternate keys are all candidate keys non chosen as the chief key.

Foreign key

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

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

Employee(EID, Get-go Name, Last Name, SIN, Address, Phone, BirthDate, Bacon, DepartmentID)

Nulls

A goose egg is a special symbol, independent of data type, which ways either unknown or inapplicable. Information technology does non mean cypher or blank. Features of zippo include:

• No information 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 bug when functions such as COUNT, AVERAGE and SUM are used
• Can create logical bug when relational tables are linked

Note: The result of a comparing operation is null when either argument is null. The event of an arithmetics performance is null when either argument is null (except functions that ignore nulls).

Example of how null can exist used

Use the Salary table (Salary_tbl) in Figure 8.half dozen to follow an example of how zip tin exist used.

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

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

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

This issue does not include E13 because of the zip value in  the commission column. To ensure that the row with the nada value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the upshot will be a null value. The solution is shown beneath.

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

Relationships

Relationships are the glue that holds the tables together. They are used to connect related data between tables.

Human relationship strength is based on how the primary cardinal of a related entity is defined. A weak, or non-identifying, relationship exists if the principal key of the related entity does not comprise a primary central component of the parent entity. Company database examples include:

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

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

• Class( CrsCode , DeptCode, Description)
• Class( CrsCode, Department , ClassTime…)

Types of Relationships

Below are descriptions of the various types of relationships.

One to many (ane:K) relationship

A 1 to many (1:M) relationship should be the norm in whatever relational database design and is found in all relational database environments. For example, one department has many employees. Figure viii.seven shows the human relationship of one of these employees to the department.

Effigy 8.seven. Example of a one to many relationship.

Ane to one (one:i) relationship

A i to one (1:1) human relationship is the relationship of one entity to only one other entity, and vice versa. It should exist rare in any relational database design. In fact, it could bespeak that two entities actually belong in the same table.

An instance from the Company database is one employee is associated with one spouse, and i spouse is associated with one employee.

Many to many (Thou:N) relationships

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

• It cannot be implemented equally such in the relational model.
• It tin be changed into two 1:Yard relationships.
• It can exist implemented by breaking upward to produce a set of i:M relationships.
• Information technology involves the implementation of a composite entity.
• Creates two or more than one:M relationships.
• The blended entity table must contain at to the lowest degree the master keys of the original tables.
• The linking table contains multiple occurrences of the foreign key values.
• Additional attributes may be assigned as needed.
• It can avoid issues inherent in an M:N relationship by creating a blended entity or bridge entity. For example, an employee tin work on many projects OR a project can accept many employees working on information technology, depending on the business rules. Or, a student can take many classes and a course can agree many students.

Figure 8.8 shows another another aspect of the Thou:Due north human relationship where an employee has different start dates for dissimilar projects. Therefore, nosotros need a Join table that contains the EID, Lawmaking and StartDate.

Figure eight.8. Example where employee has unlike commencement dates for different projects.

Case of mapping an Grand:N binary relationship type

• For each Chiliad:Northward binary human relationship, place two relations.
• A and B represent two entity types participating in R.
• Create a new relation Due south to stand for R.
• S needs to comprise the PKs of A and B. These together tin be the PK in the South tabular array OR these together with some other uncomplicated attribute in the new tabular array R can be the PK.
• The combination of the primary keys (A and B) will make the chief primal of S.

Unary relationship (recursive)

A unary relationship, also called recursive, is 1 in which a human relationship exists betwixt occurrences of the same entity set. In this human relationship, the master and foreign keys are the same, but they represent two entities with different roles. Run into Figure 8.ix for an example.

For some entities in a unary relationship, a separate column tin 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 human relationship is a relationship type that involves many to many relationships between three tables.

Refer to Figure 8.ten for an example of mapping a ternary relationship type. Noten-ary means multiple tables in a relationship. (Call up, 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 hold the N (many) side.
• In well-nigh cases of an northward-ary relationship, all the participating entities concord a many side.

Figure eight.x. Example of a ternary relationship.

alternate key: all candidate keys not chosen as the primary fundamentalcandidate central: a simple or composite primal that is unique (no ii rows in a table may accept the same value) and minimal (every cavalcade is necessary)

characteristic entities: entities that provide more information about another table

composite attributes: attributes that consist of a hierarchy of attributes

blended cardinal: composed of ii or more attributes, but it must exist minimal

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

derived attributes: attributes that comprise 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 being that tin exist differentiated from other objects

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

entity relationship schema : run into entity relationship data model

entity set:a collection of entities of an entity blazon at a signal of time

entity type: a collection of similar entities

strange key (FK): an attribute in a table that references the main key in another tabular array OR it can be zip

contained entity: every bit the edifice blocks of a database, these entities are what other tables are based on

kernel: run into independent entity

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

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

due north-ary: multiple tables in a relationship

zero: a special symbol, independent of information type, which means either unknown or inapplicable; it does non mean zero or blank

recursive relationship: see unary relationship

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

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

secondary cardinal an attribute used strictly for retrieval purposes

simple attributes: drawn from the atomic value domains

SIN: social insurance number

unmarried-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 relationship: 1 in which a relationship exists betwixt occurrences of the same entity set.

Attribution

This chapter of Database Design (including images, except as otherwisse noted) is a derivative re-create of Data Modeling Using Entity-Relationship Model  past  Nguyen Kim Anh  licensed underArtistic Commons Attribution License 3.0 license

The following material was written by Adrienne Watt:

1. Nulls section and case
2. Key Terms
3. Exercises

agarwalsuccur94.blogspot.com

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