Design and build a data mart, Database Management System

Assignment Help:

You are required to design and build a data mart/data warehouse using Oracle.

A national bank allocates funding every year for its local branches to provide temporary staff cover for its bank workers while they are either on holiday, absent due to illness, maternity or paternity leave amongst a number of other reasons.
If a branch has a temporary position, they will contact a temping agency and request a temp (temporary worker) to cover the position for a given date and time called a session. The temping agency can check available staff and try to arrange the necessary cover within the timely period requested by the bank branch. The cover could be for a bank clerk or admin staff for example. Summer can be a very busy time for the branches and many staff will take annual leave during this time period. The type of cover can be viewed within the ‘TempTypeofCover' table.

When a temp registers for the agency, their references will be thoroughly checked together with credit checks. The H&B Temps Agency must normally apply to the bank head office for its costs and details of time sessions covered by agency temping staff.
The managing directors of the bank have requested that an Oracle based data warehouse will be built which will collect data from individual branches and the temp agency. H&B Temps Agency has agreed to provide access to their data as they have had a long standing business relationship with the bank. The data within the Data Warehouse will then be analysed by the bank head office with the intention of providing more information regarding next year's individual branch funding budget.

Data from the H&B Temps Agency and bank branches has been collated and added into one Access database called H&BTempsAndBranches. The H&B Temps Agency has some serious concerns about the quality of the data provided. The H&B Temps Agency normally store information about temporary bank clerks, admin staff, sessions covered etc. The database model for the system is displayed below. H&B Temps Agency database is available as attached.

You should design the Data Warehouse which will provide information on the following:

• Sessions filled by type of branch cover by month

• Requests by type of branch cover by week

• Temp requests by county by month

• Temp requests by town by week

• The number of temp requests unbooked or cancelled by month

Design Data Mart/Data Warehouse

You should produce a star schema for your data mart design.

ETL

Firstly you will need to export the data from Access in into Oracle. You should then create a staging area in your own area. The data should be cleansed and necessary transformations carried out.

Data Cleansing

You should plan your cleansing exercise by identifying the various types of error that you will search for (e.g. missing primary keys, missing foreign keys, misspellings etc.) while describing techniques you used to identify errors and cleanse the data.

You should demonstrate how you used SQL for both purposes.

Building the Warehouse

Create and populate the dimension tables for your star schema.

The FACT table and TIME table should be populated at the same time with the use of a cursor.

Write SQL queries on the star schema to provide the required information for the bank head office.

Deliverables

Submit a report to support your implementation which should include:

• Your Star Schema Design.

• Documentation for your ETL processes to include all scripts.

• A data cleansing plan together with any scripts to identify and rectify errors.

• PL/SQL code listings for your system.

• Scripts for SQL for your queries.

• A screenshot of any forms, reports or other GUIs.

• A discussion of any problems that you encountered and how you tried to solve them.

• Submit a .zip file of your data warehouse.


For bonus points you may include some of the following features:

An extended TIME table should be created and populated.

Write the SQL to create a materialized view to show:

The Average, length of session cover, by month.

Write a SQL script to dump the data from the TEMP table into a flat file and use SQL*Loader to populate your data warehouse TEMP table.

Write the Create table statement that will Partition the Fact table by year.

Create a tool that will automate the cleansing exercise. 


Related Discussions:- Design and build a data mart

Transactions and concurrency management, Transactions And Concurrency Manag...

Transactions And Concurrency Management  Introduction One of the main benefits of storing data in an integrated repository or a database is to permit sharing of it betwee

In which relational modes cardinality is termed, In which relational modes,...

In which relational modes, cardinality is termed? Answer : Cardinality is termed in a number of tuples relational modes.

Explain the term- query, Explain the term- Query. Queries are used to re...

Explain the term- Query. Queries are used to repossess and show specific records in a table. They can also be used to show specific fields of specific records, which meet the co

What is derived and stored attribute, What is Derived and stored Attribute?...

What is Derived and stored Attribute? Derived and Stored Attribute - In a few cases, two or more attribute values are associated, for example, Age and BirthDate attributes of a

Draw the E/R diagrams, for the following business rules . Identify all enti...

for the following business rules . Identify all entities, attributes, relationships, primary keys, cardinalities and constraints where appropriate 3. (9) An art museum owns a larg

Explain concurrency problems, Question: a) There are three concurrency...

Question: a) There are three concurrency problems, i.e. three types of potential mistake which could occur if concurrency control is not properly enforced in the database syst

Define a relation schema, A Relation Schema represented by R(A1, A2, .....

A Relation Schema represented by R(A1, A2, ..., An) is made up of the relation name the list of attributes and R Ai that it contains. A relation is described as a set of tuples

What is a shadow copy scheme, What is a shadow copy scheme? It is simpl...

What is a shadow copy scheme? It is simple, but efficient, scheme known as the shadow copy schemes. It is based on making copies of the database called shadow copies that singl

Views, what is view?explain

what is view?explain

Datawarehouse, 1) Define a job scheduling strategy that will meet business ...

1) Define a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pict

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd