CSc 8711. Databases and the Web
Spring 2004, Programming Project 2 (Multidimensional Database Views/Queries)
Due: Thursday, February 12, 2004

In this project, we will implement a Web based system using Java Servlets, that will perform aggregate queries and roll-up/drill-down operations on a multi-dimensional database stored as relations. The system should work with any multi-dimensional database (the user will choose the database in the first screen). The meta data describing the schemas of the various multi-dimensional databases is available in the following tables: 
drop table mdbs cascade constraints;
create table mdbs (
  dbname  varchar2(20), -- name of multi-dimensional database
  factTableName varchar2(20), -- name of fact table for mdb
  factColumnName varchar2(20), -- name of column in fact table for fact
  primary key (dbname)
);
--
insert into mdbs values ('salesMDB','SALES','AMOUNT');
insert into mdbs values ('inventoryMDB','INVENTORY','QUANTITY');
--
create table dimensions (
  dbname  varchar2(20),
  dimTableName  varchar2(20),
  primary key (dbname,dimTableName),
  foreign key (dbname) references mdbs
);
--
insert into dimensions values ('salesMDB','PRODUCT');
insert into dimensions values ('salesMDB','LOCATION');
insert into dimensions values ('salesMDB','TIME');
--
insert into dimensions values ('inventoryMDB','ITEM');
insert into dimensions values ('inventoryMDB','SIZE');
insert into dimensions values ('inventoryMDB','COLOR');
insert into dimensions values ('inventoryMDB','PATTERN');
--
drop table dimTableSchema cascade constraints;
create table dimTableSchema (
  dbname        varchar2(20),
  dimTableName        varchar2(20),
  columnName        varchar2(30),
  dataType varchar2(6) check dataType in ('String','Number'),
  pkey  char(1) check pkey in ('Y','N'),
  primary key (dbname,dimTableName,columnName),
  foreign key (dbname,dimTableName) references dimensions
);
--
insert into dimTableSchema values ('salesMDB','PRODUCT','PID','Number','Y');
insert into dimTableSchema values ('salesMDB','PRODUCT','PNAME','String','N');
insert into dimTableSchema values ('salesMDB','PRODUCT','CATEGORY','String','N');
insert into dimTableSchema values ('salesMDB','PRODUCT','PRICE','Number','N');
--
insert into dimTableSchema values ('salesMDB','LOCATION','LOCID','Number','Y');
insert into dimTableSchema values ('salesMDB','LOCATION','CITY','String','N');
insert into dimTableSchema values ('salesMDB','LOCATION','STATE','String','N');
insert into dimTableSchema values ('salesMDB','LOCATION','COUNTRY','String','N');
--
insert into dimTableSchema values ('salesMDB','TIME','TID','Number','Y');
insert into dimTableSchema values ('salesMDB','TIME','DAY','Number','N');
insert into dimTableSchema values ('salesMDB','TIME','MONTH','Number','N');
insert into dimTableSchema values ('salesMDB','TIME','QUARTER','Number','N');
insert into dimTableSchema values ('salesMDB','TIME','YEAR','Number','N');
--
drop table hierarchy cascade constraints;
create table hierarchy (
  dbname varchar2(20)
  dimTableName varchar2(20)
  columnName  varchar2(30)
  levelNum    number(2)
)
--
insert into hierarchy values ('salesMDB','PRODUCT','CATEGORY', 1)
insert into hierarchy values ('salesMDB','PRODUCT','PNAME', 2)
--
insert into hierarchy values ('salesMDB','LOCATION','COUNTRY', 1)
insert into hierarchy values ('salesMDB','LOCATION','STATE', 2)
insert into hierarchy values ('salesMDB','LOCATION','CITY', 3)
--
insert into hierarchy values ('salesMDB','TIME','YEAR', 1)
insert into hierarchy values ('salesMDB','TIME','QUARTER', 2)
insert into hierarchy values ('salesMDB','TIME','MONTH', 3)
insert into hierarchy values ('salesMDB','TIME','DAY', 4)
--
The user interface is shown here.