A shipping company serves a limited group of customers. A shipment invoice contains the following information: Invoice Number Date Picked Up Date of Delivery Ship From Customer Ship To Customer Ship From Warehouse Ship From City Ship To City Shipment Mode (like airisea/truck/train) Shipment Class (codes like 1.2.3 which translate to express, expedited, standard) Contract ID Total Shipping Charges Deal ID (refers to a discount deal in effect) Discount Amount Taxes Total Billed Amount Total Ship Weight Total Ship Volume In addition, each Line in Invoice contains: Product ID, Product Name, Quantity, Ship Weight, Ship Volume, Charge We assume: quantity is number of items.i.e you do not have to worry about unit measure issues. Discount and Taxes can be allocated per line. All the customers are registered with the company and treated as a single table of customers. For our model, we assume any customer may ship items to any other customer. Customer is a large, single table. Customer table also contains info on: - customer size (small, medium or large) - customer ship frequency (low, medium, or high) - credit status(excellent, good, average) These tend to be correlated; for example, large customers tend to be high frequency and have excellent or good credit status. Contract - number of contracts is relatively small. Each customer may have multiple contracts. Deal - number of Deals is relatively small. Some shipments may have no deal applicable. We do want to query information like total revenue by deal. For a given product, source-city, destination-city, and date, we can identify the deal in existence, if any. You can simplify source city to be source warehouse if you want On contract and deal. you are free to make assumptions on whether each shipment involves one or more contracts/deals Ship from city is same as the city where the Warehouse ls (not the city where Ship From Customer is. We can find an Estimated Date of Delivery for each shipment from operational systems, even though it is not printed on the invoice. There is some correlation present more Shipment Mode, and Shipment Class Products roll o to brand category. Warehouses roll up to subregion, region territory Some queries that we may be interested in query by customer type, product category.region year, shipment mode contract terms for measures like discount, ship weight, volume, delay in delivery, revenue. Design a dimensional model There will be only one line in each moice for each product. That the same product will not show up in multiple lines in the same For the fact table, show all foreign keys and the fact measures. For the dimension tables, you can simplify the list of attributes for each table. You don't need to provide detailed list of attributes For example a table can be listed as its key, and just the words: Other Attributes Date, other attributes: for Date table When information is given about other attributes, include them in your list: for example, Product dimension When information is not given about any attributes at all, and you conclude we need a dimension for this, you can make suitable assumptions. A similar example would be you know Policy is a dimension in a model, but don't know attributes, and you write: Policy Key. Policy Terms You can either draw up the star schema diagram as connected tables, or simply provide a list: fact table, attributes, dimension tables attributes. Some of the comments above are designed in anticipation of specific questions you may have that also implies that certain sentences may initially appear odd to you - 'should I do something about this? so, what? - and when they do, move on Sketch out a model using your basic understanding and then revisit the description Also Antwer the following question If we want to track information about dainaistence on any given date, will the fact table of Invoice line items give us that info? net, what do we need to add to the model? A shipping company serves a limited group of customers. A shipment invoice contains the following information: Invoice Number Date Picked Up Date of Delivery Ship From Customer Ship To Customer Ship From Warehouse Ship From City Ship To City Shipment Mode (like airisea/truck/train) Shipment Class (codes like 1.2.3 which translate to express, expedited, standard) Contract ID Total Shipping Charges Deal ID (refers to a discount deal in effect) Discount Amount Taxes Total Billed Amount Total Ship Weight Total Ship Volume In addition, each Line in Invoice contains: Product ID, Product Name, Quantity, Ship Weight, Ship Volume, Charge We assume: quantity is number of items.i.e you do not have to worry about unit measure issues. Discount and Taxes can be allocated per line. All the customers are registered with the company and treated as a single table of customers. For our model, we assume any customer may ship items to any other customer. Customer is a large, single table. Customer table also contains info on: - customer size (small, medium or large) - customer ship frequency (low, medium, or high) - credit status(excellent, good, average) These tend to be correlated; for example, large customers tend to be high frequency and have excellent or good credit status. Contract - number of contracts is relatively small. Each customer may have multiple contracts. Deal - number of Deals is relatively small. Some shipments may have no deal applicable. We do want to query information like total revenue by deal. For a given product, source-city, destination-city, and date, we can identify the deal in existence, if any. You can simplify source city to be source warehouse if you want On contract and deal. you are free to make assumptions on whether each shipment involves one or more contracts/deals Ship from city is same as the city where the Warehouse ls (not the city where Ship From Customer is. We can find an Estimated Date of Delivery for each shipment from operational systems, even though it is not printed on the invoice. There is some correlation present more Shipment Mode, and Shipment Class Products roll o to brand category. Warehouses roll up to subregion, region territory Some queries that we may be interested in query by customer type, product category.region year, shipment mode contract terms for measures like discount, ship weight, volume, delay in delivery, revenue. Design a dimensional model There will be only one line in each moice for each product. That the same product will not show up in multiple lines in the same For the fact table, show all foreign keys and the fact measures. For the dimension tables, you can simplify the list of attributes for each table. You don't need to provide detailed list of attributes For example a table can be listed as its key, and just the words: Other Attributes Date, other attributes: for Date table When information is given about other attributes, include them in your list: for example, Product dimension When information is not given about any attributes at all, and you conclude we need a dimension for this, you can make suitable assumptions. A similar example would be you know Policy is a dimension in a model, but don't know attributes, and you write: Policy Key. Policy Terms You can either draw up the star schema diagram as connected tables, or simply provide a list: fact table, attributes, dimension tables attributes. Some of the comments above are designed in anticipation of specific questions you may have that also implies that certain sentences may initially appear odd to you - 'should I do something about this? so, what? - and when they do, move on Sketch out a model using your basic understanding and then revisit the description Also Antwer the following question If we want to track information about dainaistence on any given date, will the fact table of Invoice line items give us that info? net, what do we need to add to the model