User Account

All Pages

Determine the relevant attributes and suitable granularity

Assignment Help Database Management System
Reference no: EM132335614

Data Mart Design

The learning outcomes that are assessed by this coursework are:

1. A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW.

2. A systematic knowledge of how to apply ERD and Star Schema to design databases, DW or data mart.

3. The ability to design appropriate data extraction, transformation and loading strategy and create reasonable queries.

Midlands Theatre (MT) Company

Scenario

Company Overview:

Midlands Theatre (MT) is a chain of small theatres that are found within the suburbs of several cities and towns within the Midlands, including Leicester and Birmingham. It specialises in high quality theatre productions that may be seen to be insufficiently "mainstream" for the general population. The company purchased its first theatre in January 1982, and since then has seen substantial increases in the number of clients that want to visit a local theatre to see a production that is more alternative to those typically found within the city centre theatres and/or large entertainment complexes. In response to the growing client base, MT sometimes offers a matinee (i.e., afternoon) performance of a popular production in addition to the traditional evening performance time.

Your Tasks

You are to develop a prototype of ORACLE data mart for ticket sales as a part of a potential data warehouse for MT. For this assignment you are required to work individually.

You have been given the MT company scenario, together with an Extended Entity Relationship (EER) Diagram and corresponding tables for the existing relational database.

Task 1: Analyse the given database design and the requirement for data mart, identify the dimensions and fact for your data mart

Task 2: Design the star schema for the data mart and identify the corresponding PKs and FKs

Task 3: According to given data and requirements, determine the relevant attributes and suitable granularity in your data mart

Task 4: Map your star schema to logical relations

Task 5: Create the corresponding tables in Oracle using SQL

Task 6: Identify your source data from the OLTP data base and design your data extraction rules. You need to give a detailed mapping and transformation list from the source to the destination.

Task 7: Implement your data extraction, transformation and loading through Oracle SQL. The number of rows extracted into each dimension or fact table in your data mart should be printed from Oracle query.

Task 8: Comment on how your data mart satisfies the requirements of MT. Implement the required data analysis requirements for both data mart and the original OLTP database. Compare your queries and comments on the advantages of data mart in analysis operations. Test results from Oracle query should be included.

Deliverables:

The deliverable is a report that summarises your work and justifies your design decisions, it includes the following sections

1. Dimension selection and fact identification
2. Star Schema
3. Logical relations and granularity
4. SQL for table creation and constraints
5. Data Sources Mapping (using diagrams)
6. SQL for ETL
7. SQL for required queries
8. Comparison between Data Mart and relational models

Each part should be associated with a concise explanation, and the execution results from your SQL code should be provided as well. Everything should be put into one MSWord document in your submission.

Attachment:- Data Warehouse Design and OLAP.rar

Verified Expert

In this assignment, we have done assignment of database.This assignment has done in Oracle.I have used online tool for complete this assignment.Here I have design the er diagram and write SQL query.I have also write sequence and also write query lap and tell.

Reference no: EM132335614

Questions Cloud

Policy and procedure for responsibilities of organization : Policy and procedure for responsibilities of the organization. Policy and procedure for addressing issues and concerns in the workplace
What are some of the airport safety measures : What are some of the airport safety measures that we, as the public, are not aware of or are somewhat invisible; but are always present and protecting us around
Describe traditional methods of investment appraisal : HIGHER NATIONAL DIPLOMA IN BANKING AND FINANCE-HBF 417-MANAGERIAL ECONOMICS -Evaluate the internal and external influence of objective.
Submit bibliography of the sources you have found : Submit bibliography of sources you have found, to date, for your project paper. you are making progress toward completion of your project in a timely manner
Determine the relevant attributes and suitable granularity : IIMAT5167 - Data Mart Design - Data Warehouse Design and OLAP - Faculty of Computing, Engineering & Media - Analyse the given database design
Give your analysis of which strategy starbuck uses : Give your analysis of which strategy Starbuck's uses. Is your strategy similar or different?
Primary drivers of competitive landscape is more influential : From the first e-Activity, determine which of the two primary drivers of the competitive landscape is more influential. Explain your rationale.
Course learning outcomes listed in your syllabus : Each student is to research a current event that is related to one or more of the Course Learning Outcomes (CLO) listed in your syllabus
Why could this be a huge problem for all involved : An effective leader is only as good as the people who s/he works with. Perhaps this is a mistake that many managers and leaders make....they surround themselves

Reviews

len2335614

7/9/2019 2:52:02 AM

I have attached two files one is the assignment and one is the assignment that one of the students did i want the assignment to be like that but it has to have nothing copied from the solved assignment. Let me know if its doable so that i can give you the oracle link and login so that the sql work can be done there.

len2335614

7/9/2019 1:57:25 AM

All tables created correctly. Appropriate implementation of integrity rules Same as excellent. Optimised table creation with minimum code and full set of constraints All data sources are correctly identified, efficient transformation rules are set and all data are loaded correctly Same as excellent. Minimum data sources and optimised ETL code produced, any potential problems discussed Excellent justification for all requirements with excellent comparison between most OLTP and DM queries Same as excellent. More than one solutions are presented and difference discussed.

len2335614

7/9/2019 1:57:19 AM

Excellent 80-89% Outstanding 90-100% Same as previous, and correct multiplicity, no unjustified redundancy As excellent. Minimum set of dimensions and facts in fact table. Perfect granularity Excellent choice of tables, and attributes, primary & foreign keys Same as excellent. Clear explanation on the map from star schema to logical relations.

Write a Review

Database Management System Questions & Answers

  Create a form using form tool based on the presenters table

Create a form using the Form tool based on the Presenters table. Change the title in the Form Header to Enter/Edit Presenters.

  Relationship between customer and products

relationship between customer and products

  Create database objects to track the construction projects

You will use a database template to enter data into the Events table. You will create a simple query, a form, and a report.

  Display the invoice number and the invoice date

Display the invoice number, the invoice date, the customer id, and the customer name for each order in the database

  Construct a list of potential entities.

create an IDEF1X diagram that shows only entities and relationships. Name each relationship and specify its cardinalities. Justify the cardinality decisions.

  Explain it project to coordinate and maintain records

The system should support notifications to management personnel whenever their direct reports have submitted documentation. The system should also notify employees if their deadline to complete professional-development requirements is approaching ..

  Oracle sql complete the following steps to create a

complete the following steps to create a procedure to calculate the tax on an order. the bbtax table contains the

  The chief financial officer

The chief financial officer (CFO) of NCWR keeps a huge number of reports, spreadsheets, and other critical financial information on his computer, which runs Windows 7

  Create a query to display the workshop enrolment lists

Create a MySQL database using phpMyAdmin. Create a query to display the workshop enrolment lists for all students enrolled in Session 1, 2015.

  Explain the factors necessary to ensure referential integrit

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finan..

  Compare the two non-sequential file structure models

What advantages does the first one have over the second and what advantages does the second have over the first?

  Discuss business analytics and data mining tools

Explain Business Intelligence in three to four paragraphs. Focus upon what it is, its architecture and purpose - Discuss business analytics and data mining tools including the purpose of each and what an organization is attempting to accomplish with..

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!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

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