QUESTION 1

(a) Give 6 differences between OLTP and OLAP

(b) Describe the process of transformation of OLTP data so that it gives acceptable performance in an OLAP system

(c) Give 5 security procedures for the protection of information

QUESTION 2

XYZ Company has decided to create a normalised relational database to store its orders. A customer places one order per day and an order can have more than one item. Currently, the system stores all the orders in Excel as shown below

(a) What is normalisation and give the definition for the first normal form

(b) What is a primary field and which field/fields would you use to put the above table in first normal form?

(c) What is the relationship between Orders and Items? Draw an Entity Relationship diagram to illustrate this

(d) What is the relationship between Customers and Orders? Draw an Entity Relationship diagram to illustrate this

(e) Are there any many-to-many relationships between the entities mentioned in (c) or (d)? If so, add an intermediary table to break this relationship and draw the corresponding Entity Relationship diagram to illustrate this

(f) Give the definition for the third normal form and draw the conceptual model for the system, giving the primary key(s) for each entity

(g) Define the following terms

1. Data Marts
2. Dimension tables
3. Fact tables

(h) Describe the 3 ways in which changes are stored in slowly changing dimensions

(i) Management requires a report of ‘Weekly Sales of Ballpoint Pens per City'. Draw a STAR Schema to support the query for this report