Wgu C170 Vht2 Task 1 Normalization And Database Design

WGU C170 VHT2 TASK 1 NORMALIZATION AND DATABASE DESIGN Alexander Coffey Student ID: SECTION A: Nora’s Bagel Bin Database Blueprints First Normal Form (1NF) BAGEL ORDER PK Bagel Order ID PK Bagel ID Order Date First Name Last Name Address 1 Address 2 City State Zip Mobile Phone Delivery Fee Bagel Name Bagel Description Bagel Price Bagel Quantity Special Notes This 1NF table achieves the following: • captures all necessary data fields from the unnormalized “Nora’s Bagel Bin Catering Order” form • excludes calculated fields like subtotal and sales tax as it is not necessary to store these values • establishes primary keys • does not contain any repeated groups (data that depends on both pieces of the composite primary key) • contains only atomic data (no attributes will contain more than one piece of data) NOTE: The attributes given above (e.g., address lines, phone number) do not need to be processed any further into subcomponents during the normalization process. Second Normal Form (2NF) 1:M M:1 I separated BAGEL Attributes to the Bagel table. I separated ORDER Attributes to the BAGEL ORDER table. Each BAGEL ORDER can have many LINE ITEMs, but each LINE ITEM belongs to only 1 BAGEL ORDER Each LINE ITEM can have only 1 type of BAGEL, but each BAGEL can belong in many LINE ITEMs • Separate the data that depend on just one of the two parts of the primary key into separate tables. • Keep any columns in the original table (now named "Bagel Order Line Item") that still depend on both parts of the original primary key. • Fill in each shad