To be valuable, a database must be organized so that users can retrieve, analyze, and understand the data they need. A key to designing an effective database is the data model. A data model is a diagram that represents entities in the database and their relationships. An entity is a person, place, thing, or event—such as a customer, an employee, or a product—about which informa-tion is maintained. Entities can typically be identifi ed in the user’s work environment. A record generally describes an entity. An instance of an entity is a specifi c, unique representation of the entity. For example, an instance of the entity STUDENT would be a particular student.
Each characteristic or quality of a particular entity is called an attribute. For example, if our entities were a customer, an employee, and a product, entity attributes would include cus-tomer name, employee number, and product color.
Every record in a fi le must contain at least one fi eld that uniquely identifi es that record so that it can be retrieved, updated, and sorted. This identifi er fi eld is called the primary key.
For example, a student record in a U.S. university would use a unique student number as its primary key. (Note: In the past, your Social Security number served as the primary key for your student record. However, for security reasons, this practice has been discontinued.) In some cases, locating a particular record requires the use of secondary keys. A secondary key is another fi eld that has some identifying information but typically does not identify the record with complete accuracy. For example, the student’s major might be a secondary key if a user wanted to identify all of the students majoring in a particular fi eld of study. It should not be the primary key, however, because many students can have the same major. Therefore, it cannot uniquely identify an individual student.
Entity-Relationship Modeling. Designers plan and create the database through the process of entity-relationship modeling, using an entity-relationship diagram. There are many ap-proaches to ER diagramming. You will see one particular approach here, but there are others.
The good news is that if you are familiar with one version of ER diagramming, then you will be able to easily adapt to any other version.
ER diagrams consist of entities, attributes, and relationships. Entities are pictured in boxes, and relationships are represented as diamonds. The attributes for each entity are listed, and the primary key is underlined.
Relationships illustrate an association between two entities. A relationship has a name that is a verb. Cardinality and modality are the indicators of the business rules in a relationship.
Cardinality refers to the maximum number of times an instance of one entity can be associ-ated with an instance in the relassoci-ated entity. Modality refers to the minimum number of times an instance of one entity can be associated with an instances in the related entity. Cardinality
147
SECTION 5.3 The Database Approach can be 1 or Many, and its symbol is placed on the outside of
the relationship line, closest to the entity. Modality can be 1 or 0, and its symbol is placed on the inside of the relation-ship line, next to the cardinality symbol. Figure 5.3 displays the cardinality and modality symbols. Figure 5.4 depicts an ER diagram.
As defi ned earlier, an entity is a person, place, or thing that can be identifi ed in the users’ work environment. For example, consider student registration at a university. Stu-dents register for courses, and they also register their cars for parking permits. In this example, STUDENT, PARKING PERMIT, CLASS, and PROFESSOR are entities, as illus-trated in Figure 5.4.
Entities of a given type are grouped in entity classes. In our example, STUDENT, PARKING PERMIT, CLASS, and PROFESSOR are entity classes. An instance of an entity class is the representation of a particular entity. There-fore, a particular STUDENT (James Smythe, 145-89-7123) is an instance of the STUDENT entity class; a particular
parking permit (91778) is an instance of the PARKING PERMIT entity class; a particular class (76890) is an instance of the CLASS entity class; and a particular professor (Margaret Wilson, 115-65-7632) is an instance of the PROFESSOR entity class.
Entity instances have identifi ers, which are attributes (attributes and identifi ers are syn-onymous) that are unique to that entity instance. For example, STUDENT instances can be identifi ed with Student Identifi cation Number; PARKING PERMIT instances can be identifi ed with Permit Number; CLASS instances can be identifi ed with Class Number;
and PROFESSOR instances can be identifi ed with Professor Identifi cation Number. These identifi ers (or primary keys) are underlined on ER diagrams, as in Part b of Figure 5.4.
Entities have attributes, or properties, that describe the entity’s characteristics. In our example, examples of attributes for STUDENT are Student Name and Student Address.
Examples of attributes for PARKING PERMIT are Student Identifi cation Number and Car Type. Examples of attributes for CLASS are Class Name, Class Time, and Class Place. Exam-ples of attributes for PROFESSOR are Professor Name and Professor Department. (Note that each course at this university has one professor—no team teaching.)
Why is Student Identifi cation Number an attribute of both the STUDENT and PARK-ING PERMIT entity classes? That is, why do we need the PARKPARK-ING PERMIT entity class?
If you consider all of the interlinked university systems, the PARKING PERMIT entity class is needed for other applications, such as fee payments, parking tickets, and external links to the state Department of Motor Vehicles.
Entities are associated with one another in relationships, which can include many entities.
(Remember that relationships are represented by diamonds on ER diagrams.) The number of entities in a relationship is the degree of the relationship. Relationships between two items are called binary relationships. There are three types of binary relationships: one, one-to-many, and many-to-many.
In a one-to-one (1:1) relationship, a entity instance of one type is related to a single-entity instance of another type. Figure 5.4a labels STUDENT–PARKING PERMIT as a 1:1 relationship. The relationship means that a student can have a parking permit but does not need to have one. (Clearly, if a student does not have a car, then he or she will not need a parking permit.) Note that the relationship line on the PARKING PERMIT side shows zero or one—that is, a cardinality of 1, and a modality of 0. On the STUDENT side of the relationship, only one parking permit can be assigned to one student. Note that the relation-ship line on the STUDENT side shows one and only one—that is, a cardinality of 1 and a modality of 1.
The second type of relationship, one-to-many (1:M), is represented by the CLASS–
PROFESSOR relationship in Figure 5.4a. This relationship means that a professor can have
e
FIGURE 5.3 Cardinality and modality symbols.
148 CHAPTER 5 Data and Knowledge Management
Can have
Relationships
Keyfield Professor
Entities Key
A student can have many classes.
(a) ER diagram
(b) Entities, Attributes, and Identifiers A class can have
many students.
A professor can have many classes.
A class can have only 1 professor.
A student can have only 1 parking permit.
A parking permit can have only 1 student.
1
M
1
1:1
M:M
1:M
Can have
Can have
M
M
1 Class
Parking Permit
Student Identification Number STUDENT
Student Name Student Address
Permit Number PARKING PERMIT
Student Identification Number Car Type
Class Number CLASS
Class Name Class Time Class Place
Professor Identification Number PROFESSOR
Professor Name Professor Department Student
FIGURE 5.4 Entity-relationship diagram model.
one or more courses, but each course can have only one professor. Note that the relationship line on the PROFESSOR side shows one and only one—that is, a cardinality of 1 and a modality of 1. In contrast, the relationship line on the CLASS side shows one or many—that is, a cardinality of Many and a modality of 1.
The third type of relationship, many-to-many (M:M), is represented by the STUDENT–
CLASS relationship in Figure 5.4a. This M:M relationship indicates that a student can have one or more courses, and a course can have one or more students. Note that the relationship line on the STUDENT side shows one or more—that is, a cardinality of Many and a modality of 1.
149
SECTION 5.4 Database Management Systems Further, the relationship line of the CLASS side also shows one or more—that is, a cardinality
of Many and a modality of 1.
ER modeling is valuable because it allows database designers to communicate with users throughout the organization to ensure that all entities and the relationships among the entities are represented. This process underscores the importance of taking all users into account when designing organizational databases. Notice that all entities and relationships in our example are labeled in terms that users can understand. Now that you understand how a database is designed, you can turn your attention to database management systems.