Assignment2
Question1
1. How many nonempty aggregated (i.e., non-base) cells a complete cube will contain?
Answer: 1918.
Explaination: Initially, for those cuboids with at least one of the first three dimensions not aggregated(i.e. not *), for example cuboid (dim1, ∗, ∗, ∗, ..., ∗), there are two cells. The number of such cuboids is (23 − 1) ∗ 27 = 896. Second, for cuboids with the first three dimensions aggregated, for example cuboid (∗, ∗, ∗, dim4, ..., dim10), there is only one cell. The number of such cuboids is 27 = 128. So the number of non-based cells is 896 ∗ 2 + 128 − 2 = 1918.
2. How many nonempty aggregated cells an iceberg cube will contain, if the condition of the iceberg cube is count ≥ 2?
Answer: 128.
Explaination:
Only those cells with the first three dimensions aggregated (i.e.*), for example the cell (∗, ∗, ∗, b4, ..., b10), have count 2. And the number of such cells is 27 = 128.
3. How many (non-star) dimensions does the closed cell with count 2 have?
Answer: 7.
Explaination: There is only one closed cell with count 2, i.e. (∗, ∗, ∗, b4, ..., b10).
So the number of non-star dimensions is 10 − 3 = 7.
4. How many closed cells in the full cube?
Answer: 3.
Explaination: A closed cube is a data cube consisting of only closed cells.
The three closed cells are (∗, ∗, ∗, b4, ..., b10),(a1, a2, a3, b4, ..., b9, b10), and (b1, b2, b3, b4, ..., b9, b10).
Question 2
1.How many cuboids are there in this cube
Answer: 24
Explaination:
Where is the number of levels associated with dimension i.
So for the Total number of cuboid = (2+1) *(1+1) *(1+1)*(1+1) =24
2.Counting cells in the cuboid(Location(city),Category,Rating,Price)
Answer = 48
SELECT distinct city,category,rating,price ,count(*) as tot FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A group by A.category,A.rating,A.price,A.city
3.Now let's drill up by climbing up in the Location dimension from City to State. How many cells are there in the cuboid.
1. How many nonempty aggregated (i.e., non-base) cells a complete cube will contain?
Answer: 1918.
Explaination: Initially, for those cuboids with at least one of the first three dimensions not aggregated(i.e. not *), for example cuboid (dim1, ∗, ∗, ∗, ..., ∗), there are two cells. The number of such cuboids is (23 − 1) ∗ 27 = 896. Second, for cuboids with the first three dimensions aggregated, for example cuboid (∗, ∗, ∗, dim4, ..., dim10), there is only one cell. The number of such cuboids is 27 = 128. So the number of non-based cells is 896 ∗ 2 + 128 − 2 = 1918.
2. How many nonempty aggregated cells an iceberg cube will contain, if the condition of the iceberg cube is count ≥ 2?
Answer: 128.
Explaination:
Only those cells with the first three dimensions aggregated (i.e.*), for example the cell (∗, ∗, ∗, b4, ..., b10), have count 2. And the number of such cells is 27 = 128.
3. How many (non-star) dimensions does the closed cell with count 2 have?
Answer: 7.
Explaination: There is only one closed cell with count 2, i.e. (∗, ∗, ∗, b4, ..., b10).
So the number of non-star dimensions is 10 − 3 = 7.
4. How many closed cells in the full cube?
Answer: 3.
Explaination: A closed cube is a data cube consisting of only closed cells.
The three closed cells are (∗, ∗, ∗, b4, ..., b10),(a1, a2, a3, b4, ..., b9, b10), and (b1, b2, b3, b4, ..., b9, b10).
Question 2
1.How many cuboids are there in this cube
Answer: 24
Explaination:
Where is the number of levels associated with dimension i.
So for the Total number of cuboid = (2+1) *(1+1) *(1+1)*(1+1) =24
2.Counting cells in the cuboid(Location(city),Category,Rating,Price)
Answer = 48
SELECT distinct city,category,rating,price ,count(*) as tot FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A group by A.category,A.rating,A.price,A.city
3.Now let's drill up by climbing up in the Location dimension from City to State. How many cells are there in the cuboid.