CS614 Assignment 2 Solution Spring 2018

Question Statement:
“Auto Mart” is web based Auto Parts Shop that offers purchase service of auto parts to the webpage visitors
online. It offers different variants of a single part. The administrative staff can update auto parts information,
and manage categories of products. Main categories includes Engine parts and Body parts. The buyer or the
customer can view the information about the parts which he wants to buy and can include them into order.
Customer will confirm the order and checkout, he/she can also cancel order before the order is shipped. Payment
will be made through credit card. Total price of the auto parts also includes sales tax. Auto Parts will be
delivered to the address provided by client. Given below is the ERD of above given scenario.
Now, using the above scenario consider the process “Auto Parts Sales”. The entities which are involved in the process are given in above ERD. The process has the following information;
“Customer ‘A’ ordered 10 auto parts”.
“Customer ‘B’ ordered 5 auto parts”.
“Customer ‘C’ ordered 15 auto parts”.
“Customer ‘A’ ordered 10 body part from auto parts shop”.
“Customer ‘B’ ordered 10 engine part from auto parts shop”.
“Customer ‘C’ ordered 20 engine part and body parts from auto parts shop”.
You have studied four steps which are involved in dimensional modeling. First step is to identify the business process.
Consider the process “Auto Parts Sales” described above.
Apply the remaining steps of dimensional modeling to find
out the grain, facts and dimensions and then create a simplified star schema from it.
Note: This assignment covers lectures 13 and 14.
Due Date: 22/05/2018