Exam_03 - EXAMINATIONS 2003 END-YEAR COMP442 COMP 442...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
COMP 442 continued 1 EXAMINATIONS 2003 COMP442 END-YEAR COMP 442 Issues in Database and Information Systems Time Allowed: 3 Hours (180 minutes) Instructions: Attempt all questions. Make sure your answers are clear and to the point. Non-programmable calculators without full alphabetic keys are permitted. Non-electronic foreign language dictionaries are permitted. No reference material is allowed. There are 180 possible marks on the exam. Answer in the appropriate heavily outlined boxes or follow the instructions given in the questions. Question Topic Marks 1 SQL:1999 and OLAP Queries [25 marks] 2 OLAP Query Rewriting [30 marks] 3 The Bit-Slice Index [20 marks] 4 OLAP Architectures [10 marks] 5 Valid XML Documents [30 marks] 6 Mapping Documents to Relational Databases [15 marks] 7 Querying XML Documents and Databases [25 marks] 8 Publishing Databases as XML Documents [25 marks]
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COMP 442 continued 2 Question 1. SQL:1999 and OLAP Queries [25 marks] In this question you should analyse the properties of CUBE and ROLLUP operators using the following star schema: { Sales ({ TimeId , ProdId , ShopId , Amnt }, { TimeId + ProdId + ShopId }), Time ({ TimeId , Day , Week , MonthId , Month_Name , Year }, { TimeId }), Product ({ ProdId , Prod_Name , Prod_Type , Industry }, { ProdId }), Shop ({ ShopId , City , District , Country }, { ShopId })}, where the location attribute hierarchy is defined using the following set of functional dependencies: ShopId City , City District , District Country . a) [10 marks] Consider the following two SQL:1999 statements: Q1: SELECT Year, Industry, Country, AVG(Amnt) FROM Sales NATURAL JOIN Time NATURAL JOIN Product NATURAL JOIN Shop GROUP BY CUBE (Year, Industry, Country); Q2: SELECT Year, Industry, Country, AVG(Amnt) FROM Sales NATURAL JOIN Time NATURAL JOIN Product NATURAL JOIN Shop GROUP BY ROLLUP (Year, Industry, Country); How many views (groupings) will produce the query Q1 and how many views will produce the query Q2? List the views produced by each query in your answer. ANSWER
Background image of page 2
COMP 442 continued 3 b) [7.5 marks] How many views with different aggregate granularities will produce the following SQL statement: SELECT ShopId, City, District, Country, AVG(Amnt) FROM Sales NATURAL JOIN Shop GROUP BY ROLLUP (ShopId, City, District, Country); Justify your answer. ANSWER c) [7.5 marks] How many views with different aggregate granularities will produce the following SQL statement: SELECT Country, District, City, ShopId, AVG(Amnt) FROM Sales NATURAL JOIN Shop GROUP BY ROLLUP (Country, District, City, ShopId); Justify your answer. ANSWER
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COMP 442 continued 4 Question 2. OLAP Query Rewriting [30 marks] Consider the following star schema: { Sales ({ TimeId , ProdId , ShopId , Amnt }, { TimeId + ProdId + ShopId }), Time ({ TimeId , Day , Week , MonthId , Month_Name , Year }, { TimeId }), Product ({ ProdId , Prod_Name , Prod_Type , Industry }, { ProdId }), Shop ({ ShopId , City , District , Country
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 22

Exam_03 - EXAMINATIONS 2003 END-YEAR COMP442 COMP 442...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online