First Normal Form – 1 Table
I gleaned this table from the unstructured data contained within the order
form. I made sure not to have any redundant
... [Show More] groups, and each column in the
table will contain atomic values. Donut ID, Donut Name, Donut Quantity, Unit
Price, and Donut Description are an example of a repeating group. As I
mentioned earlier, in order to achieve first normal form, we must eliminate
repeating groups. This requires the use of a composite key made up of Donut
Order ID and Donut ID.
Second Normal Form – 3 Tables
In order to achieve second normal form we need to split the first
table into three separate tables so that all non-key attributes are
functionally dependent on the entire primary key. I took the
attributes that are partially dependent on the primary key, and
placed them into separate relations. Donut Name, Donut
Description, and Unit Price depend only on Donut Order ID. Donut
Quantity and Item Total depend on both Donut Order ID and Donut
ID.Third Normal Form – 4 Tables
In order to achieve third normal form we need to eliminate any transitive dependency, meaning an attribute depends on
another attribute that is not the primary key. For example, looking at our second normal form tables, Customer Last
Name is dependent on Donut Order ID. (Each Donut Order ID has only one Customer Last Name value associated with it)
To transform into third normal form we simply move any transitively dependent attributes to their own relation where
they depend on only the primary key.Entity Relationship Diagram using tables from A1C:
Both the Customer and Donut entities have a single primary key and related attributes that describe them. The
orderDetails table is an associative entity that shows the interactive relationship between the Donut, and donutOrder
entities. The DonutOrder entity has a primary key of DonutOrder_ID, and a foreign key consisting of Customer_ID to
record the order date, notes, and totals that describe the interaction of the Customer and Donut purchase with
corresponding foreign keys. The cardinality from customer to donutOrder is 1-to-many as a customer can place multiple
orders, while the cardinality in the other direction will be many-to-1 because a donutOrder will have at most one
customer per order. The donut to orderDetails cardinality is 1-to-many as one donut can fill multiple orderDetail
records, while in the other direction, an orderDetails record will have 1 donut per record, representing a many-to-1
relationship. The donutOrder to orderDetails cardinality is 1-to-many as one donutORder may reference multiple
orderDetail records, and in the other direction, one orderDetails record will only pertain to one donutOrder. The
orderDetails table has a composite primary and composite foreign key key consisting of donutOrder_ID and Donut_ID
which shows the intersection data of the Donut and donutOrder tables [Show Less]