The first step in the E-R approach is to select the entities that will be used to model the enterprise. An entity is quite similar to an object, except that an entity does not have methods. It might be a concrete object in the real world, such as Nurse Paula, the Pajero Sport parked at 123 Main Street, or the Foreign Affair Building, or it might be an abstract object, such as the patient medical record account 123456789, the database course MK305, or the Engineering Faculty at UGM Yogyakarta.
Similar entities are aggregated into entity types. For instance, Kevin Cruise, Mary Hart, Joe Fitzgerald, and Giselle White might be aggregated into the entity type Person based on the fact that these entities represent humans. Kevin Cruise and Joe Fitzgerald might also belong to the entity type Patient because in our sample database these objects presumably represented patients. Similarly, Mary Hart and Giselle White might be classified as members of the entity type Nurse.
Attributes for describing entities
As with relations and objects, entities are described using attributes. Every attribute of an entity specifies a particular property of that entity. For instance, the Name attribute of a Person entity normally specifies a string of characters that denotes the real-world name of the person represented by that database entity. Similarly, the Age attribute specifies the number of times the Earth had circled around the Sun since the moment that real-world person was born.
Domains as the set of values
As in the relational model, the domain of an attribute specifies the set from which its value can be drawn. Unlike the relational model, however, E-R attributes can be set-valued. This means that the value of an attribute can be a set of values from the corresponding domain rather than a single value. For example, an entity type Person might have set-valued attributes ChildrenNames and Hobbies.
Key constraints
As with the relational model, it is useful to introduce the key constraints associated with entity types. A key constraint on an entity type, S, is a set of attributes, A, of S such that
- No two entities in S have the same values for every attribute in A (for instance, two different Patients entities cannot agree on both the Name and the MedicalNumber attributes).
- No proper subset of the attributes in A has property 1 (i.e., the set A is minimal with respect to this property).
This concept of entity keys is analogous to that of candidate keys in the relational model. One subtle point, though, is that attributes in the E-R approach can be set-valued and, in principle, such an attribute can be part of a key. However, in practice set-valued attributes that occur in keys are not very natural and often indicate poor design.
As with the relational model, we define the schema of an entity type to consist of the name of that type, the collection of the attributes, and the key constraints.
E-R diagram representation
Entity types are represented in E-R diagrams as rectangles, and their attributes are represented as ovals. Set-valued attributes are represented as double ovals. Figure below depicts one possible representation of the Person entity type. Note that in this picture Hobbies is specified as a set-valued attribute and IDCard is underlined to indicate that it is a key.
This simplicity might seem suspicious in view of the fact that entities can have set-valued attributes while relations cannot. How, then, can a set-valued attribute of an entity be turned into a single-valued attribute of the corresponding relation without violating the property of data atomicity of the relational model?
The answer is that each entity that has a set-valued attribute is represented in the translation by a set of tuples, one for each element in the attribute value.
The set-valued attribute Hobby is the trouble maker. To obtain a key of the relation in question, we must include the offending attribute. Thus, the key of the Person relation is {IDCard, Hobby}.
The following CREATE TABLE statement defines the schema for the Person relation.
CREATE TABLE Person (
IDCard INTEGER,
Name CHAR(20),
Address CHAR(50),
Hobby CHAR(10),
PRIMARY KEY (IDCard, Hobby) )
The preceding example is the first indication that the E-R approach alone is not a sufficient tool for guaranteeing good relational design. Next article will provide a host of objective criteria that can help the database designer evaluate the relational schema obtained by converting E-R diagrams into relations. In particular, the problem with the relation is that it is not in a certain “normal form” defined next article. That article proceeds to develop algorithms that can automatically rectify the problem by splitting such offending relations into smaller relations that are in a desired normal form.
