Assignment: Problem Set-Week 2
Sanjib Kumar Shil
International American University
MIS 560: Database Management Systems
Professor Dr. Prasanth Kalakota
January 14, 2024
Problem 4.9: Create a Crow’s Foot notation ERD to support the following business
operations.
Solution:
Here is the Crow’s Foot notation ERD to support the following business operations
Problem 3.17: For each table, identify the primary key and the foreign key(s). If a table
does not have a foreign key, write None.
Solution:
The primary key and the foreign key of the table given below are:
Table Name Primary Key Foreign Key
TRUCK TRUCK_NUM BASE_CODE, TYPE_CODE
BASE BASE_CODE None
TYPE TYPE_CODE None
Problem 3.18: Do the tables exhibit entity integrity? Answer yes or no, and then explain
your answer.
Solution:
Yes, the tables exhibit entity integrity. This means that each table has a primary key that
uniquely identifies each row, and that the primary key values are not null. As you can see from
the table definitions and data, each table has a primary key constraint defined on one column,
and none of the primary key values are null.
Table Name Primary Key Entity Integrity
TRUCK TRUCK_NUM YES
BASE BASE_CODE YES
TYPE TYPE_CODE YES
Problem 3.19: Do the tables exhibit referential integrity? Answer yes or no, and then
explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.
Solution:
These table exhibit referential integrity or not are follows:
TRUCK table: Yes, the TRUCK table exhibits referential integrity. This means that the
foreign key values in the TRUCK table match the primary key values in the referenced
tables, or are null. As you can see from the table data, all the values in the BASE_CODE
and TYPE_CODE columns either exist in the BASE and TYPE tables, or are null.
BASE table: NA, the BASE table does not have a foreign key.
TYPE table: NA, the TYPE table does not have a foreign key.
Problem 3.20: Identify the TRUCK table’s candidate key(s).
Solution:
The candidate key for the TRUCK table is TRUCK_SERIAL_NUM. A candidate key is
a column or set of columns that uniquely identifies a record See additional data. A table can have
multiple candidate keys. Candidate keys are Unique and refer to the primary key. Because
TRUCK_NUM is the primary key of TRUCK table, TRUCK_SERIAL_NUM can be the
primary key. So that may be the key to the candidate. So the candidate key for the TRUCK table
is TRUCK_SERIAL_NUM.
Problem 3.21: For each table, identify a superkey and a secondary key.
Solution:
A superkey is a set of attributes of a table. It is used to uniquely identify a string. table
Multiple superkeys can be used. Secondary keys are non-unique keys, secondary key Multiple
records can be referenced.
TRUCK table: A superkey is any set of columns that contains TRUCK_NUM, since
TRUCK_NUM is the primary key. For example, {TRUCK_NUM, TRUCK_MILES} is a
superkey. The TRUCK table secondary key TRUCK_SERIAL_NUM.
BASE table: A superkey is any set of columns that contains BASE_CODE, since
BASE_CODE is the primary key. For example, {BASE_CODE, BASE_CITY} is a
superkey. A secondary key is any candidate key that is not the primary key. The BASE
table has a secondary key is BASE_PHONE.
TYPE table: A superkey is any set of columns that contains TYPE_CODE, since
TYPE_CODE is the primary key. For example, {TYPE_CODE, TYPE_DESCRIPTION}
is a superkey. A secondary key is any candidate key that is not the primary key. Since the
TYPE table has only one candidate key, it has no secondary key.
Problem 3.22: Create the ERD for this database.
Solution:
Here is the ERD for this database,
References:
01) Merson, P. (2009). Data model as an architectural view(p. 0035). Carnegie Mellon University, Software
Engineering Institute.
02) Coronel, C., & Morris, S. (2018). Database systems: design, implementation, and management
(13th ed.). Cengage Learning.
