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 ).
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.
b. 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.
c. 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
- 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. - 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 - 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
