The entity relationship (ER) data model has actually existed because that over 35 years. It is fine suited come data modelling for use through databases since it is fairly abstract and is simple to discuss and also explain. ER models space readily interpreted to relations. ER models, likewise called an er schema, are represented by ER diagrams.

You are watching: Another name for a composite entity is a(n) _____ entity.

ER modelling is based upon two concepts:

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

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

For the rest of this chapter, we will use a sample database called the firm database to highlight the concepts of the ER model. This database contains information about employees, departments and also projects. Necessary points to keep in mind include:

There are number of departments in the company. Every department has actually a unique identification, a name, place of the office and a specific employee who manages the department.A department controls a number of projects, every of which has actually a unique name, a distinct number and a budget.Each employee has actually a name, identification number, address, salary and also birthdate. An employee is assigned to one department however can join in numerous projects. We should record the start day of the employee in every project. We also need to know the direct supervisor of every employee.We desire to keep track that the dependents for each employee. Every dependent has actually a name, birthdate and relationship v the employee.

Entity, Entity set and reality Type

An entity is an item in the real civilization with one independent presence that can be identified from other objects. One entity can be

An object v 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 on their strength. An reality is considered weak if the tables are visibility dependent.

That is, it cannot exist there is no a relationship with an additional entityIts primary an essential is acquired from the primary key of the parental entityThe Spouse table, in the agency database, is a weak entity since its primary an essential is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.

An reality is considered solid if it can exist personal from every one of its associated entities.

Kernels are strong entities.A table without a foreign key or a table that has a foreign vital that can contain nulls is a solid entity

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

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

*
Figure 8.1. ERD with entity type EMPLOYEE.

Existence dependency

An entity’s visibility is dependence on the visibility of the associated entity. The is existence-dependent if it has a mandatory foreign key (i.e., a foreign key attribute that cannot it is in null). Because that example, in the firm database, a Spouse entity is existence -dependent ~ above the Employee entity.

Kinds the Entities

You should additionally be familiar with various kinds the entities including independent entities, dependency entities and also characteristic entities. This are described below.

Independent entities

Independent entities, also referred to as kernels, are the backbone the the database. They are what various other tables room based on. Kernels have actually the adhering to characteristics:

They are the structure blocks of a database.The primary an essential may be simple or composite.The primary key is not a foreign key.They do not rely on an additional entity for your existence.

If we refer earlier to our firm database, examples of an independent entity encompass the customer table, Employee table or Product table.

Dependent entities

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

Dependent entities are supplied to affix two kernels together.They are claimed to be visibility dependent on two or more tables.Many to plenty of relationships end up being associative tables with at the very least two international keys.They may contain various other attributes.The foreign vital identifies each associated table.There room three alternatives for the main key:Use a composite of international keys of associated tables if uniqueUse a composite of international keys and a qualifying columnCreate a new simple primary key

Characteristic entities

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

They stand for multivalued attributes.They define other entities.They typically have a one to countless relationship.The foreign vital is offered to more identify the defined table.Options for primary vital are together follows:Use a composite that foreign crucial plus a qualifying columnCreate a brand-new simple major key. In the firm database, these might include:Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.EmployeePhone (EID, Phone) – EID is part of a composite major key. Here, EID is also a foreign key.

Attributes

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

Each attribute has a name, and also is associated with one entity and a domain of legal values. However, the information about attribute domain is no presented ~ above the ERD.

In the entity partnership diagram, displayed in number 8.2, every attribute is stood for by an oval through a name inside.

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

Types of Attributes

There are a couple of types of qualities you must be familiar with. Several of these are to be left together is, however some need to be adjusted to facilitate representation in the relational model. This first section will talk about the varieties of attributes. After that we will comment on fixing the features to fit correctly into the relational model.

Simple attributes

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

Composite attributes

Composite attributes room those that consist of a hierarchy of attributes. Utilizing our database example, and shown in number 8.3, resolve may consist of Number, Street and Suburb. For this reason this would certainly be composed as → address = 59 + ‘Meek Street’ + ‘Kingsford’

*
Figure 8.3. An instance of composite attributes.

Multivalued attributes

Multivalued attributes are qualities that have a collection of worths for every entity. An instance of a multivalued attribute indigenous the company database, as viewed in number 8.4, space the levels of one employee: BSc, MIT, PhD.

*
Figure 8.4. Example of a multivalued attribute.

Derived attributes

Derived attributes are qualities that contain worths calculated from various other attributes. An instance of this deserve to be viewed in number 8.5. Age can be obtained from the attribute Birthdate. In this situation, Birthdate is referred to as a stored attribute, which is physically saved to the database.

*
Figure 8.5. Example of a obtained attribute.

Keys

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

Types the Keys

There space several varieties of keys. This are explained below.

Candidate key

A candidate key is a an easy or composite an essential that is unique and also minimal. The is unique since no two rows in a table may have actually the exact same value at any type of time. It is minimal since every tower is crucial in bespeak to attain uniqueness.

From our agency database example, if the entity is Employee(EID, an initial Name, last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), feasible candidate secrets are:

EID, SINFirst Name and also Last name – assuming over there is no one else in the firm with the exact same nameLast Name and also DepartmentID – presume two human being with the exact same last name don’t work in the same department

Composite key

A composite key is composed of two or more attributes, yet it must be minimal.

Using the example from the candidate an essential section, feasible composite keys are:

First Name and also Last surname – assuming there is no one else in the agency with the very same nameLast Name and also Department id – presume two world with the very same last name don’t work-related in the very same department

Primary key

The primary crucial is a candidate crucial that is selected by the database designer come be used as one identifying system for the totality entity set. It must uniquely recognize tuples in a table and not be null. The primary crucial is indicated in the ER version by underlining the attribute.

A candidate key is selected through the designer to uniquely identify tuples in a table. It have to not it is in null.A key is preferred by the database designer to be offered as an identifying device for the totality entity set. This is referred to as the main key. This vital is indicated by underlining the attribute in the ER model.

In the following example, EID is the primary key:

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

Secondary key

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

Alternate key

Alternate keys are every candidate tricks not preferred as the main key.

Foreign key

A foreign vital (FK) is one attribute in a table that referrals the primary an essential in an additional table OR it can be null. Both foreign and primary tricks must be of the very same data type.

In the firm database example below, DepartmentID is the foreign key:

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

Nulls

A null is a distinct symbol, independent of data type, which means either unknown or inapplicable. It does not average zero or blank. Features of null include:

No data entryNot permitted in the primary keyShould it is in avoided in other attributesCan representAn unknown attribute valueA known, however missing, attribute valueA “not applicable” conditionCan develop problems when attributes such as COUNT, AVERAGE and also SUM room usedCan develop logical troubles when relational tables space linked

NOTE: The an outcome of a comparison operation is null when either debate is null. The result of one arithmetic operation is null once either debate is null (except functions that ignore nulls).

Example of just how null can be used

Use the value table (Salary_tbl) in number 8.6 come follow an instance of exactly how null deserve to be used.

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

To begin, find all employee (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 the the row v the null value is included, we must look at the individual fields. By adding commission and also salary for employee E13, the result will it is in a null value. The solution is displayed below.

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

Relationships

Relationships are the glue that holds the tables together. Lock are used to connect related information between tables.

Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the associated entity does not contain a primary crucial component the the parent entity. Company database instances include:

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

A strong, or identifying, partnership exists as soon as the primary crucial of the related entity consists of the primary an essential component the the parental entity. Instances include:

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

Types of Relationships

Below room descriptions that the various types of relationships.

One to countless (1:M) relationship

A one to numerous (1:M) relationship should be the share in any kind of relational database design and is discovered in all relational database environments. Because that example, one room has countless employees. Number 8.7 shows the connection of one of these employee to the department.

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

One to one (1:1) relationship

A one to one (1:1) relationship is the relationship of one reality to just one various other entity, and also vice versa. It should be rarely in any type of relational database design. In fact, it can indicate that two entities actually belong in the very same table.

An instance from the agency database is one employee is associated with one spouse, and also one spouse is connected with one employee.

Many come many (M:N) relationships

For a numerous to numerous relationship, take into consideration the adhering to points:

It cannot be implemented therefore in the relational model.It deserve to be adjusted into two 1:M relationships.It deserve to be applied by break up to produce a collection of 1:M relationships.It involves the implementation that a composite entity.Creates two or much more 1:M relationships.The composite entity table need to contain at the very least the primary keys of the original tables.The linking table includes multiple events of the foreign key values.Additional features may it is in assigned as needed.It deserve to avoid problems inherent in an M:N relationship by creating a composite entity or bridge entity. Because that example, one employee deserve to work on many projects OR a project can have countless employees working on it, depending upon the service rules. Or, a student have the right to have many classes and a course can hold plenty of students.

Figure 8.8 shows one more another aspect of the M:N partnership where an employee has different start dates for various projects. Therefore, we require a join table that includes the EID, Code and StartDate.

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

Example the mapping an M:N binary connection type

For each M:N binary relationship, determine two relations.A and also B stand for two entity species participating in R.Create a new relation S to stand for R.S demands to save the PKs of A and B. This together can be the PK in the S table OR these together with another basic attribute in the new table R have the right to be the PK. The combination of the primary keys (A and also B) will make the primary an essential of S.

Unary connection (recursive)

A unary relationship, also called recursive, is one in i beg your pardon a connection exists in between occurrences the the very same entity set. In this relationship, the primary and also foreign keys are the same, however they stand for two entities with different roles. See number 8.9 for an example.

For part entities in a unary relationship, a different column can be created that describes the primary an essential of the same entity set.

*
Figure 8.9. Instance of a unary relationship.

Ternary Relationships

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

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

For each n-ary (> 2) relationship, develop a brand-new relation to stand for the relationship.The primary crucial of the new relation is a mix of the primary keys of the participating entities that organize the N (many) side.In most instances of one n-ary relationship, every the participating entities organize a many side.

See more: If Wachovia® Needed To Borrow Money, The Bank Manager Would Contact The

*
Figure 8.10. Example of a ternary relationship.