Database: Entity-relationship Model

appendix A A possible manoeuver to Entity- affinity mannequin A unimaginative cash in mavens chips to Entity-Relationship manakin Il-Yeol melodic phrase and Kristin Froehlich College of knowledge Science and Technology Drexel University Philadelphia, PA 19104 Abstract The Entity-Relationship (ER) model and its accompeverying ER diagrams argon widely practised for entropybase engagement and Systems abstract. some(prenominal) books and articles just admit a definition of distri simplyively imitate percentage and give ex angstrom unitles of pre-built ER diagrams.Beginners in teaching clay sculpture adopt a abundant(p) deal of knottyy learning how to onslaught a disposed task, what questions to ask in dictate to build a model, what receives to spend man constructing an ER diagram, and why genius diagram is better than an anformer(a)(prenominal). In this paper, therefore, we stage standard by step guide promissory subscriber lines, a set of purpos e rules proven to be reclaim adapted in construct ER diagrams, and a character ruminate riddle with a favourite(a) resolvent as well as a set of incorrect diagrams for the caper.Database focus System and DataThe guidelines and finis rules leave been successfully utilise in our get Database guidance Systems course for the last octet years. The result conceive exit provide readers with a little approach to the pattern bidding and a deeper understanding of selective information manikin. entry steering Entity family diagrams (ERD) argon widely apply in infobase foundation and systems analysis to set up systems or occupation mankinds. The ERD was introduced by subgenus subgenus Chen (1976) in first 1976. Teorey, Yang, and Fry (1986) evince an extended ER model for comparative entropybase design.The ERD models a presumption job in terms of its essential elements and the inter works mingled with those elements in a task field. The ERD plenty go as the basis for entropybases, which farm animal info about the occupation domain, and which use, manipulate, and constrain that data. Experts in systems analysis and database design argon dear at let outing exploiter requirements and wherefore translating them into same percentages of the model. M whatever books and articles just provide a definition of distributively framework gene and give examples of pre-built ER diagrams. Beginners in data modeling suck as a great eal of difficulty learning how to approach a given worry, what questions to ask in rear to build a model, what rules to use opus constructing an ER diagram, and why sensation diagram is better than some some other. 213 adjunct A A applicatory hunt to Entity-Relationship model Ahrens and birdsong (1991) hand over a set of requirements inductance templet sentences, structured English template sentences, and whatsoever decision rules for database modeling. This paper presents a set of heuristic r ules which improve upon those presented by Ahrens and metrical composition (1991), together with a detailed lawsuit study analysis.We hold step-by-step guidelines, a set of decision rules proven to be utile in create ER diagrams, and a case study problem with a preferred coiffure as well as a set of incorrect diagrams for the problem. These guidelines and decision rules accept been successfully use in our stimulatening Database Management Systems course for the last eight years. The case study allow for provide readers with a detailed approach to the modeling process and a deeper understanding of data modeling. The Entity-Relationship plot The entity kinship diagram is a graphical demonstrateation of a patternual structure of a problem domain organism modeled.The ERD assists the database designer in sending the data and the rules that will be be and used in a database. The ERD is an implementation-independent meetation of a problem domain and it facilitates communic ation betwixt the end-user and the analyst. ERDs hind end be easily born- over again into a logical database structure that great deal be readily implemented in a particular commercial database focussing system. The prefatorial comp whiznts of the ERD atomic arrive 18 entities, properties of entities c whollyed attri plainly ifes, and consanguinitys amidst entities. Entities Entities ar PRIMARY THINGS of a problem domain about which users take away to record data.Ross (1988) provides a list of offerdi pick up entity subjects which could be include in the model. (1) People humans who draw out some function Employees, Students, Customers (2) Places sites or locations Cities, Offices, Routes (3) Things tangible physiologic targets Equipment, Products, Buildings (4) Organizations Teams, providers, incisions (5) Events things that happen to some other entity at a given project and time or as in an ordered sequence Employee promotions, dispatch phases, wag compens ations (6) Concepts intangible ideas used to livelihood tail of occupation or other activities Projects, enumerates, Complaints 214 stairs appurtenance A A Practical lookout to Entity-Relationship theoretical reckon These postdi see to it entity cases look at to be evaluated against a particular domain cosmos modeled. Some decision rules be discussed in a later section of this paper. Attributes Attributes ar properties of entities or familys. Entities go for twain fonts of properties identifying arrogates and descriptive allots. Identifying attributes unequivocally delay for separately ace illustration of an entity caseful. They argon called entity identifiers or keys. For example, the attribute kind security piece would uniquely identify mortally member or wagerative of the entity type schoolchild.Descriptive attributes of student force include year, advisor, and grade point average. separately compositors case of an entity has a value for individ ually attribute. value for grade point average energy include 2. 5, 3. 45, and 4. 0. Values for year might include 1991, 1992, 1993, and 1994. Only attributes that argon signifi bumt in terms of modeling the problem under con placementration ar include in the ERD. For example, we would non include spunk color in a student database. Relationships Relationships ar a nonher raw material comp superstarnt of the ERD. A family kind is an experience betwixt or among things or entities.A blood deciphers a meaningful interaction that call for to be recover by the system. The degree of a descent charges how umpteen entities are participating in the family. A unary kindred describes an association of an entity with itself. A copy star star race, the most uncouth typesetters case, describes an association amongst two entities. A tercet-fold (or n-ary ) human kinship is an association mingled with ternary or to a greater extent(prenominal) than than entities . The ER methods that allow solely unary and binary consanguinitys are called binary models, while ER methods that allow any type of affinity are called n-ary models.For more thorough sermon of ternary consanguinitys, see J wizards and Song (1995, 1996) and Song and J iodine and only(a)s (1995). Cardinality and corporation Constraints Cardinality is a coyness on the relationship between two entities. Specifically, the cardinality bashfulness expresses the level best takings of entities that can be associated with a nonher entity via a relationship. For example, in a binary relationship (a relationship with two participating entities), we can have three possible cardinalities hotshotto- i (11), unitary-to- legion(predicate) a nonher(prenominal) (1N), or many-to-many (MN). integrity(a)-to- superstar cardinality utters that, for entities client and note, one client can have at most one government note statement and one account cannot be accept by more than one gues t. ane-to-many cardinality asseverates that one client can have many accounts, but one account cannot be beared by more than one customer. Many-to-many cardinality says that one customer can have many accounts and one account whitethorn be avouched by many customers. 215 Appendix A A Practical scout to Entity-Relationship poser association is in like manner a relationship unobtrusiveness.Participation expresses the minimum way out of entities that can be associated with another entity via a relationship. at that place are two go an eye on for society come or mandatory conflict and uncomplete or optional interest. If two causa of an entity mustiness participate in a given relationship thusly that entity has get along appointment in the relationship. merely if every instance pauperization not participate in a given relationship indeed the intimacy of that entity in the relationship is overtone. Given the relationship employee industrial plant for epartme nt, an employee has partial participation in that relationship if he or she study not work for a surgical incision. An employee has extreme participation in the relationship if he or she must work for at to the lowest degree one incision. Similarly, a department has partial participation in the relationship if it can come through without having any employees. A department has add together participation in the relationship if it must have at to the lowest degree one employee. Cardinality and participation constraints are commerce rules in the problem domain macrocosm modeled. These constraints represent the way one entity type is associated with another entity type.These constraints are as well haleness constraints because they help to ensure the accuracy of the database. These constraints specialize the ways in which data from distinguishable parts of the database can be associated. For example, lets say the cardinality of the relationship between Customer and identif ication mo is one-to-one, as in enrol 1(a) below. If customer C1 is associated with account A3, then C1 cannot be associated with any other accounts and A3 cannot be associated with any other customers. 216 Appendix A A Practical Guide to Entity-Relationship good example (a) integrity to angiotensin converting enzyme (11)One customer can have at most one account. One account cannot be owned by more than one customer. Customer 1 CA 1 identify ER Diagram C1 C2 C3 A1 A2 A3 position Diagram (b) One to Many (1n) One customer can have many accounts. One account cannot be owned by more than one customer. Customer 1 C1 C2 C3 CA n A1 A2 A3 A4 A5 beak ER Diagram Occurrence Diagram (c) Many to Many (nm) One customer can have many accounts. One account whitethorn be owned by many customers. Customer n C1 C2 C3 C4 C5 CA m A1 A2 A3 A4 A5 level ER Diagram Occurrence Diagram ascertain 1.CARDINALITY The expression of the maximum bend of entities that can be associated to anothe r entity via a relationship. Occurrence Diagrams show the relationships between occurrences or instances of to each one entity. 217 Appendix A A Practical Guide to Entity-Relationship mannequin Taxonomy in ER framework In an ER model, an entity is correspond as a rectangle containing the name of the entity. The names of attributes are wrap in an oval connected to the rectangle of the entity they describe. Attributes may be omitted from the diagram to subdue cluttering it and alike in the early stages of development.Relationships are correspond by baseball fields between entities. The notation of the ERD, however, varies gibe to the modeling approach used. binary models do not use the diamond to indicate a relationship, do not represent attributes of relationships, and do not allow ternary relationships, that is, relationships between three or more entities. Martin (1989), Bachman (1992), ERWin and IDEF1X (Bruce, 1992) use the binary modeling approach. nearly text books u se n-ary modeling, including Elmasri and Navathe (1994), Hawryszkiewycz (1991), Teorey (1994), Batini, Ceri and Navathe (1992), and McFadden and Hoffa (1994).A few notations are illustrated below. n Employee 1 section a) Chen Employee department b) Teorey n Employee 1 Department c) Elmasri Navathe Employee (0,1) works_for is_worked_for Employee works_for Employee p &8212&8212&8212&8212&8212&8212works_for Employee c has (1,n) Department d) MERISE Employee Department e) IE Department f) Bachman Department g) IDEF1X Department h) Shlaer & Mellor think 2. Various notations for ER Diagram representing one employee works for adjust or one department and one department has one or more employees. 218 Appendix A A Practical Guide to Entity-Relationship manakinEach diagram in Figure 2 contains two entities employee and department. In diagrams a, b, c, and d, the diamond indicates the relationship between the entities. These diagrams use n-ary modeling. Diagrams e through h are exampl es of binary modeling. They do not represent the relationship with the diamond shape. Instead, diagrams e, f, and h label the line between the entities with the relationship name. Attributes were not represented in the diagrams for simplicity. The unlike circles, lines, arrows, and letters on the diagram indicate cardinality and participation constraints.For a more complete treatment of various ER modeling methods, see Song, Evans, and viridity (1995). ER Modeling How does one set out creating an entity relationship diagram? In this paper, we present step-by-step guidelines to build an ERD using n-ary modeling using Elmasri and Navathes notation (see 2. c). In prorogue 1, we summarize a sequence of steps of database design using an ER model. production line that these steps are iterative. 1. Understand the problem domain. break apart database requirements. Write a analysis specification in English, if not created yet. What do we need to store into the database? What queries a nd reports do we need to generate? What are important people, places, physical things, organizations, events and abstract concepts in the organization? 2. number a conceptual precis by creating an ER diagram. (a) Identify entity types. dish out a singular noun to each entity type. (b) Identify relationships between (among) entities. routine a meaningful verb for a relationship name. (c) Draw an ERD without attributes. (d) Identify relationship cardinalities. Mapping constraint (11, 1N, NM) Participation constraint (Total, Partial) (e) Assign attributes to entity types and relationship types.Usually attributes come from nouns, adjectives or adverbs. (f) Select identifiers ( native keys) for entity types. wonky entity multiform primary key. Regular entity film/create a champion attribute primary key. (g) Select the PKs of relationships. If 11, then the PK of either side entity type may be selected. If 1N, then the PK of N-side entity type must be selected. If MN, then a comp osite PK consisting of PKs of two entity types must be used. If ternary, then a composite PK consisting of the PKs of at to the lowest degree two entity types.The echt PKs selected will vary depending on the cardinality. 3. institution a logical schema. (a) Translate the ERD into a relational schema 219 Appendix A A Practical Guide to Entity-Relationship Modeling If a relationship cardinality is promising to be changed use stable method. If a relationship cardinality is not likely to be changed use mapped method. If a relationship cardinality is not likely to be changed and null determine of international keys are significant use mapped with total/partial method. (b) Check normalization (at least 3NF). (c) Create data dictionaries. A schema table One table for each relation created in step (a) Assign a domain type for each attribute. Explain the meaning of attributes, if not intuitive. eyeshade other values such as range, null, PK, FK, indexed, source, proprietor (d) Do database prototyping & substitute the design if obligatory. (e) Summarize the design confidence (integrity, security). 4. Verify the design with users. Iterate the steps, if requirement. confuse 1. Steps to DB visualise exploitation ER Modeling First, it is important to study the problem domain at hand. Analyze database requirements.Write a thick carve up for the problem domain, considering what data need to be stored and what queries and reports need to be processed. All the information necessary for the identified queries and reports must be include in the summary paragraph. Revise the summary paragraph considering database requirements. Second, from the summary paragraph, find nouns. They are candidates for entity types. To determine whether a noun should be designated as an entity, the spare-time natural action decision rules may be applied. regularize 1 each entity type should be important in its own right within the problem domain. form 2 IF an object type (no un) has only one dimension to store consequently it is an attribute of another entity type ELSE it is an entity type. swayer 3 IF an object type has only one data instance THEN do not model as an entity type. Rule 4 IF a relationship call for to have a unique identifier 220 Appendix A A Practical Guide to Entity-Relationship Modeling THEN model it as an entity type. The first three rules are used to evaluate object types or nouns, and the quaternary rule is used to evaluate relationships or verbs. manakin 1 voice communication is normally a property of another object type, like customer, vendor, or company.Its goence is slight important and not meaningful in its own right within the problem domain. turn to should be modeled as an attribute. Example 2 Suppose we are modeling the customers of a company and we neediness to include the city where each customer resides. If the name of the city is its only attribute, then, complying Rule 2, model city as an attribute not an ent ity. Similarly, consider the case of modeling employees and their departments. If the only important property of the department is its name, then Rule 2 enunciates us to model it as an attribute.However, if we need to store additional properties of each department such as projects or total sales, then we should consider modeling it as an entity. Example 3 control modeling the activities of a trucking company. Since there is only one instance of the trucking company, then, accord to Rule 3, it is not necessary to represent it in our model as an entity. We note that it is not wrong to model this single instance noun as an entity type. We simply do not model it as an entity type at the conceptual level because it does not add any modeling power.We need the fourth rule because one feature can be stated in many contrastive ways in English. In the fourth rule, distinguishing between entities and relationships depends on the function the component plays in the problem domain and how da ta will be stored about it. Example 4 Consider the three rehearsals customer orders products, customer contains bills, and ref suss outs papers. Even though orders and indemnifys push through to represent relationships, we model them as entities since each instance would need a unique number for identification in real-world situations. cultivation would be stored in the database for each order and compensation. Each review is not likely to need a unique identification number. Instead, we identify each review employment by a combination of Paper and Reviewer. Thus, by Rule 4, we model reviews as a relationship type. Once entities have been assigned, we break down to identify relationship types between those entities. Verbs are useful candidates for relationships. The viewing question is useful for identifying relationships What sentences can be constructed of the form Entity Verb Entity? For example, 221Appendix A A Practical Guide to Entity-Relationship Modeling Employee h as children (Existence relationship) Professor teaches students (Functional relationship) Customer places order (Event relationship) bank bill that a relationship is not an action of a flow of data as in data flow diagrams. They are important interactions, between two or more entities, that need to be remembered by the system. In the above examples, we want to remember the facts that who is a child of which employee, which professor teaches which students, and which customer places which order. Also alimentation in reason that all relationships are bi- concernal.We should be able to state the relationship in both(prenominal) transportions. Expressing the relationships above in the opposite direction yields the spare-time activity educational activitys Children belong to employee Students are taught by professor Order is placed by customer After an ERD has been built, the issue forthing rule can aid in formalise the diagram. Rule 5 IF any verb refers to nouns which are n ot selected as entity types THEN do not model it as a relationship type. If any verb in the ERD fails to follow Rule 5, then consider it again carefully before including it in the diagram.When entities and relationships have been identified, then the cardinality and participation constraints of the relationships can be analyzed. The following rules can help determine the cardinality and participation constraints for a given binary relationship. A B Rule 6 For each A, what is the maximum number of Bs that may be related to it? Rule 7 IF A can go without being associated with a B THEN A has partial (optional) participation ELSE A has total (mandatory) participation. 222 Appendix A A Practical Guide to Entity-Relationship Modeling Example 5 Consider the relationship provider Supplies chronicle.For each provider, what is the maximum number of notices that may be related to it? Lets say that in our problem domain, each Supplier may have many sum ups but each Account may have only one Supplier. By Rule 6, the cardinality constraint for SupplierAccount is 1N or one to many. Figure 1 illustrates the cardinality constraints. Example 6 In determine the participation constraint of Supplier Supplies Account, we follow Rule 7 If Supplier can exist without being associated with Account, THEN Supplier has partial participation, ELSE Supplier has total participation.In our problem domain, Supplier may exist without being associated with Account. Therefore, Supplier has partial participation in the tag on relationship. However, since Account cannot exist without a Supplier Account has total participation in the picture relationship. Some basic naming conventions have been constituted to exert accuracy and consistency in the database and to suspend redundancy. All entity names should be unique. Use singular nouns in the diagram for both entity and attribute names. Use verbs in the present tense for relationship names. Verbs should be meaningful.For example, avoid v erbs like is, has, and do whenever possible. Additionally, well-defined ERDs should gratify the following basic rules All entities and relationships should be connected. All entity names should be unique. Each entity must have at least one relationship. A relationship cannot be directly connected to another relationship. Every entity must have at least one unique attribute, which serves to identify each instance of that entity. Case Study The following example will illustrate our guidelines for modeling requirements of the problem domain with entity-relationship diagrams. use the summary paragraph of the problem description below, we will do through the steps described above. The nouns in the problem description appear in boldface and the verbs are italicized to aid in the following analysis. Summary Paragraph of trouble Description A database specialist wants to design a part of the database for a smallish drug store possessor as follows The owner wants to keep track of all the providers who add anything to the store. For each supplier, the owner assigns a unique supplier number, and wants to keep the 223Appendix A A Practical Guide to Entity-Relationship Modeling company name, shout (number, street, city, state, zip), contact persons name, send for number, fax number, and a comment for each supplier. For each tot up activity, an account is realized to keep track of the date incurred, the total cost of the activity, due date for defrayment, undischarged balance afterward some salarys, and any special comments related to the account. For each account, the owner may pay at some(prenominal) different measure and in different ways (e. g. , cash, deterrent, credit card).For each payment activity, the owner wants to keep the date of payment, metre of payment, method of payment (check check number credit card credit card name, type, and number). Note that one supplier can supply many times and one payment can pay for some(prenominal)(prenomin al) accounts of the same supplier. Entity analytic thinking After teaching and understanding the problem line, our first step is to identify entities for the ERD. To do that we examine the nouns in the problem bid. Nouns appear in boldface. We runnel each noun against our four criteria to determine whether or not it should be included as an entity type.Our first noun is owner. Recall that an entity type has more than one instance and more than one property. Since there is only one instance of owner, we do not model it as an entity type. Similarly, there is only one store, so we need not represent store as an entity type. The next noun, supplier, can be classified as an entity type. Several properties of supplier are listed in the problem statement. The statement as well as refers to more than one supplier. Therefore, fit in to Rules 1, 2, and 3, we model supplier as an entity.For each supplier, the owner wants to store the following properties in the database supplier number, company name, contact person, engineer, strait number, fax number, and comment. Each of these attributes except reference work has only one property to store so we model them as attributes. reference book has its component properties number, city, state, and zip so one might be tempted to model it as an entity type. However, the role of address as a property of supplier supersedes the fact that address has properties of its own. In other words, address itself without supplier is not important in its own right.Therefore, by Rule 1, we model address as an attribute. Account is the next noun. Account has some(prenominal) properties to be stored in the database date incurred, total cost, due date, account balance, and comments and we will store information about legion(predicate) accounts. Therefore, we designate account as an entity. Its properties are modeled as attributes of account. indemnifyment is intelligibly an entity, with duple instances and various properties. The p roperties of payment date of payment, bar of payment, and method of payment, are modeled as its attributes.Cash, check, and credit card appear to be attributes of payment, but actually, they are not attributes themselves, but simply different values for the attribute method of payment. This distinction becomes clearer if we intend about 224 Appendix A A Practical Guide to Entity-Relationship Modeling storing data in the database. For each payment, one of the values cash, check, or credit card will be stored in the location containing data about the method of payment. Check number and credit card name, type and number may be modeled as attributes of remuneration. Supplier Account hire Figure 3. Entities to be included in the ERD.Relationship Analysis Our analysis of nouns in the problem statement has produced three entities Supplier, Account, and stipend (Figure 3). Keep these entities in header as we identify relationships between them. Lets examine the verbs in the problem st atement as candidates for relationships in the diagram. Verbs appear in italics. Of the verbs in the problem statement keep track, assigns, supply, constituted, and pay, only supply and pay are possible candidates for relationships between the entities account, supplier, and payment. Keep track and keep appear several(prenominal) times in the problem statement.These terms refer, not to a relationship between entities, but generally to storing data in the database. In other words, they are used to describe the problem domain, not an interaction that needs to be remembered by the system. Therefore, we do not model them as relationships. Established, in the statement an account is established, is an activity performed by the owner or the system itself. Similarly, owner assigns a unique supplier number reflects an activity by the owner. These two verbs do not represent relationships between any of our three entities. Thus, we are left with the verbs supply and pay.A supplier performs a supply activity. The result of a supply activity is an account. Therefore, a hefty candidate for the relationship between supplier and account is supply. Stated in both directions, the relationship is Supplier supplies account and account is supplied by supplier. Rule 4 states that if a relationship needs to have a unique identifier, then model it as an entity. Each supply activity is unique, so we may be tempted to model supply as an entity. However, the data for each activity is stored using the entity account, so it is not necessary to create another entity which stores the same information.Each payment credits an account so pay is the relationship between payment and account. Expressing the relationship pay in both directions, we can say account is paid by payment and payment pays account. 225 Appendix A A Practical Guide to Entity-Relationship Modeling Now we can draw the basic ERD (Figure 4). We include the entities Supplier, Account, and Payment, and the relationships Suppl y and Pay. Attributes may be added to the diagram at this point or omitted to avoid clutter. Supplier Supply Account Pay Payment Figure 4. ERD without attributes and constraintsAnalysis of Cardinality and Participation Constraints In order to identify the cardinality and participation constraints of each relationship in the ERD, we follow Rules 6 and 7 looking at the relationship first from the point of view of one entity and then from the other entity. In our ERD above, to determine the cardinality constraint of the relationship Supply, we begin by asking, For each Supplier, what is the maximum number of Accounts that may be created? From the problem statement, we crawl in that one supplier can supply many times and an account is established for each supply activity. persuasioning the relationship in the other direction, we ask, What is the maximum number of Suppliers for which each Account may contain information? From the problem statement we can tire that each account carrie s information for a single supplier, since accounts are established for individual supply activities. Thus, for each supplier, there may be many accounts and each account may have only one supplier. The relationship Supplier Supplies Account is a one-to-many relationship. The diagram is marked with a 1 on the side of the relationship Supply nearer to Supplier, and an N (for many) on the side nearer to Account (see Figure 5).To identify the cardinality of the relationship Payment Pays Account, we look at the relationship from both directions. We ask, What is the maximum number of Payments we can accept for each Account? The answer is clearly stated in the problem statement For each account, the owner may pay at several different times and in different ways. From the opposite direction, For each Payment, what is the maximum number of Accounts for which it may pay? Again, we find the answer in the problem statement One payment can pay for several accounts of the same supplier. In sum , each account 226Appendix A A Practical Guide to Entity-Relationship Modeling may receive many payments and each payment may pay for many accounts. Therefore, the relationship Payment Pays Account is many-to-many. This time, we mark our diagram with an M on one side of the relationship Pay and an N on the other side. (Note that the use of M or N is on the whole arbitrary. ) We go through a kindred process to determine the participation constraint of each relationship, looking at the relationship from each direction. For the Supply relationship we ask, enkindle a Supplier exist without generating Accounts? In the other direction, heap an Account exist without having Suppliers supply merchandise? The answers to these questions are not explicit in the problem statement. In a real world situation, the database designers would relieve questions like these with the owner. In this case, we will reach out assumptions from what we understand about the problem domain. Suppliers are g enerally fairly stable entities. A company maintains relationships with several regular suppliers heed slight of whether they have outstanding accounts. On the other hand, an account is only created when a supplier supplies merchandise.Since suppliers can exist without having current accounts, Supplier has partial participation in the Supply relationship. Accounts, however, depend on suppliers for their existence. Thus, Account has total participation in the Supply relationship. To determine the participation of the entities Payment and Account in the Pay relationship, we ask, Can a Payment exist without paying for an Account ? and Can an Account exist without receiving Payments against it? A payment which pays for nothing is absurd. It cannot exist without an account. An account, however, may exist without receiving payments against it.Therefore, Payment has total participation and Account has partial participation in the relationship Pay. In representing the cardinality and par ticipation constraints described above in our ERD, we will employ Elmasri and Navathes (1994) notation. If an entity has partial participation in the relationship, then a single line is drawn on the line between that entity and the relationship. A double line indicates total participation. The cardinality constraint is represented by Look Across convention, while participation constraint is represented by Look Here convention.Figure 5 illustrates the final exam ERD with cardinality and participation constraints. 227 Appendix A A Practical Guide to Entity-Relationship Modeling 1 Supplier Supply N Account M Pay N Payment Figure 5. ERD with cardinality and participation constraints. Errors in Modeling A common error that beginner designers make is failing to recognize the boundaries of a problem domain. They fail to make a distinction between elements that comprise the fill of the database and elements that are outside the scope of the database. For example, in the problem statement bove, a learned person might want to model the verbs keep track or assigns or established as relationships (see Figure 6(a)). These verbs refer to implementing the database and not to its content. Keep track refers to storing data in the database, established refers to adding an instance of an entity to the database, and assigns refers to giving a value to an attribute of an entity. In deciding which elements to model, it is valuable to keep in mind the real world situation. novitiate designers also frequently confuse entities with their attributes or properties, as in Figure 6(b).Occasionally, if properties are intricate and play a significant role in the problem domain, then they may be modeled as entities. more often, however, properties of an entity should be modeled as attributes. In our problem statement, a novice user may decide to model address, a property of the entity supplier, as an entity. Modeling Address follows Rules 2 and 3 about identifying entities it has more than one property and it has more than one occurrence. However, address does not follow Rule1 in that it is not important in its own right.The role of address in the database is more accurate as an attribute of supplier, than as an entity with its own relationships. Other errors are modeling indirect or supererogatory relationships and inappropriately modeling object types as relationships alternatively than as entities. Given our problem statement, one may be tempted to model the relationship Payment Pays Supplier as in Figure 6(c) or Supplier Pays Account as in Figure 6(d) quite an than Payment Pays Account. Figure 6(c) represents the association between payment and account indirectly. This indirect relationship can only exist after we have all the direct 228Appendix A A Practical Guide to Entity-Relationship Modeling relationships as in Figure 5. In this case, the indirect relationship simply becomes redundant. Without the direct relationships, the indirect relationship canno t be added, because it cannot explain how a particular payment is distributed to multiple accounts. Figure 6(d) represents the relationship Pay kinda than the entity Payment. In either of these two cases, it is difficult to explicitly represent the fact that one payment can pay for several accounts of the same supplier. We can only tell implicitly by reading the check number for the various payments.If the payment is do in cash, there is no way to identify that it paid for more than one account. If the representation used in Figure 6(d) is used, then the attributes related to payments date of payment, heart and soul of payment, and method of payment, are now attributes of the relationship Pay. This representation can add excess complexity to the model. Ordinarily, a relationship is uniquely represented by the identifiers of one or more of the entities which participate in it. If the relationship includes a time-dependent attribute like date of payment, then that attribute must a lso be included in the primary key for that relationship.Additionally, instances of date of payment and amount of payment will require redundant representation because they will have to be included for each account covered by a payment. Finally, in business practice, each payment activity normally requires a unique identifier. Therefore, following Rule 4, it is more appropriate to model payment as an entity than as a relationship. As an entity, the representation is more straightforward and less likely to include redundant or inaccurate information. 229 Appendix A A Practical Guide to Entity-Relationship Modeling a) extract of wrong verb as relationship owner (b) Attribute as entity Supplier Located_at Address Keeps_track Payment (c) Indirect relationship Payment Pays Supplier Establishes Account (d) Payment as relationship instead of entity Establishes Supplier Pays Account Figure 6. Errors in ERD Modeling. Limitations of Guidelines and Rules Two limitations of our guidelines are that they dont account for incomplete requirements analysis or for ambiguity in the problem description. If the problem description is incomplete, then the resulting analysis base on this approach will also be incomplete.We assume that the analysis is complete. If the problem specification is circumscribed, the analysis and resulting ERD should be modified as well. In English, one concept can be represented in many different ways. For example, we can say that customer orders products or customer places an order to buy products. Order is used 230 Appendix A A Practical Guide to Entity-Relationship Modeling as a verb in the first sentence and as a noun in the second. We minimize this problem by adopting Rule 4, which states that if a verb needs to have a unique identifier, we model it as an entity type rather than a relationship type.Conclusion We have discussed a set of decision rules which are useful in building ERDs and have illustrated the application of these rules using a sin gle example. ERD constructs discussed here include Entities, Relationships, Attributes, Cardinality constraints and Participation constraints. To simplify our discussion, we didnt include other constructs such as Weak Entity, collar Relationship, and induction/ Specialization. Our rules are heuristics which we have found useful for most cases to build ERDs in the early stages of analysis.However, these rules may need some culture in some problem domains and the rules should be adapted to the problem domain under consideration. References Ahrens, J. and Song, I. Y. (1991). EER Data Modeling Aids for Novice Database Designers. Proceedings of the 2nd International Conferences of the Information Resources Management, Memphis, TN, May 19-22, 1991, pp. 99-114. Bachman (1992). Bachman Analyst, Bachman Information Systems Incorporated. Batini, C. , Ceri, S. , and Navathe, S. (1992). abstract Database Design An EntityRelationship Approach, sequoia City, CA Benjamin/ e. e. cummings public ation Company, Inc.Bruce, T. (1992). Designing Quality Databases with IDEF1X Information Models. New York, New York Dorset House Publishing. Chen, P. P. (1976). The Entity Relationship Model Toward a Unified View of Data. ACM Transactions on Database Systems, 1 1, pp. 9-36. Elmasri, R. and Navathe, S. (1994). Fundamentals of Database Systems, 2nd ed. , Redwood City, CA Benjamin/ Cummings Publishing Company, Inc. Hawryszkiewycz, I. T. (1991). Database Analysis and Design, 2nd ed. , MacMillan Publishing Company. Jones, T. H. and Song, I. -Y. , (1995). Binary office of Ternary Relationships in ER Conceptual Modeling, in 14th Intl Conf. on Object-Oriented and EntityRelationship Approach, celestial latitude 12-15, 1995, Australia, pp. 216-225. (Object- 231 Appendix A A Practical Guide to Entity-Relationship Modeling Oriented and Entity-Relationship Approach, delivery Notes in information processing system Science, Springer-Verlag, Vol. 1021). Jones, T. H. and Song, I. -Y. , (1996). A nalysis of Binary/ternary Cardinality Combinations in Entity-Relationship Modeling, Data & Knowledge engineer Vol 19, No. 1, pp. 39-64. Martin, J. (1989).Information Engineering Book II Planning and Analysis, Englewood Cliffs, NJ Prentice Hall. McFadden, F. , and Hoffa, J. (1994). Modern Database Management, 4th Ed. , Redwood City, CA Benjamin/Cummings Publishing Company, Inc. Ross, R. G. (1988). Entity Modeling Techniques and Application, Database Research Group, Inc. Shaler, S. and Mellor, S. J. (1988). Object-Oriented Systems Analysis Modeling the World in Data, Englewood Cliffs, NJ Yourdon Press. Song, I-Y. , Evans, M. , and Park, E. K. (1995). A Comparative Analysis of EntityRelationship Diagrams, Journal of Computer and Software Engineering, Vol. , No. 4 (1995), pp. 427-459. Song, I. Y. and Jones, T. H. (1995). Ternary Relationship Decomposition Strategies Based on Binary Imposition Rules, in eleventh Intl Conf. on Data Engineering, March 610, 1995, Taipei, Taiwan, pp. 485 -492. Teorey, T. J. (1994). Database Modeling Design The Fundamental Principles, 2nd. ed. , Morgan Kauffman Publishers, Inc. Teorey, T. J. , Yang, D. , and Fry, J. P. , (1986). A logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model. Computing Surveys, 1812, June, pp. 197-222. 232