Problem

Table 4-5 shows a shipping manifest. Your assignment is as follows: a. Draw a relationa...

Table 4-5 shows a shipping manifest. Your assignment is as follows:

a. Draw a relational schema and diagram the functional dependencies in the relation.

b. In what normal form is this relation?

c. Decompose MANIFEST into a set of 3NF relations.

d. Draw a relational schema for your 3NF relations and show the referential integrity constraints.

e. Draw your answer to part d using Microsoft Visio (or any other tool specified by your instructor).

Step-by-Step Solution

Solution 1

Step #1 of 11

Normalization:

The process used to minimize data redundancy and dependency in a relational database is known as normalization.

• The database table is divided into two or more tables and it defines the relationship between those tables.

• If the modifications, deletion or updates occur in one table, the other tables in database get updated by just propagating it.

• In a relational database, the tables with no redundant data and less null values are considered as efficient table.

o This is because, having redundant data and more null values increases the storage space and wastage of memory is imminent.

o Null values cause a problem for data integrity and produces more error in the database.

• If the wasted storage space and null values which cause data integrity hazards are minimized, a highly optimized relational database can be designed.

• The project team should consider using normalization when designing the database. It is the best source to optimize data storage efficiently; this is because it reduces the data storage space and provides data integrity.

Step #2 of 11

Note:

In the diagrams, to indicate primary and foreign keys notational conventions are used.

• The attributes noted with underlined name are primary key values.

• The attributes noted with italics letters are foreign key values.

Step #3 of 11

(a) Relational schema and functional dependencies for the SHIPPING MANIFEST relation:

• The relational schema is shown along with the functional dependency in the relation.

• It describes the details about the shipment, captain and item number. It also gives correlation among all.

• Based upon some assumption and constraints the relational schema is developed from the given table 4-5. The diagram to show this is as given below:

Functional dependencies in the relation:

• An association between two attributes or two set of attributes in a same relational database table which is having some constraints is known as functional dependency.

• In a table one attribute is functionally dependent on another attribute to take one value.

Functional dependencies for the relation are illustrated below.

Functional dependency #1:

• In this relation, shipment date, expected arrival date, origin and destination of the ship, ship number, captain ID and captain name are functionally dependent on ShipmentID.

Functional dependency #2:

• In this relation, type of item, description about the item, weight and quantity of the item are functionally dependent on ItemNumber.

Functional dependency #3:

• In this relation, quantity of the item is functionally dependent on ShipmentID.

Functional dependency #4:

• In this relation, name of the captain is functionally dependent on CaptainID.

Step #4 of 11

(b) Normal form of the relation:

The relation is provided is in the .

• It gives the rational dependency of the anomaly. It provides precise relationships. There are no discrepancies made.

• The relation does not contain any repeated attributes.

Step #5 of 11

Third normal form:

• For the third normal form, the first and basic requirement is that the model should be in the first and second normal form.

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes are dependent on any other non-primary key attribute then it should moved or deleted.

o It is termed as transitive dependency.

• In this a new entity should be created, which is used to connect the old and new entities by adding of the relationship.

• Using some user defined constraints new relations SHIPMENT, SHIPMENT LINE, ITEM, and CAPTAIN are created and attributes are added to it.

The third normal form shows relation which is created from the SHIPPING MANIFEST is shown below.

$Z:\01_Solutions_Authoring\Computer_Science\10943\04_Artwork\10943-4-9PE_fig2.jpg$

Explanation:

SHIPMENT relation is created along with the attributes that are functionally dependent on it.

• In the SHIPMENT relation,

o The attributes belong to the relation are ShipmentID, ShipmentDate, ExpectedArrivalDate, Origin, Destination, ShipNumber and CaptainID.

o ShipmentID is unique in the relation and it acts as the primary key.

SHIPMENT LINE relation is created along with the attributes that are functionally dependent on it.

• In the SHIPMENT LINE relation,

o The attributes belong to the the relation are ShipmentID, ItemNumber and Quantity.

o ShipmentID and ItemNumber are the foreign key attributes in the relation which is used to refer its respective tables.

ITEM relation is created along with the attributes that are functionally dependent on it.

• In the ITEM relation,

o The attributes belong to the relation are ItemNumber, Type, Description and Weight.

o ItemNumber is unique in the relation and it acts as the primary key.

CAPTAIN relation is created along with the attributes that are functionally dependent on it.

• In the CAPTAIN relation,

o The attributes inside the relation are CaptainID and CaptainName.

o CaptainID is unique in the relation and it acts as the primary key.

Step #6 of 11

(d) Relational schema for 3NF relations with referential integrity:

The diagram given below describes the third normal form relations. It also shows the referential integrity. It also shows functional dependencies.

$Z:\01_Solutions_Authoring\Computer_Science\10943\04_Artwork\10943-4-9PE_fig3.jpg$

Explanation:

•

Step #7 of 11

In the SHIPMENT relation,

o The attributes inside the relation are ShipmentID, ShipmentDate, ExpectedArrivalDate, Origin, Destination, ShipNumber and CaptainID.

o ShipmentID is unique in the relation and it acts as the primary key.

• In the SHIPMENT LINE relation,

o The attributes inside the relation are ShipmentID, ItemNumber and Quantity.

o ShipmentID and ItemNumber are the foreign key attributes in the relation which is used to refer its respective tables.

• In the ITEM relation,

o The attributes inside the relation are ItemNumber, Type, Description and Weight.

o ItemNumber is unique in the relation and it acts as the primary key.

• In the CAPTAIN relation,

o The attributes inside the relation are CaptainID and CaptainName.

o CaptainID is unique in the relation and it acts as the primary key.

Step #8 of 11

(e) 3NF relations using Microsoft Visio:

Third normal form:

• For the third normal form the first and basic requirement is that the model should be in the first and second normal form.

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.

o If any non-primary key attributes are dependent on any other non-primary key attribute then it should moved or deleted.

o It is termed as transitive dependency.

• In this a new entity should be created, which is used to connect the old and new entities by adding of the relationship.

The third normal form for the SHIPPING MANIFEST relation using Microsoft Visio is shown below.

$Z:\CHEGG\01_Solutions_Authoring\Computer_Science\10943\04_Artwork\10943-4-9PE_fig1.jpg$

$C:\Users\300889\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\XNEWY8E1\10943-3-17PE_fig4.jpg$

Step #9 of 11

Note: PK denotes the Primary Key, FK denotes the foreign key.

Step #10 of 11

Explanation:

ITEM entity:

• ITEM is the entity name and it is identified using ItemNumber.

• The attributes inside the entity are ItemNumber, Type, Description and Weight.

SHIPMENT LINE entity:

• SHIPMENT LINE is the entity name.

• The attributes inside the entity are ShipmentID, ItemNumber and Quantity.

SHIPMENT relation:

• SHIPMENT is the entity name and it is identified using ShipmentID.

• The attributes inside the entity are ShipmentID, ShipmentDate, ExpectedArrivalDate, Origin, Destination, ShipNumber and CaptID.

CAPTAIN relation:

• CAPTAIN is the entity name and it is identified using CaptID

• The attributes inside the entity are CaptID and CaptainNAME.

IsPartOf relationship:

Step #11 of 11

The relationship between the entity ITEM and SHIPMENT LINE is “IsPartOf” and the cardinality relationship is “mandatory one to optional many”.

• This is because an item is part of zero or many shipment lines.

Composes relationship:

The relationship between the entity SHIPMENT and SHIPMENT LINE is “Composes” and the cardinality relationship is “mandatory one to optional many”.

• This is because a shipment composes of zero or many shipment lines.

Pilots relationship:

The relationship between the entity SHIPMENT and CAPTAIN is “Pilots” and the cardinality relationship is “mandatory one to optional many”.

• This is because a captain may pilots zero or many shipment.

Add your Solution

Textbook Solutions and Answers Search

Solutions For Problems in Chapter 4

Free Homework Help App

Download From Google Play

Scan Your Homework
to Get Instant Free Answers

Need Online Homework Help?

Ask a Question

Get Answers For Free
Most questions answered within 3 hours.

Recent Solutions