Chapter 6 – Transforming Data Models into Database Designs
Referential integrity actions for required parents are shown in the E-R diagram.
COMPANY (REQ’D PARENT) → PRODUCT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
SET DEFAULT
COMPANY (REQ’D PARENT) → FDA_REPORT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
NO ACTION
INSERT CHILD
SET DEFAULT
INGREDIENT (REQ’D PARENT) → PRODUCT_INGREDIENT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
Page 6-42
PRODUCT (REQ’D PARENT) → PRODUCT_INGREDIENT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
INGREDIENT (REQ’D PARENT) → FDA_REPORT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
NO ACTION
INSERT CHILD
SET DEFAULT
INGREDIENT (REQ’D PARENT) → INGREDIENT_SUPPLIER M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
SUPPLIER (REQ’D PARENT) → INGREDIENT_SUPPLIER M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
Page 6-43
PRODUCT (REQ’D PARENT) → FDA_REPORT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
PRODUCT (REQ’D PARENT) → PRODUCT_VIT_IRON M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
VITAMIN_IRON (REQ’D PARENT) → PRODUCT_VIT_IRON
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
PRODUCT (REQ’D PARENT) → PRODUCT_NUTRIENT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
RESTRICT
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
DELETE PARENT
NO ACTION
UPDATE PARENT
NO ACTION
INSERT CHILD
SET DEFAULT
Chapter 6 – Transforming Data Models into Database Designs
Page 6-44
NUTRIENT (REQ’D PARENT) → PRODUCT_NUTRIENT M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
6.57 Answer question 5.62 if you have not already done so. Design a database for your
model in question 5.62(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.
SongTitle: char(50) NOT NULL
ARTIST
ArtistName: char(50) NOT NULL
SONGWRITER_SONG
SONG_ARTIST
ArtistName: char(50) NOT NULL (FK)
I:R
D:C
U:C
I:SD
D:NOA
U:NOA
D:C
U:C
I:R
U:NOA I:R
D:C
U:C
I:R
D:NOA
U:NOA
I:R
D:C
U:C
D:NOA
I:R
U:NOA
D:C
U:C
CD
CDTitle: char(50) NOT NULL
CDComments: varchar(255) NOT NULL
CDCoverNotes: varchar(255) NULL
SONG
ArtistType: char(25) NOT NULL
CD_TRACK
CDTitle: char(50) NOT NULL (FK)
TrackNumber: int NOT NULL
SongTitle: char(50) NOT NULL (FK)
ARTIST_INDIVIDUAL
ArtistName: char(50) NOT NULL (FK)
ArtistLastName: char(25) NOT NULL
ArtistFirstName: char(25) NOT NULL
IS_SINGER: char(3) NOT NULL
IS_SONGWRITER: char(3) NOT NULL
ARTIST_GROUP
ArtistName: char(50) NOT NULL (FK)
NumberOfMembers: int NOT NULL
ROLE
ArtistName: char(50) NOT NULL (FK)
GROUP_INDIVIDUAL
ARTIST_INDIVIDUAL_ArtistName: char(50) NOT NULL (FK)
ARTIST_GROUP_ArtistName: varchar(20) NOT NULL
SONGWRITER
ArtistName: char(50) NOT NULL (FK)
ARTIST_INDIVIDUAL_ArtistName: char(50) NOT NULL (FK)
ROLE_ArtistName: char(50) NOT NULL
Chapter 6 – Transforming Data Models into Database Designs
The E-R Crow’s Foot model above is based on the following data:
RELATIONSHIP
CARDINALITY
[Blue = Inferable]
PARENT
CHILD
TYPE
MAX
MIN
CD_TRACK
ID-Dependent
1:N
M-M
ARTIST
SONG_ARTIST
ID-Dependent
1:N
M-O
SONG
SONG_ARTIST
ID-Dependent
1:N
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
ARTIST_INDIVIDUAL
GROUP_INDIVIDUAL
ID-Dependent
1:N
M-O
ARTIST_GROUP
GROUP_INDIVIDUAL
ID-Dependent
1:N
M-O
SONG
SONGWRITER_SONG
ID-Dependent
1:N
M-O
SONGWRITER
SONGWRITER_SONG
ID-Dependent
1:N
M-O
ARTIST
ROLE
Subtype
1:N
M-O
ROLE
ARTIST_INDIVIDUAL_ROLE
ID-Dependent
1:N
M-O
ARTIST_INDIVIDUAL
ARTIST_INDIVIDUAL_ROLE
ID-Dependent
1:N
M-O
Chapter 6 – Transforming Data Models into Database Designs
Page 6-47
Referential integrity actions for required parents are shown in the E-R diagram.
CD (REQ’D PARENT) → CD_TRACK (REQ’D CHILD) M-M
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
SONG (REQ’D PARENT) → CD_TRACK M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
U:NOA
UPDATE PARENT
NO ACTION
INSERT CHILD
SET DEFAULT
ARTIST (REQ’D PARENT) → SONG_ARTIST M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
Page 6-48
SONG (REQ’D PARENT) → SONG_ARTIST M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
U:C
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
ARTIST (Supertype parent) → ARTIST_INDIVIDUAL (Subtype)
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
ARTIST (Supertype parent) → ARTIST_GROUP (Subtype)
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
U:C
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
U:C
UPDATE PARENT
CASCADE
I:R
INSERT CHILD
RESTRICT
U:NOA
UPDATE CHILD
NO ACTION
Chapter 6 – Transforming Data Models into Database Designs
Page 6-49
ARTIST_INDIVIDUAL (REQ’D PARENT) → GROUP_INDIVIDUAL M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
ARTIST_GROUP (REQ’D PARENT) → GROUP_INDIVIDUAL M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
SONG (REQ’D PARENT) → SONGWRITER_SONG M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
U:C
UPDATE PARENT
CASCADE
INSERT CHILD
RESTRICT
U:C
UPDATE PARENT
CASCADE
D:NOA
DELETE CHILD
NO ACTION
INSERT CHILD
RESTRICT
U:NOA
UPDATE CHILD
NO ACTION
Chapter 6 – Transforming Data Models into Database Designs
Page 6-50
SONGWRITER (REQ’D PARENT) → SONGWRITER_SONG M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
ARTIST (Supertype parent) → ROLE (subtype)
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
ROLE (PARENT) → ARTIST_INDIVIDUAL_ROLE M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
CASCADE
UPDATE PARENT
CASCADE
ARTIST_INDIVIDUAL (REQ’D PARENT) → ARTIST_INDIVIDUAL_ROLE M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
NO ACTION
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
Chapter 6 – Transforming Data Models into Database Designs
Page 6-51
There is one required child in the database design: a CD requires a CD_TRACK. Here is the
analysis of the required referential integrity enforcement actions for the M-M relationship
between CD (Parent) and CD_TRACK (Child) — it is based on Figure 6-28(b). Note the
referential integrity actions on the required child are handled, as usual, buy the DBMS. We need
a series of triggers to handle the actions on the required parent.
Both
CD and CD_TRACK
are Required
CD
[Parent]
CD_TRACK
[Child]
Insert
Create a new CD_TRACK row.
Use a trigger to create new row
in CD_TRACK.
Use a trigger to enforce this
requirement.
replacement. Use a trigger to
implement this logic.
Chapter 6 – Transforming Data Models into Database Designs
Page 6-52
❖ ANSWERS TO PROJECT QUESTIONS
Answer the Writer’s Patrol Case Questions in Chapter 5 if you have not already done so. Design
a database for your data model from 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
I:SD
D:C
U:C
I:SD
D:NOA
U:C
DRIVER
LicenseState: CHAR(2) NOT NULL
DriversLicense: CHAR(25) NOT NULL
DriverLastName: CHAR(25) NOT NULL
DriverFirstName: CHAR(25) NOT NULL
DriverInitial: CHAR(1) NULL
BirthDate: DATE NOT NULL
Height: NUMBER(4,2) NOT NULL
Weight: NUMBER(5,2) NOT NULL
EyeColor: CHAR(10) NOT NULL
CORRECTION_NOTICE
NoticeNumber: INTEGER NOT NULL
Month: INTEGER NOT NULL
Date: INTEGER NOT NULL
Year: INTEGER NOT NULL
Time: DATE NOT NULL
ActionRequired: INTEGER NOT NULL
LicenseState: CHAR(2) NOT NULL (FK)
DriversLicense: CHAR(25) NOT NULL (FK)
VIN: CHAR(25) NOT NULL (FK)
PersonnelNumber: INTEGER NOT NULL (FK)
OFFICER
PersonnelNumber: INTEGER NOT NULL
OfficerLastName: CHAR(25) NOT NULL
OfficerFirstName: CHAR(25) NOT NULL
VEHICLE
Chapter 6 – Transforming Data Models into Database Designs
Page 6-53
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
Referential integrity actions for required parents are shown in the E-R diagram.
DRIVER (REQ’D PARENT) → CORRECTION_NOTICE M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
INSERT CHILD
SET DEFAULT
OFFICER (REQ’D PARENT) → CORRECTION_NOTICE M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:NOA
DELETE PARENT
NO ACTION
UPDATE PARENT
CASCADE
INSERT CHILD
SET DEFAULT
OFFICER
CORRECTION_NOTICE
Strong
1:N
M-O
VEHICLE
CORRECTION_NOTICE
Strong
1:N
M-O
Chapter 6 – Transforming Data Models into Database Designs
Page 6-54
VEHICLE (REQ’D PARENT) → CORRECTION_NOTICE M-O
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
CORRECTION NOTICE (REQ’D PARENT) → VIOLATION
SYMBOL
INDICATES
SQL ACTION
REF INTEGRITY ACTION
D:C
DELETE PARENT
CASCADE
U:C
UPDATE PARENT
CASCADE
There are no required children in the database design.
B. Describe how you have represented weak entities, if any
VIOLATION has rounded edges and yellow color.
C. Describe how you have represented supertype and subtypes entities, if any exist.
DELETE PARENT
NO ACTION
UPDATE PARENT
RESTRICT
Chapter 6 – Transforming Data Models into Database Designs
❖ SAN JUAN SAILBOAT CHARTERS CASE QUESTIONS
San Juan Sailboat Charters (SJSBC) is an agency that leases (charters)
sailboats. SJSBC does not own the boats. Instead, SJSBC leases boats on behalf of
boat owners who want to earn income from their boats when they are not using them,
and SJSBC charges the owners a fee for this service. SJSBC specializes in boats that
can be used for multiday or weekly charters. The smallest sailboat available is 28 feet in
length and the largest is 51 feet in length.
Keeping track of equipment is an important part of SJSBC’s responsibilities.
Much of the equipment is expensive, and those items not physically attached to the boat
can be easily damaged, lost or stolen. SJSBC holds the customers responsible for all of
the boat’s equipment during the period of the charter.
Sailboats need maintenance. Note that two definitions of boat are: (1) “break out
another thousand” and (2) “a hole in the water into which one pours money.” SJSBC is
required by its contracts with the boat owners to keep accurate records of all
maintenance activities and costs.
A data model of a proposed database to support an information system for
SJSBC is shown in Figure 6-45. Note that because the OWNER entity allows for owners
Chapter 6 – Transforming Data Models into Database Designs
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.
Corrections for 15th Edition:
Add CompanyName field to OWNER
Change Email to EmailAddress in OWNER and CUSTOMER.
This project is a good one to work with the MySQL Workbench “Data Modeling” tools. Using
these tools is described in Appendix E, Getting Started with MySQL Workbench Data Modeling
Tools. Based on the SJSBC data model, the following tables exist. The following MySQL
Chapter 6 – Transforming Data Models into Database Designs
Page 6-57
There are three N:M relationships in the SJSBC data model:
• OWNER-to–BOAT