ANSWERS TO REVIEW QUESTIONS

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

Figure 5-54 E-mail List

Create a single-entity data model for this list. Specify the identifier and attributes.

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

Modify your answer to A to include entities SENDER and SUBJECT. Specify the identifiers and attributes of entities and the types and cardinalities of the relationships. Explain which cardinalities can be inferred from Figure 5-54 and which need to be checked out with users.

The E-R Crows 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 relationship between EMAIL_MESSAGE and SUBJECT can be implied because the subject repeats. For example, there are three messages with the Subject RE:Hotel.

the one-to-many relationship between EMAIL_MESSAGE and SENDER can also be implied because the sender repeats. For example, there are three messages from Tom Cooper.

The e-mail address in the From column in Figure 5-54 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.

The E-R Crows Foot model is identical to the design in part B except for the addition of two 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 DISPLAY_NAME) are implied because of the supertype/subtype relationship.

We should ask the users whether subject lines are required for all messages.

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

Figure 5-55 Stock Quotations

Create a single-entity data model for this list. Specify the identifier and attributes.

Modify your answer to A to include the entities COMPANY and INDEX. Specify the identifier and attributes of the entities and the types and cardinalities of the relationships. Explain which cardinalities can be inferred from Figure 5-55 and which need to be checked out with users.

The E-R Crows 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 INDEX and STOCK_QUOTE can be implied because there are multiple quotes in the single INDEX shown in the figure.

We need to determine if:

the one-to-many relationship between COMPANY and STOCK_QUOTE is correct. It is only correct if a COMPANY can be listed on multiple INDEXes.

the M-M relationship between INDEX and STOCK_QUOTE is correct. We have assumed that we are working 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 list in Figure 5-55 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.

The E-R Crows Foot model above is based on the data in the table on the next page.

Note that both the SYMBOL and STOCK_QUOTE entities are necessary. This is because a symbol now has multiple quotes, so the LastQuote information is now multivalued. We could also explain this in term of functional dependencies as follows: 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 own table.

RELATIONSHIP			CARDINALITY [Blue = Inferable]
PARENT	CHILD	TYPE	MAX	MIN
INDEX	SYMBOL	ID-Dependent	1:N	M-M
COMPANY	SYMBOL	ID-Dependent	1:N	M-O
SYMBOL	STOCK_QUOTE	ID-Dependent	1:N	M-M

We can infer that:

the one-to-many relationship between INDEX and STOCK_QUOTE can be implied because there are multiple quotes in the single INDEX shown in the figure.

the one-to-many relationship between SYMBOL and STOCK_QUOTE can be implied because there are multiple Quote Dates.

We need to determine if:

the one-to-many relationship between COMPANY and STOCK_QUOTE is correct. It is only correct if a COMPANY can be listed on multiple INDEXes.

the M-M relationship between INDEX and SYMBOL is correct. We have assumed that we are only recording stocks included in some stock 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 SYMBOL and STOCK_QUOTE is correct. We have assumed that we only work with SYMBOLs for stocks for which we want and have obtained a STOCK_QUOTE.

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 each relationship.

The E-R Crows Foot model above is based on the data in the table on the next page.

RELATIONSHIP			CARDINALITY [Blue = Inferable]
PARENT	CHILD	TYPE	MAX	MIN
INDEX	SYMBOL	ID-Dependent	1:N	M-M
COMPANY	SYMBOL	ID-Dependent	1:N	M-O
SYMBOL	STOCK_QUOTE	ID-Dependent	1:N	M-M
PORTFOLIO	PORT_ITEM	ID-Dependent	1:N	M-O
SYMBOL	PORT_ITEM	Non-ID-Dependent	1:N	M-O

We can infer that:

the one-to-many relationship between INDEX and STOCK_QUOTE can be implied because there are multiple quotes in the single INDEX shown in the figure.

the one-to-many relationship between STOCK_QUOTE and SYMBOL can be implied because there are multiple Quote Dates.

the one-to-many relationship between PORTFOLIO and PORT_ITEM can be implied because PORTFOLIOs by definition are intended to hold many PORT_ITEMS.

the one-to-many relationship between SYMBOL and PORT_ITEM can be implied because a single stock can be an item in many different portfolios.

We need to determine if:

the one-to-many relationship between COMPANY and STOCK_QUOTE is correct. It is only correct if a COMPANY can be listed on multiple INDEXes.

the M-M relationship between INDEX and 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 SYMBOL and STOCK_QUOTE is correct. We have assumed that we only work with SYMBOLs for stocks for which we want and have obtained a STOCK_QUOTE.

the M-O relationship between PORTFOLIO and PORT_ITEM is correct. We have assumed that a PORTFOLIO can be created before any PORT_ITEMS are added.

the M-O relationship between SYMBOL and PORT_ITEM is correct. We have assumed that just because we have a SYMBOL and an associated STOCK_QUOTE, this does not mean that we have actually purchased any shares of the stock itself.

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 each relationship.

The E-R Crows Foot model on the previous page is identical to the design in part D except for the addition of two subtypes STOCK_PURCHASE and STOCK_SALE to the entity PORT_ITEM. An additional attribute, TransactionType, is added to PORT_ITEM as a discriminator.

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

RELATIONSHIP			CARDINALITY [Blue = Inferable]
PARENT	CHILD	TYPE	MAX	MIN
PORT_ITEM	STOCK_PURCHASE	Subtype	1:N	M-O
PORT_ITEM	STOCK_SALE	Subtype	1:N	M-O

We can infer that:

the one-to-many relationships between (PORT_ITEM and STOCK_PURCHASE) and (PORT_ITEM and STOCK_SALE) are implied because of the supertype/subtype relationship.

the M-O relationships between (PORT_ITEM and STOCK_PURCHASE) and (PORT_ITEM and STOCK_SALE) are implied because of the supertype/subtype relationship.

The subtyping is exclusive (D for disjoint) since a transaction is either a sale or a purchase but not both.

We need to confirm that the subtyping is total (C for complete): we have assumed that every transaction is a sale or a purchase there are no other kinds of transactions.