Deliverable 2 and 3
Overview: Deliverable 2 The objective of Project #1 is to develop a normalized relational data model describing the primary business processes of Polytechnic Supply Corporation (PSC). PSC is in the business of wholesale distribution, whereby they purchase most of their products directly from various manufacturers, and then they sell these products to their customers. PSC can actually fabricate some of the products they sell, in addition to purchasing them from the manufacturers. In Deliverable 2 you will start with a copy of your Deliverable 1, and build upon it by adding the necessary functionality to support the following business rules: Business Rules: The initial entities for your model are presented below. In addition to these initial entities, you are provided with several business rules. You should treat this project as a business assignment in which your instructor is your customer. You are to deliver the results of this project in a very professional and accurate format. The quality of your project submission is a reflection of your attention to detail, and it is an important factor that influences how your customers will think of you and how quickly they will pay you. Your instructor (customer) will . deduct points for incorrect formatting, as well as, inaccurate or incomplete work. Your grade represents your pay . 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. A Sales Rep may service multiple customers. Employees that are Sales Reps are paid commission on their sales based on a commission rate for the Sales Representative. Different Sales Reps may have different commission rates. All products are purchased from suppliers, and multiple suppliers can provide each product; 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 components (parts) of each product. Component parts are products too. Each employee can complete a series of PSC courses. It is essential to document the courses employees complete, as these skills are used to evaluate the employees. Initial Entities: CUSTOMER ORDER PRODUCT In Deliverable 2, you will create a new logical Schema beginning with the information contained in your Deliverable 1 and then add additional entities, attributes and relationships to the model. You must maintain 3rd Normal Form in this enhanced model. . CustomerID Customer Name Customer Address CreditLimit OrderID CustomerID Customer PONumber Order Date DueDate ShipDate ProductID Product Description Unit Price StockQuantity Deliverable 3 Deliverable 1 You are to create an Logical Schema (not an ERD). Begin with the initial entities for CUSTOMER, ORDER, and PRODUCT provided above, and normalize your model to 3rd Normal Form. This is your deliverable #1. Realize that the three entities are not in 3d Normal Form. This is obvious by the many-to-many relationship that would need to exist between ORDER and PRODUCT. You are to do the steps necessary to obtain 3rd Normal Form. You do not have to show how you got there, but you are to provide a document that shows the design you come up with once you have achieved 3r' Normal Form. Hint: The solution has more than three relations. Your answer will be within the Word document you submit as your Project 1 Assignment response along with the other deliverables for this assignment. Deliverable 3 contains the same information as Deliverable 2, except Deliverable 3 is an Entity Relationship Diagram (ERD) that you develop in Erwin. All of the functionality indicated in the business rules must be present. Also, this exercise is an excellent opportunity to show your understanding of Enhanced Entity Relationship Diagrams (EERD), as you will have the opportunity to demonstrate Supertype/Subtypes. A helpful hint here is to take a screenshot of your Erwin ERD instead of printing it. ERwin places a big watermark description across the page when you print using the free Community Edition of the software; however, if you simply take a screenshot, then the watermark does not appear. All entities, attributes, and relationships must be properly labeled. Although they are presented in different visual formats, the information contained on this Deliverable 3 ERwin Entity Relationship Diagram (ERD) must exactly correspond to the information contained on Logical Schema of deliverable #2. Overview: Deliverable 2 The objective of Project #1 is to develop a normalized relational data model describing the primary business processes of Polytechnic Supply Corporation (PSC). PSC is in the business of wholesale distribution, whereby they purchase most of their products directly from various manufacturers, and then they sell these products to their customers. PSC can actually fabricate some of the products they sell, in addition to purchasing them from the manufacturers. In Deliverable 2 you will start with a copy of your Deliverable 1, and build upon it by adding the necessary functionality to support the following business rules: Business Rules: The initial entities for your model are presented below. In addition to these initial entities, you are provided with several business rules. You should treat this project as a business assignment in which your instructor is your customer. You are to deliver the results of this project in a very professional and accurate format. The quality of your project submission is a reflection of your attention to detail, and it is an important factor that influences how your customers will think of you and how quickly they will pay you. Your instructor (customer) will . deduct points for incorrect formatting, as well as, inaccurate or incomplete work. Your grade represents your pay . 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. A Sales Rep may service multiple customers. Employees that are Sales Reps are paid commission on their sales based on a commission rate for the Sales Representative. Different Sales Reps may have different commission rates. All products are purchased from suppliers, and multiple suppliers can provide each product; 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 components (parts) of each product. Component parts are products too. Each employee can complete a series of PSC courses. It is essential to document the courses employees complete, as these skills are used to evaluate the employees. Initial Entities: CUSTOMER ORDER PRODUCT In Deliverable 2, you will create a new logical Schema beginning with the information contained in your Deliverable 1 and then add additional entities, attributes and relationships to the model. You must maintain 3rd Normal Form in this enhanced model. . CustomerID Customer Name Customer Address CreditLimit OrderID CustomerID Customer PONumber Order Date DueDate ShipDate ProductID Product Description Unit Price StockQuantity Deliverable 3 Deliverable 1 You are to create an Logical Schema (not an ERD). Begin with the initial entities for CUSTOMER, ORDER, and PRODUCT provided above, and normalize your model to 3rd Normal Form. This is your deliverable #1. Realize that the three entities are not in 3d Normal Form. This is obvious by the many-to-many relationship that would need to exist between ORDER and PRODUCT. You are to do the steps necessary to obtain 3rd Normal Form. You do not have to show how you got there, but you are to provide a document that shows the design you come up with once you have achieved 3r' Normal Form. Hint: The solution has more than three relations. Your answer will be within the Word document you submit as your Project 1 Assignment response along with the other deliverables for this assignment. Deliverable 3 contains the same information as Deliverable 2, except Deliverable 3 is an Entity Relationship Diagram (ERD) that you develop in Erwin. All of the functionality indicated in the business rules must be present. Also, this exercise is an excellent opportunity to show your understanding of Enhanced Entity Relationship Diagrams (EERD), as you will have the opportunity to demonstrate Supertype/Subtypes. A helpful hint here is to take a screenshot of your Erwin ERD instead of printing it. ERwin places a big watermark description across the page when you print using the free Community Edition of the software; however, if you simply take a screenshot, then the watermark does not appear. All entities, attributes, and relationships must be properly labeled. Although they are presented in different visual formats, the information contained on this Deliverable 3 ERwin Entity Relationship Diagram (ERD) must exactly correspond to the information contained on Logical Schema of deliverable #2
