Data Warehousing (MSc/6+), Demos

Install the sample Foodmart database (which has a star schema) as follows.

Prerequisites:

1. We assume that a Postgres server is installed and accessible
2. We assume that the postgres superuser is called postgres
3. we assume that users have local access without passwords. If this is not the case, passwords have to be provided below
4. .

Database installation:

• Download and unzip the Zip file
• in a shell or command window, navigate to the directory where you unzipped the zip file
• create a user (e.g., fuser)
• create a database foodmart with the owner you create just before
• create tables:
  psql -U fuser -d foodmart -f create-tables.sql
• load data:
  psql -U postgres -d foodmart -c "\copy account from ./account.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy category from ./category.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy currency from ./currency.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy customer from ./customer.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy days from ./days.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy department from ./department.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy employee from ./employee.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy expense_fact from ./expense_fact.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy inventory_fact from ./inventory_fact.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy position from ./position.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy product from ./product.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy product_class from ./product_class.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy promotion from ./promotion.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy region from ./region.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy salary from ./salary.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy sales_fact from ./sales_fact.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy store from ./store.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy time_by_day from ./time_by_day.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy warehouse from ./warehouse.csv with (FORMAT csv, header TRUE);"
  psql -U postgres -d foodmart -c "\copy warehouse_class from ./warehouse_class.csv with (FORMAT csv, header TRUE);"
• create primary and foreign key constraints:
  psql -U fuser -d foodmart -f create-constraints.sql