Big Project /need Help

A Southwest Booksellers has stores in three cities:
Albuquerque {Winrock, OldTown, NobHill}
Santa Fe {VistaView, MesaVerde}
Taos {Zia, Zuni}
and sells nine categories of books:
{Computers,Science,ArtHealth,SelfHelp,Business,Fiction,History,Religion}.

The bookstores will keep a database of transaction records for one year.
A sales transaction record contains fields for city id, store id,
book category id, date, and quantity of category sold.
They would like to have a database of aggregate historical data from which
to generate the following reports:


1) Show city sales by quarter for a given year.
2) Show store sales by quarter for a given year.
3) Show Book Category sales by quarter for a given year.
4) Show city sales by year.
5) Show store sales by year.
6) Show Book Category sales by year.
7) Show sales by Book Category and quarter for a given store and year.

Your solution should simulate annual processing and
contain the following elements:

1) Script to generate transaction records.
2) Star Schema database of aggregate historical data.
3) ETL algorithm.
4) Reports implemented as Stored Procedures.

Demonstrate your solution using the following data:

Year Transactions
---- ------------ --------------------------------
2000 1000
2001 1500 TheCaverns store in Carlsbad
2002 2100
2003 2800 Philosophy category
2004 3600 CasaDelRio store in SantaFe
2005 4500 Travel category

A transaction is one book category of quantity between 1 and 5.


please see what i did
create table city
(
idc tinyint,
city varchar(20)
);

insert into city values(1,'Albuquerque');
insert into city values(2,'Santa Fe');
insert into city values(3,'Taos');


create table Alb

(
idal tinyint,
store varchar(20)
);

insert into Alb values(1,'Winrock');
insert into Alb values(2,'Oldtown');
insert into Alb values(3,'NobHill');


create table Santa

(
idsan tinyint,
store varchar(20)
);

insert into Santa values(1,'VistaView');
insert into Santa values(2,'MesaVerde');

create table Taos
(
idta tinyint,
store varchar(20)
);

insert into taos values(1,'Zia');
insert into taos values(2,'Zuni');

create table books

(
bid tinyint,
type varchar(10)
);

insert into books values(1,'Computers');
insert into books values(2,'Science');
insert into books values(3,'Arthealth');
insert into books values(4,'Selfhelp');
insert into books values(5,'Business');
insert into books values(6,'Fiction');
insert into books values(7,'History');
insert into books values(8,'Religion');

create table y2000
(
qtr tinyint,

);

insert into y2000 values(1);
insert into y2000 values(2);
insert into y2000 values(3);
insert into y2000 values(4);

create table SalesCube
(
idal tinyint,
idsan tinyint,
idta tinyint,
bid tinyint,
qtr tinyint,
qty smallint

);
create table ST
(
idal tinyint,
idsan tinyint,
idta tinyint,
bid tinyint,
qtr tinyint,
qty tinyint
)


-- Populate Transaction Table

declare @n smallint,
@idal tinyint,
@idsan tinyint,
@idta tinyint,
@bid tinyint,
@qtr tinyint,
@qty tinyint

set @n = 1

while @n <= 1000
BEGIN
set @idal = convert(tinyint, rand()*100) % 5 + 1
set @idta = convert(tinyint, rand()*100) % 5 + 1
set @idsan = convert(tinyint, rand()*100) % 5 + 1
set @bid = convert(tinyint, rand()*100) % 5 + 1
set @qty = convert(tinyint, rand()*100) % 5 + 1
set @qtr = convert(tinyint, rand()*100) % 5 + 1

insert ST values( @idal ,
@idsan ,
@idta ,
@bid ,
@qtr ,
@qty )
set @n = @n + 1
END

insert salescube (idal,idsan,idta,qtr,qty)
select idal ,
idsan,
idta,qtr,sum(qty)from st
group by idal,idsan,idta,qtr
with cube


this is not working properly. i really do not know how to aproach quarters in the year.
possibly my tables are wrong too.
how to populate transaction tables?
if anyone has any advices i will be happy to apply it.
thanks