Long before the tables are created and data is moved or inserted into them, the database designer creates the logical and physical models of the database. In this application, you will examine the process of relational database design and modeling and understand and use normalization techniques to identify new tables, update business rules for a database, and create an updated entity relationship diagrams.

To complete this Assignment:

Normalize the tables from the Week 2 Assignment, specifically, the tables identified within your Learning Resources.

Tasks:

1. Identify the new tables. (25 points)
2. Update the business rules for the database. (25 points)
3. Provide an updated ERD. (25 points)

Customer Table int Foreign Key Foreign Key int Order ID Part ID Number Ordered Quoted Price int List of Attributes Data Type decimal Primary Key/Foreign Key Primary key Part Table Primary Key/Foreign Key Primary key Customer ID Last Name First Name Street address City State Zip Code Current balance Credit limit Sales Rep. ID int Varchar Varchar Varchar Varchar Varchar int decimal decimal int List of Attributes Part ID Part Description Units on hand class Warehouse number Unit Price Data Type int Varchar int Varchar int decimal Foreign Key Sales Representative Table Primary key/Foreign Key Primary key List of Attributes Sales Rep ID Last Name First Name Street addre City State Zip Code Region Data Type int Varchar Varchar Varchar Varchar Varchar int Varchar 2. Business Rules: Assuming each region has only one sales representative and a part may or may not be ordered by any customer. Each customer belongs to one and only one sales representative. Each sales representative is responsible for at least one customer within their region. Each customer may or may not place an order at a particular date/time. Each order is specific to one and only one person depending on the date and time the order is Each order may have one or more order lines depending on the different parts that are ordered. Each order line is specific to one and only one order, depending on the order id. Each order line may include one or more parts. Each part may or may not belong to an order line. r is being placed 3. Required ER-Diagram: Region Description Total Commission Commission Rate Varchar decimal decimal Foreign Key Order Table Primary key/Foreign Key Primary key List of Attributes Order ID Order Date Customer | Shipping Date Data Type int date int date Foreign Key Order Line Table Customer Table int Foreign Key Foreign Key int Order ID Part ID Number Ordered Quoted Price int List of Attributes Data Type decimal Primary Key/Foreign Key Primary key Part Table Primary Key/Foreign Key Primary key Customer ID Last Name First Name Street address City State Zip Code Current balance Credit limit Sales Rep. ID int Varchar Varchar Varchar Varchar Varchar int decimal decimal int List of Attributes Part ID Part Description Units on hand class Warehouse number Unit Price Data Type int Varchar int Varchar int decimal Foreign Key Sales Representative Table Primary key/Foreign Key Primary key List of Attributes Sales Rep ID Last Name First Name Street addre City State Zip Code Region Data Type int Varchar Varchar Varchar Varchar Varchar int Varchar 2. Business Rules: Assuming each region has only one sales representative and a part may or may not be ordered by any customer. Each customer belongs to one and only one sales representative. Each sales representative is responsible for at least one customer within their region. Each customer may or may not place an order at a particular date/time. Each order is specific to one and only one person depending on the date and time the order is Each order may have one or more order lines depending on the different parts that are ordered. Each order line is specific to one and only one order, depending on the order id. Each order line may include one or more parts. Each part may or may not belong to an order line. r is being placed 3. Required ER-Diagram: Region Description Total Commission Commission Rate Varchar decimal decimal Foreign Key Order Table Primary key/Foreign Key Primary key List of Attributes Order ID Order Date Customer | Shipping Date Data Type int date int date Foreign Key Order Line Table