Question

The business process we are interested in is sales(i.e., selling products to customers). Develop a logical star- or snowflake-schema dimensional model.

orders customers + PK FK orderdetails PK/EK OrderID PK/FK Products UnitPrice Quantity Discount PK OrderID Customer D FK Employeeld Order Date RequiredDate Shipped Date FK ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode Ship Country CustomerID CompanyName ContactName Contact Title Address City Region PostalCode Country Phone shipppers PK Shipperid CompanyName Phone Fax products PK ProductID + employees PK Employeeld LastName FirstName Title TitleOfCourtesy BirthDate Hire Date ProductName FK Supplier FK CategoryID QuantityPerUnit UnitPrice UnitsInStock Units OnOrder ReorderLevel Discontinued + Address suppliers PK Supplier CompanyName ContactName Contact Title Address City Region PostalCode Country Phone Fax HomePage categories PK CategoryID CategoryName Description Picture City Region PostalCode Country Home Phone Extension Photo Notes FK Reports To PhotoPath

Answer 1

Answer is as follows :

Star Schema for given scenario is as follows :

Customer Customer id (PK) Onder details Order_id (PR) Product-id (PK) Unit Price Grantity Categion.id Lid (FK Name contact title Address Phone fat fact Orders Order id (FR) Product id CAK) Customer id (FK) Shipper shipper id (eko Company_xlame Shippa-id (AR) Eup_id Car supplinit (ft) Order date ship date Employee Eceployee.Id (PK) Name Phone Plone Noten supplier suppen -rd (PKD | Company Name litle Address Pleone fas

Here Order_Details , Employees, Customer , Shipper and Supplier are dimension tables and Fact_Orders is Fact table.

Fact Table

In given star Schema Fact Table describe the OrderId , Supplierid , ShipperId , ProductId, EmployeeId , CustomerId , OrderDate and ShipDate.These are used to get full details of particular order.

What is describe in Question 2

The attributes described in Fact Table are unique keys of other tables, so due to this we get full details of particular order. With this fact table we get any type of detail like by whom it order, employee involve, product ordered, shipping company , supplier of product and which product is ordered on a particular order date and on which date it is shipped.Due to Technical issues, i am unable to use lucid chart etc. , so i draw schema by hand. but if you want it let me know in comments...