(Solved): CIS 355 Final Exam | Attempt 1 | Scored 120 out of 120 | May 6, 2022...

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