• Author / Uploaded
• MN Nabi

Citation preview

Chapter Three Entity-Relationship Data Modeling: Process and Examples

CHAPTER 3 ENTITY-RELATIONSHIP DATA MODELING: PROCESS AND EXAMPLES

 ANSWERS TO GROUP I QUESTIONS 3.1

List the steps in the data modeling process. Plan Project Determine Requirements Specify Entities Specify Relationships Determine Identifiers Specify Attributes Specify Domains Validate Model

3.2

Describe tasks to be accomplished during the planning step. Major tasks in this step are obtaining project authorization and budget; building the project team; planning the team’s activities; establishing tools, techniques, and standards so that team members will produce consistent results; and defining the project’s scope. Scope definition is particularly important for data modeling because everything is related to something else in most organizations.

3.3

List common sources of requirements. User Interviews Observations of User Activity Existing Forms and Reports New Forms and Reports Existing Manual Files Existing Computer Files/Databases Formally Defined Interfaces(XML) Domain Expertise

3.4

Explain how user interviews and observations can be used to develop components of a data model. The goal of a user interview is to learn of potential entities and their relationships to one another. It is also important to obtain source documents, such as existing forms and reports, which indicate entities and their relationships and also provide lists of potential attributes. The goal in an interview is to obtain an accurate description of that user’s or user group’s perspective or external view(s) of the data. Results from user interviews and observations will be documented as notes, sketches, data element definitions, and so forth. Team members should create consistent documentation and record their results into a consolidated repository.

3-1

Chapter Three Entity-Relationship Data Modeling: Process and Examples

3.5

Why is it important to clarify terms during user interviews? Keep the possibility of terminology differences in mind when interviewing users. CUSTOMER_HISTORY in accounting may be a very different thing from CUSTOMER_ HISTORY in sales. Thinking that you already know what CUSTOMER_HIS-TORY means because you understand the accounting version of it is risky when you interview the sales department.

3.6

How can forms and reports be used to develop components of the data model? Existing documents can be analyzed to determine how data is currently viewed and processed, and new documents indicate either changes in data views or the need for additional data to be maintained.

3.7

How can existing files be used to develop components of the data model? Existing files are excellent source of requirements. You can often determine entities and attributes of entities by examining these files. For larger databases, data modeling products such as ERWin and Visio can be used to reverse engineer an existing database to create a data model.

3.8

What does the term reverse engineer mean in the context of data models? In the context of data modeling, the term reverse engineer means to that a current database in its physical form or its internal schema and create a conceptual schema.

3.9

Explain how formal interfaces can be used to obtain requirements. A formal interface is a standardized external view. Such interfaces are created by governmental agencies, industry groups, and large enterprises to facilitate interorganizational data transfer. Sometimes, new databases systems are required to receive data from or publish data to such an interface. If so, the interface is a good source of data requirements.

3.10

Why is the use of formal interfaces increasing? The need for interorganizational standards increased with the advent of the Internet. Additionally, the use of XML makes the publication and use of such standards much easier. Almost every business profession or major industry category has (or is in the process of developing) such standards.

3.11

Describe a domain expert and explain advantages and disadvantages of using domain experts in requirements specification. Domain experts are individuals who have deep knowledge of a particular problem area. For example, in a company such as 3M, there may be thousands upon thousands of abrasive products. In this situation, there may be dozens of different ways to develop subtypes/supertype relationships or category clusters. Domain experts can be invaluable for sorting through the myriad of data modeling choices, eliminating many possibilities on the basis of their knowledge. Using domain experts, however, has a downside. Nothing is more dangerous in a development project than a group of “experts” sitting around a table saying, “If I were a user, I’d want…” No one but a user knows what data users want or the form(s) in which they need it. So, although domain expertise can shed light on a particular project, the primary focus for requirements determination must be the users themselves.

3.12

In the following statement, what is probably an entity and what is probably an attribute?

3-2

Chapter Three Entity-Relationship Data Modeling: Process and Examples

I group the student applications by date Student applications would probably be the entity while Date would probably the an attribute 3.13

In the following phrase, what is probably an entity and what is probably an attribute? Can two entities be referenced here? If so, what are they? I use the department name of professor… Professor is probably the entity while department name is probably the attribute. You would probably not think of two entities being referenced here because of the wording ‘department name of professor’. The ‘of’ would imply that department name belong to (or is an attribute of) professor. One might also imply that since the wording preceding the 'of' references a department name, there may be a department entity.

3.14

Explain why the list of entities is likely to change as the data modeling project proceeds. As the project progresses, this list will be modified many times. Some of the items in the list will be found to be synonyms of one another; some items will be removed because they are found to be irrelevant, and others will be added.

3.15

Why is it important to define the meaning and use of entities? Sometimes different users use different names for the same entity. For example, when one refers to a purchase, is this a purchase from the firm by a customer or is it a purchase from a supplier by the firm? Defining the meaning and use of entities makes us determine if the are the same and if they are not the same, create different names.

3.16

What elements comprise the definition of a relationship? The definition of a relationship includes the identity of the parent and child entities, the relationship type, the minimum and maximum cardinalities, and the name of the relationships.

3.17

Describe two ways to find relationships. One is to examine every combination of two entities and determine whether a relationship might exist. If so, next is to examine all requirements to determine the cardinalities and other characteristics of the relationship. The second technique is to process all requirements documents and find all relationships indicated in those documents. Sometimes, a combination of the two approaches is used.

3.18

Explain in your own words why there is no such thing as a one-way relationship. One thing cannot be related to a second thing with out the second thing being related to the first. If I am you brother then (like it or not) you are my brother or sister. Even in a recursive relationship where only one entity is involved, instances of the entity are related to each other.

3.19

Is it possible that only one direction of a relationship is used in an application? Why or why not? YES. Applications are designed for a specific function. For example, if there is a relationship between student and class, an application would probably create a Class Roster by finding all students in a class. That application would probably not be the same application that finds all classes a student takes and creates a Student Schedule.

3-3

Chapter Three Entity-Relationship Data Modeling: Process and Examples

3.20

Give an example of an entity that has no obvious identifier. What should be done with the entity? A very simple example is a house or address. Initially you may think that the phone number of a house would be an identifier but some houses may not have phones and all instances of the entity must have an identifier. Next you may think house number and street name but what about rural areas. These areas typically have Route Numbers but the houses do not have house numbers. Then you could use Route Numbers and P.O. Box but houses in the city do not have P.O. Boxes. (Note: The example above has played havoc with 911 service) When this occurs, check to see if the entity is ID-dependent. If not, makes sure that it has all its attributes and is not part of another entity.

3.21

Give an example, other than the one in this text, of two different entities that have the same identifier. Assume you are developing a database of a University. You find the entities Student, Professors, Staff, and Parents. All four of these may have the same identifier, Social Security, Number.

3.22

Explain the relationship of an attribute and a domain. Domains identify the characteristics of attributes. Each time an attribute is found it is assigned to a domain. If it does not fit an existing domain a new domain is defined and the attribute is assigned to the new domain.

3.23

How are domains created? Domains are creates as lists of values allowed for an attribute or as a description of how the attribute can be stored and any constraints placed on the attribute.

3.24

Explain the advantage of domain property inheritance. When the domain properties change, all the attribute properties change as well. For example, if the data type of a domain is changed, all attributes based on that domain will inherit the new data type.

3.25

How can domains be used to enforce organizational data standards? Some organizations develop a data dictionary of standardized domains and properties. These domains and properties are imported into data modeling tools and are used to create attributes in the data model. In this way, every data modeling project will be working with the same standardized components.

3.26

Give an example of two attributes that suggest the need for a new entity. Choose one whose name includes the word name and one whose name does not include name. Attributes that are names of something other than the entity in which they reside can suggest the need for an additional entity or relationship. For example, in a SALESPERSON entity, an attribute such as Region suggests that there may be a need for a new entity named REGION. If so, the Region attribute is replaced by the relationship between SALESPERSON and REGION. Similarly, an entity PART with an attribute Category suggests the need for a CATEGORY entity with a relationship to PART.

3.27

Why is it important to validate the data model?

3-4

Chapter Three Entity-Relationship Data Modeling: Process and Examples

This is perhaps the most important step because if the data model is incorrect, then the database design will be incorrect. 3.28

Explain what is wrong with this statement: “My data model is a better model of reality than your data model.” We need to construct a data model that is the best model of the mental model that is in the minds of the users of the system. Period. We need to know whether a model accurately reflects how the users think about their world. The only relevant question when evaluating a data model is the following: “How well does it fit the mental models of the people who are going to use the system?” The person who is doing the data modeling may think the model under construction is a weird way of viewing the world, but that is not the point. The only valid point is: Does it fit how the users view their world?

3.29

If someone made the statement in question 3.28 to you, how would you respond? The best model is the one that most closely fits fit how the users view their world.

3.30

Explain the following statement: “We cannot say that human models are an accurate representation of the real world because we know nothing about that world. We can only say that those model work well enough that humans can think and communicate in a fashion that has, so far, facilitated human species survival.” We know only what we have learned. We have learned only what we have been taught through a variety of ways. Everything we do, including the way we model, is influenced by our personal history. We have now way of knowing how our model would appear if our experiences were changed.

3.31

What is the sole criterion for evaluating a data model? “How well does it fit the mental models of the people who are going to use the system?”

3.32

Explain how a team review of a data model is conducted. First, the data modeling group conducts design reviews among its own members. During these reviews, the data model is evaluated against system requirements. During the reviews, the team ensures that it will be possible to support user descriptions of the data needs. Additionally, the team ensures that it will be possible to construct an external schema to support every form and report in the requirements. During the team review, questions about the requirements are often generated. After the data model is corrected to remove any problems discovered during the team review, a second round of reviews is conducted with the users themselves.

3.33

Explain how a user review of a data model is conducted. Usually one or two key users from each group that will use the new system are involved in these reviews. Any questions that developed during the team review are answered during the user review. Also, there are sometimes aspects of the data model that were decided by artistic judgment rather than absolute requirement. There may, for example, be several different ways of accomplishing the same goal. If so, sometimes the users are asked to comment upon the various alternatives that could be used. If possible, the best way to conduct user interviews is to each the users the meaning of the E-R symbols being used and then to ask users to review and comment upon the data model in diagram format.

3-5

Chapter Three Entity-Relationship Data Modeling: Process and Examples

3.34

Explain how to use a mockup to demonstrate to users the consequences of maximum cardinality between two entities. An example order form that has space for only one salesperson manifests the fact that the relationship from order to salesperson has a maximum cardinality of one. If the users ask, “Where do I put the second salesperson?” it is likely that the maximum cardinality from order to salesperson is greater than one.

3-6

Chapter Three Entity-Relationship Data Modeling: Process and Examples

 ANSWERS TO GROUP II QUESTIONS Answer the following questions using IDEF1X notation: 3.35

Examine the subscription form shown in Figure 3-28. Using the structure of this form, do the following: A. Create a single entity model. Specify the identifier and attributes. SUBSCRIPTION Address Zip Name City State Subscription Length Payment Status First Issue B. Create a model with two entities, one for customer and a second for subscription. Specify identifiers, attributes, relationship name, type, and cardinalities. CUSTOMER

SUBSCRIPTION

Name Address City State Zip

OrderBy / SentTo

P

Subscription_Length First_Issue Payment_Status

C. Under what conditions do you prefer the model in A to that in B? Model A would be the best model if a Customer were only allowed to have one subscription. D. Under what conditions do you prefer the model in B to that in A? Model B would be the best model if a Customer were only allowed to have one multiple subscriptions. 3.36

Consider the traffic citation shown in Figure 3-29. The rounded corners on this form provide graphical hints about the boundaries of entities represented here. A. Create a data model with five entities. Use the data items on the form to specify identifiers and attributes for those entities.

3-7

Chapter Three Entity-Relationship Data Modeling: Process and Examples DRIVER License_Id Last_Name First_Name AddressStreet City State Zip Gender BirthDate Height Weight EyeColor ViolationType DriverSignature

TICKET ViolationDay ViolationMonth ViolationYear ViolationTime Receives / GivenTo

Dist Detach LocationMiles LocationDirection Location Route/Street

VIOLATIONS DescriptionOf / ViolatedOn

P

ViolationDescription

Gives / GivenBy RegisteredBy / RegisteredTo OFFICER PersonnelNumber

VEHICLE

Officer_Name

VehicleLicense State Color Make Type VIN

B. Specify relationships among the entities. Name the relationship and give its type and cardinalities. Indicate which cardinalities can be inferred from data on the form and which need to be checked out with systems users. Relationships, Types, and Cardinalities are show on the E-R Diagram. From the form you can infer that a Driver register only one Vehicle. This is because of the single Registered Owner in the Vehicle area. You can also imply that a Ticket can have several Violations because of the multiple lines and the wording Violations (plural). You can imply that only one Officer can give the Ticket because there is room for only one Officer Signature. 3.37

Examine the list of email messages in Figure 3-30. Using the structure and example data items in this list, do the following: A. Create a single entity data model for this list. Specify the identifier and all entities. EMailMessage MailID From Subject Date Size

Since no attribute is unique, a MailID was created.

3-8

Chapter Three Entity-Relationship Data Modeling: Process and Examples

B. Modify your answer to A to include entities SENDER and SUBJECT. Specify the identifiers and attributes of entities and the type and cardinalities of relationships. Explain which cardinalities can be inferred from Figure 3-30 and which need to be checked out with users. EMailMessage SUBJECT

MailID Date Size

MessagesFor / About

Subject

SENDER SenderID

Sends / SentBy

The one-to-many relationships between EMailMessage and Subject can be implied because the subject repeats. For example, there are three messages with the Subject RE:Hotel. The one-to-many relationships between EMailMessage and Sender can also be implied because the sender repeats. For example, there are three messages from Tom Cooper. C. There are two different styles of email address in the From column in Figure 3-30. One style has the true email address; the second style (Tom Cooper, for example) is the name of an entry in the user’s email dictionary. Create two categories of SENDER for these two styles. Specify identifiers and attributes. SUBJECT

EMAILMESSAGE Mail_Id Date Size

Subject

For / About Sends / SentBy

SENDER TrueAddress

DICTIONARYENTRY

EMAILADDRESS

LogicalName

Logical_Name

3-9

Chapter Three Entity-Relationship Data Modeling: Process and Examples

3.38

Examine the list of stock quotations in Figure 3-31. Using the structure and example data items in this list, do the following: A. Create a single entity data model for this list. Specify the identifier and attributes. STOCKQUOTE Symbol Name LastQuote Change PercOfChange

B. Modify your answer to A to include the entities COMPANY and INDEX. Specify the identifier and attributes of the entities and the type and cardinalities for relationships. Explain which cardinalities can be inferred from Figure 3-31 and which need to be checked out with users. COMPANY

INDEX Symbol

Symbol

Index_Name

Name

Lists / ListedIn

Valued / ValurFor 1

1

STOCKQUOTE Change PercOfChange

Each of these relationships is a One-to-1. A Quote is for a single Index or Company and an Index or Company has one quote. They are Identifying relationships because the key to all three tables is the same, the Symbol. This is a unique situation because a Stockquote will belong to either an Index or a Company but not both. All of these can be implied by the data shown. C. The list in Figure 3-31 is for a quotation on a particular day at a particular time of day. Suppose that the list were changed to show closing daily prices for each of these stocks and that it includes a new column: QuoteDate. Modify your model in B appropriately.

3-10

Chapter Three Entity-Relationship Data Modeling: Process and Examples COMPANY

INDEX Symbol

Symbol

Index_Name

Name

Lists / ListedIn

Valued / ValurFor P

P

STOCKQUOTE QuoteDate Change PercOfChange

The only things that would change are the identifier and the maximum cardinality for the weak entity StockQuote. The identifier would now need to include the QuoteDate and the cardinality would be 1:N rather than 1:1. D. Change your model in C to include the tracking of a portfolio. Assume the portfolio has an owner name, phone, email address, and a list of stocks held. The list includes the identity of the stock and the number of shares held. Specify all additional entities, their identifiers and attributes, and the type and cardinality of all relationships. PORTFOLIO OwnerName Phone Email

STOCKPURCHASE Buys / SoldTo

NumberOfShares SoldTo / BoughtFrom

INDEX

SoldTo / BoughtFrom COMPANY

Symbol

Symbol

Index_Name

Name

Lists / ListedIn

Valued / ValurFor P

P

STOCKQUOTE QuoteDate Change PercOfChange

E. Change your answer to question D to keep track of portfolio stock purchases and sales in a portfolio. Specify entities, their identifiers and attributes, and the type and cardinality of all relationships.

3-11

Chapter Three Entity-Relationship Data Modeling: Process and Examples PORTFOLIO OwnerName Phone Email

STOCKPURCHASE Buys / SoldTo

SoldTo / BoughtFrom SoldTo / BoughtFrom

Sells / SoldBy INDEX

STOCKSALE NumberOfShares

NumberOfShares

BoughtFrom / SoldTo

COMPANY

Symbol

Symbol

Index_Name SoldTo / BoughtFrom Lists / ListedIn

Name Valued / ValurFor P

P

STOCKQUOTE QuoteDate Change PercOfChange

3.39

Figure 3-32 shows the specifications for single stage air compressor products. Notice that there are two categories of products for different Air Performance: the A models are at 125 (pounds per square inch of pressure), and the E models are at 150 (pounds per square inch of pressure). Using the structure and example data items in this list, do the following: A. Create a category cluster to represent these compressors. The generic entity will have attributes for all single stage compressors, and the category entities will have attributes for products having the two different types of Air Performance. Assume there might be additional products with different types of Air Performance. Specify the entities, identifiers, attributes, relationships, type of category cluster, and possible determinant.

3-12

Chapter Three Entity-Relationship Data Modeling: Process and Examples AIRCOMPRESSOR Model HP Tank AppShipWt Length Weight Height

125LB PumpRPM CFMDisp DELDAir

150LB PumpRPM CFMDisp DELDAir

The Entities, Identifiers, Attributes, relationships are sown in the diagram above. This is a complete category cluster. There does not appear to be a determinate. B. Figure 3-33 shows a different model for the compressor data. Explain the entities, their type, the relationship, its type, and its cardinality. How well do you think this model fits the data shown in Figure 3-32? The SS_COMPRESSOR entity is a strong entity and the AIR_PERFORMANCE_TYPE entity is a weak entity. The relationship is one-to-many since a compressor may have more than one type. The relationship is an identifying connection relationship since the identifier for a type requires the Model and the AirPerformance to be unique. Each type must belong to an air compressor but an air compressor does not have to have a type. Both models (A. and B.) fit the data show. C. Compare your answer in question A to the model in Figure 3-33. What are the essential differences in the two models? Which do you think is better? The essential difference is that A. models the Type as a category cluster with a maximum of two types. The model will allow additional types because it is incomplete but the additional types cannot have the PumpRPM, CFMDisp, and DELDAir attributes. Model B. models the Type as a weak entity with an unlimited number of types. I would prefer the model in B. because it will allow you to have more than two Types. D. Suppose you had the job of explaining the differences in these two models to a highly motivated, intelligent end user. How would you accomplish this? I would focus the discussion on the ability of model B. to allow an unlimited number of air compressor types. This makes the model much more versatile. 3.40

Figure 3-34 shows a listing of movie times at theaters in Seattle. Using this data as an example, do the following: A. Create a model to represent this report using the entities MOVIE, THEATER, and SHOW_TIME. Assume that theaters may be showing movies other than this one. Although this report is for a particular day, your data model should allow for movie

3-13

Chapter Three Entity-Relationship Data Modeling: Process and Examples

times on different days as well. Specify the identifier of the entities and their attributes. Name the relationships, describe the relationships’ type, and indicate relationship cardinality. THEATER

SHOWTIMES

MOVIE Movie_Name MovieDescription

WhenShown / WhatShown

Day Time

Theater_Name Shown / ShownAt

Distance Street City Phone

Relationship Types are identifying and cardinalities are 1-to-Many. Explain which cardinalities you can logically deduce from Figure 3-34 and which need to be checked out with users. Assume that distance is an attribute of THEATER. From the report you can tell that a Move can be show at many different Theaters. You cannot tell that a theater may show several different movies. This cardinality would need to be checked out with users. You can also tell that Movies are shown at different times. B. This report was prepared for a user who is located near downtown Seattle. Suppose that it is necessary to produce this same report for these theaters, but for a user located in a suburb such as Bellevue, Renton, Redmond, and Tacoma (these are suburbs of Seattle). In this case, distance cannot be an attribute of THEATER. Change your answer in A for this situation. Specify the identifier of entities and their attributes. Name the relationships, describe the relationships’ type, and indicate relationship cardinality. SUBURB Area_Name

DISTANCE DistanceTo / DistanceFrom

MovieDescription

THEATER

SHOWTIMES

MOVIE Movie_Name

Distance

WhenShown / WhatShown

Day Time

DistanceTo / DistanceFrom

Theater_Name Shown / ShownAt

Street City Phone

The new relationships are Identifying relationships. C. Now, suppose that you want to make this data model national. Change your answer to question B so that it can be used for other metropolitan areas. Change your

3-14

Chapter Three Entity-Relationship Data Modeling: Process and Examples

answer in A for this situation. Specify the identifier of entities and their attributes. Name the relationships, describe the relationships’ type, and indicate relationship cardinality. A. and B. Both models would be done as shown below. SUBURB Area_Name State_Name

DISTANCE DistanceTo / DistanceFrom

MovieDescription

Distance

THEATER

SHOWTIMES

MOVIE Movie_Name

DistanceTo / DistanceFrom

WhenShown / WhatShown

Day Time

Theater_Name Shown / ShownAt

Street City Phone

The only change that would need to be made is to add the State to the key of the Suburb. This is done in case the same suburb name is used to more than one state. D. Modify your answer to question C to include the leading cast members. Assume that the role of a cast member is not to be modeled. Specify the identifier of new entities and their attributes. Name new relationships, describe their relationships’ type, and indicate their cardinality.

3-15

Chapter Three Entity-Relationship Data Modeling: Process and Examples SUBURB Area_Name State_Name

DISTANCE DistanceTo / DistanceFrom

Distance

DistanceTo / DistanceFrom

LEADINGCASTS Cast_Name

PlaysIn / Stars

Movie_Name MovieDescription

THEATER

SHOWTIMES

MOVIE WhenShown / WhatShown

Day Time

Theater_Name Shown / ShownAt

Street City Phone

Since a cast member can be in many movies and a move can have many leading castmembers, the relationship is Many-to-Many or a Non-specific connection relationship. 3.41

Consider the three reports in Figure 3-35. The data are samples of data that would appear in the reports like these. A. Make a list of as many potential entities as these reports suggest to you. Issuer Ingredient Supplier Product B. Examine your list to determine whether any entities are synonyms. If so, consolidate your list. It is possible that Issuer and Supplier are synonyms. This cannot be determined from the report data so would need to check with the user. C. Construct an IDEF1X diagram showing relationships among your entities. Name each relationship and specify cardinalities. Indicate which cardinalities you can justify on the basis of these reports and which you will need to check out with the users.

3-16

Chapter Three Entity-Relationship Data Modeling: Process and Examples PRODUCT

ISSUER IssuerName

Produces / ProducedBy

ProductName

INGREDIENT Uses / UsedIn

IngredientName

Costs / SellsFor PriceList

SUPPLIER SupplierName

3.42

Sells / Charges

Price

Consider the CD cover in Figure 3-36. A. Specify identifiers and attributes for the entities CD, ARTIST, ROLE, and SONG. CD (Title, Comment, BookBy, MusicBy, LyricsBy, ProducedBy, CoreographedBy) ARTIST (ArtistName) SONG (Title, Time) ROLE (Role) B. Construct an IDEF1X diagram showing relationships among these three entities. Name each relationship and specify cardinalities. Indicate which cardinalities you can justify on the basis of the CD cover and which you will need to check out with the users. CD CDTitle Comment BookBy MusicBy LyricsBy DirectedBy CoreographedBy

SONG Title Time

ARTIST ArtistName

ROLE Role

The cardinalities seem to be apparent. The only one I could question is the cardinality between CD and Song. Could a Song appear on more than one CD. C. Consider a CD that does not involve a musical, so there is no need for ROLE. There is, however, a need for an entity SONG_WRITER. Create an IDEF1X diagram for CD, ARTIST, SONG, and SONG_WRITER. Assume that an ARTIST can either be a group or an individual. Assume that some artists record individually and as part of a group.

3-17

Chapter Three Entity-Relationship Data Modeling: Process and Examples ARTIST ArtistName

CD CDTitle Comment BookBy MusicBy LyricsBy DirectedBy CoreographedBy

SONG

ARTISTGROUP

Title

GroupName

Time

1 SONGWRITER WriterName

D. Combine the models you developed in your answers to questions B and C. Create new entities if necessary, but strive to keep your model as simple as possible. Specify identifiers and attributes of new entities, name new relationships, and indicate their cardinalities. ROLE

ARTIST

Role

ArtistName 1

CD CDTitle Comment BookBy MusicBy LyricsBy DirectedBy CoreographedBy

SONG

ARTISTGROUP

Title

GroupName

Time

1 SONGWRITER WriterName



ANSWERS TO MODELING PROBLEMS

3.43 The Jefferson Dance Club teaches social dancing and offers both private and group lessons. Jefferson charges $45 per hour per student (or couple) for a private lesson and charges $6 per hour per student for a group lesson. Private lessons are offered six days per week (from noon until 10 p.m.). Group lessons are offered in the evenings.

3-18

Chapter Three Entity-Relationship Data Modeling: Process and Examples

Jefferson employs two types of instructors: full-time salaried instructors and part-time instructors. The full-time instructors are paid a fixed amount per week, and the part-time instructors are paid either a set amount for an evening or a set amount for teaching a particular class. In addition to the lessons, Jefferson sponsors two weekly social dances featuring recorded music. The admission charge is $5 per person. The Friday night dance is the more popular and averages around 80 people; the Sunday night dance attracts about 30 attendees. The purpose of the dances is to give the students a place in which to practice their skills. No food or drinks are served. Jefferson wants to develop an information system to keep track of students and the classes they have taken. Jefferson’s managers also want to know how many and which types of lessons each teacher has taught and to compute the average revenue generated per lesson for each of their instructors. Suppose Jefferson has hired you to construct a database for them. To do this, you decide to create a data model. You know you will need to interview users and gather forms, reports, and other requirements. Before you do this, however, you decide to construct a trial data model. You are hoping that this model will help you determine specific questions that you will need to ask Jefferson personnel. A. To construct such a trial model, read through the preceding description and pick out all of the important nouns. From these nouns, construct a list of potential entities. Nouns club hour evening fixed amount admission Sunday drinks them forms personnel

lessons couple instructor set amount person attendees manager you reports

private lesson student full-time-salaried instructor dance Friday skill revenue data model requirements

group lesson week part-time instructor music people food database user question

Entities lessons instructor person user

private lesson dance attendee

group lesson full-time-salaried instructor skill

student part-time instructor manager

B. Review the list you made in A and eliminate any synonyms. Also, identify entities that are subtypes or category entities. Name a likely identifier for each entity. Eliminate any hard-to-justify entities. Lesson: Id = LessonID Group lesson: Subtype of Lesson Instructor: Id = Instructor Name part-time instructor: Subtype of Instructor

3-19

Private lesson: Subtype of Lesson Student: Id = Student Name full-time-salaried instructor: Subtype of Instructor Attendee: Attendee Name

Chapter Three Entity-Relationship Data Modeling: Process and Examples

C. This description does not provide sufficient data for you to specify attributes of the entities you have identified. Therefore, create an IDEF1X diagram that shows only entities and relationships. Name each relationship and specify its cardinalities. Justify the cardinality decisions you make. ATTENDEE

Attends / AttendedBy

DANCE

Attends / AttendedBy

STUDENT Takes / IsTakenBy

Teaches / TaughtBy

INSTRUCTORS

PARTTIME

FULLTIME

LESSON

PRIVATE

GROUP

D. Now, examine your model and make a list of questions that you would need to ask in order to create an accurate data model. 1. Can people other than students attend a dance. If no, then ATTENDEE can be eliminated. 2. Can more than on instructor teach a lesson. This affects the cardinality of the IsTaughtBy relationship. 3. Are there any kind of Instructors other than part-time and full-time. E. Do you think the process of creating a trial data model before interviewing users is a good idea? What are the advantages? What are the disadvantages? It is definitely a good idea. You can get answers more quickly this way. It also gets the users involved in the process much earlier. The only disadvantage is the extra time required from users but the advantages far outweigh this disadvantage. 3.44San Juan Sailboat Charters is an agent that leases sailboats to customers for a fee. San Juan does not own any sailboats; it leases boats on behalf of the owners who wish to earn income when they are not using their boats. San Juan charges a fee for its service. San Juan specializes in boats that can be used for multi-day or weekly charters—the smallest sailboat in its inventory is 28 feet and the largest is 51 feet. Each sailboat is fully equipped at the time it is leased. Most of the equipment is provided by the owners, but some is added by San Juan. The owner-provided equipment includes what is fixed on the boat, such as radios, compasses, depth indicators and other instrumentation, stoves, and refrigerators. Other owner-provided equipment is not installed as part of the boat. Such equipment includes sails; lines; anchors; dinghies; life preservers; and (in the cabin) dishes, silverware, cooking utensils, bedding, and the like. San Juan provides consumable equipment, (which can also be considered supplies) such as charts, navigation books, tide and current tables, soap, dishtowels, toilet paper, and similar items.

3-20

Chapter Three Entity-Relationship Data Modeling: Process and Examples

An important part of San Juan’s responsibilities is keeping track of the equipment on the boat. Much of it is expensive, and some of it, particularly what is not attached to the boat, can easily be lost or stolen. Customers are responsible for all equipment during the period of their charter. San Juan likes to keep accurate records of its customers and the charters, not only for marketing but also for recording the trips that customers have taken. Some itineraries and weather conditions are more dangerous than others, so San Juan likes to know which customers have what experience. Most of San Juan’s business is bare-boat chartering, which means that no skip-per or other crew is provided. In some cases, however, customers request the services of a skipper or other crew member, so San Juan hires such personnel on a part-time basis. Sailboats often need maintenance. San Juan is required by its contracts with the boat owners to keep accurate records of all maintenance activities and costs, including normal activities (for example, cleaning or engine-oil changes) and unscheduled repairs. In some cases, repairs are necessary during a charter. A boat engine, for example, might fail while the boat is far away from San Juan’s facility. In this case, the customers radio the San Juan dispatcher, who determines the best facility to make the repair and sends the facility’s personnel to the disabled boat. To make these decisions, the dispatchers need information about repair facilities as well as past histories of repair quality and costs. Suppose that San Juan has hired you to construct a database for them. To do this, you decide to create a data model. You know you will need to interview users and gather forms, reports, and other requirements. Before you do this, however, you decide to construct a trial data model. You are hoping that this model will help you determine specific questions that you will need to ask Jefferson personnel. A.

B.

To construct such a trial model, read through the preceding description and pick out all of the important nouns. From these nouns, construct a list of potential entities. Nouns agent owners feet trip crew unscheduled repair personnel

sailboat income equipment itineraries personnel repair history

customer boat owner-provided equipment weather condition contract dispatcher

fee charter consumable equipment skipper maintenance activity facility

Entities Sailboat Equipment Personnel Repair

customer skipper contract facility

owner owner-provided equipment maintenance activity personnel

charter consumable equipment unscheduled repair

Review the list you made in A and eliminate any synonyms. Also, identify entities that are subtypes or category entities. Name a likely identifier for each entity. Eliminate any hard-to-justify entities. Sailboat: Id = BoatID

Customer: Id = Customer_Id

3-21

Chapter Three Entity-Relationship Data Modeling: Process and Examples

Owner: Id = Owner_Id Charter: Id = BoatId and Date Equipment: Id = BoatId and Description Contract: Id = BoatId Repair: Id = BoatId and RepairId Facility: Id = FacilityName maintenance activity: Subtype of repair unscheduled repair: Subtype of repair Skipper: ID = SkipperID Personnel Id = CrewId owner-provided equipment: Subtype of equipment consumable equipment: Subtype of equipment C.

This description does not provide sufficient data for you to specify attributes of the entities you have identified. Therefore, create an IDEF1X diagram that shows only entities and relationships. Name each relationship and specify its cardinalities. Justify the cardinality decisions you make. OWNERPROVIDED

CONSUMABLE

SKIPPER

OWNER EQUIPMENT AgreesTo / SignedBy CONTRACT

IsOn / SailedBy

IsOn / Carries AssignedTo / IsFor

SAILBOAT

Sails / SailedBy

AssignedTo / IsFor

CHARTER

Signs / SignedBy

PERSONNEL

CUSTOMER

Requires / MakeTo FACILITY

Makes / MadeBy

REPAIR

UNSCHEDULED

D.

MAINTENANCE

Now, examine your model and make a list of questions that you would need to ask in order to create an accurate data model. 1. Does Sam Juan make repairs or are all repairs made by other Facilities. This affects the minimum cardinality between Facility and Repair. 2. Are Skippers a set of people different than other crew personnel. If a person may be a skipper on a crew for one charter and simply be one of the crew on a different charter, Skippers and Personnel will be combined into one entity. That entity would have two relationships with charter.

E.

Do you think the process of creating a trial data model before interviewing users is a good idea? What are the advantages? What are the disadvantages?

3-22

Chapter Three Entity-Relationship Data Modeling: Process and Examples

It is definitely a good idea. You can get answers more quickly this way. It also gets the users involved in the process much earlier. The only disadvantage is the extra time required from users but the advantages far outweigh this disadvantage.

 ANSWERS TO FIREDUP PROJECT QUESTIONS Review the FiredUp Project Questions at the end of Chapter 2 and answer the questions there if you have not already done so. A. Consider the data modeling steps in Figure 3-1 in the context of FiredUp’s business. Without having access to the personnel at FiredUp, it is not possible to plan a data modeling project. Instead, make a list of questions that you would need to have answered in order to be able to plan a data modeling project. The answer here will depend upon the student’s experience with business subjects. Most of the questions are answered by the time you complete problem E. A few additional questions that might be asked would be; 1. Can multiple Stoves be placed on the same Repair order? 2. Do all Customers have an Email address. B. With regard to requirements determination, consider the sources of requirements in Figure 3-2. Describe how you think each of these types of requirements might apply to FiredUp. How would you document the results of your requirements documentation? First, create a database to store requirements. User Interviews must be done with FiredUp staff only. You would not be able to interview Customers. You could observe processing of an invoice as well as observing the processing of a repair. You would need to use the Stove Warranty registration to obtain some information. The forms and reports would all be manual and probably no many would be available for this type of firm. At this point, there would be no formally defined interfaces. You would probably not use domain expertise here. Most of your information would come from the Robarbs. C. Consider your answer to FiredUp question E, Chapter 2. How would you go about validating this model? Make a list of questions that you would ask FiredUp personnel. In what ways could you use mockups? In what ways could you use prototypes? The first step is to develop a model based upon your requirements and the business background you have. Next, sit down with Curt and Julie Robarbs and review the design. At this point, your primary interest is whether you are missing any data and that you have the relationships and cardinalities are correct. Ask questions regarding these. You may use mockups of data entry forms and invoices if the Robarbs resist reviewing ER diagrams. You may want to create a prototype. On a project this size, it should be fairly easy to create and the prototype would eventually become the implemented database.

 ANSWERS TO TWIGS TREE TRIMMING SERVICE Review the Twigs Service Project Questions at the end of Chapter 2 and answer the questions there if you have not already done so. A. Consider the data modeling steps in Figure 3-1 in the context of Twigs business. Without having access to Samantha and other personnel (if any) at Twigs, it is not possible to

3-23

Chapter Three Entity-Relationship Data Modeling: Process and Examples

plan a data modeling project. Instead, make a list of questions that you would need to have answered in order to be able to plan a data modeling project. The answer here will depend upon the student’s experience with business subjects. Most of the questions are answered by the time you complete problem E. A few additional questions that might be asked would be; a. Should all jobs be recurring or should. b. Can there be Services that are neither recurring nor nor-recurring. c. Is it possible to be considered a customer but not have a service (prospective customer). B. With regard to requirements determination, consider the sources of requirements in Figure 3-2. Describe how you think each of these types of requirements might apply to Twigs. How would you document results of your requirements documentation? First, create a database to store requirements. User Interviews must be done with Twigs staff. You could observe Samantha billing a Customer. This may help understand how to relation Customer, Service, and Chip_Delivery. The forms and reports would all be manual and probably no many would be available for this type of firm. At this point, there would be no formally defined interfaces. You would probably not need to use domain expertise here. Most of you information would be obtained from Samantha. C. Consider your answer to Twigs question E, Chapter 2. How would you go about validating this model? Make a list of questions that you would ask Samantha. In what ways could you use mockups? In what ways could you use prototypes? The first step is to develop a model based upon your requirements and the business background you have. Next, sit down with Samantha and review the design. At this point, your primary interest is whether you are missing any data and that you have the relationships and cardinalities are correct. Ask questions regarding these. You may use mockups of data entry forms and invoices if the Samantha resist reviewing ER diagrams. You may want to create a prototype. On a project this size, it should be fairly easy to create and the prototype would eventually become the implemented database.

3-24