The Building Tools company sells various tools. It has sales offices and warehouses all over Australia. The company developed a data warehouse to store information about sales, customers and products. These data are stored in the AppliedProject2 Access file provided for you.
The database consists of 6 tables:
• CompanyDim – with the sales office address, state and email
• Productid – product information
• WarehouseDim – warehouse locations
• CustomerDim – information about customers
• DateDim – sales dates
• SaleFacts – information about sales
 notice that all data (customers, sales, dates, warehouses and products) are randomly generated. Table and field names are self-explanatory.
The Building Tools company wants to retrieve information from the database for reporting and data analysis.
You must do the following tasks
1. In the MS Word document:
a. Analyze and describe the tables (fields, field datatypes, records etc.).
b. Identify dimension and fact tables. (You need to remember that the database structure is a database star schema).
c. For each table, identify and describe primary and foreign (if any) keys.
d. Draw the database diagram.
e. Copy from the database all queries to the Word document and explain them.

2. In the SQLite Browser, you need to write, save and run the following SQL queries:
a. Create all primary and foreign keys.
b. Insert a new sale date. Devise your own data for this record.
c. Create a new sale fact related to the new date. You can choose any existing product, warehouse, customer and sales office.
d. Display all product names, brands and prices.
e. Display all customer names from New South Wales.
f. Display the total amount of all company sales in the first quarter of 2016.
g. Display total amount of sales for each customer separately.
h. Display total amount of sales for each product and warehouse separately, i.e. the output should contain 3 columns: product, warehouse division and total amount for this product and warehouse.
i. Display products sold from offices located in Victoria.
j. For each sale, display a customer name and a purchase date and a quarter.

Answer rating: 100% (QA)

In the MS word document a There are 6 tables in total out of which 5 are dimension tables and 1 is a fact table I The Company Dim table consists of primary key company ID This is the key which will be ... View the full answer