Chapter 6 – Transforming Data Models into Database Designs
Page 6-58
Chapter 6 – Transforming Data Models into Database Designs
Page 6-59
INTERSECTION TABLES:
Chapter 6 – Transforming Data Models into Database Designs
Page 6-60
B Describe how you have represented weak entities, if any exist.
There are two weak entities:
• SCHEDULED_MAINTENANCE has a surrogate primary key, and therefore is a weak,
non-ID-dependent table.
• LOG is and ID-dependent entity in the data model, and is also an ID-dependent table in
the database design, dependent on CharterID (the primary key of CHARTER).
C Describe how you have represented supertype and subtype entities, if any exist.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-61
Chapter 6 – Transforming Data Models into Database Designs
Page 6-62
E 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.
Relationship
Referential Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON
UPDATE
ON
DELETE
OWNER
BOAT_OWNER
OwnerID in BOAT_OWNER
must exist in OwnerID in
OWNER
NO
YES
EQUIPMENT
EQUIPMENT_OWNER
ItemIDTagNumber in
EQUIPMENT_OWNER
must exist in
ItemIDTagNumber in
EQUIPMENT
YES
YES
CHARTER
CHARTER_EQUIPMENT
CharterID in
CHARTER_EQUIPMENT
must exist in CharterID in
CHARTER
NO
YES
EQUIPMENT
CHARTER_EQUIPMENT
ItemIDTagNumber in
CHARTER_EQUIPMENT
ItemIDTagNumber in
EQUIPMENT
YES
MAINTENANCE
SCHEDULED_
MAINTENANCE must exist
in CoastGuardRegNumber
CUSTOMER
CHARTER
CustomerID in CHARTER
must exist in CustomerID in
NO
CHARTER
LOG
CharterID in LOG must exist
in CoastGuardRegNumber
in BOAT
OWNER
EQUIPMENT_OWNER
OwnerID in
must exist in OwnerID in
OWNER
Chapter 6 – Transforming Data Models into Database Designs
Page 6-63
❖ THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
If you have not already done so, complete The Queen Anne Curiosity Shop project at
the end of Chapter 5.
A Convert this data model to a database design. Specify tables, primary keys, and foreign
keys. Using Figure 6-43 as a guide, specify column properties.
See the E-R diagram in part D.
This design uses surrogate keys for all tables except ORDER. This assumes that the Queen Anne
Note that these and other possible variations may exist for this design. For example:
• EMPLOYEE could be N:M to SALE.
Discuss such possibilities with your students.
Chapter 6 – Transforming Data Models into Database Designs
PERSON ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
PersonID
Integer
Primary Key
Yes
None
Surrogate (1, 1)
isEmployee
Char(4)
Yes
None
isCustomer
Char(4)
Yes
None
LastName
Char(25)
Yes
None
FirstName
Char(25)
Yes
None
Address
Char(35)
None
City
Char(35)
None
State
Char(2)
None
ZIP
Char(10)
None
Phone
Char(12)
Yes
None
CUSTOMER ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
PersonID
Integer
Primary Key
Yes
None
CreditCardType
Yes
None
CreditCardNumber
Integer
Yes
None
CreditCardExpirationDate
DateTime
Yes
None
EMPLOYEE ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Remarks
PersonID
Integer
Primary Key
Yes
DateOfHire
DateTime
Yes
HourlyPayRate
Number(9,2)
Yes
SALE ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
SaleID
Integer
Primary Key
Yes
None
Surrogate (1, 1)
SaleDate
DateTime
Yes
None
SubTotal
Numeric(9,2)
None
Tax
Numeric(9,2)
None
Total
Numeric(9,2)
None
Chapter 6 – Transforming Data Models into Database Designs
Page 6-65
SALE_ITEM ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
SaleID
Integer
Primary Key
Yes
None
SaleItemID
Integer
Primary Key
Yes
None
Sequential
ITEM ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
ItemID
Integer
Primary Key
Yes
None
Surrogate (1, 1)
ItemDescription
VarChar255)
Yes
None
ReorderPointQuantity
Integer
No
None
QuantityOnHand
Integer
No
None
ItemPrice
Numeric(9,2)
Yes
None
VENDOR ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
VendorID
Integer
Primary Key
Yes
None
Surrogate (1, 1)
CompanyName
VarChar(100)
No
None
ContactLastName
Char(25)
Yes
None
ContactFirstName
Char(25)
Yes
None
Address
Char(35)
No
None
City
Char(35)
No
State
Char(2)
No
None
ZIP
Char(10)
No
None
Phone
Char(12)
Yes
None
Char(12)
No
Email
VarChar(100)
No
None
ORDER ATTRIBUTE SPECIFICATIONS
Column Name
Data Type
Key
Required
Default
Value
Remarks
InvoiceNumber
Integer
Primary Key
Yes
None
Surrogate
(m,n)
DateOrdered
DateTime
Yes
None
DateReceived
DateTime
No
None
QuantityOrdered
Integer
Yes
None
ItemCost
Numeric(9,2)
Yes
None
OrderSubTotal
Numeric(9,2)
Yes
None
OrderTax
Numeric(9,2)
Yes
None
Quantity
Integer
Yes
None
ItemPrice
Numeric(9,2)
Yes
ExtendedPrice
Numeric(9,2)
Yes
None
Chapter 6 – Transforming Data Models into Database Designs
Page 6-66
B Describe how you have represented weak entities, if any exist.
SALE_ITEM is an ID-dependent weak entity in relation to SALE and has the key of SALE added
to it as part of its primary key.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-67
Relationship
Referential Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
PERSON
CUSTOMER
PersonID in CUSTOMER must
exist in PersonID in PERSON
NO
YES
SALE
SALE_ITEM
SaleID in SALE_ITEM must exist
in SaleID in SALE
NO
YES
ITEM
SALE_ITEM
ItemID in SALE_ITEM must exist
in ItemID in ITEM
NO
NO
VENDOR
ORDER
VendorID in ORDER must exist
in VendorID in VENDOR
NO
PERSON
EMPLOYEE
PersonID in EMPLOYEE must
exist in PersonID in PERSON
NO
YES
CUSTOMER
SALE
exist in PersonID in CUSTOMER
NO
EMPLOYEE
SALE
EmployeePersonID in
EMPLOYEE must exist in
NO
Chapter 6 – Transforming Data Models into Database Designs
Page 6-68
SALE_ITEM is
ID-Dependent
on SALE
SALE_ITEM
SaleID (FK)
SaleItemID
Quantity
ItemPrice
ExtentedPrice
ItemID (FK)
SALE
CUSTOMER
EMPLOYEE
VENDOR
VendorID
CompanyName
ContactLastName
ContactFirstName
Address
City
State
ZIP
Phone
Fax
Email
PERSON
PersonID
isEmployee
isCustomer
LastName
FirstName
Address
City
State
ZIP
Phone
Email
ITEM
ItemID
ORDER
InvoiceNumber
Chapter 6 – Transforming Data Models into Database Designs
Page 6-69
❖ MORGAN IMPORTING PROJECT QUESTIONS
If you have not already done so, answer the Morgan Importing project at the end of
Chapter 5.
A Convert this data model to a database design. Specify tables, primary keys, and foreign
keys. Using Figure 6-43 as a guide, specify column properties.
The following database design is the solution that will be implemented in Chapter 7—your students may
create other acceptable variations. This database design was drawn using the MySQL Workbench. Note
that primary keys (gold key symbol), foreign keys fired diamond), NULL status (white diamond is NULL,
blue diamond is NOT NULL), and data types are shown. Maximum and minimum cardinalities and
identifying or nonidentifying relationships are also indicated in the diagram.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-70
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
STORE
ITEM
Strong
1:N
M-O
EMPLOYEE
ITEM
Strong
1:M
M-O
SHIPMENT
SHIPMENT_ITEM
ID-Dependent
Associative
1:N
M-O
EMPLOYEE
SHIPMENT_RECEIPT
Strong
1:N
M-O
SHIPMENT
SHIPMENT_RECEIPT
Strong
1:N
M-O
ITEM
SHIPMENT_RECEIPT
Strong
M-O
ITEM
SHIPMENT_ITEM
Strong
1:N
M-O
SHIPPER
SHIPMENT
Strong
1:N
M-O
EMPLOYEE
SHIPMENT
Strong
1:N
M-O
Chapter 6 – Transforming Data Models into Database Designs
Chapter 6 – Transforming Data Models into Database Designs
Page 6-72
B Describe how you have represented weak entities, if any exist.
There is one weak entity:
• SHIPMENT_ITEM is an ID-dependent table, dependent on SHIPMENT.
C Describe how you have represented supertype and subtype entities, if any exist.
There are no supertype and subtype entities in this design
Chapter 6 – Transforming Data Models into Database Designs
Page 6-73
D Create a visual representation of your database design as a Crow’s Foot E-R diagram
similar to the one in Figure 6-39.
See the E-R diagram for part A above.
E 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.
Relationship
Referential Integrity
Constraint
Cascading Behavior
PARENT
CHILD
ON UPDATE
ON DELETE
STORE
ITEM
StoreID in ITEM must exist in
StoreID in STORE
NO
NO
SHIPMENT
SHIPMENT_ITEM
ShipmentID in
SHIPMENT_ITEM must exist
in ShipmentID in SHIPMENT
NO
YES
EMPLOYEE
SHIPMENT
PurchasingAgentID in
SHIPMENT must exist in
EmployeeID in EMPLOYEE
NO
NO
must exist in Shipper in
SHIPPER
EMPLOYEE
SHIPMENT
ITEM
SHIPMENT_RECEIPT
ItemID in
SHIPMENT_RECEIPT must
exist in ItemD in ITEM
NO
NO
EMPLOYEE
ITEM
PurchasingAgentID in ITEM
must exist in EmployeeID in
EMPLOYEE
NO
NO
ITEM
SHIPMENT_ITEM
ItemID in SHIPMENT_ITEM
must exist in temID in ITEM
NO