p. 1

the entity-relationship unified view of data peter pin-shan massachusetts chen model-toward a institute of technology a data model called the entity-relationship model is proposed this model incorporates some of the important semantic information about the real world a special diagrammatic technique is introduced as a tool for database design an example of database design and description using the model and the diagrammatic technique is given some implications for data integrity information retrieval and data manipulation are discussed the entity-relationship model can be used as a basis for unification of different views of data t,he network model the relational model and the entity set model semantic ambiguities in these models are analyzed possible ways to derive their views of data from the entity-relationship model are presented key words and phrases database design logical view of data semantics of data data models entity-relationship model relational model data base task group network model entity set model data definition and manipulation data integrity and consistency cr categories 3.50 3.70 4.33 4.34 1 introduction the logical view of data has been an important issue in recent years three major data models have been proposed the network model [2 3 71 the relational model [s and the entity set model [25 these models have their own strengths and weaknesses the network model provides a more natural view of data by separating entities and relationships to a certain extent but its capability to achieve data independence has been challenged [s the relational model is based on relational theory and can achieve a high degree of data independence but it may lose some important semantic information about the real world [12 15 231 the entity set model which is based on set theory also achieves a high degree of data independence but its viewing of values such as 3 or red may not be natural to some people [25 this paper presents the entity-relationship model which has most of the advantages of the above three models the entity-relationship model adopts the more natural view that the real world consists of entities and relationships it copyright 1976 association for computing machinery inc general permission to republish but not for profit all or part of this material is granted provided that acm s copyright notice is given and that reference is made to the publication to its date of issue and to the fact that reprinting privileges were granted by permission of the association for computing machinery a version of this paper was presented at the international conference on very large data bases framingham mass sept 22-24 1975 author s address center for information system research alfred p sloan school of management massachusetts institute of technology cambridge ma 02139 acm transactions on database systems vol 1 no 1 march 1976 pages 9-36.

[close]

p. 2

10 p p s chen incorporates some of the important semantic information about the real world other work in database semantics can be found in [l 12 15 21 23 and 29 the model can achieve a high degree of data independence and is based on set theory and relation theory the entity-relationship model can be used as a basis for a unified view of data most ivork in the past has emphasized the difference between the network model and the relational model [22 recently several attempts have been made to reduce the differences of the three data models [4 19 26 30 311 this paper uses the entity-relationship model as a framework from which the three existing data models may be derived the reader may view the entity-relationship model as a generalization or extension of existing models this paper is organized into three parts sections 2-4 section 2 introduces the entity-relationship model using a framework of multilevel views of data section 3 describes the semantic information in the model and its implications for data description and data manipulation a special diagrammatric technique the entity-relationship diagram is introduced as a tool for database design section 4 analyzes the network model the relational model and the entity set model and describes how they may be derived from the entity-relationship model 2 the entity-relationship 2.1 multilevel views of doto model in the study of a data model we should identify the levels of logical views of data with which the model is concerned extending the framework developed in [ls 251 we can identify four levels of views of data figure 1 1 information concerning entities and relat,ionships which exist in our minds 2 information struct,ure-organization of information in which entities and relationships are represented by data 3 access-path-independent data structure-the data structures which are not involved with search schemes indexing schemes etc 4 access-path-dependent data st.ructure in the following sections we shall develop the entity-relationship model step by step for the first two levels as we shall see later in the paper the network model as currently implemented is mainly concerned with level 4 the relational model is mainly concerned with levels 3 and 2 the entity set model is mainly concerned with levels 1 and 2 2.2 information concerning entities and relationships level 1 at this level we consider entities and relationships an entity is a thing which can be distinctly identified a specific person company or event is an example of an entity a relationship is an association among entities for instance father-son is a relationship between two clperson entities iit is possible that some people may view something e.g marriage as an entity while other people may view it as a relationship we think that this is a decision which has to be made by the enterprise administrator [27 he should define what are entit,ies and what are relationships so that the distinction is suitable for his environment acm transactions on database systems vol 1 no 1 march 1976.

[close]

p. 3

the entity-relationship levels of logical views entity-relationship model 11 models network relational entity-set level i entities entity sets relationships relationship attributes values value sets roles entities relationships sets attributes values i entities entity sets roles information concerning entities and relationships level 2 structure information entity/relationship relation f entity-relationship diagram 1 i /t iiiiii 3nf entity 1 similar+relations description sets i 4 decomposition approach relations tables 1 level 3 1 table access-pathindependent data structure level 4 access-pathdependent data structure records i structuredata-structurediagram i fig 1 analysis of data models using mult.iple levels of logical views the database of an enterprise contains relevant information concerning entities and relationships in which the enterprise is interested a complete description of an entity or relationship may not be recorded in the database of an enterprise it is impossible and perhaps unnecessary to record every potentially available piece of information about ent,ities and relationships from now on we shall consider only the entities and relationships and the information concerning them which are to enter into the design of a database 2.2.1 entity and entity set let e denote an entity which exists in our minds entities are classified into different entity sets such as employee project and department there is a predicate associated with each entity set to test whether an entity belongs to it for example if we know an entity is in the entity set employee then we know that it has the properties common to the other entities in the entity set employee among these properties is the aforementioned test predicate let ri denote entity sets note that entity sets may not be mutually disjoint for example an entity which belongs to the entity set maleperson also belongs to the entity set person in this case male-person is a subset of person 2.2.2 relationship role and relationship set consider associations among entities a relationship set ri is a mathematical relation [5 among n entities acm transactions on dstabase systems vol 1 no 1 march 1976.

[close]

p. 4

12 p p s chen each taken from an entity set gel,e2 e 1el e el e2 e e2 e e en and each tuple of entities [el et e is a relationship note that the et in the above definition may not be distinct for example a marriage is a relationship between two entities in the entity set person the role of an entity in a relationship is the function that it performs in the relationship llhusband and wife are roles the ordering of entities in the definition of relationship note that square brackets were used can be dropped if roles of entities in the relationship are explicitly stated as follows rr/er rs/e2 r e where ri is the role of ei in the relationship 2.2.3 attribute value and value set the information about an entity or a relationship is obtained by observation or measurement and is expressedby a set of attribute-value pairs 3 red peter and johnson are values values are classified into different va2ue sets such as feet color first-name and last-name there is a predicate associated with each value set to test whether a value belongs to it a value in a value set may be equivalent to another value in a different value set for example 12 in value set inch is equivalent to 1 in value set feet an attribute can be formally defined as a function which maps from an entity set or a relationship set into a value set or a cartesian product of value sets f ei or ri vi or vi x vi x a x vi figure 2 illustrates someattributes defined on entity set person the attribute age maps into value set no-of-years an attribute can map into a cartesian product of value sets for example the attribute name maps into value sets first-name and last-name note that more than one attribute may map from the same entity set into the same value set or same group of value sets for example name and alternative-name map from the entity set employee into value sets first-name and last-name therefore attribute and value set are different concepts although they may have the same name in some cases for example employee-no maps from employee to value set employee-no this distinction is not clear in the network model and in many existing data management systems also note that an attribute is defined as a function therefore it maps a given entity to a single value or a single tuple of values in the caseof a cartesian product of value sets note that relationships also have attributes consider the relationship set project-worker figure 3 the attribute percentage-of-time which is the portion of time a particular employee is committed to a particular project is an attribute defined on the relationship set project-worker it is neither an attribute of employee nor an attribute of project since its meaning depends on both the employee and project involved the concept of attribute of relationship is important in understanding the semantics of data and in determining the functional dependenciesamong data 2.2.4 conceptual information structure we are now concerned with how to organize the information associated with entities and relationships the method proposed in this paper is to separate the information about entities from the inforacm tranaaotions on database systems vol 1 no 1 marah 1076.

[close]

p. 5

the entity-relationship entity ei employee set attributes value i employee-no sets model l 13 f4 age v4 no-of-years fig 2 attributes defined on the entity set person mation about relationships we shall see that this separation is useful in identifying functional dependencies among data figure 4 illustrates in table form the information about entities in an entity set each row of values is related to the same entity and each column is related to a value set which in turn is related to an attribute the ordering of rows and columns is insignificant figure 5 illustrates information about relationships in a relationship set note that each row of values is related to a relationship which is indicated by a group of entities each having a specific role and belonging to a specific entity set note that figures 4 and 2 and also figures 5 and 3 are different forms of the same information the table form is used for easily relating to the relational model acm transaotions on database systems vol 1 no 1 maroh 1976.

[close]

p. 6

14 entity p p s chen sets relationship sets attribute value set ek employee ej project fig 3 attributes defined on the relationship set project-worker 2.3 information structure level 2 the entities relationships and values at level 1 see figures 2-5 are conceptual objects in our minds i.e we were in the conceptual realm [ls 27-j at level 2 we consider representations of conceptual objects we assume that there exist direct representations of values in the following we shall describe how to represent entities and relationships 2.3.1 primary key in figure 2 the values of attribute employee-no can be used to identify entities in entity set employee if each employee has a different employee number it is possible that more than one attribute is needed to identify the entities in an entity set it is also possible that several groups of attributes may be used to identify entities basically an entity key is a group of attributes such that the mapping from the entity set to the corresponding group of value sets is one-to-one if we cannot find such one-to-one mapping on available data or if simplicity in identifying entities is desired we ma.y define an artificial attribute and a value set so that such mapping is possible in the case where acm transactions on database systems,vol 1 no 1 march 1976.

[close]

p. 7

the entity-relationship model 15 age i kzei ino-of-years iiii peter jones sam 31 jones 41 25 iiie2 i2 3378 23 mary 32 chen `24 barb 33 chen 42 23 i r l i l i i fig 4 information about entities in an entity set table form r i role worker l 1 i i project 1 1 i i i f relationship 1 pe~ce+dgg-of i i attribute r entity set 4 r-ei employee i i i e project i vk i percentage value set l fig 5 information about relationships in a relationship set table form acm transactions on detsbase systems vol 1 no 1 march 1976.

[close]

p. 8

16 p p s chen empoyee-no employee-no last name no-of-years fig 6 representingentities by values employeenumbers several keys exist we usually choose a semantically meaningful key as the entity primary key pk figure 6 is obtained by merging the entity set employee with value set employee-no in figure 2 we should notice some semantic implications of figure 6 each value in the value set employee-no represents an entity employee attributes map from the value set employee-no to other value sets also note that the attribute employee-no maps from the value set employee-no to itself 2.3.2 entity/relationship relations information about entities in an entity set can now be organized in a form shown in figure 7 note that figure 7 is similar to figure 4 except that entities are represented by the values of their primary keys the whole table in figure 7 is an entity relation and each row is an entity tuple since a relationship is identified by the involved entities the primary key of a relationship can be represented by the primary keys of the involved entities in acm transactions on database systems vol 1 no 1 maroh 1976.

[close]

p. 9

the entity-relationship model 17 attribute value set domain entity tuple employee-no alternativename `ca ya age i no-of-years employee-no i i 2566 3378 fig 7 regular entity relation employee figure 8 the involved entities are represented by their primary keys employeeno and project-no the role names provide the semantic meaning for the values in the corresponding columns note that employee-no is the primary key for the involved entities in the relationship and is not an attribute of the relationship percentage-of-time is an attribute of the relationship the table in figure 8 is a relationship relation and each row of values is a relationship tuple in certain cases the entities in an entity set cannot be uniquely identified by the values of their own attributes thus we must use a relationships to identify them for example consider dependents of employees dependents are identified by their names and by the values of the primary key of the employees supporting them i.e by their relationships with the employees note that in figure 9 entity name role relation attribute project-no fig 8 regular relationship relation project-worker acm transactions on database systems vol 1 no 1 march 1976.

[close]

p. 10

18 p p s chen entity relation role entity attribute name t 2 i5 5 i2 _t employee-no employee-no 2566 2173 name first-name victor george age no-of-years 3 6 relationship attribute relationship attribute value set domain entity tuple fig 9 a weak entity relat,ion dependent employee-no is not an attribute of an entity in the set dependent but is the primary key of the employees who support dependents each row of values in figure 9 is an entity tuple with employee-no and name as it.s primary key the whole table is an entity relation theoretically any kind of relationship may be used to identify entities for simplicity we shall restrict ourselves to the use of only one kind of relationship the binary relationships with 1:n mapping in which the existence of the n entities on one side of t,he relationship depends on the existence of one entity on the other side of the relationship for example one employee may have n 0 1 2 dependents and the existence of the dependents depends on the existence of the corresponding employee this method of identification of entities by relationships with other entities can be applied recursively until the entities which can be identified by their own attribute values are reached for example the primary key of a department in a company may consist of the department number and the primary key of the division which in turn consists of the division number and the name of the company therefore we have two forms of entity relations if relationships are used for identifying the entities we shall call it a weak entity relation figure 9 if relationships are not used for identifying the entities we shall call it a regular entity relation figure 7 similarly we also have two forms of relationship relations if all entities in the relationship are identified by their own attribute values we shall call it a regular rehtionship relation figure 8 if some entities in the relationship are identified by other relationships we shall call it a weak relationship relation for example any relationships between dependent entities and other entities will result in weak relationship relations since a dependent entity is identified by its name and its relationship with an employee entity the distinction between regular entity/relationship relations and weak entity/relationship relations will be useful in maintaining data integrity acm tranmctions on database systems vol 1 no 1 march 1976.

[close]

p. 11

the entity-relationship model 19 entity set relationship set entity set fig 10 a simple entity-relationship diagram 3 entity-relationship data description diagram and and inclusion of semantics in manipulation diagram 3.1 system analysis using the entity-relationship in this section we introduce a diagrammatic technique for exhibiting entities and relationships the entity-relationship diagram figure 10 illustrates the relationship set project-worker and the entity sets employee and project using this diagrammatic technique each entity set is represented by a rectangular box and each relationship set is represented by a diamond-shaped box the fact that the relationship set project-worker is defined on the entity sets employee and project is represented by the lines connecting the rectangular boxes the roles of the entities in the relationship are stated department supplier employee fig 11 an entity-relationship diagram for analysis of information in a manufacturing firm acm trsnsaotions on database systems vol 1 no 1 march 1976.

[close]

p. 12

20 p p s chen figure 11 illustrates a more complete diagram of some entity sets and relationship sets which might be of interest to a manufacturing company department employee dependent project supplier and part are entity sets department-employee employee-dependent projectworker project-manager supplier-project-part project-part and component are relationship sets the component relationship describes what subparts and quantities are needed in making superparts the meaning of the other relationship sets need not be explained several important characteristics about relationships in general can be found in figure 11 1 a relationship set may be defined on more than two entity sets for example the supplier-project-part relationship set is defined on three entity set,s supplier project and part 2 a relationship set may be defined on only one entity set for example the relationship set component is defined on one entity set part 3 there may be more than one relationship set defined on given entity sets for example the relationship sets project-worker and projectmanager are defined on the entity sets project and employee 4 the diagram can distinguish between 1 12,m 12 and 1 1 mappings the relationship set department-employee is a 1:n mapping that is one department may have 12n 0 1,2 employees and each employee works for only one department the relationship set project-worker is an m:n mapping that is each project may have zero one or more employees assigned to it and each employee may be assigned to zero one or more projects it is also possible to express 1 1 mappings such as the relationship set marriage information about the number of entities in each entity set which is allowed in a relationship set is indicated by specifying l m in the diagram the relational model and the entity set model2 do not include this type of information the network model cannot express a 1 1 mapping easily 5 the diagram can express the existence depedency of one entity type on another for example the arrow in the relationship set employee-dependent indicates that existence of an entit,y in the entity set dependent depends on the corresponding entity in the entity set employee that is if an employee leaves the company his dependents may no longer be of interest note that the entity set dependent is shown as a special rectangular box this indicates that at level 2 the information about entities in this set is organized as a weak entity relation using the primary key of employee as a part of its primary key 3.2 an example of a database design and description there are four steps in designing a database using the entity-relationship model 1 identify the entity sets and the relationship sets of interest 2 identify semantic information in the relationship sets such as whet.her a cert.ain relationship z this mapping information is included in diam ii [24-j acm transactions on database systems vol 1 no 1 march 1976.

[close]

p. 13

the entity-relationship model 21 set is an 1:n mapping 3 define the value sets and attributes 4 organize data into entity/relationship relations and decide primary keys let us use the manufacturing company discussedin section 3.1 as an example the results of the first two steps of database design are expressed in an entityrelationship diagram as shown in figure 11 the third step is to define value sets and attributes see figures 2 and 3 the fourth step is to decide the primary keys for the entities and the relationships and to organize data as entity/relationship relations note that each entity/relationship set in figure 11 has a corresponding entity/relationship relation we shall use the names of the entity sets at level 1 as the names of the corresponding entity/relationship relations at level 2 as long as no confusion will result at the end of the section we illust,rate a schema data definition for a small part of the database in the above manufacturing company example the syntax of the data definition is not important note that value sets are defined with specifications of representations and allowable values for example values in employee-no are represented as 4-digit integers and range from 0 to 2000 we then declare three entity relations employee project and dependent the attributes and value sets defined on the entity sets as well as the primary keys are stated dependent is a weak entity relation since it uses employee.pk as part of its primary key we also declare two relationship relations project-worker and employee-dependent the roles and involved entities in the relationships are specified we use employee.pk to indicate the name of the entity relation employee and whatever attributevalue-set pairs are used as the primary keys in that entity relation the maximum number of entities from an entity set in a relation is stated for example projectworker is an m:n mapping we may specify the values of m and n we may also specify the minimum number of entities in addition to the maximum number employee-dependent is a weak relationship relation since one of the related entity relations dependent is a weak entity relation note that the existence dependenceof the dependents on the supporter is also stated declare value-sets employee-no first-name last-name no-of-years project-no percentage declare representation integer character 4 8 allowable-values 0,200o all all ww 1,500 0,100.00 character 10 integer 3 integer fixed 3 5.2 employee regular entity relation attribute/value-set employee-no/employee-no name first-name last-name alternativename first-name,last-name age/no-of-years primary key employee-no acm transactiona on database systems vol 1 no 1 march 1976.

[close]

p. 14

22 declare p p s chen regular entity relation project attribute/value-set project-no/project-no primary key project-no declare regular relationship relation project-worker role/entity-relation.pk/max-no-of-entities worker/employee.pk/m project/project.pk/n attribute/value-set percentage-of-time/percentage declare weak relationship relation employee-dependent m:n mapping role/entity-relation.pk/max-no-of-entities supporter/employee.pk/l dependent/dependent.pk/n existence existence declare weak entity of dependent of supporter relation dependent depends on attribute/value-set name/first-name age/no-of-years primary key name employee.pk through employee-dependent 3.3 implications on data integrity some work has been done on data integrity for other models [s 14 16 28-j with explicit concepts of entity and relationship the entity-relationship model will be useful in understanding and specifying constraints for maintaining data integrity for example there are three major kinds of constraints on values 1 constraints on allowable values for a value set this point was discussed in defining the schema in section 3.2 2 constraints on permitted values for a certain attribute in some cases not all allowable values in a value set are permitted for some attributes for example we may have a restriction of ages of employees to between 20 and 65 that is agee e 20,65 where e e employee note that we use the level 1 notations to clarify the semantics since each entity relationship set has a corresponding entity/relationship relation the above expression can be easily translated into level 2 notations 3 constraints on e&sting values in the database there are two types of constraints i constraints between sets of existing values for example {namee acm transactions 1 e e male-person j2 namee 1e e person on d&abase systems vol 1 no 1 march 1976.

[close]

p. 15

the entity-relationship model 23 ii constraints between particular values for example taxe budget salarye e e employee or ~budgetei where ei e company ei e department and [e ei e company-department of information retrieval requests 3.4 semantics and set operations the semantics of information retrieval requests become very clear if the requests are based on the entity-relationship model of data for clarity we first discuss the situation at level 1 conceptually the information elements are organized as in figures 4 and 5 on figures 2 and 3 many information retrieval requests can be considered as a combination of the following basic types of operations 1 selection of a subset of values from a value set 2 selection of a subset of entities from an entity set i.e selection of certain rows in figure 4 entities are selected by stating the values of certain attributes i.e subsets of value sets and/or their relationships with other entities 3 selection of a subset of relationships from a relationship set i.e selection of certain rows in figure 5 relationships are selected by stating the values of certain attributes and/or by identifying certain entities in the relationship 4 selection of a subset of attributes i.e selection of columns in figures 4 and 5 an information retrieval request like what are the ages of the employees whose weights are greater than 170 and who are assigned to the project with projectno 254 can be expressed as {agee 1 e e employee weighte 170 [e ej e project-worker ej e project project-noej 254 or {ageemployee weightemployee 170 [employee,project e project-worker project-noemployee 254 to retrieve information as organized in figure 6 at level 2 entities and relationships in 2 and 3 should be replaced by entity pk and relationship pk the above information retrieval request can be expressed as iageemployee.pk weightemployee.pk worker/employee.pk,project/project.pk project-no project.pk 254 170 e project-worker.pk to retrieve information as organized in entity/relationship 8 and 9 we can express it in a sequel-like language [s select from where age employee weight 170 relations figures 7 acm transactions on database systems vol 1 no 1 march 1976.

[close]