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 airlsea/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 is. (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 among Shipment Mode, and Shipment Class Products roll up 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 invoice for each product. That is, the same product will not show up in multiple lines in the same invoice. 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? If so, what? - and when they do, move on. Sketch out a model using your basic understanding and then revisit the description. Also Answer the following question: If we want to track information about deals in existence on any given date, will the fact table of Invoice line items give us that info? If not, 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 airlsea/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 is. (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 among Shipment Mode, and Shipment Class Products roll up 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 invoice for each product. That is, the same product will not show up in multiple lines in the same invoice. 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? If so, what? - and when they do, move on. Sketch out a model using your basic understanding and then revisit the description. Also Answer the following question: If we want to track information about deals in existence on any given date, will the fact table of Invoice line items give us that info? If not, what do we need to add to the model