Expert Answers

CIS 355 Final Exam | Attempt 1 | Scored 120 out of 120
Submitted May 6, 2022
This attempt took 69 minutes.

 
Question 1
3 / 3 pts
When a fact can be summed in certain situations but not in others, it is referred to as:

• Non-additive
• Type 3
• Semi-additive
• None of the above

 
 Question 2
3 / 3 pts
True or False: Once an organization builds data warehouses and data marts, they can no longer receive business reports from their operational source systems.

• True
• False

 
Question 3
3 / 3 pts
True or False: With a "rolling append" ETL pattern, you will start with some fixed period of history - for example, 3 years of sales data - and then expand the time frame of your historical data from that point.

• True
• False

Question 4
3 / 3 pts
True or false: you will find data warehousing used in governmental settings as well as in business.

• True
• False

Question 5
3 / 3 pts
True or False: In a star schema, you can always determine the order of a hierarchy (e.g., a PRODUCT hierarchy that also includes BRAND and CATEGORY) by looking at the order of how the database columns are listed. For example, if the PRODUCT columns are listed first, then the BRAND columns, and then the CATEGORY columns last, that means that products are part of brands, which are then part of categories.

• True
• False

Question 6
3 / 3 pts
Which slowly changing dimension (SCD) model does not retain history?

• Type 1
• Type 2
• Type 3
• Neither Type 1 nor Type 2 retains history
• Neither Type 1 nor Type 3 retains history
• None of the above is correct

  
Question 7
3 / 3 pts
True or False: Type 3 slowly changing dimensions (SCDs) are used less frequently and commonly than Type 1 and Type 2 SCDs in real-world data warehousing.

• True
• False

Question 8
3 / 3 pts
True or False: A data element that is numeric is always a fact; it can never be a dimension.

• True
• False

Question 9
3 / 3 pts
Which of the following is not one of the original "Bill Inmon 4 rules for data warehousing":

• Subject-oriented
• Volatile
• Time-variant
• All of the above are "Inmon rules"

 
Question 10
3 / 3 pts
True or False: For the same set of data that we want to dimensionalize, a snowflake schema has more foreign keys in a fact table than in a star schema.

• True
• False

Question 11
3 / 3 pts
Which of the following statements is/are correct?

• A. In SQL, a primary key/surrogate key must have an INT data type
• B. In SQL, if your dimension table is including natural keys, a natural key must have an INT data type
• C. In SQL, a fact must have an INT data type
• A and B are correct
• A and C are correct
• B and C are correct
• A, B, and C are correct 

 
Question 12
3 / 3 pts
An accumulating snapshot fact table is used to track and analyze the progress of a business process through formally defined stages.

• True
• False

Question 13
3 / 3 pts
True or False: In a snowflake schema, you can have at most one "flat" dimension that isn't part of a hierarchy.

• True
• False

Question 14
3 / 3 pts
The most "architecturally flexible" slowly changing dimension (SCD) model for maintaining history of changes in a data warehouse is:

• Type 1
• Type 2
• Type 3
•  All of the above maintain history and do so with equivalent "architectural flexibility"

 
Question 15
3 / 3 pts
"Branching" in a snowflake schema can only occur at the lowest-level dimension table in a hierarchy.

• True
• False

Question 16
3 / 3 pts
True or False: If a source system contains a natural key for data that you will be putting into a fact table - for example, SHIPMENT_ID for data about a shipment of products, how much revenue will come in from the shipment, etc. - you should use that natural key such as SHIPMENT_ID for the primary key of the fact table, rather than a combination key made up of all the surrogate keys/foreign keys. So your SQL would look like:
CREATE TABLE SHIPMENT_FACT (
SHIPMENT_ID CHAR(15) NOT NULL,
PRODUCT_KEY INT NOT NULL,
CUSTOMER_KEY INT NOT NULL,
{{{... rest of your columns...}}}
PRIMARY KEY (SHIPMENT_ID),
FOREIGN KEY PRODUCT_KEY REFERENCES PRODUCT_DIM (PRODUCT_KEY),
...

• True
• False

Question 17
3 / 3 pts
True or False: In a snowflake schema, you will typically have less duplicated dimension data overall (especially for the higher levels of your hierarchies) than in a star schema

• True
• False

Question 18
3 / 3 pts
Which of the following scenarios is/are most applicable to using a Type 3 slowly changing dimension?

• A. Employees receiving salary increases
• B. Sales territory reorganizations
• C. Employee home addresses changing
• Both A and B
• Both A and C
• Both B and C
• A, B, and C are all applicable for Type 3 SCDs
• Neither A, B, nor C is applicable for Type 3 SCDs

Question 19
3 / 3 pts
True or False: A factless fact table is the most appropriate dimensional modeling structure for recording online users viewing web pages along with the amount of time a user spent on each web page during each view.

• True
• False

Question 20
3 / 3 pts
A "cube" is typically used for VERY large volumes of data in a data warehouse.

• True
• False

Question 21
3 / 3 pts
Which of the following scenarios is/are most applicable to using a Type 1 slowly changing dimension?

• A. Employees receiving salary increases
• B. Sales territory reorganizations
• C. Employee home addresses changing
• Both A and B
• Both A and C
• Both B and C
• A, B, and C are all applicable for Type 1 SCDs
• Neither A, B, nor C is applicable for Type 1 changes

  
Question 22
3 / 3 pts
Which of the following statements is/are true about a "non-terminal dimension table" in a snowflake schema?

• A. You have a compound primary key made up of more than one surrogate keys
• B. You have only one foreign key constraint
• C. You have one or more foreign key constraints
• D. You have no foreign key constraints
• A and B are correct
• A and C are correct
• A and D are correct
• Neither A, B, C, nor D is correct

  
Question 23
3 / 3 pts
True or False: If you decide to use a "tracking fact" in a factless fact table, that tracking fact must always have a value of zero.

• True
• False

Question 24
3 / 3 pts
Which of the following words are signals to look for a dimension?

• "COUNT" and "SUM"
• "SELECT" and "BY"
• "FOR" and "BY"
• None of the above

  
Question 25
3 / 3 pts
True or False: A "factless fact table" is another name for a dimension table.

• True
• False

Question 26
3 / 3 pts
For your ETL design, which type of dimensional data do you process first?

• Dimensional data
• Fact table data
• Either dimensional data or fact table data, based on whichever data volume is lesser
• Either dimensional data or fact data, depending on which data is more important

  
Question 27
3 / 3 pts
With a persistent staging layer:

• Once data has been transformed and loaded into the performance layer, that data is no longer needed and is deleted
• After data has been transformed and loaded into the performance layer, the data is still retained in the staging layer
• The data in the staging layer is stored in the form of dimension tables and fact tables
• None of the above is true

 
Question 28
3 / 3 pts
A capability introduced into Relational Database Management Systems (RDBMSs) to improve performance for data warehousing was:

• A. Star joins
• B. Snowflake joins
• C. Bitmapped indices
• A and B above
• A and C above
• B and C above
• A, B, and C above
• None of the above

  
Question 29
3 / 3 pts
Which of the following statements is/are true about a "coverage/eligibility/state of being" faceless fact table?

• A. You typically need 2 relationships back to a date/time dimension
• B. You will always have one and only one fact/measurement in the table
• C. You do not need to have FOREIGN KEY/REFERENCES constraint clauses, unlike other fact tables
• All of the above - A, B, and C - are correct

  
Question 30
3 / 3 pts
With a non-persistent staging layer:

• Once data has been transformed and loaded into the performance layer, that data is no longer needed and is deleted
• After data has been transformed and loaded into the performance layer, the data is still retained in the staging layer
• The data in the staging layer is stored in the form of dimension tables and fact tables
• None of the above is true

  
Question 31
3 / 3 pts
True or False: For the same set of data that we want to dimensionalize, a snowflake schema typically has the same number of dimension tables as a star schema.

• True
• False

Question 32
3 / 3 pts
Which of the following scenarios is/are most applicable to using a Type 2 slowly changing dimension?

• A. Employees receiving salary increases
• B. Sales territory reorganizations
• C. Employee home addresses changing
• Both A and B
• Both A and C
• Both B and C
• A, B, and C are all applicable for Type 2 SCDs
• Neither A, B, nor C is applicable for Type 2 SCDs

 
Question 33
3 / 3 pts
True or False: In a CREATE TABLE statement for a fact table, presuming that you have a combination primary key, you will have one FOREIGN KEY constraint clause for each column/field used as part of your combination primary key.

• True
• False

 
Question 34
3 / 3 pts
Which of the following statements is not true about a star schema?

• We can work with dimension tables without needing to use fact tables
• We can work with fact tables without needing to use dimension tables
• In a star schema, you only have one “level” of dimension tables connected to a fact table
• All 3 of the above statements are actually true

  
Question 35
3 / 3 pts
In a snowflake schema, each "non-terminal" dimension table will contain:

• At least one FOREIGN KEY clause, but possibly more than one of the hierarchy branches
• A composite/combination PRIMARY KEY
• Always at least two FOREIGN KEY clauses

 
Question 36
3 / 3 pts
True or False: Best practices call for storing natural keys in fact tables, but not in dimension tables.
   

• True
• False

Question 37
3 / 3 pts
True or False: In a star schema, if you have a many-to-many relationship between dimensional data - for example, a doctor can perform multiple surgeries, and any given surgery can be performed by multiple doctors - then your model needs to use foreign keys in your dimension tables show that many-to-many relationship between the two dimension tables (e.g., DOCTOR_DIM and SURGERIES_DIM) that you create.

• True
• False

Question 38
3 / 3 pts
When a fact cannot be meaningfully summed in any situation, it is referred to as:

• Non-additive
• Hybrid
• Semi-additive
• None of the above

 
Question 39
3 / 3 pts
True or false: when doing ETL design, you will always make a Type 1 change to exactly one row in a dimension table.

• True
• False

Question 40
3 / 3 pts
True or False: when phrasing a dimensional question, every applicable dimension must be "signaled" by either the word "BY" or the word "FOR."

• True
• False

CIS 355 Midterm Exam 

Points 100 |  Questions 25 

 Attempt 1
Score for this quiz: 100 out of 100

Time Limit 75 Minutes

 
Question 1
4 / 4 pts
Which of the following is not a common incremental ETL pattern?

• In-palce updates
• Virtual append
• Complete replacement
• All of the above are common incremental ETL patterns

  
Question 2
4 / 4 pts
True or False: “Data Virtualization” is a related but different approach to building a “Data Warehouse” – the two terms do not mean exactly the same.

• True
• False

  
Question 3
4 / 4 pts
Which of the following is not one of the four original “Bill Inmon Rules” for data warehousing:

• Integrated
• Time-variant
• Dimensional data
• All of the answer selections are among the four original “Bill Inmon Rules”

 
 
Question 4
4 / 4 pts
True or False: all enterprise data warehouses are built using relational databases, while all data marts are built using cubes.

• True
• False

  
Question 5
4 / 4 pts
In early-generation data warehouses, architects and planners would typically need to make design compromises because of technology limitations. Which of the following was a common design compromise found in early data warehouses:

• Building centralized rather than component-based data warehouses
• Aggregation rather than individual transaction data
• Incorporating front-end data marts into the overall architecture
• All of the answer selections were early-generation DW design compromises

  
Question 6
4 / 4 pts
Today’s enterprise-scale data warehouses commonly receive data from _____ (number) of source applications:

• No more than ten
• Thousands
• Hundreds of thousands
• None of the answer selections are correct

  
Question 7
4 / 4 pts
True or False: Today’s data warehouses can easily support many terabytes of data.

• True
• False

 
Question 8
4 / 4 pts
Which of the following is one of the common “flavors” or types of business intelligence (BI)?

• Tell me what happened, and why
• Tell me what is likely to happen (predictive analytics)
• Tell me something interesting and important
• All of the answer selections are common “flavors” or types of BI

 
 
Question 9
4 / 4 pts
The unlabeled “disk symbol” on the right side of the diagram could represent which one or more of the following:

• A. An independent data mart
• B. A dependent data mart
• C. A centralized data warehouse
• Answers (A) and (C) both are correct
• Answers (B) and (C) both are correct
• Answers (A), (B), and (C) are all correct

 
 
Question 10
4 / 4 pts
True or False: A multidimensional database “cube” is commonly used rather than a relational database when an organization has exceptionally large volumes of data to include in the data warehouse.

• True
• False

Question 11
4 / 4 pts
In the data warehousing “wholesaler-retailer” paradigm, which of the following components can be thought of as a “supplier” of data?

• Source application
• Data warehouse
• Data mart
• Business Intelligence (BI) users

 
Question 12
4 / 4 pts
True or False: An independent data mart is architecturally identical to a data warehouse.

• True
• False

Question 13
0 / 4 pts
In an environment that makes use of front-end data marts, the enterprise data warehouse (EDW) component in such an environment provides which type of business intelligence/reporting/analytics?

• Organization-specific operational reporting
• Enterprise-wide strategic reporting and BI
• Organization-specific predictive analytics
• None of the answer selections are correct

 
 
Question 14
4 / 4 pts
True or False: When you have an enterprise data warehouse that feeds data to dependent data marts, every dependent data mart must have its data organized dimensionally to support “classic” business intelligence and online analytical processing (OLAP). You would never feed data from an EDW into a data mart that is structured to support data mining.

• True
• False

 
Question 15
4 / 4 pts
We’ve discussed how one particular relational database capability created difficulties for early-generation relational databases, and how significant work needed to occur in RDBMS products to give RDBMSs satisfactory performance for data warehousing. What relational database capability is this?

• CREATE TABLE
• WHERE clauses
• JOIN operations
• None of the answer selections

 
 
Question 16
4 / 4 pts
True or False: We typically do not delete all information about a customer in a data warehouse or data mart even if that customer has been deleted from a source application.

• True
• False

 
Question 17
4 / 4 pts
Two common ETL transformation models are:

• Data hubs and source merging
• Non-persistent staging layers and de-duplication
• Address matching/standardization and data virtualization
• Dropping columns and value based row/record filtering

 
 
Question 18
4 / 4 pts
When we refer to a relational database management system (RDBMS) as sort of a “Swiss Army Knife” for data management, we mean:

• An RDBMS can only support analytical processing
• An RDBMS can only support transactional processing
• An RDBMS can be built using either physical pointers among database tables or logical relationships among database tables
• None of the answer selections are correct

  
Question 19
4 / 4 pts
True or False: “ETL” is an important aspect of data warehousing, and is an acronym for several combinations of words; one of those combinations is “Extraction, Transaction, and Loading.”

• True
• False

Question 20
4 / 4 pts
True or False: “Front end data marts” and “Dependent data marts” are the same thing.

• True
• False

Question 21
4 / 4 pts
True or False: a “Foreign Key” in a relational database table is the formal name for the unique identifier of any given row in that table.

• True
• False

 
Question 22
4 / 4 pts
True or False: a “star join” is a technical means for a relational database management system to efficiently join data from potentially a large number of database tables , typically when a database is being used for data warehousing purposes.

• True
• False

Question 23
4 / 4 pts
True or False: In a Corporate Information Factory (CIF) approach to component-oriented data warehousing, users access data from either the EDW component or the data marts, whichever suits their needs best.

• True
• False

 
 
Question 24
4 / 4 pts
You will find which of the following in the Federated EDW architectural approach:

• A. An architected, component-oriented data warehousing environment
• B. Independent data marts
• C. An integrated, centralized EDW
• A and B above
• A and C above
• B and C above
• A, B, and C above
• None of the answer selections are correct

 
Question 25
4 / 4 pts
True or False: a “cube” used for data warehousing could have more than 3 dimensions, despite the usage of the “cube” term.

• True
• False

Using the same subject area as you did for Assignment #3 (unless directed by your instructor to choose another subject area), you will create a star schema SQL Server model, with tables pasted as in Assignment 3, containing both types of factless fact tables, as discussed in the course lecture:

Case 1: business activity ("something happened”), but nothing to measure

Case 2: “conditions, coverage, or eligibility” – a “state of being” – but nothing to measure

If you had any points deducted for your dimension tables in Assignment #3, you will need to "fix" any issues for Assignment #4.

You will remove your transaction-grained fact tables from your model and replace them with factless fact tables, as described above.

You also may need to add a DATE/TIME-related dimension table if you didn't have one in Assignment #3; if you do, you can EITHER delete one of your Assignment 3 dimension tables and replace with DATE/TIME, or just add the new DATE/TIME one to the existing 3; your choice based on what makes sense for your model.

You will upload your PPTX (or PDF) deliverable that contains your model to this link before the due date/time.

GRADING RUBRIC:

25 points total: 9 points for dimension tables, 8 points for each of your faceless fact tables

• Dimension tables: 3 points for each one (including DATE/TIME dimension; if you have 4 total, all 4 will factor into this portion of the grading), "all or nothing" correctness: 
  • surrogate key as primary key with correct data type and NOT NULL clause
  • correct PRIMARY KEY clause
  • no FOREIGN KEY clause or other syntax violations
• Fact tables: 8 points for each, based on:
  • SQL syntax: 4 points, "all or nothing" including PRIMARY and FOREIGN KEY clauses/constraints; correct primary key designation; no facts/measurements, only keys; other syntax
  • Correct fact table usage: 4 points
    • For both fact tables: your dimensions are correctly and fully related to the business subject area that you selected - (example: if you were to do "SEASON TICKETS" then you must have one or more appropriate dimensions related to SEASON TICKETS  - in other words, you need to have more than just syntactically correct PK and FK columns, your model needs to "make sense")
    • "Business event with nothing to measure" must be correct and related to health care/hospital
    • "Coverage/eligiblity/'state of being'" must be correct and related to ASU ATHLETICS - make sure that your relation(s) to your date/time dimensions are syntactically and semantically correct

Knowledge Check 1

Managerial Economics

(JWI515006JWONL-1224-001)

Attempt Score    4 out of 4 points  

QUESTION 1
GDP is expressed in inflation adjusted dollars.

• True
• False

0.4 points  

 
QUESTION 2
Which of the following statements in NOT true?
    A.    Raising the Interest rate decreases the rate of Inflation.
    B.    Lagging Indicators can predict the economy's future direction.
    C.    Perfect Competition involves many sellers offering similar goods.
    D.    Cost-push is one of the causes of Inflation.
0.4 points   

QUESTION 3
GDP calculation does not reflect depreciation of machinery and other capital assets. Therefore, it is considered to be a ______ measure.
    A.    Net
    B.    Nominal
    C.    Gross
    D.    Annual
    E.    Marginal
0.4 points   

QUESTION 4
The Federal Reserve regulates banks and enacts U.S. monetary policy.

• True
• False

0.4 points   

QUESTION 5
Decreasing the Interest Rate ...
    A.    Slows down the Rate of Inflation.
    B.    Makes it harder for consumers to borrow money.
    C.    Increases the flow of cash into the marketplace.
    D.    Makes it harder for businesses to borrow money.
0.4 points  

 
QUESTION 6
Which of the following is NOT a significant Indicator in Macroeconomics?
    A.    The Unemployment Rate.
    B.    The Gross Domestic Product.
    C.    Consumption of Goods and Services.
    D.    The Rate of Inflation.
0.4 points   

QUESTION 7
The Consumer Price Index (CPI) is reported once every quarter.

• True
• False

0.4 points   

QUESTION 8
A basket of goods bought by a typical consumer is used to ...
    A.    Calculate the Interest Rate.
    B.    Measure the Gross Domestic Product.
    C.    Calculate the Consumer Price Index.
    D.    Illustrate the law of Supply and Demand.
0.4 points   

QUESTION 9
Prior to policy changes due to COVID-19, to be counted in the Unemployment Rate, a person must ... 
    A.    Have given up trying to find employment.
    B.    Be less than the retirement age of 65 years old.
    C.    Have lost a job through involuntary layoffs.
    D.    Have been actively seeking a job for the past 4 weeks.
0.4 points   

QUESTION 10
The Federal Reserve (FED) sets the Interest Rate for the U.S.A. In this function, the Fed operates as a ...
    A.    Monopoly.
    B.    Coincident Indicator.
    C.    Regulating entity.
    D.    Market Structure.
 

Task: Create Pricing Formula for renting Airbnb in Chicago.

create a new column in the data where you do a formula that would calculate all the fees together and create a "cost per day" or something that could be used to determine the spectrum of cost for each rental from least to most expensive.

Hypothesis:  All areas have the same average cost?

USE DATA: Fees: (cleaning, minimum nights required, security deposit, cancellation fee)

• Use JMP Pro Excel, and Tableau
• Must perform some data cleaning (outlier analysis)
• Test correlations between data variables
• Perform appropriate statistical test (t-test, ANOVA, Regression, Cluster analysis)

Questions

Q1: Is the hypothesis true explain why in a few sentences.

Q2: Use tableau and show the areas with rentals from least to most expensive. Explain in your own words why they are expensive and why they are cheap.

Q3: If hypothesis is not true, explain why its not true in a few sentences.

202202 BUS278 21583 - Employment Law

Chapter 12 Practice Test

Attempt 20 out of 20 points  

QUESTION 1

Can a state (such as Iowa or Michigan) pass legislation that contradicts ERISA law?          2 points  

• Yes. Insurance and pensions are largely regulated by state laws.  ERISA is a federal law that supplements - not replaces - state laws.  Regardless if a state law is deemed to be "reasonable" and "fair" then a state may pass legislation that contradicts ERISA regulations.        
• No. ERISA regulations may not be contradicted by state laws. ERISA preempts or supersedes any state law that attempts to contradict those plans covered by ERISA.

QUESTION 2

Which of the following is a qualifying event necessitating an offer of COBRA continuation coverage?     2 points          

• an employee is reprimanded        
• an employee leaves on vacation        
• an employee adopts a child        
• an employee quits his job

QUESTION 3

About ERISA, the Employee Retirement Income Security Act, it is correct to say that:        2 points        

• the law governs only pension plans        
• the law only applies to "welfare" plans         
• the law governs benefit plans broadly, not just pension plans 

QUESTION 4

An employee whose wife suffered from breast cancer was terminated after a change of ownership of the company. He asked whether their health insurance would continue, and was told verbally that it would. Nine months later when his wife sought treatment, she was advised the policy had been terminated. He and his wife sued for a violation of COBRA. The court should rule: ?        2 points          

• ?for the employer, since the employee never requested in writing that their insurance be continued        
• ?for the employee, since he was not given notice of his COBRA rights in writing        
• ?for the employee’s wife, because she was also an insured, but was given no notice of her COBRA rights        
• ?for the employer, since it was a new owner, and not the employer of the employee.

QUESTION 5

The Affordable Care Act ("ObamaCare") was written so that the basic strategy for expanding health care coverage was to increase enrollment in        

• Medicare.        
• Medicaid.        
• Social Security        
• All the above are correct answers.

QUESTION 6

Defined benefit pension plans:                2 points  

• promise a specific pension benefit upon retirement        
• are not insured through the Pension Benefit Guaranty Corporation (PBGC)        
• are less regulated pension plans        
• require a specific contribution each pay period

QUESTION 7

Under ObamaCare (Affordable Care Act)        

• health care plans are required to cover dependent children up to age 26 years.        
• pre-existing conditions exclusions in health plans are prohibited.        
• health care plans may not place a lifetime dollar benefit payable to a person.        
• All the other answers are correct.

QUESTION 8

In Chapter 12 of the Walsh book (at the very end of the chapter) the author discusses wellness programs. These programs are often used as a way to keep group health insurance costs lower by improving employee health.  Which of the below statements is a correct overview of the author's views about such programs?          2 points          

• He is a strong advocate of such programs and calls them a win-win situation.  He especially likes the fact that such wellness programs are pretty much free of any possible government laws or requirements as they are most always voluntary on the part of employees.  In short, the author sees no problems with such programs since better health can only lead to better results for everyone.        
• The author appears to take a cautious approach to wellness programs.  He states such programs can violate HIPAA and PPACA nondiscrimination requirements since employees may be charged more for their health insurance coverage based on their health factors.  He points out that such programs, while certainly well intended, need to be carefully implemented by employers.

QUESTION 9

An employee would likely prefer this kind of pension plan:         

• a hybrid plan         
• a defined benefit plan         
• a defined contribution plan

QUESTION 10

An employee is terminated for poor attendance. The employer sends a letter on May 1 notifying him of his right to receive continuation health insurance coverage.  The letter states that the former employee must respond by May 30 to be eligible for up to 6 months of continuation coverage.  The employer’s letter:        

• should state that the employee has 60 days to decide on coverage that would last up to 18 months        
• should state that the employee has 60 days to decide on coverage that would last up to 3 years        
• should state that the employee has 45 days to decide on coverage that would last up to 3 years        
• accurately states the former employee’s rights under COBRA

QUESTION

Which of the following is a qualifying event necessitating an offer of COBRA continuation coverage?                2 points  

• an employee is reprimanded        
• an employee leaves on vacation        
• an employee adopts a child        
• an employee quits his job

QUESTION 

The Affordable Care Act ("ObamaCare") was written so that the basic strategy for expanding health care coverage was to increase enrollment in          2 points

• Medicare.        
• Medicaid.        
• Social Security        
• All the above are correct answers.

QUESTION 

Which of the following is a qualifying event necessitating an offer of COBRA continuation coverage?        

• an employee is reprimanded        
• an employee leaves on vacation        
• an employee adopts a child        
• an employee quits his job

QUESTION 

In Chapter 12 of the Walsh book (at the very end of the chapter) the author discusses wellness programs. These programs are often used as a way to keep group health insurance costs lower by improving employee health.  Which of the below statements is a correct overview of the author's views about such programs?        

• He is a strong advocate of such programs and calls them a win-win situation.  He especially likes the fact that such wellness programs are pretty much free of any possible government laws or requirements as they are most always voluntary on the part of employees.  In short, the author sees no problems with such programs since better health can only lead to better results for everyone.        
• The author appears to take a cautious approach to wellness programs.  He states such programs can violate HIPAA and PPACA nondiscrimination requirements since employees may be charged more for their health insurance coverage based on their health factors.  He points out that such programs, while certainly well intended, need to be carefully implemented by employers.

QUESTION 

Wells Fargo lost the case of Harrison v Wells Fargo Bank (4th Cir 2014) (Pages 465-468 Walsh text). What was the main reason the employer lost this case?        

• Wells Fargo did not meet the "full and fair review" requirements imposed by ERISA.  In other words, Wells Fargo did not engage in proper review of Nancy Harrison's case or properly obtain the necessary medical records to have a full understanding of her situation / case.        
• It was proven that Wells Fargo had a systematic plan to automatically deny any person's claim for benefits until at least the second appeal level.  Once a case reached a second appeal then Wells Fargo would come up with a technical reason to deny the claim of most any claimant.  Nancy Harrison was just one of many, many employees Wells Fargo had treated this way.        
• The U.S. Department of Justice got involved in this case - which it called a scheme - and prosecuted the employer for violating federal ERISA law.  The employer was ordered to make full restitution to all the victims of this scheme (including Nancy Harrison).        
• All the other answers are correct.

QUESTION

In Chapter 12 of the Walsh book (at the very end of the chapter) the author discusses wellness programs. These programs are often used as a way to keep group health insurance costs lower by improving employee health.  Which of the below statements is a correct overview of the author's views about such programs?        

• He is a strong advocate of such programs and calls them a win-win situation.  He especially likes the fact that such wellness programs are pretty much free of any possible government laws or requirements as they are most always voluntary on the part of employees.  In short, the author sees no problems with such programs since better health can only lead to better results for everyone.        
• The author appears to take a cautious approach to wellness programs.  He states such programs can violate HIPAA and PPACA nondiscrimination requirements since employees may be charged more for their health insurance coverage based on their health factors.  He points out that such programs, while certainly well intended, need to be carefully implemented by employers.

CIS 365 – Business Database Systems
HW11.2 – Stored Procedure and Trigger (200 points)
This is an individual exercise, NOT a group assignment.

Calculate Adam McLean's final invoice bill for his family visit started on 2019-08-03. Adam McLean's guest ID is G5. The family visited FamCation once before in 2017. Make sure your calculation doesn't include their 2017 activities. Follow the steps below to complete this assignment.

CIS 365 – Business Database Systems
HW11.1 – View (50 points)
This is an individual exercise, NOT a group assignment.

1. View and subquery (30 points)
Create a view named 'Top_Guide' listing guides who have led more than 10 reservations. The view will display guide ID, last name, hired date, and total count of reservations.
Use the created view to list all guests (first name and last name) who have made reservations with these top guides via a 'noncorrelated' subquery. Don't show duplicate guest names.
Show the following three query statements –
1. View creation (10 points)
2. The result of your view, Top_Guide. (10 points)
3. The query to list all guests led by these top Guides (10 points)

2. View and subquery (20 points)

Use the same Top_Guide view to find the manager of these top guides.
Due Date: 11/12 EOD Friday
• Take a screenshot of your query statement and result in MS SQL. Paste all queries/results in your Word doc. (SnagIt available in ASU software tool library is a good screen shot tool)
• Convert the entire document into one pdf file for submission.
• Save your file name as HW11.1 - Student Name.pdf.
• Submissions must be posted to Canvas to be graded.

Quiz #1 

Points 40 

Score for this quiz: 40 out of 40

Question 1

2 / 2 pts

True or False: An “independent data mart” requires the existence of a data warehouse to provide data content.

•   TrueCorrect!
•   False

Question 2

2 / 2 pts

True or False: If you were to build a data warehouse today, you would use almost the exact same technology as if you had built a first-generation data warehouse back in the early 1990s.

•   TrueCorrect!
•   False

Question 3

2 / 2 pts

The “E” in “EDW” stands for:

•   Error-free orrect!
•   Enterprise
•   Extended
•   None of the answer selections

Question 4

2/ 2 pts

The primary difference between a CIF architecture and a Data Warehouse Dimensional Bus architecture is:

•   The CIF has no data marts, only an EDW, while the DW Bus architecture has data martsYou Answered
•   The DW Bus architecture has an “EDW” component that contains integrated data from all sources, while the CIF has only data marts
•   The CIF architecture has an “EDW” component that contains integrated data from all sources, while the DW bus has only data marts
•   The CIF and the DW Bus architectures are identical; there are no differences between the two

Question 5

2 / 2 pts

The “front-end data mart” architecture:u Answered

•   Sends data from source applications and systems into an enterprise data warehouse, and then a subset of that enterprise data is extracted and sent to one or more data marts 
•   Sends data from source applications into data marts, and then all data from all data marts is further sent downstream to an enterprise data warehouse
•   Sends data from source applications directly into data marts, which are then “declared” to be an enterprise data warehouse even though the data marts are totally independent from one another
•   None of the answer selections are correct

Question 6

2 / 2 pts

A persistent staging layer:

•   Purges all of the staging layer data after the data has been successfully loaded into the data warehouse’s “performance layer”orrect!
•   Retains data in the staging layer, even after the data has been successfully loaded into the data warehouse’s “performance layer”
•   Is only used in data marts – never in an enterprise data warehouse
•   None of the answer selections are correct

Question 7

2 / 2 pts

True or False: “Initial ETL” is the term used when you are first loading a brand new empty data warehouse with dataect!

•   True 
•   False

Question 8

2 / 2 pts

Which ETL pattern adds recently created data but also removes or deletes the oldest data from the data warehouse?

•   Complete replacement
•   AppendCorrect!
•   Rolling append
•   In-place update

Question 9

2 / 2 pts

True or False: You are required to use the same ETL pattern (e.g., in-place update, append, etc.) on all database tables within your data warehouse; you are not permitted to “mix and match” on the same incremental ETL job.orrect!

•   False 
•   True

Question 10

2 / 2 pts

Suppose that you are bringing in customer data from one application that has the customer’s last name stored as “CHAR (30)” and the customer’s first name stored as CHAR (20)” – and then you are also bringing in customer data from another application that has the customer’s last name stored as “CHAR (35)” and the customer’s first name stored as CHAR (25).”

Which of the following statements is most true?

•   You will store the customer data in the performance layer of the data warehouse with the same data types and sizes as in the respective sources, which means that some customer data will have the last name stored as CHAR (30) and other customer data will have the last name stored as CHAR (35). You Answered
•   You will use the “data value substitution” transformation pattern to store all customer with the same data types/sizes for the last name and first name in each database row 
•   You aren’t permitted to bring customer data with different types and sizes into the data warehouse; you instead would contact the application maintenance team for one of the two systems and have them make changes to how they store their customer data
•   None of the answer selections are true

Question 11

2 / 2 pts

If you have two identical applications running in different parts of your company – for example, one payroll system for your western region, and the same payroll system software for your eastern region – which of the following ETL transformation models would you most likely use to bring the payroll data together in the data warehouse?

•   Dropping columns
•   Value-based row filteringCorrect!
•   Source data merging 
•   None of the answer selections are true

Question 12

2 / 2 pts

True or False: Address matching and standardization is a valuable ETL transformation model to ensure that you don’t have unwanted duplicate customer data in your data warehouse.

•   TrueCorrect!
•   False

Question 13

2 / 2 pts

A federated data warehouse architecture:You Answered

•   Is highly centralized and monolithic in nature 
•   Is made up of a collection of dependent data marts
•   Is made up of a collection of independent data marts
•   None of the answer selections are true

Question 14

2 / 2 pts

True or False: “Data warehouse” and “data virtualization” mean the same thing, and can be used interchangeably.

•   TrueCorrect!
•   False

Question 15

2 / 2 pts

True or False: You will never find a relational database management system (e.g., Microsoft SQL Server) being used for a data warehouse’s platform.

•   TrueCorrect!
•   False

Question 16

2 / 2 pts

True or False: An advantage of a non-persistent staging layer is that if a data warehouse performance layer “blows up” you can try to reconstruct the data warehouse’s contents from within the data warehouse, rather than having to reacquire all data from its original source applications.

•   Trueorrect!
•   False

Question 17

2 / 2 pts

“ETL” stands for:

•   Extraction, Targeting, and Loading 
•   Engineering, Transformation, and Loading
•   Engineering, Targeting, and LayoutCorrect!
•   None of the answer selections are correct

Question 18

2 / 2 pts

Which of the following “tell me…” types of business intelligence is not considered to fall under the “data mining” grouping?

•   Tell me what is likely to happenorrect!
•   Tell me what happened
•   Tell me what might have happened if we had done something differently
•   Tell me something interesting and important
•   All of the above fall under “data mining”

Question 19

2 / 2 pts

True or False: The modern era of business intelligence began a decade before the modern era of data warehousing.ou Answered

•   True
•   False

Question 20

2 / 2 pts

Which of the following is not one of the original 4 “Inmon rules” for data warehousing?

•   Subject-oriented 
•   Non-volatile 
•   Time-variant 
•   Integrated
•   All of the above are “Inmon rules”

Assignment 02 – Due on 02/16/22, 11.59pm

ii) SQL Practice

Import the My Guitar Shop database into MySQL workbench using attached create_my_guitar_shop.sql file for answering these questions. The expected output file is also attached (HWK2 – Expected Output.pdf). Write the following queries in a single sql file. You must submit the .sql file named hwk02-part-ii-lastname.sql (Example hwk02-part-ii-kumar.sql) for this section.
All answers must be formatted for easy comprehension. MySQL Workbench has a format command (Edit -> Format -> Beautify Query).

1. Write a SELECT statement that returns four columns from the Products table: product_code, product_name, list_price, and discount_percent. Then, run this statement to make sure it works correctly.

Add an ORDER BY clause to this statement that sorts the result set by list price in descending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.

Only submit the final query including the ORDER BY clause.

2. Write a SELECT statement that returns one column from the Customers table named full_name that joins the last_name and first_name columns.

Format this column with the last name, a comma, a space, and the first

name like this: Doe, John

Sort the result set by last name in ascending sequence.

Return only the customers whose last name begins with letters from M to Z.

NOTE: When comparing strings of characters, ‘M’ comes before any string of characters that begins with ‘M’. For example, ‘M’ comes before ‘Murach’.

3. Write a SELECT statement that returns these columns from the Products table:

product_name       The product_name column
list_price               The list_price column
date_added           The date_added column

Return only the rows with a list price that’s greater than 500 and less than 2000.

Sort the result set in descending sequence by the date_added column.

4. Write a SELECT statement that returns these columns from the Orders table:

order_id         The order_id column
order_date     The order_date column
ship_date       The ship_date column

Return only the rows where the ship_date column contains a null value. (Hint: use IS NULL)

5. Write a SELECT statement that joins the Categories table to the Products table and returns these columns: category_name, product_name, list_price.

Sort the result set by category_name and then by product_name in ascending sequence.

6. Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: first_name, last_name, line1, city, state, zip_code.

Return one row for each customer, but only return addresses that are the shipping address for a customer.

7. Write a SELECT statement that joins the Customers, Orders, Order_Items, and Products tables. This statement should return these columns: last_name, first_name, order_date, product_name, item_price, discount_amount, and quantity.

Must use aliases for the tables to get full credit. Sort the final result set by last_name, order_date, and product_name.