Chapters 7 and 8: Final Exam

2) A dimensional model cannot be based on a single source.

FALSE

2) The operational data sources include the data warehouses and data marts.

FALSE

4) The performance of operational day-to-day tasks involving data use can be severely diminished if such tasks have to compete for computing resources with analytical queries.

TRUE

16) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS DATABASE, the dimension MEDICATION in the STATEWIDE HEALTH star schema has ________ records. A) 0 (the dimension MEDICATION does not exist) B) 2 C) 3 D) 5

C

5) In a typical, properly designed star schema, the number of records (rows) in any of the dimension tables is: A) Larger than in the fact table B) Same as in the fact table C) Smaller than in the fact table D) No typical rule—there is an even number of dimension tables with larger, smaller and same number of records as in the fact table

C

30) The CUSTOM SPA star schema contains the dimension PATRON.

TRUE

36) The CUSTOM SPA DAILY VISITS DATABASE column ProcedureName provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE

3) ________ contain analytically useful information. A) Fact tables B) Dimension tables C) Both dimension and fact tables D) None of the above

C

40) The CUSTOM SPA DAILY VISITS DATABASE column VisitTimeOfDay provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

32) The CUSTOM SPA star schema contains the dimension PROCEDURE.

TRUE

35) The detailed fact table in the CUSTOM SPA star schema has ________ records. A) 0 B) 5 C) 8 D) 50

C

39) The CUSTOM SPA DAILY VISITS DATABASE column VisitID provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

6) Increasing the speed of a single query is always a priority, even if it requires slowing down the majority of other queries.

FALSE

7) Keeping data in the operational system past its time horizon enables the operational queries to run faster.

FALSE

8) Aggregated fact tables have a finer level of granularity than detailed tables.

FALSE

9) When a data warehouse is normalized, its dependent data marts also must be normalized.

FALSE

6) Transaction time is typically included as a column in the fact table.

TRUE

7) A dimensional model can contain more than one fact table.

TRUE

18) The detailed fact table in the STATEWIDE HEALTH star schema has ________ records. A) 0 B) 5 C) 9 D) 82

B

21) The value 2mg from the STATEWIDE HEALTH PRESCRIPTIONS database appears all together ________ time(s) in the populated tables of the STATEWIDE HEALTH star schema. A) 1 B) 2 C) 3 D) 5

B

38) The value 60611 from the CUSTOM SPA DAILY VISITS DATABASE appears altogether ________ time(s) in CUSTOM SPA star schema. A) 1 B) 2 C) 5 D) 8

B

5) The purpose of the source system is: A) Original operational purpose only B) As a source system for the data warehouse only C) Both the original operational purpose and as a source system for the data warehouse D) As a write-only source system

C

8) Which of the following allows reusing (instead of duplication) of dimensions? A) Time column B) Snowflaking C) Galaxy of stars D) Degenerate dimension

C

16) Data marts are always implemented as relational databases.

FALSE

23) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication, such an aggregated table would be connected to ________ dimensions. A) 2 B) 3 C) 4 D) 5

A

27) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication per each doctor, such an aggregated table would be connected to ________ dimensions. A) 2 B) 3 C) 4 D) 5

B

1) Data time-horizon is: A) Typically longer in operational systems than in analytical systems B) Typically equal in operational systems and in analytical systems C) Typically shorter in operational systems than in analytical systems D) Typically nonexistent in operational systems and in analytical systems

C

11) The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated. A) Type 1 B) Type 2 C) Type 3 D) Type 0

C

40) The fact table in the CUSTOM SPA star schema is a ________ fact table. A) constellation B) degenerate C) transaction-level detailed D) line-item detailed

D

4) Analytical queries on the dimensionally modeled database can be significantly simpler to create than on the equivalent nondimensional database.

TRUE

9) Operational data typically represents the current state of affairs in the real world, while analytical data can represent both the current situation and snapshots from the past.

TRUE

28) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication per each doctor, such an aggregated table would have ________ records in the fact table. A) 2 B) 3 C) 4 D) 5

C

31) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension CALENDAR in the CUSTOM SPA star schema has ________ record(s). A) 0 (the dimension CALENDAR does not exist) B) 1 C) 4 D) 5

C

4) The dimensional schema is often referred to as the: A) Fact table B) Dimension table C) Star schema D) Transaction schema

C

12) The ________ slowly changing dimension approach changes the old value in the dimension's record with the new value. A) Type 1 B) Type 2 C) Type 3 D) Type 0

A

17) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS DATABASE, the dimension PRESCRIPTION in the STATEWIDE HEALTH star schema has ________ records. A) 0 (the dimension PRESCRIPTION does not exist) B) 5 C) 9 D) 82

A

37) The value Daisy from the CUSTOM SPA DAILY VISITS DATABASE appears altogether ________ time(s) in CUSTOM SPA star schema. A) 1 B) 3 C) 5 D) 8

A

13) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS DATABASE, the dimension PATIENT in the STATEWIDE HEALTH star schema has ________ record(s). A) 0 (the dimension PATIENT does not exist) B) 1 C) 3 D) 4

C

13) Analytical databases are referred to as application-oriented.

FALSE

36) The value Claire from the CUSTOM SPA DAILY VISITS DATABASE appears altogether ________ time(s) in CUSTOM SPA star schema. A) 1 B) 3 C) 5 D) 8

A

5) It is always possible to structure a database that can be used in a straightforward manner for both operational and analytical purposes.

FALSE

1) Dimensional modeling can be employed as a relational data modeling technique.

TRUE

33) The CUSTOM SPA star schema contains the dimension VISIT.

FALSE

18) The STATEWIDE HEALTH star schema contains the dimension PATIENT.

TRUE

39) The value Mani from the CUSTOM SPA DAILY VISITS DATABASE appears altogether ________ time(s) in CUSTOM SPA star schema. A) 1 B) 3 C) 5 D) 8

A

2) Operational queries typically process: A) Larger amounts of data than analytical queries B) Smaller amounts of data than analytical queries C) Equal amounts of data as analytical queries D) Infinite amounts of data, incomparable to analytical queries

B

15) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS DATABASE, the dimension CALENDAR in the STATEWIDE HEALTH star schema has ________ record(s). A) 0 (the dimension CALENDAR does not exist) B) 1 C) 2 D) 5

C

7) Including a transaction identifier in the fact table is often referred to as: A) Aggregated fact table B) Snowflaking C) Galaxy of stars D) Degenerate dimension

D

Which of the following column names would not appear in a fact table? A) SalesTransactionID B) Product Key (FK) C) TimeOfDay D) UnitsSold E) Product Name

E

29) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescTimeOfDay provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

37) The CUSTOM SPA DAILY VISITS DATABASE column SEYearOfHire provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE

10) It is usually possible to configure the system to optimally process frequent queries requiring smaller amounts of data and to optimally process less frequent queries requesting large amounts of data at the same time.

FALSE

12) Independent data marts approach can be used for designing an enterprise-wide analytical data warehouse.

FALSE

13) Normalized data warehouse approach results in multiple unrelated ETL infrastructures.

FALSE

10) Normalized data warehouse approach can be used for designing an enterprise-wide analytical data warehouse.

TRUE

24) The STATEWIDE HEALTH PRESCRIPTIONS database column MedicationName provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE

3) A data warehouse is created within an organization as a separate data store whose primary purpose is data analysis.

TRUE

6) Typically, in a star schema all ________ are given a simple, non-composite system-generated key, also called a surrogate key. A) Fact tables B) Dimension tables C) Time columns D) Transaction identifiers

B

8) Summarized operational data is typically physically stored, while summarized analytical data is typically derived (calculated) rather than physically stored.

FALSE

32) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension PROCEDURE in the CUSTOM SPA star schema has ________ records. A) 0 (the dimension PATRON does not exist) B) 3 C) 5 D) 8

B

1) A typical organization maintains and utilizes a number of operational data sources.

TRUE

29) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension PATRON in the CUSTOM SPA star schema has ________ records. A) 0 (the dimension PATRON does not exist) B) 4 C) 5 D) 8

B

21) The STATEWIDE HEALTH star schema contains the dimension PRESCRIPTION.

FALSE

23) The STATEWIDE HEALTH PRESCRIPTIONS database column DoctorSpecialty provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE

24) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication, such an aggregated table would have ________ records in the fact table. A) 2 B) 3 C) 4 D) 5

B

18) ETL infrastructure has to account for and reconcile all of the differences in the metadata and the data between the operational sources and the target data warehouses.

TRUE

19) Data warehouse front-end applications are often referred to as business intelligence (BI) applications.

TRUE

19) The STATEWIDE HEALTH star schema contains the dimension DOCTOR.

TRUE

20) The design and creation of data warehouse front-end applications can take place in parallel with data warehouse creation.

TRUE

10) The ________ slowly changing dimension approach creates a new additional dimension record using a new value for the surrogate key every time a value in a dimension record changes. A) Type 1 B) Type 2 C) Type 3 D) Type 0

B

14) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS DATABASE, the dimension DOCTOR in the STATEWIDE HEALTH star schema has ________ records. A) 0 (the dimension DOCTOR does not exist) B) 3 C) 4 D) 5

B

19) The value Family Medicine from the STATEWIDE HEALTH PRESCRIPTIONS database appears all together ________ time(s) in the populated tables of the STATEWIDE HEALTH star schema. A) 1 B) 2 C) 3 D) 5

B

25) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication per each patient, such an aggregated table would be connected to ________ dimensions. A) 2 B) 3 C) 4 D) 5

B

22) The value 10 from the STATEWIDE HEALTH PRESCRIPTIONS database appears all together ________ time(s) in the populated tables of the STATEWIDE HEALTH star schema. A) 0 B) 1 C) 2 D) 3

C

10) Creating ETL infrastructure includes creating necessary procedures and code for: A) Designing and creating applications for indirect use by the end users B) The seamless load of the transformed data into the operational data bases C) Automatic extraction of relevant data from the data warehouses D) Transformation of the extracted data, so that its quality is assured and its structure conforms to the structure of the modeled and implemented data warehouse

D

Which of the following is likely to be a user of a bank's data warehouse? A) Bank's customer B) Bank's security office C) Bank's CEO D) Bank's teller E) None of the above

C

17) The ER modeling and dimensional modeling methods cannot be combined and used within the same data warehousing project.

FALSE

7) Which of the following is NOT a part of the data warehouse requirements step? A) Requirements collection B) Requirements definition C) Conceptual modeling D) Logical modeling

D

28) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescID provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

14) Dimensionally modeled data warehouse approach results in multiple unrelated ETL infrastructures.

FALSE

34) The CUSTOM SPA star schema contains the dimension VISITPROCEDURE.

FALSE

38) The CUSTOM SPA DAILY VISITS DATABASE column AmountCharged provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

5) All sources for a dimensional model must be operational data sources from the company that is building the dimensional model.

FALSE

12) Analytical databases are created for the analysis of one or more specific business subject areas, such as sales, returns, cost, or profit.

TRUE

15) Independent data marts approach results in multiple unrelated ETL infrastructures.

TRUE

20) The STATEWIDE HEALTH star schema contains the dimension MEDICATION.

TRUE

25) The STATEWIDE HEALTH PRESCRIPTIONS database column PatientWeight provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE

3) In addition to giving each record of a dimension a new value that serves as a primary key within the dimensional model instead of the operational key value, surrogate key values contain additional information about each record in the dimension.

FALSE

2) ________ contain measures related to the subject of analysis. A) Fact tables B) Dimension tables C) Both dimension and fact tables D) None of the above

A

1) ________ contain descriptions of the business, organization, or enterprise to which the subject of analysis belongs. A) Fact tables B) Dimension tables C) Both dimension and fact tables D) None of the above

B

30) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension SPAEMPLOYEE in the CUSTOM SPA star schema has ________ records. A) 0 (the dimension SPAEMPLOYEE does not exist) B) 2 C) 5 D) 8

B

4) Operational queries are typically issued: A) By fewer users than analytical queries B) By more users than analytical queries C) By an equal number of users as analytical queries D) By no users if the corporation has users issuing analytical queries

B

6) Which of the following is NOT true: A) Data marts typically have fewer data sources than data warehouses B) Data marts typically have broader focus than data warehouses C) Data marts are typically not as big as data warehouses D) Data marts typically have shorter implementation time than data warehouses

B

Which of the following allows use of the same dimensions with multiple fact tables? A) Time column B) Snowflaking C) Galaxy of stars D) Transaction identifier E) Degenerate dimension

C

26) Based on the data shown in the STATEWIDE HEALTH PRESCRIPTIONS database, if we created an aggregated fact table that contains data about daily prescribed quantities of each medication per each patient, such an aggregated table would have ________ records in the fact table. A) 2 B) 3 C) 4 D) 5

D

8) Data warehouse requirements can be refined and/or expanded following: A) Data warehouse use B) Data warehouse deployment C) Creating ETL infrastructure D) All of the above

D

9) Implicit changes of data warehouse requirements are permitted during: A) Data warehouse use B) Data warehouse deployment C) Creating ETL infrastructure D) Implicit changes of requirements are not permitted

D

15) A data mart is a data store based on the same principles as a data warehouse but with a wider scope.

FALSE

20) The value 250lb from the STATEWIDE HEALTH PRESCRIPTIONS database appears all together ________ time(s) in the populated tables of the STATEWIDE HEALTH star schema. A) 1 B) 2 C) 3 D) 5

A

9) Due to multiple subjects of analysis sharing dimensions, the following occurs: A) A star schema is composed strictly of fact tables B) A dimensional model contains more than one fact table C) Some dimensions are eliminated D) All dimensions are reduced

B

Which of the following is a part of the data warehouse requirements step? A) Data warehouse deployment B) Creating ETL Infrastructure C) Conceptual modeling D) All of the above

C

16) The relational model is the only way through which dimensionally modeled databases can be implemented.

FALSE

22) The detailed fact table in the STATEWIDE HEALTH star schema is a line-item detailed fact table.

FALSE

26) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescQuantity provides the data for a dimension in the STATEWIDE HEALTH star schema.

FALSE

14) Operational databases are referred to as subject-oriented.

FALSE

3) Operational queries are typically issued: A) More often than analytical queries B) Less often than analytical queries C) Equally as often as analytical queries D) Never in a corporation that has users issuing analytical queries

A

33) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension VISIT in the CUSTOM SPA star schema has ________ records. A) 0 (the dimension VISIT does not exist) B) 3 C) 5 D) 8

A

34) Based on the data shown in the CUSTOM SPA DAILY VISITS DATABASE, the dimension VISITPROCEDURE in the CUSTOM SPA star schema has ________ records. A) 0 (the dimension VISIT does not exist) B) 3 C) 5 D) 8

A

Assume that aggregated fact table B is based on the detailed fact table A. Which of the following is FALSE? A) Table B has more records than Table A. B) Number of records in Table B is equal or less than the number of records in Table A. C) Number of dimensions that Table B is connected to is equal or less than the number of dimensions that Table A is connected to. D) All dimensions connected to Table B are connected to Table A. E) Table B provides aggregated view of facts in Table A.

A

17) Separate operational data sources cannot contain overlapping information.

FALSE

27) The STATEWIDE HEALTH PRESCRIPTIONS database column MedicationDosage provides the data for the fact table in the STATEWIDE HEALTH star schema.

FALSE

11) In contrast to a narrow constituency of operational data, analytical data is used by a widespread set of users.

FALSE

11) Dimensionally modeled data warehouse approach can be used for designing an enterprise-wide analytical data warehouse.

TRUE

31) The CUSTOM SPA star schema contains the dimension SPAEMPLOYEE.

TRUE

35) The CUSTOM SPA DAILY VISITS DATABASE column PatronZip provides the data for a dimension in the STATEWIDE HEALTH star schema.

TRUE