Pine Valley Furnitures

Customer Order Report see image.

Instructions:

Complete the following tasks:

1. Begin with the initial entities for CUSTOMER, ORDER, and PRODUCT provided above, transform them into relations, and normalize your model to 3rd Normal Form. This is your deliverable #1. A handwritten schema is acceptable, but make sure it includes proper formatting and capitalization, as well as the appropriate relationhips.

2. Combine the relations from delivarable #1 with the business rules and decompose the report into relational schema and show referential integrity constaints. Be sure to maintain 3rd Normal Form. This is your delivarable #2. A handwritten schema is acceptable, but make sure it includes proper formatting, capitalization, and relationships.

Initial entities: see image.

Business Rules:

• Each customer may place multiple orders, and each order may consist of multiple products
• Each customer is serviced by a Sales Representative that is an employee of PSC.
• Employees that are Sales Reps are paid commission on their sales based on a commission rate for the Sales Rep. Different Sales Reps may have different commission rates.
• All products are purchased from suppliers, and multiple suppliers can provide products; however, some products can also be fabricated by PSC. Different suppliers charge different amounts for the products. Fabricated products consist of a Bill of Materials that describes the component (parts) of each products. Fabricated products consists of a Bill of Materials that describes the components (parts) of each product. Component parts are products too.
• Each employee is required to complete a series of PSC courses. It is essential to document the courses employees complete, as these skills are used to evaluate promotion eligibility.