|
faizanayan
Starting Member
4 Posts |
 Posted - 2007-06-06 : 12:22:18
|
| databse name: bookorderRealtions:customer (customerid,l_name,f_name,city, district,country)cust_order(orderid,orderdate,customerid)order_detail(orderid,itemno,isbn,quantity)book(isbn,title,edition_no,price)author(authorid,name,surname)book_author(authorid,isbn,authorseqno)Now populate the datawarehouse name: book_orderdwhaving fact table & three dimension tables given below from the above bookorder databasefact tablefactsales(customerid,timeid,isbn,unit_price,discount_price,sales_quantity,sales_amount)dimension tablesdimcustomer(customerid,l_name,f_name,city,district,country)dimtime(timeid,orderdate,dayofweek, month, year)dimbook(isbn,title,edition_no,price)Now I have to populate the fact & dimension tables by writing sql scripts.Now I have already populated the dimension tables by writing sql script,But I have to populate the fact table taking into account, here I am facing problem in wriring sql script (i) unit_price is taken from the book base table with reference to the isbn(ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate)(iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn(iv) sales_amount is sales_quantity * discount_price |
|
How to populate the fact table