When a database is “normalized” it is restructured (according to a series of normal-forms, which we do not discuss here) in order to reduce data redundancy and improve data integrity. Typically this means decomposing one table into two or more new tables that are newly related. You must employ the following normalizations when reducing your ER diagram to a final relational database schema diagram: ● You must further normalize (or decompose) the sales_persons, customers, drivers, contractors, and shipments tables, so that all addresses are maintained in a new table called “addresses”. In doing so, each of these five tables newly participates in a many-to-one relationship with the addresses table. The primary key of the newly created addresses table is called address_id and is a unique identification number arbitrarily assigned by the logistics company. ● In this assignment, all relationship sets will be eliminated from the final database design. This will be done by exploiting many-to-one mapping cardinalities, as well as by employing the additional constraints detailed below in this section. ● Each shipment can have up two trailers and two drivers. These must be represented in the shipments table. (Hint: In the final database schema, a shipment with only one driver would have a NULL value for driver two.) ● All primary and foreign keys must be identified in the diagram appropriately (see example below). ● All multivalued attributes should be decomposed into separate tables
Relational Schema Normalization
When a
we do not discuss here) in order to reduce data redundancy and improve data integrity.
Typically this means decomposing one table into two or more new tables that are newly related.
You must employ the following normalizations when reducing your ER diagram to a final
relational database schema diagram:
● You must further normalize (or decompose) the sales_persons, customers, drivers,
contractors, and shipments tables, so that all addresses are maintained in a new table
called “addresses”. In doing so, each of these five tables newly participates in a
many-to-one relationship with the addresses table. The primary key of the newly
created addresses table is called address_id and is a unique identification number
arbitrarily assigned by the logistics company.
● In this assignment, all relationship sets will be eliminated from the final database design.
This will be done by exploiting many-to-one mapping cardinalities, as well as by
employing the additional constraints detailed below in this section.
● Each shipment can have up two trailers and two drivers. These must be represented in
the shipments table. (Hint: In the final database schema, a shipment with only one
driver would have a NULL value for driver two.)
● All primary and foreign keys must be identified in the diagram appropriately (see
example below).
● All multivalued attributes should be decomposed into separate tables
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 1 images
