BUDT 703 Fall 2022 Homework #2 – Relational Model
Due by 11:59pm, Thursday, September 29th, 2022
Note: The file
name must be renamed to HW2_YourLastName_YourFirstName.docx.
Repeat steps below for the Terps Consultant entity-relationship diagram (ERD) on the last page
of this document (not what you submitted for Homework #1).
1.
Convert ERD into relations (as relational
schema):
i.
Map strong entities to relations.
ii. Map
multivalued attributes. If any
iii. Map
weak entities if any.
iv. Map
binary relationships.
v. Map
associative entities if any.
vi. Map
unary relationships if any.
vii. Map
ternary and n-ary relationships if any.
2.
Propose a set of meaningful business rules on
all referential integrities.
3.
For each foreign key in each relation:
i.
Identify the primary key and base relation that
is being referenced.
ii. Identify
the referential integrity for ON DELETE and the associated business rule.
iii. Identify
the referential integrity for ON UPDATE and the associated business rule.
Example: Publisher, Inc. ERD:
![]()
Example Answers:
Relations:
Book (bokISBN, bokPubYear, bokPrice, bokPages)
Author (autFirstName, autLastName,
autPhone)
Publisher (pubName)
PublisherPhone (pubName, pubPhone)
Editor (pubName, edtName, edtPhone)
Write (bokISBN, autFirstName,
autLastName,
role)
Review (bokISBN, pubName,
expDate)
Sign (bokISBN, autFirstName, autLastName, pubName, conDate)
Business rules:
[R1] When
a book is deleted from the database, the authorship information should be
deleted from the database.
[R2] When
the information on a book is changed in the database, the corresponding
authorship information should be changed accordingly.
[R3] When
an author is no longer in the database, the authorship information should be
deleted from the database.
[R4] When
an author changes information in the database, the corresponding authorship
information should be changed accordingly.
[R5] When
a publisher is reviewing a book, the book and the publisher cannot be deleted
or changed in the database.
[R6] When
a publisher is out of business and deleted from the database, all editors for
the publisher should be deleted from the database as well.
[R7] When
publisher information is changed in the database, all editors for the publisher
should be changed accordingly.
[R8] When
publisher information is deleted from or changed in the database, all phone
numbers of the publisher should be deleted or changed accordingly.
[R9] When
there is a contract on a book signed by one author and the publisher, the book,
the author and the publisher cannot be deleted or changed in the database.
Referential integrity:
Relation
|
Foreign Key
|
Base Relation
|
Primary Key
|
Business Rule
|
Constraint: ON DELETE
|
Business Rule
|
Constraint: ON UPDATE
|
PublisherPhone
|
pubName
|
Publisher
|
pubName
|
R8
|
CASCADE
|
R8
|
CASCADE
|
Editor
|
pubName
|
Publisher
|
pubName
|
R6
|
CASCADE
|
R7
|
CASCADE
|
Write
|
bokISBN
|
Book
|
bokISBN
|
R1
|
CASCADE
|
R2
|
CASCADE
|
Write
|
(autFirstName,autLastName)
|
Author
|
(autFirstName,autLastName)
|
R3
|
CASCADE
|
R4
|
CASCADE
|
Review
|
bokISBN
|
Book
|
bokISBN
|
R5
|
NO ACTION
|
R5
|
NO ACTION
|
Review
|
pubName
|
Publisher
|
pubName
|
R5
|
NO ACTION
|
R5
|
NO ACTION
|
Sign
|
bokISBN
|
Book
|
bokISBN
|
R9
|
NO ACTION
|
R9
|
NO ACTION
|
Sign
|
(autFirstName,autLastName)
|
Author
|
(autFirstName,autLastName)
|
R9
|
NO ACTION
|
R9
|
NO ACTION
|
Sign
|
pubName
|
Publisher
|
pubName
|
R9
|
NO ACTION
|
R9
|
NO ACTION
|
Terps Consultant ERD:
![]()
Relations:
Business rules:
Referential integrities:
Relation
|
Foreign Key
|
Base Relation
|
Primary Key
|
Business Rule
|
Constraint: ON DELETE
|
Business Rule
|
Constraint: ON UPDATE
|