• No results found

Chapter 5 Solutions

N/A
N/A
Protected

Academic year: 2021

Share "Chapter 5 Solutions"

Copied!
56
0
0

Loading.... (view fulltext now)

Full text

(1)

5.1 Examine the subscription form shown in Figure 5-55. Using the structure of this form, do the following:

Figure 5-59 – Subscription Form

A Create a model with one entity. Specify the identifier and attributes. SUBSCRIPTION SubNumber StartDate EndDate AmtDue LastName FirstName Address City State Zip PayCode

(2)

B Create a model with two entities, one for customer and a second for subscription. Specify identifiers, attributes, relationship name, type, and cardinalities.

SUBSCRIPTION is a weak, but not ID-dependent entity. Orders / Is Ordered By CUSTOMER CustomerNumber LastName FirstName Address City State Zip SUBSCRIPTION SubNumber StartDate EndDate AmtDue PayCode

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

PARENT CHILD TYPE MAX MIN

CUSTOMER SUBSCRIPTION Strong 1:N M-O

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 can only 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 can have one or more (i.e., multiple) subscriptions.

(3)

5.2 Consider the traffic citation shown in Figure 5-56. The rounded corners on this form provide graphical hints about the boundaries of the entities represented.

Figure 5-59 – Traffic Citation

A Create a data model with five entities. Use the data items on the form to specify identifiers and attributes for those entities.

Note that all Correction Notices have as their identifier an identifying number that is not shown in the figure, and have an independent existence whether or not they are filled in.. The alternative would be a composite identifier of (such as Month, Date, Year, Time). But this is so cumbersome that even without a preexisting NoticeNumber we’d use a surrogate identifier.

(4)

Is Cited By / Cites Is Given / Is Given To Specifies / Is Specified In Writes / Is Written By DRIVER LicenseState DriversLicense DriverLastName DriverFirstName DriverInitial Address City State Zip Sex BirthDate Height Weight EyeColor VEHICLE VIN State Make Model Year Color OwnerLastName OwnerFirstName OwnerInitial Address CORRECTION_NOTICE NoticeNumber Month Date Year Time District Detach Distance Direction City Highway ActionRequired VIOLATION Violation OFFICER PersonnelNumber OfficerLastName OfficerFirstName

(5)

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.

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

DRIVER CORRECTION_NOTICE Strong 1:N M-O

OFFICER CORRECTION_NOTICE Strong 1:N M-O

VEHICLE CORRECTION_NOTICE Strong 1:N M-O

CORRECTION_NOTICE VIOLATION ID-Dependent

Multi-valued

1:N M-O

We can infer that:

 Only one DRIVER is cited because there is only room for one set of DRIVER information. Logically, however, one DRIVER may receive more than one CORRECTION_NOTICE.

 Only one OFFICER issues the CORRECTION_NOTICE because there only one

OFFICER Signature. Logically, however, one OFFICER may issue many CORRECTION_NOTICES.

 Only one VECHILE is recorded because there is only room for one set of VEHICLE information. Logically, however, one VEHICLE may be subject to more than one CORRECTION_NOTICE.

 A CORRECTION_NOTICE can have several VIOLATIONs because of the multiple

lines and the wording Violations (plural). However, each VIOLATION is associated with the CORRECTION_NOTICE it is listed on.

We need to check:

 The logic that one DRIVER may receive more than one CORRECTION_NOTICE.

 The logic that one OFFICER may issue many CORRECTION_NOTICES.

 The logic that one VEHICLE may be subject to more than one CORRECTION_NOTICE.

 The cardinality of RegisteredOwner on VEHICLE. For example, the RegisteredOwner

(6)

Although it’s not a cardinality, we also need to check:

 How to handle VIOLATIONs. If VIOLATIONs are standardized, we should have put a

ViolationCode in the VIOLATION entity instead of Violation. ViolationCode will have an N-to-1 relationship to a standardized text description in an additional entity named VIOLATION_DEFINITON.

(7)

5.3 Examine the list of e-mail messages in Figure 5-57. Using the structure and example data items in this list, do the following:

Figure 5-57 – E-mail List

A Create a single-entity data model for this list. Specify the identifier and all entities.

No attribute is unique, a even the combination of all the shown attributes is not necessarily unique. Therefore, a surrogate identifier of EmailMessageID was created.

EMAIL_MESSAGE EmailMessageID From Subject Date Size

(8)

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 the relationships. Explain which cardinalities can be inferred from Figure 5-57 and which need to be checked out with users.

Sent / Sent By Of / About EMAIL_MESSAGE EmailMessageID Date Size SENDER UserID EmailAddress DisplayName SUBJECT Subject

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

SENDER EMAIL_MESSAGE Strong 1:N M-O

SUBJECT EMAIL_MESSAGE Strong 1:N M-O

We can infer that:

 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.

(9)

C The e-mail address in the From column in Figure 5-57 is in two different styles. One style has the true e-mail address; the second style (e.g., Tom Cooper) is the name of an entry in the user's e-mail directory. Create two categories of SENDER based on these two styles. Specify identifiers and attributes.

FromDisplay Sent / Sent By Of / About EMAIL_MESSAGE EmailMessageID Date Size SENDER UserID EmailAddress FromDisplay SUBJECT Subject EMAIL_ADDRESS Display_Address DIRECTORY_NAME Display_Name

The E-R Crow’s Foot model on the previous page is identical to the design in part B except for the addition of to subtypes EMAIL_ADDRESS and DIRECTORY_NAME to the entity SENDER. An additional attribute, FromDisplay is added to SENDER as a discriminator. The information about the supertype / subtype relationships is in the following table:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

SENDER EMAIL_ADDRESS Subtype 1:N M-O

SENDER DIRECTORY_NAME Subtype 1:N M-O

We can infer that:

 The one-to-many relationships between (SENDER and EMAIL_ADDRESS) and

(SENDER and DISPLAY_NAME) are implied because of the supertype/subtype relationship.

 The M-O relationships between (SENDER and EMAIL_ADDRESS) and (SENDER and

(10)

5.4 Examine the list of stock quotes in Figure 5-58. Using the structure and example data items in this list, do the following:

Figure 5-58 – Stock Quotations

A Create a single-entity data model for this list. Specify the identifier and attributes. STOCK_QUOTE Symbol Name LastQuote Change PercentChange

(11)

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 of the

relationships. Explain which cardinalities can be inferred from Figure 5-58 and which need to be checked out with users.

STOCK_QUOTE IndexID CompanyID Symbol LastQuote Change PercentChange COMPANY CompanyID CompanyName CompanyCountry INDEX IndexID IndexName IndexCountry

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

INDEX STOCK_QUOTE ID-Dependent 1:N M-M

COMPANY STOCK_QUOTE ID-Dependent 1:N M-O

We can infer that:

 The one-to-many relationship between COMPANY and STOCK_QUOTE can be implied

because there are multiple Company names. We need to determine if:

 The one-to-many relationship between INDEX and STOCK_QUOTE is correct. It

(12)

 The M-M relationship between INDEX and STOCK_QUOTE is correct. We have assumed that we are only with stocks listed on an INDEX.

 The M-O relationship between COMPANY and STOCK_QUOTE is correct. We can

infer that If a COMPANY has a STOCK_QUOTE it must be in the COMPANY table, but we have assumed that a COMPANY can be included without us having obtained a STOCK_QUOTE for it yet.

C The list in Figure 5-58 is for a quote 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 to reflect this change.

STOCK_QUOTE QuoteDate IndexID CompanyID LastQuote Change PercentChange COMPANY CompanyID CompanyName CompanyCountry INDEX IndexID IndexName IndexCountry STOCK_QUOTE_SYMBOL IndexID CompanyID Symbol

The E-R Crow’s Foot model above is based on the data in the table on the next page. Note that both of the STOCK_QUOTE_SYMBOL and STOCK_QUOTE tables are necessary. This is because (IndexID, CompanyID)  Symbol, and if we had just added QuoteDate to STOCK_QUOTE in question A, we would have had Symbol functionally dependent on part of the composite key (QuoteDate, IndexID, CompanyID). This violates BCNF, and we must break out the (IndexID, CompanyID)  Symbol dependency into its

(13)

RELATIONSHIP CARDINALITY [Blue = Inferable]

PARENT CHILD TYPE MAX MIN

INDEX STOCK_QUOTE_SYMBOL

ID-Dependent 1:N M-M

COMPANY STOCK_QUOTE_SYMBOL

ID-Dependent 1:N M-O

STOCK_QUOTE_SYMBOL STOCK_QUOTE

ID-Dependent 1:N M-M

We can infer that:

 The one-to-many relationship between COMPANY and STOCK_QUOTE_SYMBOL can

be implied because there are multiple Company names.

 The one-to-many relationship between STOCK_QUOTE and

STOCK_QUOTE_SYMBOL can be implied because there are multiple Quote Dates. We need to determine if:

 The one-to-many relationship between INDEX and STOCK_QUOTE_SYMBOL is

correct. It should be because there is more than one stock index in just the United States.

 The M-M relationship between INDEX and STOCK_QUOTE_SYMBOL is correct. We

have assumed that we are only with stocks listed on an INDEX.

 The M-O relationship between COMPANY and STOCK_QUOTE is correct. We can

infer that If a COMPANY has a STOCK_QUOTE it must be in the COMPANY table, but we have assumed that a COMPANY can be included without us having obtained a STOCK_QUOTE for it yet.

 The M-M relationship between STOCK_QUOTE_SYMBOL and STOCK_QUOTE is

correct. We have assumed that we only with STOCK_SYMBOLs for stocks for which we want and have obtained an STOCK_QUOTE.

(14)

D Change your model in C to include the tracking of a portfolio. Assume the portfolio has an owner name, a phone number, an e-mail 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. STOCK_QUOTE QuoteDate IndexID CompanyID LastQuote Change PercentChange COMPANY CompanyID CompanyName CompanyCountry INDEX IndexID IndexName IndexCountry STOCK_QUOTE_SYMBOL IndexID CompanyID Symbol PORTFOLIO_ITEM PortfolioID PortfolioItemID IndexID CompanyID NumberOfShares PORTFOLIO PortfolioID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail

(15)

RELATIONSHIP CARDINALITY [Blue = Inferable]

PARENT CHILD TYPE MAX MIN

INDEX STOCK_QUOTE_SYMBOL

ID-Dependent 1:N M-M

COMPANY STOCK_QUOTE_SYMBOL

ID-Dependent 1:N M-O

STOCK_QUOTE_SYMBOL STOCK_QUOTE

ID-Dependent 1:N M-M

PORTFOLIO PORTFOLIO_ITEM

ID-Dependent

1:N M-O

STOCK_QUOTE_SYMBOL PORTFOLIO_ITEM

Non-ID-Dependent 1:N M-O

We can infer that:

 The one-to-many relationship between COMPANY and STOCK_QUOTE_SYMBOL can

be implied because there are multiple Company names.

 The one-to-many relationship between STOCK_QUOTE and

STOCK_QUOTE_SYMBOL can be implied because there are multiple Quote Dates.

 The one-to-many relationship between PORTFOLIO and PORTFOLIO_ITEM can be

implied because PORTFOLIOs by definition are intended to hold many PORTFOLIO_ITEMS.

 The one-to-many relationship between STOCK_QUOTE_SYMBOL and

PORTFOLIO_ITEM can be implied because PORTFOLIOs by definition are intended to hold many stocks, each of which will have a different STOCK_QUOTE_SYMBOL. We need to determine if:

 The one-to-many relationship between INDEX and STOCK_QUOTE_SYMBOL is

correct. It should be because there is more than one stock index in just the United States.

 The M-M relationship between INDEX and STOCK_QUOTE_SYMBOL is correct. We

have assumed that we are only with stocks listed on an INDEX.

 The M-O relationship between COMPANY and STOCK_QUOTE is correct. We can

infer that If a COMPANY has a STOCK_QUOTE it must be in the COMPANY table, but we have assumed that a COMPANY can be included without us having obtained a STOCK_QUOTE for it yet.

(16)

 The M-M relationship between STOCK_QUOTE_SYMBOL and STOCK_QUOTE is correct. We have assumed that we only with STOCK_SYMBOLs for stocks for which we want and have obtained an STOCK_QUOTE.

 The M-O relationship between PORTFOLIO and PORTFOLIO_ITEM is correct. We

have assumend that a PORTFOLIO can be created before any PORTFOLIO_ITEMS are added.

 The M-O relationship between STOCK_QUOTE_SYMBOL and PORTFOLIO_ITEM is

correct. We have assumed that we just because we have a STOCK_SYMBOL and an associated STOCK_QUOTE does not mean that we have purchased the stock.

(17)

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.

TransactionType STOCK_QUOTE QuoteDate IndexID CompanyID LastQuote Change PercentChange COMPANY CompanyID CompanyName CompanyCountry INDEX IndexID IndexName IndexCountry STOCK_QUOTE_SYMBOL IndexID CompanyID Symbol PORTFOLIO_ITEM PortfolioID PortfolioItemID IndexID CompanyID NumberOfShares TransactionType PORTFOLIO PortfolioID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail STOCK_PURCHASE PortfolioID PortfolioItemID PurchaseDate PricePerShare STOCK_SALE PortfolioID PortfolioItemID SellDate PricePerShare

(18)

The E-R Crow’s Foot model on the previous page is identical to the design in part D except for the addition of to subtypes STOCK_PURCHASE and STOCK_SALE to the entity PORFOLIO_ITEM. An additional attribute, TransactionType is added to

PORTFOLIO_ITEM as a discriminator. above is based on the data in the table on the next page.

The information about the supertype / subtype relationships is in the following table:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

PORTFOLIO_ITEM STOCK_PURCHASE Subtype 1:N M-O

PORTFOLIO_ITEM STOCK_SALE Subtype 1:N M-O

We can infer that:

 The one-to-many relationships between (PORTFOLIO_ITEM and

STOCK_PURCHASE) and (PORTFOLIO_ITEM and STOCK_SALE) are implied because of the supertype/subtype relationship.

 The M-O relationships between (PORTFOLIO_ITEM and STOCK_PURCHASE) and

(PORTFOLIO_ITEM and STOCK_SALE) are implied because of the supertype/subtype relationship.

(19)

5.5 Figure 5-59 shows the specifications for single-stage air compressor products. Note that there are two product categories that are based on 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:

Figure 5-59 – Air Compressor Specifications

(20)

A Create a set of exclusive subtypes to represent these compressors. The supertype will have attributes for all single-stage compressors, and the subtypes will have attributes for products having the two different types of Air Performance. Assume that there might be additional products with different types of Air Performance. Specify the entities, identifiers, attributes, relationships, type of category cluster, and possible determinant.

The first entity with be SS_COMPRESSOR, with an identifier of Model.

Note that the date in Figure 5-59 shows model numbers with an “A” to indicate the type A =125 PSI Air Performance Characteristics – for example F12A-17. The data sheet notes that type E = 150 PSI units are indicated by substituting an “E” for the “A” – for example, F12E-17. In our data model, we will design the identifier Model to specify models with an “x” as an indeterminant place holder for model type – “x” as in F12x-17.

An attribute named ModelType (with values “A”, “E” and others when available) will be a determinant of the subtypes. An instance diagram looks like this:

ModelType SS_COMPRESSOR Model HP TankGallons ApproxShipWeight Length Weight Height ModelType A AirPerformance PumpRPM E AirPerformance PumpRPM

(21)

The entities, identifiers, attributes, relationships are shown in the diagram. This is an incomplete category cluster – according to the question, there may be “additional products with different types of Air Performance” which means there may be more models then just the A and E models shown.

B Figure 5-60 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 5-59?

2 SS_COMPRESSOR Model HP TankGallons ApproxShipWeight Length Weight Height AIR_PERFORMANCE_TYPE AirPerformance PumpRPM CFMDisp DeliveredAir

Figure 5-60 – Alternative Model for Compressor Data

The model in Figure 5-60 uses SS_COMPRESSOR is a strong entity and

AIR_PERFORMANCE_TYPE is an ID-Dependent weak entity. The relationship is one-to-many since an SS_COMPRESSOR may have more than one AIR_PERFORMANCE_TYPE. However, the maximum cardinality of the one-to-many relationship is currently two (2) since there are currently only two AIR_PERFORMANCE_TYPEs. The relationship is

ID-dependent since the AIR_PERFORMANCE TYPE entity is meaningless without an associated SS_COMPRESSOR.

Unfortunately, the model misses the fact that each AIR_PERFORMANCE_TYPE is related to many SS_COMPRESSORs, and therefore the relationship should be N:M (or N:2 given the limit of only two (2) existing AIR_PERFORMANCE_TYPESs. This can be seen in the following instance diagram:

(22)

Further, each combination will have at least one additional attribute associated with it – namely Price. Therefore a better model would be an ID-Dependent associate model, with two strong entities – SS_COMPRESSOR and AIR_PERFORMANCE TYPE. That model would look like this:

SS_COMPRESSOR Model HP TankGallons ApproxShipWeight Length Width Height AIR_PERFORMANCE_TYPE ModelType AirPerformance PumpRPM CFMDisp DeliveredAir

(23)

C Compare your answer in question A with the model in Figure 5-60. What are the essential differences between the two models? Which do you think is better?

Model A models the ModelType as an incomplete category cluster currently with two subtypes. The model will allow additional types because it is incomplete but the additional types will just repeat the PumpRPM, CFMDisp, and DELDAir attributes. Unless there are other attributes to differentiate and distinguish between the subtypes, this is an inappropriate use of subtypes. Subtypes are intended to capture varying sets of additional attributes, not repetitions of the same set. Further, this model also creates repeated sets of basic

SS_COMPRESSOR values since there must be one set for each AirPerformanceType. Model B models the MODELTYPE entity (as AIR_PERFORMANCE_TYPE) as an ID-dependent weak entity which allows for an unlimited number of types even if they all have exactly the same set of attributes. This is a better way of modeling repeating sets of

attributes. Unfortunately, the model missed the N:M nature of the basic relationship between SS_COMPRESOR and AIR_PERFORMANCE_TYPE.

Model B uses a better model for repeated sets of attributes, but fails by being a 1:N model rather than a N:M model, or, specifically an ID-Dependent associative pattern. Since both models have serious flaws, ultimately neither is better than the other.

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:

(1) The ability of model A use supertype/subtypes to handle different sets of attributes for a basic supertype, and

(2) The ability of model B to allow an unlimited number of air compressor characteristics. This makes the model much more versatile. But,

(3) I would then have to point out that neither does the complete job and that model C is needed.

(24)

5.6 Figure 5-61 shows a listing of movie times at theaters in Seattle. Using the data in this figure as an example, do the following:

(25)

A Create a model to represent this report using the entities MOVIE, THEATER, and SHOW_TIME. Assume that theaters may show multiple movies. Although this report is for a particular day, your data model should allow for movie times on different days as well. Specify the identifier of the entities and their attributes. Name the

relationships and the type and cardinality of all relationships. Explain which

cardinalities you can logically deduce from and which need to be checked out with users. Assume that distance is an attribute of THEATER.

Is-Shown-At /

Is-Show-Time-For Is-Showing-At /Is-Show-Time-At THEATER TheaterName Address City Phone DistanceToCenterOfSeattle MOVIE MovieName Description SHOW_TIME MovieName TheaterName DayOfWeek Date ShowTime

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

MOVIE SHOW_TIME ID-Dependent 1:N M-O

THEATER SHOW_TIME ID-Dependent 1:N M-O

We can infer that:

 The one-to-many relationship between MOVIE and SHOW_TIME can be inferred

because there are multiple SHOW_TIMEs listed.

 The one-to-many relationship between THEATER and SHOW_TIME can be inferred

(26)

We need to determine if:

 The M-O relationship between MOVIE and SHOW_TIME is correct. We have assumed

that there may be MOVIEs in the database that are not currently scheduled to be shown.

 The M-O relationship between THEATER and SHOW_TIME is correct. We have

assumed that a THEATER may be in the database even though it is not currently showing any MOVIEs.

 Although it doesn’t matter, we don’t know if one THEATER can show more than one MOVIE. It doesn’t matter because the data model will automatically accommodate a THEATER showing more than one movie.

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 Seattle suburb such as Bellevue, Renton, Redmond, or Tacoma. In this case, distance cannot be an attribute of THEATER. Change your answer in A for this situation. Specify the entity identifiers and attributes. Name the relationships and identify the type and cardinality of all relationships.

Is-Shown-At / Is-Show-Time-For Is-Showing-At / Is-Show-Time-At To-Area-Is / To-Area-From To-Theater-Is / To-Theater-From THEATER TheaterName Address City Phone MOVIE MovieName Description SHOW_TIME MovieName TheaterName DayOfWeek Date ShowTime AREA AreaName DISTANCE TheaterName AreaName DistanceToCenterOfArea

(27)

The E-R Crow’s Foot model above is based on the model in question A, but adds the entities AREA and DISTANCE. Note that this must be modeled as an association pattern rather than an N:M relationship because of the attribute DistanceToCenterOfArea. The data for the new parts of the model are contained in the following table:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

AREA DISTANCE ID-Dependent 1:N M-O

THEATER DISTANCE ID-Dependent 1:N M-O

We can infer that:

 The one-to-many relationship between AREA and DISTANCE can be inferred because

there are multiple AREASs listed in Question B.

 The one-to-many relationship between THEATER and DISTANCE can be inferred

because there are multiple DISTANCEs listed in Figure 5-61. We need to determine if:

 The M-O relationship between AREA and DISTANCE is correct. We have assumed that

there may be AREAs in the database that do not currently have THEATERS in the schedule.

 The M-O relationship between THEATER and DISTANCE is correct. We have assumed

that a THEATER may be in the database even though it is not in one of the specified AREAs.

(28)

C Suppose that you want to make this data model national. Change your answer to B so that it can be used for other metropolitan areas. Change your answer in A for this situation. Specify the entity identifiers and attributes. Name the relationships and identify the type and cardinality of all relationships.

Is-Shown-At / Is-Show-Time-For Is-Showing-At / Is-Show-Time-At To-Area-Is / To-Area-From To-Theater-Is / To-Theater-From THEATER TheaterName City State Address ZIP Phone MOVIE MovieName Description SHOW_TIME MovieName TheaterName City State DayOfWeek Date ShowTime AREA AreaName AreaState DISTANCE TheaterName City State AreaName AreaState DistanceToCenterOfArea

This is the same data model shown in the answer to question C adjusted for multiple states. The attributes State and ZIP where added to THEATER and the identifier has changed to (TheaterName, City, State) to allow for non-unique TheaterNames. The Attribute AreaState was added to AREA, and made part of the composite identifier (AreaName, AreaState) to allow for non-unique AreaNames. Note that the change to these identifiers show up in the composite identifiers of SHOW_TIME and DISTANCE.

(29)

D Modify your answer to 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 the relationships and identify the type and cardinality of all

relationships. Is-Shown-At / Is-Show-Time-For Is-Showing-At / Is-Show-Time-At To-Area-Is / To-Area-From To-Theater-Is / To-Theater-From Is-In-Cast-Of / Cast-Includes THEATER TheaterName City State Address ZIP Phone MOVIE MovieName Description SHOW_TIME MovieName TheaterName City State DayOfWeek Date ShowTime AREA AreaName AreaState DISTANCE TheaterName City State AreaName AreaState DistanceToCenterOfArea ACTOR ActorName

(30)

The E-R Crow’s Foot model above is based on the model in question C, but adds the entity ACTOR. Since there are no additional attributes needed, this can be modeled as an N:M relationship. The data for the new parts of the model are contained in the following table:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

ACTOR MOVIE Strong N:M O-O

We can infer that:

 The one-to-many relationship between ACTOR and MOVIE can be inferred based on

general knowledge of movie – One ACTOR can and does appear in more than one MOVIE, and one MOVIE can and does have more than one ACTOR in the cast. We need to determine if:

 The O-O relationship between ACTOR and MOVIE is correct. We have assumed that

there may be ACTORSs in the database that are not currently in any MOVIE in the database, and that there may MOVIEs in the database that actually do use any ACTORs (a nature documentary, for example).

(31)

E Modify your answer to C to include the leading cast members. Assume that the role of a cast member is specified. Specify the identifier of new entities and their

attributes. Name the relationships and identify the type and cardinality of all relationships. Is-Shown-At / Is-Show-Time-For Is-Showing-At / Is-Show-Time-At To-Area-Is / To-Area-From To-Theater-Is / To-Theater-From Plays / Is-Played-By Has-Cast / Is-Cast-For THEATER TheaterName City State Address ZIP Phone MOVIE MovieName Description SHOW_TIME MovieName TheaterName City State DayOfWeek Date ShowTime AREA AreaName AreaState DISTANCE TheaterName City State AreaName AreaState DistanceToCenterOfArea MOVIE_CAST ActorName MovieName Role ACTOR ActorName

(32)

The E-R Crow’s Foot model above is based on the model in question D, but adds the attribute of Role. Because of this extra attribute, we now have to model the relationship between ACTOR and MOVIE using the association patter. The data for the new parts of the model are contained in the following table:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

ACTOR MOVIE_CAST ID-Dependent 1:N M-O

MOVIE MOVIE_CAST ID-Dependent 1:N M-O

We can infer that:

 The one-to-many relationship between ACTOR and MOVIE_CAST can be inferred

based on general knowledge of movie – One ACTOR can and does appear in more than one MOVIE.

 The one-to-many relationship between MOVIE and MOVIE_CAST can be inferred can

based on general knowledge of movie – One MOVIE can and does have more than one ACTOR in a MOVIE_CAST.

We need to determine if:

 The M-O relationship between ACTOR and MOVIE _CASTis correct. We have assumed

that there may be ACTORSs in the database that are not currently in any MOVIE in the database.

 The M-O relationship between MOVIE and MOVIE _CASTis correct. We have assumed

that there may MOVIEs in the database that actually do use any ACTORs (a nature documentary, for example).

(33)

5.7 Consider the three reports in Figure 5-62. The data are samples of data that would appear in the reports like these.

(34)

A Make a list of as many potential entities as these reports suggest.  COMPANY  PRODUCT  INGREDIENT  NUTRIENT  VITAMIN-IRON  USDA-RDA  FDA REPORT  ISSUER  SUPPLIER

B Examine your list to determine whether any entities are synonyms. If so, consolidate your list.

COMPANY and ISSUER are synonyms. The company issues the reports to the FDA. We will remove Issuer from the entity list.

==================================================================

Crow’s Foot Model for Question C:

FDA_REPORT is a weak, but not ID-Dependent

entity NOTE: Every PRODUCT must contain at least one INGREDIENT, but every INGREDIENT need not be used in a PRODUCT. Contains / Is Used In Has / From Is In / Contains Has / From Issues / Is Issued By Is Listed In / Lists Is Listed In / Lists

Is Available From At Cost Of / Has Available

Has Available At Price Of / Is Available From Produces / Is Produced By COMPANY CompanyName PRODUCT ProductID ProductName ServingSizeOzs ServingSizeGrams ServingsPerPackage INGREDIENT IngredientID IngredientName NUTRIENT NutientID NutrientName FDA_REPORT ReportNumber Date Title PRODUCT_NUTRIENT ProductOnlyAmt ProductOnlyAmtUnits ProductWithSkimMilkAmt ProductWithSkimMilkUnits ProductWithWholeMilkAmt ProductWithWholeMilkUnits INGREDIENT_SUPPLIER Price

(35)

C Construct a crow's foot model 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.

The E-R Crow’s Foot model is on the previous page. It is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

COMPANY PRODUCT Strong 1:N M-O

COMPANY FDA_REPORT Weak 1:N M-O

INGREDIENT PRODUCT Strong N:M M-O

INGREDIENT FDA_REPORT Weak 1:N M-O

INGREDIENT INGREDIENT_ SUPPLIER ID-Dependent Associative 1:N M-O SUPPLIER INGREDIENT_

SUPPLIER ID-DependentAssociative 1:N M-O

PRODUCT FDA_REPORT Weak 1:N M-O

PRODUCT PRODUCT_

VIT_IRON ID-DependentAssociative 1:N M-O

VITAMIN_IRON PRODUCT_

VIT_IRON ID-DependentAssociative 1:N M-O

PRODUCT PRODUCT_ NUTRIENT ID-Dependent Associative 1:N M-O NUTRIENT PRODUCT_

(36)

Cardinalities that we can infer from the reports are shown in blue in the table. In particular, we can infer that:

 A COMPANY can produce several PRODUCTS because of the multiple lines in the FDA

Report. We will assume that only COMPANYs that produce PRODUCTS listed in the database are to be listed in COMPANY. We will further assume that each PRODUCT is produce by only one COMPANY.

 A PRODUCT can use many INGREDIENTS because of the multiple INGREDIENTS

used in PRODUCTs in the FDA Report. On the same basis, we can infer that one INGREDIENT can be used in many PRODUCTS.

 A SUPPLIER can supply many INGREDIENTS, and the each INGREDIENT may be

available from more than one SUPPLIER, based on the Suppliers List Report. However, SUPPLIERS do not necessarily provide a particular INGREDIENT, and an

INGREDIENT is not necessarily available for a particular SUPPLIER. We also note that Prices vary depending upon which SUPPLIER is providing which INGREDIENT. We will assume that SUPPLIERs can be entered into the database when they are initially contacted but before they provide the information needed (INGREDIENTs they will supply and their associated Prices) for use with the INGREDIENT_SUPPLIER entity.

 The mandatory one to many relationships between:

1. PRODUCT, NUTRIENT and the ID-dependent associative entity PRODUCT_NUTRIENT.

2. PRODUCT, VITAMIN_IRON and the ID-dependent associate entity PRODUCT_VIT_IRON.

We can do this because Nutrition Information is required to be printed on PRODUCT packaging, and since that information must include information about information about NUTRIENTS and VITAMINS-IRON (Vitamins and Iron),

We need to check:

 The assumption stated above that only COMPANYs that produce PRODUCTs listed in

the database are in be listed in COMPANY.

 The assumption stated above that each PRODUCTs is produced by only one COMPANY.

 The assumption stated above that only SUPPLIERs that supply INGREDIENTs listed in the database are in be listed in SUPPLIER.

 Whether or not an INGREDIENT can be included in INGREDIENT if there is no current

SUPPLIER for it.

 Whether or not all COMPANYs must issue FDA Reports. Currently, this is optional. Although it’s not a cardinality, we also need to check:

 How to handle COMPANY and SUPPLIER. These could be designed as subtypes

PRODUCER and SUPPLIER of a supertype COMPANY. This would be necessary if a COMPANY could also be a SUPPLIER to other COMPANYs.

(37)

5.8 Consider the CD cover in Figure 5-63.

Figure 5-63 – CD Cover

A Specify identifiers and attributes for the entities CD, ARTIST, ROLE, and SONG.

We need to be careful here. West Side Story was a Broadway production in New York City, New York. Much of the data listed on the CD is about the Broadway production, not the CD itself. For example, West Side Story was produced, directed and choreographed by Jerome Robbins, but nowhere are we told who actually produced the CD.

The only data that really applies to the CD itself are: CD (CDTitle, CDComments)

where the CD_Title is “West Side Story” and the CD_Comments are “Highlights From The Complete Recording.”

To accommodate the other West Side Story material, let’s a CD_Cover_Notes column. Note that this is not a general-purpose remarks column – Many CDs have a set of cover notes describing the CD artist(s) and the material on the CD. So, now we have:

CD (CDTitle, CDComments, CDCoverNotes)

If we need to detail the data on the West Side Story production itself, we’ll need to add a BROADWAY_PRODUCTION entity to the model. In fact, that may prove necessary to handle the ROLE entity the problem calls for.

(38)

There is a problem with ROLE. Without a PRODUCTION entity, the identifier of a “role “ is incomplete. ROLE should be an ID-dependent entity dependent on PRODUCITON.

Consider the ROLE of “Maria” – are we talking about Maria in West Side Story or Maria in The Sound Of Music? We’ll add a Production attribute to ROLE to take care of this, and use the composite identifier (Production, Role).

Then there is the fact that on this CD the ROLEs are credited with singing the songs, not the ARTISTs. This is coupled with the fact that some ROLEs are generic and not associated with a specific artist. For example, consider “Chorus,” “Entire cast.” “Orchestra,” etc. We will use these as ROLEs, and these ROLEs will have no ARTIST or more than one ARTIST assigned to them even within one PRODUCTION (For example, all the ARTISTs in the PRODUCTION are in “Entire Cast”)!

In this model, if we want to know which ARTIST is singing the SONG, we will have to connect through ROLE.

Finally, another problem will be handling the attribute TrackNumber. The songs on the CD are ordered by track number. But consider that this CD has the highlights from the complete recording. Could the same SONG appear on another CD with a different TrackNumber? It seems likely. A CD_TRACK entity, similar to a SALES_ITEM entity is needed.

The other entities with a CD_TRACK entity are: CD_TRACK (CDTitle, TackNumber)

ARTIST (ArtistLastName, ArtistFirstName) ROLE (Production, Role)

(39)

B Construct a crow's foot model showing relationships among these four 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. Is-Performed-By / Performs Is-Played-By / Plays Has / Is-On Appears-As / Is CD CDTitle CDComments CDCoverNotes SONG SongTitle SongTime ROLE Production Role ARTIST ArtistLastName ArtistFirstName CD_TRACK CDTitle TrackNumber SongTitle

(40)

The E-R Crow’s Foot model above is based on the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

CD CD_TRACK ID-Dependent 1:N M-M

SONG CD_TRACK

Non-ID-dependent

1:N M-O

ROLE SONG Strong N:M M-O

ARTIST ROLE Strong N:M M-O

We can infer that:

 The one-to-many relationship between CD and CD_TRACK can be inferred because

there are multiple CD_TRACKs listed.

 The M-M relationship between CD and CD_TRACK can be inferred from the definition

of a CD. All CDs must include one or more CD_TRACKs. A CD_TRACK must appear on a CD in order to exist in the first place .

 The many-to-many relationship between ROLE and SONG can be inferred because one

ROLE is listed as performing many SONGs, and there are some SONGs are listed as being performed by multiple ROLEs.

 The many-to-many relationship between ARTIST and ROLE can be inferred because one

ARTIST is included in many ROLEs (when we include group ROLLs such as “Entire Cast”), and one ROLEs may include many ARTISTs (when we include group ROLLs such as “Entire Cast”).

We need to determine if:

 The one-to-many relationship between SONG and CD_TRACK. is correct. We have

assumed that one SONG can appear as a CD_TRACK on more than one CD. This seems reasonable based on our general knowledge of CDs, however, we only have data on one CD actually given to us.

 The M-O relationship between SONG and CD_TRACK. is correct. We have assumed

that a CD_TRACK must have an associated song, but that we may have SONGs recorded that do not yet appear as a CD_TRACK on any CD>

(41)
(42)

C Consider a CD that does not involve a musical, so there is no need for ROLE. However, the entity SONG_WRITER is needed. Create a crow's foot model 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. ArtistType Has / Is-On Appears-As / Is Is-Performed-By / Performs Is-Composed-Of / Is-Member-Of Wrote / Was-Written-By CD CDTitle CDComments CDCoverNotes SONG SongTitle SongTime ARTIST ArtistName ArtistType CD_TRACK CDTitle TrackNumber SongTitle ARTIST_INDIVIDUAL ArtistName ArtistLastName ArtistFirstName IS_SINGER IS_SONGWRITER ARTIST_GROUP ArtistName NumberOfMembers

(43)

The E-R Crow’s Foot model above is based on the answer to question B, but eliminates the ROLE entity and recognizes that

 SONGs are recorded by ARTISTs.

 ARTISTs may be either individuals (ARTIST_INDIVIDUAL) or groups

(ARTIST_GROUP) but not both (Exclusive subtypes).

 Groups (ARTIST_GROUPs are made up of individuals (ARTIST_INDIVIDUAL).

 Individuals (ARTIST_INDIVIDUAL) can be one or more of either singers (SINGER) or

songwriters(SONGWRITER)(Inclusive subtypes).

The new entities and relationships are shown in the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

ARTIST SONG Strong N:M M-O

ARTIST ARTIST_INDIVIDUAL Subtype 1:N M-O

ARTIST ARTIST_GROUP Subtype 1:N M-O

ARTIST_INDIVIDUAL SONGWRITER Subtype 1:N M-O

ARTIST_INDIVIDUAL SINGER Subtype 1:N M-O

SONG SONGWRITER Strong N:M M-O

We can infer that:

 The many-to-many relationship between ARTIST and SONG can be inferred because

ARTIST replaces ROLE in this model, but otherwise has the same relationship to SONG that ROLE had..

 The one-to-many relationship between ARTIST and ARTIST_INDIVIDUAL can be

inferred from the definition of a supertype/subtype relationship.

 The one-to-many relationship between ARTIST and ARTIST_GROUP can be inferred

from the definition of a supertype/subtype relationship.

 The one-to-many relationship between ARTIST_INDIVIDUAL and SONGWRITER can

be inferred from the definition of a supertype/subtype relationship.

 The one-to-many relationship between ARTIST_INDIVIDUAL and SINGER can be

inferred from the definition of a supertype/subtype relationship.

 The M-O relationship between ARTIST and ARTIST_INDIVIDUAL can be inferred

from the definition of a supertype/subtype relationship.

 The M-O relationship between ARTIST and ARTIST_GROUP can be inferred from the

(44)

 The M-O relationship between ARTIST_INDIVIDUAL and SONGWRITER can be inferred from the definition of a supertype/subtype relationship.

 The M-O relationship between ARTIST_INDIVIDUAL and SINGER can be inferred

from the definition of a supertype/subtype relationship. We need to determine if:

 The many-to-many relationship between SONG and SONGWRITER is correct. We have

assumed that one SONG can be by many SONGWRITERs, and that one SONGWRITER can write many SONGs. This seems reasonable based on our general knowledge of SONGs and SONGWRITERs, but needs to be checked.

 The M-O relationship between ARTIST and SONG . is correct. We have assumed that a SONG must have an associated ARTIST, but that we may have ARTISTS in the database that have not recorded a SONG yet..

 The M-O relationship between SONG and SONGWRITER is correct. We have assumed

that a SONGWRITER is only a SONGWRITER if her or she has actually written a SONG, but that a SONG may exits without a SONGWRITER (For example, tranditional folk songs).

(45)

D Combine the models you developed in your answers to 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. ArtistType Has / Is-On Appears-As / Is Is-Composed-Of / Is-Member-Of Wrote / Was-Written-By Plays / Is-Played-By Is-Performed-By / Performs CD CDTitle CDComments CDCoverNotes SONG SongTitle SongTime ARTIST ArtistName ArtistType CD_TRACK CDTitle TrackNumber SongTitle ARTIST_INDIVIDUAL ArtistName ArtistLastName ArtistFirstName IS_SINGER IS_SONGWRITER ARTIST_GROUP ArtistName NumberOfMembers SONGWRITER ArtistName Agency Publisher SINGER ArtistName VoiceType ROLE ArtistName Role

(46)

The E-R Crow’s Foot model above is based on the answer to question C, but adds back in the ROLE entity. Now however, we must figure out how to deal with ROLE. We could create an entity named PERFORMER and make ARTIST and ROLE its subtypes. But if we do that we would also need a relationship between ARTIST_INDIVIDUAL and ROLE, which creates a “looping” problem with the supertype/subtype hierarchy indentifiers. The solution is recognize ROLE as another subtype of ARTIST. This is a change for the solution in B, but in that diagram ARTIST really referred to what we are now calling ARTIST_INDIVIDUAL. The new entity and relationships are shown in the following data:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

ARTIST ROLE Subtype 1:N M-O

ARTIST_INDIVIDUAL ROLE Strong N:M M-O

We can infer that:

 The one-to-many relationship between ARTIST and ROLE can be inferred from the definition of a supertype/subtype relationship.

 The M-O relationship between ARTIST and ROLE can be inferred from the definition of a supertype/subtype relationship.

 The many-to-many relationship between ARTIST_INDIVIDUAL and ROLE can be

inferred because one INDIVIDUAL_ARTIST is included in many ROLEs on the West Side Story CD (when we include group ROLLs such as “Entire Cast”), and one ROLEs may include many ARTIST_INDIVIDUALs (when we include group ROLLs such as “Entire Cast”).

We need to determine if:

 The M-O relationship between ARTIST_INDIVIDUAL and ROLE is correct. We have

assumed that a ROLE must be played or filled by an ARTIST_INDIVIDUAL, but that we may have ARTIST_INDIVIDUALs in the database who have not played or filled any ROLE.

(47)

5.9 Consider the data model in Figure 5-45. How should this model be altered if the users want to keep track of how many of each part are used? Suppose, for example, that the wheel assembly requires four washers and the handle assembly requires just one, and the database must store these quantities. Hint: adding Quantity to this N:M relationship is analogous to adding Price to the N:M relationship in Figure 5-24.

Figure 5-45 – N:M Recursive Model for the Bill of Materials in Figure 5-44

An N:M relationship in a data model is converted to an N:M relationship in a database design by adding an intersection table. This is discussed in detail in Chapter Six, but was briefly described in this Chapter. The intersection table doe not need to be shown in a data model for an N:M relationship because it only hold a composite identifier made up of the identifiers of the two entities it is joining – there are no additional attributes beyond these in the table.

However, if an additional attribute is added to the intersection table, we now have an association pattern as discussed in this chapter, and the an additional entity must be added to the data model. The design is:

Contains-Part Is-Contained-In-Part PART PartName {OtherData} PART_QUANTITY PartName ContainsPartName QuantityUsed

(48)

ANSWERS TO MARICA’S CLEANERS PROJECT QUESTIONS

Suppose that you have been hired by Marcia's Dry Cleaning to create a database application to track customers, orders, and items. Marcia also wants to start a Frequent Cleaner's Club, whereby she will offer a 50 percent discount on every 10th customer order.

A Using your knowledge, create a data model for Marcia's business. Name each entity, describe its type, and indicate all attributes and identifiers. Name each relationship, describe its type, and specify minimum and maximum cardinalities.

Except for the addition of the Discount Award program, this is a basic Sales Order model as shown in Figure 5-35. The Item entity shown in that model is created as the Service entity in this model because Marcia offers dry cleaning services to her customers. Application logic will be needed to implement the Discount Award tracking in the database.

NOTE:

DISCOUNT_AWARD is a weak, but not ID-DEPENDENT entity Places / Is placed by Contains / Is contained in Is listed as / Is for Is awarded / Is awarded to Is based on / Is basis for 10 CUSTOMER CustomerSK Phone Email FirstName LastName ORDER InvoiceNumber Date Subtotal Discount SubtotalAfterDiscount Tax Total ORDER_ITEM ItemNumber Qty UnitPrice ExtendedPrice SERVICE Service Description UnitPrice DISCOUNT_AWARD AwardID StartCountDate OrderCount AwardDate

(49)

The following table details the relationships, their type and cardinalities:

RELATIONSHIP CARDINALITY

PARENT CHILD TYPE MAX MIN

CUSTOMER ORDER Strong 1:N M-O

CUSTOMER DISCOUNT_AWARD Weak 1:N M-O

DISCOUNT_AWARD ORDER Weak 1:10 M:O

ORDER ORDER_ITEM ID-Dependent 1:N M-O

SERVICE ORDER_ITEM Strong 1:N M-O

NOTE: VARAIBLE ANSWER - Except of the DISCOUNT_AWARD entity, this model is based on the Marcia’s Dry Cleaning database schema implemented in Chapter Seven. “SK” in the identifiers stands for Surrogate Key. Your students will probably create variations of this model.

B List any item in your answer to A that you believe should be checked out with Marcia and/or her employees.

NOTE: VARIABLE ANSWER - Your students will probably create different sets of assumptions. (1) When the customer takes their 50% discount, how will it be entered into the system?

(It is current shown as an adjustment in ORDER).

In Chapter Three and Chapter Four, we considered a number of possible functional dependencies and multivalued dependencies. Those possible dependencies would form the basis for many questions:

(2) Is each combination of First Name and Last Name unique? (Checking the composite

identifier (FirstName, LastName)

(3) How many phone numbers exist for each customer? (Checking for multivalued

dependency) CustomerSK  Phone and (FirstName. LastName)  Phone )

(4) How many customers exist for each phone? (Checking for multivalued

dependency) Phone  CustomerSK and Phone  (FirstName. LastName) )

(5) Is each Email address unique? (Checking the identifier Email)

(6) How many email addresses exist for each customer? (Checking for multivalued

(50)

If there are multivalued dependencies, then the design will have to be revised to move them into their own tables (See the Chapter Three and Chapter Four Macia’s Dry Cleaning project

(51)

ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS

Suppose that you have been hired by Morgan Importing to create a database application to track stores, purchases, shipments, and shippers. Sometimes several items are purchased from a store on a single visit, but do not assume that all of the items are placed on the same shipment. You want to track each item in a shipment and assign an insurance value to each item.

A Using your knowledge, create a data model for Morgan Importing. Name each entity, describe its type, and indicate all attributes and identifiers. Name each relationship, describe its type, and specify minimum and maximum cardinalities.

The following model implements the database that the student will actually build in Chapter Seven. It is built around the ID-Dependent association pattern shown in Figure 5-23, although in this model both of the ID-Dependent relationships of M-O.

Although we can’t assume that “all the items [purchased from a store on a single visit] are placed on the same shipment,” we will assume that all of each item purchased is on the same shipment. For example, suppose that in one purchase we buy:

Item 1 - 100 sets of fine china place settings, and Item 2 - 50 sets of bed linen.

Entities, entity types, attributes and identifiers are shown in the ER diagram on the following page. Relationships and relationship names are also shown in the ER diagram.

Then although Item 1 and Item 2 may not be on the same shipment, we assume that all 100 sets of Item 1 are on the same shipment (and the same for all 50 sets of Item 2). We will model

PURCHASE with each row documenting each item purchase even if multiple items were purchased at one time. Thus Item 1 and Item 2 above will each have one row in PURCHASE. The following table details the relationships, their type and cardinalities:

RELATIONSHIP CARDINALITY

[Blue = Inferable]

PARENT CHILD TYPE MAX MIN

STORE PURCHASE Strong 1:N M-O

PURCHASE SHIPMENT_ITEM ID-Dependent

Association 1:N M-O

SHIPPER SHIPMENT Strong 1:N M-O

(52)

Associative STORE StoreSK StoreName City Country Phone Fax Email Contact PURCHASE PurchaseSK Date Description Category PriceUSD SHIPMENT ShipmentSK ShipDate ShipperInvoiceNumber Origin Destination SHIPMENT ITEM InsuredValue SHIPPER ShipperName Phone Fax Email Contact

Also note that the 1:N relationship between PURCHASE and SHIPMENT_ITEM actually breaks the assumption that the entire lot of an purchased item must be shipped on the same shipment. The association pattern that is used for SHIPMENT_ITEM implicitly allows one PURCHASE to be matched with many SHIPMENTs. However, since this is the model the student will creating, we will keep this design, assume a business rule forbidding this practice, and document this data model in the rest of the Morgan Importing project questions in this chapter. Note that the Morgan Importing project questions in Chapter Eight will deal with just this problem.

NOTE: VARAIBLE ANSWER - This model is based on the Morgan Importing database schema implemented in Chapter Seven. “SK” in the identifiers stands for Surrogate Key. Your students will probably create variations of this model. Note, for example, some of the variations in the answer to Review Question 5.53.

(53)

STORE StoreSK StoreName City Country Phone Fax Email Contact PURCHASE PurchaseSK StoreSK (FK) Date Description Category PriceUSD SHIPMENT ShipmentSK ShipDate ShipperName (FK) ShipperInvoiceNumber Origin Destination SHIPMENT ITEM ShipmentSK (FK) Shipment_Item_Number InsuredValue PurchaseSK (FK) SHIPPER ShipperName Phone Fax Email Contact

Another variation would use a PURCHASE_ITEM entity to record the separate items that form one PURCHASE. We will still assume a pure “all of one PURCHASE (item) on one

SHIPEMENT” model, but looking ahead to Chapter Eight we will also include data on quantities and unit costs in this model. We will also show how students might handle an assumed

multivalued dependency. The E-R diagram for this model is on the next page.

Both of these last two models show possible variations, and these variation models are not fully documented in the rest of the answers to this set of project questions (the needed modification are straight forward). You and your students will probably create other variations, but these should give you a good reference point.

(54)

STORE StoreSK StoreName City Country Fax Email Contact PURCHASE PurchaseSK StoreSK (FK) Date TotalPriceUSD SHIPMENT ShipmentSK ShipDate ShipperName (FK) ShipperInvoiceNumber Origin Destination SHIPMENT ITEM ShipmentSK (FK) Shipment_Item_Number InsuredValue Purchase_Item_Number (FK) PurchaseSK (FK) SHIPPER ShipperName Phone Fax Email Contact PURCHASE_ITEM Purchase_Item_Number PurchaseSK (FK) Description Category QuantityPurchased PriceEachUSD PriceUSD STORE_PHONE StoreSK (FK) Phone

(55)

In Chapter Two it was stated that Phillip Morgan only shipped to Los Angeles. In Chapter Three and Chapter Four, we considered a number of possible functional dependencies and multivalued dependencies. These fact and those possible dependencies would form the basis for many questions:

(1) Do you ship only to Los Angeles, or also to other cities (re SHIPMENT.Destination)? (2) How many stores can exist in each city? (Checking for multivalued dependency)

City  Store

(3) Is each combination of City and Store Name unique? (Checking the composite identifier (City, StoreName)

(4) Is each Email address unique? (Checking the identifiers STORE.Email and

SHIPPER.Email)

(5) How many contacts are there per store? (Checking for multivalued dependencies

StoreSK  Contact and (City, StoreName)  Contact )

(6) How many phone numbers are there per store? (Checking for multivalued dependencies

StoreSK  Phone and (City, StoreName)  Phone )

(7) How many email addresses are there per store? (Checking for multivalued dependencies

StoreSK  Email and (City, StoreName)  Email )

(8) How many fax numbers are there per store? (Checking for multivalued dependencies

StoreSK  Fax and (City, StoreName)  Fax )

(9) How many contacts are there per shipper? (Checking for multivalued dependency

ShipperName  Contact)

(10) How many phone numbers are there per shipper? (Checking for multivalued

dependency ShipperName  Phone)

(11) How many email addresses are there per shipper? (Checking for multivalued

dependency ShipperName  Email)

(12) How many fax numbers are there per shipper? (Checking for multivalued

dependency ShipperName  Fax)

(13) Is the same item available from more than one store? (Checking for multivalued dependencies StoreSK  PURCHASE.(Description, Category) and (City,

StoreName)  PURCHASE.(Description, Category) )

If there are multivalued dependencies, then the design will have to be revised to move them into their own tables (See the Chapter Three and Chapter Four Morgan Importing project questions). The last question (number (12)) may call for a PURCHASE_ITEM table to be added to the design.

(56)

References

Related documents

We focus our proposals on institutional reform of monetary institutions for Colombia in three areas: i) the system of appointments for the Board and the Governor of the central

The original definition is due to Soille and Talbot (2001) in the case of grey level image analysis. If different scales coexist in the same image, this definition may therefore not

The first shows that despite very different factor endowments, small growers predominated in California just as they did in Europe; the second considers technological transfer

In this transformed landscape of public policy, medical evidence, and treatment options, we sought to document patterns and correlates of missed work in a contemporary

(4) An employer, having given due advance notice to terminate employment and not having exercised the option provided in subsection (2), shall be discharged forthwith of

One of the main findings is that when controlled for observed characteristics and sample selection, for men, public administration wages are at parity or lower than covered

Model 1 evaluates competition among exporting countries based on production costs, marketing costs, domestic and export subsidies, tariffs among the United States, Mexico,

The results will be com- pared with the allocation schemes proposed by Wyglinski and the ones proposed under peak error rate constraint for OFDM and LP-OFDM systems.. The