Chapter 6 – Transforming Data Models into Database Designs
Page 6-21
6.49 Explain the implications of each of the minimum cardinality specifications in Figure 6-40.
CUSTOMER → O-O TRANSACTION:
CUSTOMERs do not require TRANSACTIONs, and TRANSACTIONS do not require
ARTIST → M-O WORK:
ARTISTs (mandatory parent) can be entered into the system before their WORKs (optional child)
CUSTOMER → M-O CUSTOMER-ARTIST_INT:
CUSTOMERs do not require interests in ARTISTs. Furthermore, any interests are not associated
ARTIST → M-O CUSTOMER-ARTIST_INT:
The same logic above in CUSTOMER → M-O CUSTOMER-ARTIST_INT applies to this
relationship.
TRANSACTION → M-M WORK:
This is the most complicated relationship in the database. Once a WORK is associated with a
Chapter 6 – Transforming Data Models into Database Designs
Page 6-22
6.50 Explain the rationale for each of the entries in the table in Figure 6-42.
TRANSACTION → M-M WORK:
This is the most complicated relationship in the database.
INSERT: TRANSACTIONs include acquiring a WORK, so each WORK requires a
TRANSACTION. WorkID is a foreign key in TRANSACTION, so this value must be set
immediately. Thus an ON INSERT trigger on WORK will be used to create the associated
TRANSACTION. Since this is an acquisition and not a sale, only data for DateAcquired and
AcquisitionPrice are needed.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-23
❖ ANSWERS TO PROJECT QUESTIONS
6.51 Answer question 5.56 (note: see Chapter 5) if you have not already done so. Design a
database for your model in question 5.56(b). Your design should include a specification
of tables and attributes as well as primary, candidate, and foreign keys. Also specify how
you will enforce minimum cardinality. Document your minimum cardinality enforcement
using referential integrity actions for a required parent, if any, and the form in Figure 6-
29(b) for a required child, if any.
Minimum cardinalities are shown in the table below.
RELATIONSHIP
CARDINALITY
PARENT
CHILD
TYPE
MAX
MIN
Referential integrity actions for required parents are shown in the E-R diagram. The Foreign Key
CustomerNumber in SUBSCRIPTION is not null and referential integrity should be set.
CUSTOMER →SUBSCRIPTION
SYMBOL
INDICATES
Chapter 6 – Transforming Data Models into Database Designs
Page 6-24
6.52 Answer question 5.57 if you have not already done so. Design a database for your
model in question 5.57(b). Your design should include a specification of tables and
attributes as well as primary, candidate, and foreign keys. Also specify how you will
enforce minimum cardinality. Document your minimum cardinality enforcement using
referential integrity actions for required parents, if any, and the form in Figure 6-29(b) for
required children, if any.
ERRATA: should be 5.57(b)
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
Chapter 6 – Transforming Data Models into Database Designs
Page 6-25
Referential integrity actions for required parents are shown in the E-R diagram. There are no
required children in the database design.
SUBJECT required parent of EMAIL_MESSAGE
SQL ACTION ON PARENT (SUBJECT)
REF INTEGRITY ACTION
DELETE Subject
Prohibit or Cascade Delete all child messages
with that Subject
SQL ACTION ON CHILD (MESSAGE)
REF INTEGRITY ACTION
DELETE Message
OK
INSERT new message
Set Subject FK to existing parent, Set Defaults.
SENDER required parent of EMAIL_MESSAGE
SQL ACTION ON PARENT (SENDER)
REF INTEGRITY ACTION
DELETE Sender
Cascade Delete all child messages with that
Sender
INSERT new Sender
OK
SQL ACTION ON CHILD (MESSAGE)
REF INTEGRITY ACTION
DELETE Message
OK
INSERT new Subject
OK
Chapter 6 – Transforming Data Models into Database Designs
Page 6-26
6.53 Answer question 5.58 if you have not already done so. Design a database for your
model in question 5.58(d). Your design should include a specification of tables and
attributes as well as primary, candidate, and foreign keys. Also specify how you will
enforce minimum cardinality. Document your minimum cardinality enforcement using
referential integrity actions for required parents, if any, and the form in Figure 6-29(b) for
required children, if any.
I:R
D:C
U:C
I:SD
D:R
U:C
PORTFOLIO_ITEM
PortfolioID: int NOT NULL (FK)
PortfolioItemID: int NOT NULL
PORTFOLIO
PortfolioID: int NOT NULL
OwnerLastName: char(25) NOT NULL
Chapter 6 – Transforming Data Models into Database Designs
Page 6-27
The E-R Crow’s 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
STOCK_QUOTE_SYMBOL
ID–
Dependent
1:N
M-M
Referential integrity actions for required parents are shown in the E-R diagram and detailed
below.
INDEX required parent of STOCK_QUOTE_SYMBOL M-M
SQL ACTION ON PARENT (INDEX)
REF INTEGRITY ACTION
DELETE Index
Best to Prohibit (No Action). Cascade Delete of
Symbol child rows may cause deletion of Stock
Quote rows in another relationship.
ID–
Dependent
STOCK_QUOTE_SYMBOL
PORTFOLIO_ITEM
1:N
M-O
Chapter 6 – Transforming Data Models into Database Designs
Page 6-28
INSERT new Index
Tricky, new INDEX will require a new SYMBOL
child row that will require a parent with IndexID of
new parent. Will also need to create a Stock
Quote child for the new Symbol in another M-M
relationship, and a new Company row with the
new Symbol parent at the same time! Careful use
of triggers or action by Database Administrator
might work (lock table, remove constraints, insert
new row, add constraints, unlock table).
SQL ACTION ON CHILD (SYMBOL)
REF INTEGRITY ACTION
Symbol and Stock Quote)
Chapter 6 – Transforming Data Models into Database Designs
Page 6-29
SYMBOL required parent of STOCK_QUOTE M-M
SQL ACTION ON PARENT (SYMBOL)
REF INTEGRITY ACTION
DELETE Symbol row
Best to Prohibit (No Action). May cause problems
with Index rows in another M-M relationship,
especially if last child symbol deleted for a parent
Index.
SQL ACTION ON CHILD (STOCK_QUOTE)
REF INTEGRITY ACTION
DELETE STOCK_QUOTE row
OK if not last STOCK_QUOTE in child of
SYMBOL. If last STOCK_QUOTE, delete SYMBOL
parent also. May affect other relationships.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-30
COMPANY required parent of SYMBOL M-O
SQL ACTION ON PARENT (COMPANY)
REF INTEGRITY ACTION
DELETE Company row
Prohibit, or Cascade Delete of SYMBOL child
rows. May cause problems with INDEX and
STOCK_QUOTE tables in other relationships!
SQL ACTION ON CHILD (SYMBOL)
REF INTEGRITY ACTION
DELETE Symbol row
OK (but could cause problems in M-M
relationship between Symbol and Stock Quote or
INDEX)
Chapter 6 – Transforming Data Models into Database Designs
Page 6-31
SYMBOL required parent of PORTFOLIO_ITEM M-O
SQL ACTION ON PARENT (SYMBOL)
REF INTEGRITY ACTION
DELETE SYMBOL row
Restrict or Prohibit. May cause problems with
other relationships! Deleting a symbol should not
delete holdings in a portfolio.
SQL ACTION ON CHILD
(PORTFOLIO_ITEM)
REF INTEGRITY ACTION
DELETE PORTFOLIO_ITEM row
OK
INSERT SYMBOL row
Chapter 6 – Transforming Data Models into Database Designs
PORTFOLIO required parent of PORTFOLIO_ITEM M-O
SQL ACTION ON PARENT (PORTFOLIO)
REF INTEGRITY ACTION
DELETE PORTFOLIO row
Restrict, or Cascade Delete of PORTFOLIO_ITEM
child rows.
INSERT PORTFOLIO row
SQL ACTION ON CHILD
(PORTFOLIO_ITEM)
REF INTEGRITY ACTION
DELETE PORTFOLIO_ITEM row
OK
Chapter 6 – Transforming Data Models into Database Designs
Page 6-33
6.54 Answer question 5.59 if you have not already done so. Design databases for your model
in question 5.59(a) and for the model in Figure 5-57. Your designs should include a
specification of tables and attributes as well as primary, candidate, and foreign keys.
Also specify how you will enforce minimum cardinality. Document your minimum
cardinality enforcement using referential integrity actions for required parents, if any, and
the form in Figure 6-29(b) for required children, if any.
The information about the supertype/subtype relationships is in the following table:
RELATIONSHIP
CARDINALITY
[Blue =
Inferable]
PARENT
CHILD
TYPE
MAX
MIN
Chapter 6 – Transforming Data Models into Database Designs
Page 6-34
Referential integrity actions for required parents are shown in the E-R diagram.
SS_COMPRESSOR (PARENT) → A
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
SS_COMPRESSOR (PARENT) → C
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
UPDATE CHILD
RESTRICT
There are no required children in the database design.
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
UPDATE CHILD
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
Page 6-35
6.55 Answer question 5.60 if you have not already done so. Design a database for your
model in question 5.60(e). Your design should include a specification of tables and
attributes as well as primary, candidate, and foreign keys. Also specify how you will
enforce minimum cardinality. Document your minimum cardinality enforcement using
referential integrity actions for required parents, if any, and the form in Figure 6-29(b) for
required children, if any.
I:R
D:C
U:C
I:R
D:C
U:C
D:C
U:C
THEATER
TheaterName: char(50) NOT NULL
City: char(35) NOT NULL
State: char(2) NOT NULL
Address: char(35) NOT NULL
ZIP: char(10) NOT NULL
Phone: char(12) NOT NULL
MOVIE
MovieName: char(50) NOT NULL
Description: varchar(255) NOT NULL
State: char(2) NOT NULL (FK)
Chapter 6 – Transforming Data Models into Database Designs
Page 6-36
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
Referential integrity actions for required parents are shown in the E-R diagram.
MOVIE (REQ’D PARENT) → SHOW_TIME
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
THEATER
SHOW_TIME
ID-Dependent
1:N
M-O
AREA
DISTANCE
ID-Dependent
1:N
M-O
THEATER
DISTANCE
ID-Dependent
1:N
M-O
ACTOR
MOVIE_CAST
ID-Dependent
1:N
M-O
MOVIE
MOVIE_CAST
ID-Dependent
1:N
M-O
Chapter 6 – Transforming Data Models into Database Designs
Page 6-37
THEATER (REQ’D PARENT) → SHOW_TIME
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
I:R
INSERT CHILD
RESTRICT
AREA (REQ’D PARENT) → DISTANCE
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
THEATER (REQ’D PARENT) → DISTANCE
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
Chapter 6 – Transforming Data Models into Database Designs
Page 6-38
ACTOR (REQ’D PARENT) → MOVIE_CAST
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
MOVIE (REQ’D PARENT) → MOVIE_CAST
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:R
DELETE PARENT
RESTRICT
UPDATE PARENT
CASCADE
There are no required children in the database design.
DELETE PARENT
PROHIBIT (RESTRICT)
UPDATE PARENT
CASCADE
INSERT CHILD
PROHIBIT (RESTRICT)
Chapter 6 – Transforming Data Models into Database Designs
6.56 Answer question 5.61 if you have not already done so. Design a database for your
model in question 5.61(c). Your design should include a specification of tables and
attributes as well as primary, candidate, and foreign keys. Also specify how you will
enforce minimum cardinality. Document your minimum cardinality enforcement using
referential integrity actions for required parents, if any, and the form in Figure 6-29(b) for
required children, if any.
NutientID: int NOT NULL
VitIronID: int NOT NULL
SUPPLIER
CompanyName: char(50) NOT NULL
ProductID: int NOT NULL
IngredientID: int NOT NULL
FDA_REPORT
IngredientID: int NOT NULL (FK)
I:R
D:NOA
I:R
I:R
D:NOA
NutientID: int NOT NULL (FK)
ProductOnlyAmt: numeric(6,2) NOT NULL
ProductOnlyAmtUnits: char(8) NOT NULL
ProductWithSkimMilkAmt: numeric(6,2) NOT NULL
ProductWithSkimMilkUnits: char(8) NOT NULL
ProductWithWholeMilkAmt: numeric(6,2) NOT NULL
ProductWithWholeMilkUnits: char(8) NOT NULL
INGREDIENT_SUPPLIER
IngredientID: int NOT NULL (FK)
SupplierName: char(50) NOT NULL (FK)
Price: numeric(8,2) NULL
Chapter 6 – Transforming Data Models into Database Designs
Page 6-40
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
INGREDIENT
FDA_REPORT
Weak
1:N
M-O
INGREDIENT
INGREDIENT_
SUPPLIER
ID-Dependent
Associative
1:N
M-O
SUPPLIER
INGREDIENT_
SUPPLIER
ID-Dependent
Associative
1:N
M-O
PRODUCT_
ID-Dependent
VITAMIN_IRON
PRODUCT_
VIT_IRON
ID-Dependent
Associative
1:N
M-O
PRODUCT
PRODUCT_
NUTRIENT
ID-Dependent
Associative
1:N
M-O
NUTRIENT
PRODUCT_
NUTRIENT
ID-Dependent
Associative
1:N
M-O
COMPANY
FDA_REPORT
Weak
1:N
M-O
INGREDIENT
PRODUCT_INGREDIENT
ID-Dependent
Associative
1:N
M-O
PRODUCT
PRODUCT_INGREDIENT
ID-Dependent
Associative
1:N
M-O