Modeling Data in the Organization LEARNING OBJECTIVES After studying this chapter, you should be able to: ? Concisely define each of the following key terms: entity-relationship model (E-R model), entity-relationship diagram (E-R diagram, or ERD), business rule, entity, entity type, entity instance, strong entity type, weak entity type, identifying owner, identifying relationship, attribute, required attribute, optional attribute, composite attribute, simple (or atomic) attribute, multivalued attribute, derived attribute, identifier, composite identifier, relationship type, relationship instance, associative entity, degree, unary relationship, binary relationship, ternary relationship, cardinality constraint, minimum cardinality, maximum cardinality, and time stamp

Subject:BusinessPrice:16.86 Bought3

Share With

Modeling Data in the Organization LEARNING OBJECTIVES After studying this chapter, you should be able to: ? Concisely define each of the following key terms: entity-relationship model (E-R model), entity-relationship diagram (E-R diagram, or ERD), business rule, entity, entity type, entity instance, strong entity type, weak entity type, identifying owner, identifying relationship, attribute, required attribute, optional attribute, composite attribute, simple (or atomic) attribute, multivalued attribute, derived attribute, identifier, composite identifier, relationship type, relationship instance, associative entity, degree, unary relationship, binary relationship, ternary relationship, cardinality constraint, minimum cardinality, maximum cardinality, and time stamp. ? State reasons why many system developers believe that data modeling is the most important part of the systems development process. ? Write good names and definitions for entities, relationships, and attributes. ? Distinguish unary, binary, and ternary relationships and give a common example of each. ? Model each of the following constructs in an E-R diagram: composite attribute, multivalued attribute, derived attribute, associative entity, identifying relationship, and minimum and maximum cardinality constraints. ? Draw an E-R diagram to represent common business situations. ? Convert a many-to-many relationship to an associative entity type. ? Model simple time-dependent data using time stamps and relationships in an E-R diagram. Visit www.pearsonhighered.com/ hoffer to view the accompanying video for this chapter. INTRODUCTION You have already been introduced to modeling data and the entity-relationship (E-R) data model through simplified examples in Chapter 1. (You may want to review, for example, the E-R models in Figures 1-3 and 1-4.) In this chapter, we formalize data modeling based on the powerful concept of business rules and describe the E-R data model in detail. This chapter begins your journey of learning how to design and use databases. It is exciting to create information systems that run organizations and help people do their jobs well. Business rules, the foundation of data models, are derived from policies, procedures, events, functions, and other business objects, and they state constraints on the organization. Business rules represent the language and fundamental 45 46 structure of an organization (Hay, 2003). Business rules formalize the understanding of the organization by organization owners, managers, and leaders with that of information systems architects. Business rules are important in data modeling because they govern how data are handled and stored. Examples of basic business rules are data names and definitions. This chapter explains guidelines for the clear naming and definition of data objects in a business. In terms of conceptual data modeling, names and definitions must be provided for the main data objects: entity types (e.g., Customer), attributes (Customer Name), and relationships (Customer Places Orders). Other business rules may state constraints on these data objects. These constraints can be captured in a data model, such as an entityrelationship diagram, and associated documentation. Additional business rules govern the people, places, events, processes, networks, and objectives of the organization, which are all linked to the data requirements through other system documentation. After decades of use, the E-R model remains the mainstream approach for conceptual data modeling. Its popularity stems from factors such as relative ease of use, widespread computer-aided software engineering (CASE) tool support, and the belief that entities and relationships are natural modeling concepts in the real world. The E-R model is most used as a tool for communications between database designers and end users during the analysis phase of database development (described in Chapter 1). The E-R model is used to construct a conceptual data model, which is a representation of the structure and constraints of a database that is independent of software (such as a database management system). Some authors introduce terms and concepts peculiar to the relational data model when discussing E-R modeling; the relational data model is the basis for most database management systems in use today. In particular, they recommend that the E-R model be completely normalized, with full resolution of primary and foreign keys. However, we believe that this forces a premature commitment to the relational data model. In today’s database environment, the database may be implemented with object-oriented technology or with a mixture of object-oriented and relational technology. Therefore, we defer discussion of normalization concepts to Chapter 4. The E-R model continues to evolve, but unfortunately there is not yet a standard notation for E-R modeling. Song et al. (1995) present a side-by-side comparison of 10 different E-R modeling notations, explaining the major advantages and disadvantages of each approach. Because data modeling software tools are now commonly used by professional data modelers, we adopt for use in this text a variation of the notation used in professional modeling tools. In an actual work environment, you may not have to develop a data model from scratch. Because of the increased acceptance of packaged software (for example, enterprise resource planning with a predefined data model) and purchased business area or industry data models (which we discuss in Chapter 3), your job of data modeling has a jump start. This is good because such components and patterns give you a starting point based on generally accepted practices. However, your job is not done for several reasons: 1. There are still many times when a new, custom-built application is being developed along with the associated database. The business rules for the business area supported by this application need to be modeled. 2. Purchased applications and data models need to be customized for your particular setting. Predefined data models tend to be very extensive and complex; hence, they require significant data modeling skill to tailor the models to be effective and efficient in a given organization. Although this effort can be much faster, thorough, and accurate than starting from scratch, the ability to understand a particular organization to match the data model to its business rules is an essential task. 47 In this chapter, we present the main features of E-R modeling, using common notation and conventions. We begin with a sample E-R diagram, including the basic constructs of the E-R model—entities, attributes, and relationships—and then we introduce the concept of business rules, which is the foundation for all the data modeling constructs. We define three types of entities that are common in E-R modeling: strong entities, weak entities, and associative; a few more entity types are defined in Chapter 3. We also define several important types of attributes, including required and optional attributes, single- and multivalued attributes, derived attributes, and composite attributes. We then introduce three important concepts associated with relationships: the degree of a relationship, the cardinality of a relationship, and participation constraints in a relationship. We conclude with an extended example of an E-R diagram for Pine Valley Furniture Company. THE E-R MODEL: AN OVERVIEW An entity-relationship model (E-R model) is a detailed, logical representation of the data for an organization or for a business area. The E-R model is expressed in terms of entities in the business environment, the relationships (or associations) among those entities, and the attributes (or properties) of both the entities and their relationships. An E-R model is normally expressed as an entity-relationship diagram (E-R diagram, or ERD), which is a graphical representation of an E-R model. Entity-relationship model (E-R model) A logical representation of the data for an organization or for a business area, using entities for categories of data and relationships for associations between entities. Entity-relationship diagram (E-R diagram, or ERD) Sample E-R Diagram To jump-start your understanding of E-R diagrams, Figure 2-1 presents a simplified E-R diagram for a small furniture manufacturing company, Pine Valley Furniture Company. (This figure, which does not include attributes, is often called an enterprise data model, Supplies Submits SUPPLIER CUSTOMER ORDER Submitted By is/must Sends A graphical representation of an entity-relationship model. Requests Supplied By may Sent By Includes Requested On many SHIPMENT Used In ITEM Uses Included On PRODUCT Cardinalities Key ENTITY TYPE FIGURE 2-1 Relationship Sample E-R diagram Mandatory One Optional One Mandatory Many Optional Many 48 which we introduced Chapter 1.) A number of suppliers supply and ship different items to Pine Valley Furniture. The items are assembled into products that are sold to customers who order the products. Each customer order may include one or more lines corresponding to the products appearing on that order. The diagram in Figure 2-1 shows the entities and relationships for this company. (Attributes are omitted to simplify the diagram for now.) Entities (the objects of the organization) are represented by the rectangle symbol, whereas relationships between entities are represented by lines connecting the related entities. The entities in Figure 2-1 include the following: CUSTOMER A person or an organization that has ordered or might order products. Example: L. L. Fish Furniture. PRODUCT A type of furniture made by Pine Valley Furniture that may be ordered by customers. Note that a product is not a specific bookcase, because individual bookcases do not need to be tracked. Example: A 6-foot, 5-shelf, oak bookcase called O600. ORDER The transaction associated with the sale of one or more products to a customer and identified by a transaction number from sales or accounting. Example: The event of L. L. Fish buying one product O600 and four products O623 on September 10, 2010. ITEM A type of component that goes into making one or more products and can be supplied by one or more suppliers. Example: A 4-inch ball-bearing caster called I-27–4375. SUPPLIER Another company that may provide items to Pine Valley Furniture. Example: Sure Fasteners, Inc. SHIPMENT The transaction associated with items received in the same package by Pine Valley Furniture from a supplier. All items in a shipment appear on one bill-oflading document. Example: The receipt of 300 I-27-4375 and 200 I-27-4380 items from Sure Fasteners, Inc., on September 9, 2010. Note that it is important to clearly define, as metadata, each entity. For example, it is important to know that the CUSTOMER entity includes persons or organizations that have not yet purchased products from Pine Valley Furniture. It is common for different departments in an organization to have different meanings for the same term (homonyms). For example, Accounting may designate as customers only those persons or organizations that have ever made a purchase, thus excluding potential customers, whereas Marketing designates as customers anyone they have contacted or who has purchased from Pine Valley Furniture or any known competitor. An accurate and thorough ERD without clear metadata may be interpreted in different ways by different people. We outline good naming and definition conventions as we formally introduce E-R modeling throughout this chapter. The symbols at the end of each line on an ERD specify relationship cardinalities, which represent how many entities of one kind relate to how many entities of another kind. On examining Figure 2-1, we can see that these cardinality symbols express the following business rules: 1. A SUPPLIER may supply many ITEMs (by “may supply,” we mean the supplier may not supply any items). Each ITEM is supplied by any number of SUPPLIERs (by “is supplied,” we mean that the item must be supplied by at least one supplier). See annotations in Figure 2-1 that correspond to underlined words. 2. Each ITEM must be used in the assembly of at least one PRODUCT and may be used in many products. Conversely, each PRODUCT must use one or more ITEMs. 3. A SUPPLIER may send many SHIPMENTs. However, each shipment must be sent by exactly one SUPPLIER. Notice that sends and supplies are separate concepts. A SUPPLIER may be able to supply an item but may not yet have sent any shipments of that item. 4. A SHIPMENT must include one (or more) ITEMs. An ITEM may be included on several SHIPMENTs. 49 5. A CUSTOMER may submit any number of ORDERs. However, each ORDER must be submitted by exactly one CUSTOMER. Given that a CUSTOMER may not have submitted any ORDERs, some CUSTOMERs must be potential, inactive, or some other customer possibly without any related ORDERs. 6. An ORDER must request one (or more) PRODUCTs. A given PRODUCT may not be requested on any ORDER or may be requested on one or more orders. There are actually two business rules for each relationship, one for each direction from one entity to the other. Note that each of these business rules roughly follows a certain grammar: For example, rule 5 is This grammar gives you a standard way to put each relationship into a natural English business rule statement. E-R Model Notation The notation we use for E-R diagrams is shown in Figure 2-2. As indicated in the previous section, there is no industry-standard notation (in fact, you saw a slightly simpler Entity types Attributes ENTITY NAME Identifier Partial identifier Weak Strong Optional [Derived] {Multivalued} Composite( , , ) Associative Relationship degrees Unary Binary Ternary Relationship cardinality Mandatory one FIGURE 2-2 Mandatory many Basic E-R notation Optional one Optional many 50 notation in Chapter 1). The notation in Figure 2-2 combines most of the desirable features of the different notations that are commonly used in E-R drawing tools today and also allows us to model accurately most situations that are encountered in practice. We introduce additional notation for enhanced entity-relationship models (including classsubclass relationships) in Chapter 3. In many situations, however, a simpler E-R notation is sufficient. Most drawing tools, either stand-alone ones such as Microsoft Visio or SmartDraw (which we use in the video associated with this chapter) or those in CASE tools such as Oracle Designer, CA ERwin, or PowerDesigner, do not show all the entity and attribute types we use. It is important to note that any notation requires special annotations, not always present in a diagramming tool, to show all the business rules of the organizational situation you are modeling. We will use the Visio notation for a few examples throughout the chapter and at the end of the chapter so that you can see the differences. MODELING THE RULES OF THE ORGANIZATION Now that you have an example of a data model in mind, let’s step back and consider more generally what a data model is representing. We will see in this and the next chapter how to use data models, in particular the entity-relationship notation, to document rules and policies of an organization. In fact, documenting rules and policies of an organization that govern data is exactly what data modeling is all about. Business rules and policies govern creating, updating, and removing data in an information processing and storage system; thus, they must be described along with the data to which they are related. For example, the policy “every student in the university must have a faculty adviser” forces data (in a database) about each student to be associated with data about some student adviser. Also, the statement “a student is any person who has applied for admission or taken a course or training program from any credit or noncredit unit of the university” not only defines the concept of “student” for a particular university but also states a policy of that university (e.g., implicitly, alumni are students, and a high school student who attended a college fair but has not applied is not a student, assuming the college fair is not a noncredit training program). Your job as a database analyst is to that govern data tion systems developers and users Business rule A statement that defines or constrains some aspect of the business. It is intended to assert business structure or to control or influence the behavior of the business. - More formally, a business rule is “a statement that defines or constrains some aspect of the business. It is intended to assert business structure or to control or influence the behavior of the business . . . rules prevent, cause, or suggest things to happen” (GUIDE Business Rules Project, 1997). For example, the following two statements are common expressions of business rules that affect data processing and storage: completed the prerequisites for that course.” due account balance.” - There are various kinds of business rules that an E-R model can represent, and many it cannot. We will indicate throughout this and the following chapter where business rules are being represented in an E-R model. Data Names and Definitions Fundamental to understanding and modeling data are naming and defining data objects. Data objects must be named and defined before they can be used unambiguously in a model of organizational data. In the entity-relationship notation you will learn in this chapter, you have to give entities, relationships, and attributes clear and distinct names and definitions. 51 We will provide specific guidelines for naming entities, relationships, and attributes as we develop the entity-relationship data model, but there are some DATA NAMES Relate to business, not technical (hardware or software), characteristics; so, good names. Be meaningful, almost to the point of being self-documenting (i.e., the definition will refine and explain the name without having to state the essence of the object’s meaning); you should avoid using generic words such as has, is, person, or it. Be unique, from the name used for every other distinct data object; words should be included in a data name if they distinguish the data object from other similar data objects (e.g., Home Address versus Campus Address). Be readable, so that the name is structured as the concept would most naturally be said (e.g., Grade Point Average is a good name, whereas Average Grade Relative To A, although possibly accurate, is an awkward name). Be composed of words taken from an approved list; each organization often chooses a vocabulary from which significant words in data names must be chosen (e.g., maximum is preferred, never upper limit, ceiling, or highest); alternative, or alias names, also can be used as can approved abbreviations (e.g., CUST for CUSTOMER), and you may be encouraged to use the abbreviations so that data names are short enough to meet maximum length limits of database technology. Be repeatable, meaning that different people or the same person at different times should develop exactly or almost the same name; this often means that there is a standard hierarchy or pattern for names (e.g., the birth date of a student would be Student Birth Date and the birth date of an employee would be Employee Birth Date). Follow a standard syntax, meaning that the parts of the name should follow a standard arrangement adopted by the organization. 1. Preparing a definition of the data. (We talk about definitions next.) 2. Removing insignificant or illegal words (words not on the approved list for names); note that the presence of AND and OR in the definitio...