A very large tele$-$communications company called $$Cell$9,$ providing cellular phone

services to a number of states in various regions of the country, plans to build a data

warehouse for decision support. They have millions of subscribers in the country. They

want to track the duration $($in minutes$)$ as well as the prevailing rate $($per minute$)$ of each

phone call made by its subscribers. They also want to analyze if there is any link between

the total amount of time spent in talking on cellphones by a subscriber and the number of

graduates in the state or the number of married persons in the state or the male$-$female

ratio of the state to which the subscriber belongs. Further, they want to analyse the relation

between the age, salary and marital status of the customers to their total bill amount per

day$/$month$/$year$.$ One other important requirement is to make queries like determining the

No$.$ of Pages $=2$

No$.$ of Questions $=4$

current total number of customers in the various age groups for each state having certain

ranges of male$-$female ratio.

$($a$)$ Design a suitable relational database schema for such a data warehouse, clearly

identifying the fact table$($s$),$ the facts in the fact table$($s$),$ the dimension table$($s$),$ their

primary key$($s$)$ and foreign key$($s$).$ Your schema should at least be able to satisfy the

above mentioned analysis requirements. You may consider other suitable attributes for

the dimension table$($s$).$

$($b$)$ Classify the facts in your fact table$($s$)$ as additive, non$-$additive and semi$-$additive.

$($c$)$ Draw possible concept hierarchies for each dimension that you have designed,

identifying whether these are schema hierarchies or set grouping hierachies.

$($d$)$ Write an SQL query that runs on your schema and returns the region$-$wise yearly

average bill amounts of married and unmarried customers.

$($e$)$ Draw a cuboid to represent the result of your query.

$($f$)$ From this cuboid, which sequence of OLAP operations would you perform to get the

average monthly bill amounts of all the customers for the states of Bihar and West

Bengal?

$($g$)$ Write an SQL query to return the current total number of customers in the various age

groups for each state with male$-$female ratio between $0\mathrm{.}9$ and $1\mathrm{.}1.$

$($h$)$ For any one fact table $($You may have only one, depending on your design$),$ and any

one attribute of any one dimension table, draw the bitmap index table$($s$)$ and join index

table$($s$).$ Before drawing the index tables, first mention the representative rows in the

tables.