User Account

All Pages

Provide 4-step dimensional model in the sql

Assignment Help Database Management System
Reference no: EM132665527

PART I:

Use the OLTP logical schema below to build data warehouse tables in SQL:

Question 1: CEO of Entertainment Inc. wants to keep track of performance of engagement that the company has engaged. For each customer and agent, he wants to know total number of contracts by month, and monthly contract success ratio (calculated by total number of contracts expired each month divided by total amount of contract price each month). In addition, for each customer and agent he wants to know total numbers of contracts expired each week, and total number of contracts still active each week. Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose.

Question 2: CEO of Entertainment Inc. wants to keep track of the life cycle of engagement contract. Particularly, he wants to know the total number of agents involved for each engagement contract phrase when the contract is authorized, engaged, monitored and closeout. Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose.

PART II:

Import the midterm data code for the OLTP logical schema in Part I and develop ETL process to create data warehouse tables in SSIS.

Your dimensional and fact tables should meet these requirements:

- DimDate has DateKey values ranging from 20120901 to 20130312 (YYYYMMDD integer data type). DimDate should contain IsMartinLutherKingHoliday attribute in boolean data type, LastDayOfMonth attribute in boolean data type, and other required attributes (10 points). If you use Flat File or Excel Data Source to import datekey data, include the date source file in the submission also.

- DimEntertainer should contain entertainer's full address, entertainer's last 4 digit of SSN, entertainer's current year of active until today, and other required attributes. For example, for Entertainer ID 1 you should return "4110 Old Redmond Rd., Redmond, WA 98052" as full address, and 22 as year of active .

- DimEntertainer should be able to handle slowly changing dimension issues.

- DimAgent should contain agent' full name and agent key. For example, "William Thompson" is a full name for AgentKey 1. This table doesn't need to handle slowly changing dimension issues.

- FactlessAgent should contain 3 keys (DateKey, AgentKey, and EngagementNumber).

- FactEngagement should contain 3 keys (DateKey, EntertainerKey, and CustomerID) and 4 fact measures. The following table shows the descriptions of each fact measure:

Attachment:- DBMS Assignment.rar

Reference no: EM132665527

Questions Cloud

Devise plan for accommodating conflicting responses : Devise a plan for accommodating conflicting responses for the same procedure from two different people you interviewed.
What are some of the principles john gottman has found : What are some of the principles John Gottman has found that determine if a marriage will work? Which resonate with you most? Why?
What are Aleha income tax concerns for the current year : The rings cost $11,800 and were sold for their approximate value of $9,000. What are Aleha income tax concerns for the current year
Explain the different states of consciousness : In a multi-paragraph essay, discuss the different states of consciousness that you have experienced in the past 24 hours, including any periods when you were.
Provide 4-step dimensional model in the sql : Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose
Explain the meaning of diversification : What is the opportunity cost of capital? Explain the meaning of diversification. How does diversification reduce risk for the investor?
How helpful would the treatment planning process : How helpful would the treatment planning process outlined in this topic have been in resolving problem areas in your life, when you were an adolescent?
Qualitative and quantitative assessments : Compare/contrast qualitative and quantitative assessments. Determine which approach is best to assess IT risk.
What can we expect to learn in the future : Psychology is a contemporary science. Listed below are several recent papers. For the first part of this assignment, choose two papers to briefly summarize.

Reviews

Write a Review

Database Management System Questions & Answers

  Develop an entity relationship diagram (erd) for project

The company that we are working on distributes many products, like secure bolts and screws, to many different construction companies. In order for the company to be successful it is important for them to keep a reliable and accurate inventory syst..

  Create example model of relationship using mysq workbench

Come up with an original example of a one to many unary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

  What types of information would users of the proposed system

What types of information or data would the users of the proposed system like to have compiled. What would this data provide evidence of or answer? Provide specific examples.

  United broke artists (uba) is a broker

United Broke Artists (UBA) is a broker for not-so-famous artists. UBA maintains a small database to track painters, paintings, and galleries. A painting is created by a particular artist and then exhibited in a particular gallery

  Create a simple query for each table that returns

Create a simple query for each table that returns all of the columns and all of the rows for each table. Write a query that displays each part that has been purchased by Huffman Trucking Company

  Create a database whose tables are at least in three-nf

The dependency diagram in Figure indicates that authors are paid royalties for each book they write for a publisher. The amount of the royalty can vary.

  Convert the first normal form to second normal form

Convert each of the previously identified entities to third normal form. Make certain that the necessary foreign keys have been added to the final tables to support the relationship shown on your initial ER model.

  What do you mean by data base scheme

Database Questions:  What do you mean by data base scheme?  What do you mean by cardinality ratio?   What do you mean by degree of relation?

  Draw the e/r diagrams for the business rules

Draw the E/R diagrams for the business rules

  Describe five olap uses of the data warehouse

Happy Cruise Lines has several ships and a variety of cruise itineraries, each involving several ports-of-call. The company wants to maintain information on the sailors who currently work on each of its ships. It also wants to keep track of both..

  Implement the ecm and data governance policies

Provide a brief description of the organization (can be hypothetical) that will be used as the basis for the projects in the course. Include company size

  Develop an applicant database

A small recruitment firm that specializes in recruiting Information Technology professionals for small to medium-sized organizations has asked you to develop.

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