Md Faysal Ahmed

MIS 560 DATABASE MANAGEMENT SYSTEMS

Prasanth Kalakota

International American University

PROBLEM SET 1: Crow’s Foot notation ERD

No of entities:

1. Customer entity

2. Repair entity

3. Service entity

4. Labor entity

5. Parts entity

6. Device entity

7. Uses entity

The ER Diagram for the provided business rules is shown in Crow's Foot notation in the

following figure:

Figure 1. Crow’s Foot notation ERD

Relationships between entities in ERD are given below:

1. The "CUSTOMER" and "REPAIR" entities are related to 1:M. Several devices are available

for repair to customers. Similarly to that, a device may be brought in for repair multiple

times. As a result, the "DEVICE" and "REPAIR" entities have a 1:M connection.

2. There is a 1:M relationship between the Labor entity and the Service entity.

3. There is a 1:M relationship between the Device entity and the Repair entity.

4. A performance that calls for services is REPAIR. Several services might be necessary for

each repair. Similarly to this, various repair devices may require multiple times of each

servicing. But each SERVICE should only have been used once in a certain REPAIR. The

SERVICE may make use of many PARTs. So, There is M: N relationship between the PARTS

entity and the SERVICE entity.

PROBLEM SET 2: The answer for Figure P3.17 is given below:

17. The primary key and the foreign key(s) of each table in figure P3.17 are given below:

TABLE PRIMARY KEY FOREIGN KEY(S)

TRUCK TRUCK_NUM BASE_CODE, TYPE_CODE

BASE BASE_CODE NONE

TYPE TYPE_CODE NONE

Here, The TRUCK_SERIAL_NUM could also be designated as the primary key. Because the

TRUCK_NUM was designated to be the primary key, TRUCK_SERIAL_NUM is an example

of a candidate key.

18. Yes. The tables exhibit entity integrity. The explanation is given below:
 TABLE PRIMARY ENTITY EXPLANATION

KEY INTEGRITY

TRUCK TRUCK_NUM YES The TRUCK_NUM values in the

TRUCK table are all unique and

there are no nulls.

BASE BASE_CODE YES The BASE_CODE values in the

BASE table are all unique and there

are no nulls.

TYPE TYPE_CODE YES The TYPE_CODE values in the

TYPE table are all unique and there

are no nulls.

19. No. The tables do not exhibit referential integrity.

The table TRUCK exhibits referential integrity. The referential integrity ensures that the foreign

key must always reference an existing primary key or contain a null value. The foreign key of the

TRUCK table points to the existing primary key of the TYPE table. The table BASE and TYPE

don’t have the foreign key.

TABLE FOREIGN REFERENTIAL EXPLANATION

KEY(S) INTEGRITY

TRUCK BASE_CODE, YES The BASE_CODE values in the

TYPE_CODE TRUCK table reference existing

BASE_CODE values in the

BASE table or they are null. (The

 TRUCK table's BASE_CODE is

null for TRUCK_NUM = 1004.)

Also, the TYPE_CODE values in

the TRUCK table reference

existing TYPE_CODE values in

the TYPE table

BASE NONE NA

TYPE NONE NA

Here, the tables BASE and TYPE do not have foreign key values. Therefore, the tables do not

exhibit referential integrity.

20. The candidate key of the TRUCK table is TRUCK_SERIAL_NUM.

Explanation:

A record can be uniquely identified without referencing any other data by using a column or

collection of columns as the candidate key. Several potential keys may exist in a table. The

candidate key is unique and refers to the primary key. As the TRUCK_NUM is the primary key

of the TRUCK table, the TRUCK_SERIAL_NUM can be a primary key. So, it can be a

candidate key. Therefore, the candidate key of the TRUCK table is TRUCK_SERIAL_NUM.

21. Superkey and a secondary key of each table in Figure P3.17 are given below:

TABLE SUPER KEY SECONDARY KEY

TRUCK TRUCK_NUM + TRUCK_MILES BASE_CODE + TYPE_CODE

 TRUCK_NUM + TRUCK_MILES + (This secondary key is likely to

TRUCK_BUY_DATE produce multiple matches, but it is

not likely that end-users will know

TRUCK_NUM + TRUCK_MILES + attribute values such as

TRUCK_BUY_DATE + TYPE_CODE TRUCK_MILES or

TRUCK_BUY_DATE. Therefore,

the selected attributes create a

reasonable secondary key.)

BASE BASE_CODE + BASE_CITY BASE_CITY + BASE_STATE

BASE_CODE + BASE_CITY + (This is a very effective secondary

BASE_CITY key since it is not likely that a state

contains two cities with the same

name.)

TYPE TYPE_CODE + TYPE_DESCRIPTION TYPE_DESCRIPTION

22. ERD for this database is shown below:

 Figure 2. The Ch03_TransCo Crow's Foot ERD

There is a 1:M relationship between the BASE table and the TRUCK table. Also, the 1:M

relationship between the TYPE table and the TRUCK table.

References

Rayan, J. C. (2015, February 20). Data Modelling using ERD with Crow Foot Notation.

[Link]; CodeProject. [Link]

Modelling-using-ERD-with-Crow-Foot-Notation

“Crow’s Foot Notation in Entity-Relationship Diagrams.” [Link],

[Link]/blog/[Link].

“Entity-Relationship ER Modeling Discussi.” [Link], [Link]/doc/25304298/entity-

relationship-er-modeling-discussi. Accessed 31 Oct. 2023.

“Ex3 Answers - Exercise 3 Use the Database Shown in Figure 1 to Answer Problems 1-7.

FIGURE 1 The.” StuDocu, [Link]/en-us/document/arkansas-state-

university/database-management/ex3-answers/9558105.

Vuong, Hieu. “Chapter 3 In-Class Key.” [Link],

[Link]/10646624/Chapter_3_In_Class_Key. Accessed 31 Oct. 2023.