Chapter 8
27) The STATEWIDE HEALTH PRESCRIPTIONS database column MedicationDosage provides the data for the fact table in the STATEWIDE HEALTH star schema.
FALSE
7) A dimensional model can contain more than one fact table.
TRUE
37) The CUSTOM SPA DAILY VISITS DATABASE column SEYearOfHire provides the data for a dimension in the STATEWIDE HEALTH star schema.
TRUE
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
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 8.2 True/False Questions
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
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
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
39) The CUSTOM SPA DAILY VISITS DATABASE column VisitID provides the data for a dimension in the STATEWIDE HEALTH star schema.
FALSE
31) The CUSTOM SPA star schema contains the dimension SPAEMPLOYEE.
TRUE
32) The CUSTOM SPA star schema contains the dimension PROCEDURE.
TRUE
8) Aggregated fact tables have a finer level of granularity than detailed tables.
FALSE
6) Transaction time is typically included as a column in the fact table.
TRUE
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
35) The detailed fact table in the CUSTOM SPA star schema has ________ records. A) 0 B) 5 C) 8 D) 50
C
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
16) The relational model is the only way through which dimensionally modeled databases can be implemented.
FALSE
12) Independent data marts approach can be used for designing an enterprise-wide analytical data warehouse.
FALSE
18) The detailed fact table in the STATEWIDE HEALTH star schema has ________ records. A) 0 B) 5 C) 9 D) 82
B
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
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
2) A dimensional model cannot be based on a single source.
FALSE
19) The STATEWIDE HEALTH star schema contains the dimension DOCTOR.
TRUE
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
1) Dimensional modeling can be employed as a relational data modeling technique.
TRUE
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
29) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescTimeOfDay provides the data for a dimension in the STATEWIDE HEALTH star schema.
FALSE
35) The CUSTOM SPA DAILY VISITS DATABASE column PatronZip provides the data for a dimension in the STATEWIDE HEALTH star schema.
TRUE
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
38) The CUSTOM SPA DAILY VISITS DATABASE column AmountCharged provides the data for a dimension in the STATEWIDE HEALTH star schema.
FALSE
9) When a data warehouse is normalized, its dependent data marts also must be normalized.
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
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
14) Dimensionally modeled data warehouse approach results in multiple unrelated ETL infrastructures.
FALSE
28) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescID provides the data for a dimension in the STATEWIDE HEALTH star schema.
FALSE
33) The CUSTOM SPA star schema contains the dimension VISIT.
FALSE
13) Normalized data warehouse approach results in multiple unrelated ETL infrastructures.
FALSE
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
3) ________ contain analytically useful information. A) Fact tables B) Dimension tables C) Both dimension and fact tables D) None of the above
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
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
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
36) The CUSTOM SPA DAILY VISITS DATABASE column ProcedureName provides the data for a dimension in the STATEWIDE HEALTH star schema.
TRUE
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
21) The STATEWIDE HEALTH star schema contains the dimension PRESCRIPTION.
FALSE
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
17) The ER modeling and dimensional modeling methods cannot be combined and used within the same data warehousing project.
FALSE
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
STATEWIDE HEALTH The following questions citing STATEWIDE HEALTH assume that a single-source star schema is created. The purpose of this star schema is to analyze quantities of prescribed medications by all dimensions provided by its source: the STATEWIDE HEALTH PRESCRIPTIONS DATABASE shown below. In this database, each prescription is prescribed for one patient by one doctor, and it contains one medication. STATEWIDE HEALTH PRESCRIPTIONS DATABASE PATIENT PatientID PatientName PatientWeight P11 Jim 160lb P22 Mary 125lb P33 Fred 250lb DOCTOR SPECIALTY DoctorID DoctorName Specialty SpecialtyID SpecialtyName D11 Lee S1 S1 Family Medicine D11 Hank S1 S2 Internal Medicine D33 Luisa S2 MEDICATION MedicationID MedicationName Dosage M11 Zolex 2mg M22 Luneva 1mg M33 Luneva 2mg PRESCRIPTION PrescID Patient Doctor PrescDate PrescTimeOfDay PrescMedication PrescQty RX11 P11 D11 1/1/2013 8:00 a.m. M11 12 RX22 P22 D22 1/1/2013 8:30 a.m. M22 10 RX33 P22 D22 1/1/2013 8:30 a.m. M11 20 RX44 P33 D11 1/1/2013 8:00 a.m. M11 10 RX55 P33 D33 1/2/2013 9:00 a.m. M33 30 18) The STATEWIDE HEALTH star schema contains the dimension PATIENT.
TRUE
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
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
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
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
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
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
40) The CUSTOM SPA DAILY VISITS DATABASE column VisitTimeOfDay 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
10) Normalized data warehouse approach can be used for designing an enterprise-wide analytical data warehouse.
TRUE
11) Dimensionally modeled data warehouse approach can be used for designing an enterprise-wide analytical data warehouse.
TRUE
15) Independent data marts approach results in multiple unrelated ETL infrastructures.
TRUE
20) The STATEWIDE HEALTH star schema contains the dimension MEDICATION.
TRUE
24) The STATEWIDE HEALTH PRESCRIPTIONS database column MedicationName provides the data for a dimension in the STATEWIDE HEALTH star schema.
TRUE
25) The STATEWIDE HEALTH PRESCRIPTIONS database column PatientWeight provides the data for a dimension in the STATEWIDE HEALTH star schema.
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
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
34) The CUSTOM SPA star schema contains the dimension VISITPROCEDURE.
FALSE
22) The detailed fact table in the STATEWIDE HEALTH star schema is a line-item detailed fact table.
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
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
26) The STATEWIDE HEALTH PRESCRIPTIONS database column PrescQuantity provides the data for a dimension in the STATEWIDE HEALTH star schema.
FALSE
4) Analytical queries on the dimensionally modeled database can be significantly simpler to create than on the equivalent nondimensional database.
TRUE
CUSTOM SPA The following questions citing CUSTOM SPA assume that a single-source star schema is created. The purpose of this star schema is to analyze procedure revenue by all dimensions provided by its source: the CUSTOM SPA DAILY VISITS DATABASE, shown below. In this database, each visit includes one patron, one spa employee, and one or more procedures. The price for each procedure varies from visit to visit. CUSTOM SPA DAILY VISITS DATABASE PATRON PatronID PatronName PatronZip PT1 Daisy 60611 PT2 Ellena 60650 PT3 Nelly 60611 PT4 Marcia 60650 SPAEMPLOYEE SEID SEName SEYearOfHire SE1 Claire 2000 SE2 Jamie 2008 PROCEDURE ProcedureID ProcedureName PR1 Mani PR2 Pedi PR3 Massage VISIT VisitID Patron SEmployee VisitDate VisitTimeOfDay V1 PT1 SE1 1/1/2013 8:00 a.m. V2 PT2 SE2 1/2/2013 8:30 a.m. V3 PT3 SE1 1/3/2013 8:30 a.m. V4 PT1 SE2 1/3/2013 8:00 a.m. V5 PT4 SE1 1/4/2013 8:00 a.m. VISITPROCEDURES VisitID ProcedureID AmountCharged V1 PR1 $20 V1 PR2 $30 V2 PR1 $25 V3 PR2 $35 V4 PR3 $100 V5 PR1 $15 V5 PR2 $20 V5 PR3 $80 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
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
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
STATEWIDE HEALTH The following questions citing STATEWIDE HEALTH assume that a single-source star schema is created. The purpose of this star schema is to analyze quantities of prescribed medications by all dimensions provided by its source: the STATEWIDE HEALTH PRESCRIPTIONS DATABASE shown below. In this database, each prescription is prescribed for one patient by one doctor, and it contains one medication. STATEWIDE HEALTH PRESCRIPTIONS DATABASE PATIENT PatientID PatientName PatientWeight P11 Jim 160lb P22 Mary 125lb P33 Fred 250lb DOCTOR SPECIALTY DoctorID DoctorName Specialty SpecialtyID SpecialtyName D11 Lee S1 S1 Family Medicine D11 Hank S1 S2 Internal Medicine D33 Luisa S2 MEDICATION MedicationID MedicationName Dosage M11 Zolex 2mg M22 Luneva 1mg M33 Luneva 2mg PRESCRIPTION PrescID Patient Doctor PrescDate PrescTimeOfDay PrescMedication PrescQty RX11 P11 D11 1/1/2013 8:00 a.m. M11 12 RX22 P22 D22 1/1/2013 8:30 a.m. M22 10 RX33 P22 D22 1/1/2013 8:30 a.m. M11 20 RX44 P33 D11 1/1/2013 8:00 a.m. M11 10 RX55 P33 D33 1/2/2013 9:00 a.m. M33 30 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
CUSTOM SPA The following questions citing CUSTOM SPA assume that a single-source star schema is created. The purpose of this star schema is to analyze procedure revenue by all dimensions provided by its source: the CUSTOM SPA DAILY VISITS DATABASE, shown below. In this database, each visit includes one patron, one spa employee, and one or more procedures. The price for each procedure varies from visit to visit. CUSTOM SPA DAILY VISITS DATABASE PATRON PatronID PatronName PatronZip PT1 Daisy 60611 PT2 Ellena 60650 PT3 Nelly 60611 PT4 Marcia 60650 SPAEMPLOYEE SEID SEName SEYearOfHire SE1 Claire 2000 SE2 Jamie 2008 PROCEDURE ProcedureID ProcedureName PR1 Mani PR2 Pedi PR3 Massage VISIT VisitID Patron SEmployee VisitDate VisitTimeOfDay V1 PT1 SE1 1/1/2013 8:00 a.m. V2 PT2 SE2 1/2/2013 8:30 a.m. V3 PT3 SE1 1/3/2013 8:30 a.m. V4 PT1 SE2 1/3/2013 8:00 a.m. V5 PT4 SE1 1/4/2013 8:00 a.m. VISITPROCEDURES VisitID ProcedureID AmountCharged V1 PR1 $20 V1 PR2 $30 V2 PR1 $25 V3 PR2 $35 V4 PR3 $100 V5 PR1 $15 V5 PR2 $20 V5 PR3 $80 30) The CUSTOM SPA star schema contains the dimension PATRON.
TRUE