v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
w:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}

Normal
0
false

false
false
false

EN-US
X-NONE
X-NONE

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:none;
mso-hyphenate:none;
text-autospace:ideograph-other;
font-size:12.0pt;
mso-bidi-font-size:10.0pt;
font-family:”Times New Roman”,serif;
mso-font-kerning:1.5pt;
mso-ansi-language:EN-GB;}

Assignment task

This assignment will provide you with an opportunity to practice basic elements of the OLTP system design and data manipulation and retrieval in a relational database. You will also be able to design a simple data warehouse based on the original OLTP system model. This is an individual assignment worth 40% of the overall module grade.

 

To do:

1.     Choose one of the sample ER diagrams (see Figure 1 below) and:

a.     Implement an OLTP database (Oracle is your default target DBMS here[1]

b.     ).
Assumptions as to the attribute semantic/functional meaning and data type, as well as choosing the appropriate data types, and interpreting the semantic meaning of relationships in the diagrams are subject to student’s discretion. Modification of number of attributes is allowed. All assumptions and modifications made should be clearly explained in the answer.

c.     Populate the new database with sample data.
It is recommended that each table has at least 5 records, and by all means the amount of data should be enough to clearly illustrate all the queries from Part 2.

d.     Include the database creation / population scripts in your answer.

2.     Develop queries to illustrate the relational operations listed below:

a.     Projection

b.     Union

c.     Set difference

d.     Intersection

e.     Cartesian product

f.      Equijoin

g.     Natural join

h.     Outer join

For every query produce the following:

a.     Written formulation of the query (e.g. “Get last names of all employees whose first name starts with “…”)

b.     SQL statement to implement the query (e.g. SELECT “lname” FROM “employees” WHERE “fname” LIKE ‘%…%’)

c.     Result screenshot of executing the query in Oracle SQL Developer

 

3.     Develop queries to illustrate the aggregate functions listed below:

SUM / COUNT / AVG / MIN / MAX

Use the same “written formulation” / “SQL statement” / “result screenshot” format here.

4.     There are a number of functional areas where the information from the above OLTP solution could be used for business analytics (e.g. book sales, career progression within the organisation, title portfolios held by publishers, etc.). Choose 2 of those and for each of the chosen do the following:

a.     Define the functional area (a couple of sentences explaining the purpose of business performance analytics here and the perceived benefits for the business in question)

b.     Write one business performance analysis question which would be the kind of question to help the company CEO in his/her decision making (a single sentence in English, e.g. “How do the sales look for the last quarter?”, “What was the average net profit over the last four quarters?”, etc.)

c.     Create a dimensional model for a data mart structure that would contain information required to answer your business performance analysis question

d.     Use this dimensional model to construct an SQL statement to implement your business performance analysis question

5.     Merge the two data marts created in the previous step to create a consolidated data warehouse model. Provide:

a.     Dimensional model for the consolidated data warehouse

b.     Explanations as to how the potential conflicts with matching dimensions were resolved

 

 

 

 Guidelines

The report should be presented in five clear sections, one “to do” item (see above) per section. This should be submitted in electronic form only through TurnitIn on VLE.

 

Marking Criteria

Item	Points max	Points scored
Implementing OLTP database based on a chosen ER diagram	20
·      DB creation SQL script	10
·      DB population SQL script	5
·      Explanation of assumptions and modifications made to the original ER diagram	5
Queries illustrating relational operations	16
·      8 queries, 2 marks each	16
Queries illustrating aggregate functions	10
·      5 queries, 2 marks each	10
Producing 2 data marts	40
·      Definition of 2 functional areas for analysis, 4 marks each	8
·      Formulation of 2 business performance analysis questions (1 per functional area), 4 marks each	8
·      Dimensional models for 2 data marts (1 per functional area / business performance analysis question), 8 marks each	16
·      SQL statements implementing 2 business performance analysis questions (1 statement per question)	8
Producing consolidated data warehouse model	14
·      Dimensional model for the data warehouse	10
·      Explanation on how the potential conflicts with matching dimensions were dealt with	4
Total	100