Scribd
Upload
22 views6 pages

MIS Week 2 Assignment

Uploaded by

sanjib.job.bd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views6 pages

MIS Week 2 Assignment

Uploaded by

sanjib.job.bd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

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.

You might also like