0. Go to www.kx.com
Download k2
Go to our web site, download
gentable.k
assignment
Also, the sample file 
roomtypespec

Then type:
k gentable 3000 roomtype roomtypespec 2

You should get a 3000 row table (or so)
having approximately 3000*0.25 different hotelids.
4 different room types
10 different numbers available
16 different descriptions.





I. Hotel assignment

roomtype(hotelid, roomtypeid, numberavailable, description) 
-- hotelid, roomtypeid is the key

k gentable 3000 roomtype roomtypespec 2

roomtypespec:
hotel 0.25
n 4.0
n 100.0
desc 16.0

inventory(hotelid, roomtypeid, daynumber,  numbertaken)
-- hotelid, roomtypeid, daynumber is the key

k gentable 100000 inventory inventoryspec 3

inventoryspec:
hotel 0.125
n 4.0
day 20
n 5.0

reservations specify a hotel, roomtypeid and daynumber

k gentable 1000 reservation reservationspec 0

reservationspec:
hotel 0.2
n 4.0
day 10

If you create a table for the reservations, let the
schema be
reservation( hotelid, roomtypeid, daynumber) 

Your mission:

0. Generate the files.
1. Import the files into roomtype and inventory.
2. Decide on your indexes.
3. Eliminate inventory rows that have numbertaken > numberavailable
4. Each time a reservation comes in for a particular hotel-roomtype-day
combination, update the appropriate inventory record by setting
numbertaken to numbertaken+1 (unless that exceeds the number
available for that night). If there is no such approriate inventory
record (in other words, no record in inventory having that hotel-roomtype-day
combination), then insert one with numbertaken = 1
5. Consider an alternate implementation in which 
there is an inventory record for all possible days up to 180.
Then the upserts would always be updates.
Which is faster?

To generate the alternative table which we'll call inventoryfull

inventoryfull(hotelid, roomtypeid, daynumber,  numbertaken)

You may find the following table to be useful

day(daynum)

k gentable 10000 day dayspec 1
dayspec:
day 180


II. Data warehouse assignment

lineitem(orderid, itemid, storeid, day, quantity, price)
-- orderid, itemid, storeid are keys

200,000 rows
total day range is 100 days

k gentable 200000 lineitem lineitemspec 3

lineitemspec:
order 0.1
item 0.01
store 1000.0
n 100.0
n 50.0
n 1000.0


storecity(storeid, city)
-- storeid is the key

1000 rows

citycountry(city, country)
-- cityid is the key

100 rows

countryregion(country,region)
-- country is the key

10 rows

itemtype(itemid, type)
-- itemid is the key

1000 items

typecategory(type, category)
-- type is the key

100 types
10 categories

Your mission:

1. Create these tables.
2. Ensure that foreign key constraints hold.
Which are they?
3. Decide on indexes and perhaps table redesign to support the following.
What is the average quantity of sales in some region over
a specified day range?
Which city had the best sales for some category?
Insert 10,000 lineitems.
4. See how well the queries do if you use sampling by 10%?


III. Superlinearity Exercise

sales(id, itemid, customerid, storeid, amount, price)
-- id is the key

k gentable 100000 sales salesspec 1

salespec:
sale 1.0
item 0.01
customer 0.01
store 100.0
n 100.0
n 50.0


item(itemid)
-- itemid is the key

1000 rows

customer(customerid)
-- customerid is the key


1000 rows

store(storeid)
-- storeid is the key

1000 rows

You want to produce two tables.
A sale row goes into
successfulsales if all of the foreign keys are present.
Otherwise it goes into unsuccessfulsales.

We looked at several implementations.
First we looked at alternatives for building successfulsales: 


insert successfulsales
select sales.*
from sales
where sales.itemid in (select itemid from item)
and sales.customerid in (select customerid from customer)
and sales.storeid in (select storeid from store)

insert successfulsales
select sales.*
from sales, item, customer, store
where sales.itemid = item.itemid
and sales.customerid = customer.customerid
and sales.storeid = store.storeid

Then we looked at ways to build unsuccessfulsales:

insert into unsuccessfulsales 
select * from sales;

delete from unsuccessfulsales
where id in (select id from successfulsales)

And other ways:

insert unsuccessfulsales
select sales.*
from sales
where sales.itemid not in (select itemid from item)
or sales.customerid not in (select customerid from customer)
or sales.storeid not in (select storeid from store)

Then we looked at an outer join approach:

insert into successfulsales
select sales.id, item.itemid, customer.customerid, store.storeid, sales.amount, sales.quantity
from 
((sales left outer join item on sales.itemid = item.itemid)
left outer join customer on sales.customerid = customer.customerid)
left outer join store on  sales.storeid = store.storeid;

insert into unsuccessfulsales
select *
from successfulsales
where itemid is null
or customerid is null
or storeid is null;

delete from successfulsales
where itemid is null
or customerid is null
or storeid is null