Exam 2 Database Design
Two common data access privilege DCL commands include:
grant and revoke
What does a query do?
answer a question(s)
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
A data warehouse is created within an organization as a separate data store whose primary purpose is data analysis.
True
Which of the following would typically be stored in target (data warehousing) systems?
All of these are typically present in target systems.
The data dictionary created by the ________ is often referred to as the catalog.
DBMS
Which of the following differences, renders normalization of data warehouses unnecessary?
Data Update Difference and Data Redundancy Difference
Which of the following is NOT true:
Data marts typically have more subjects of analysis than data warehouses
What does ETL stand for?
Extract - Transform - Load
Retrieval of data from operational data sources takes place during _____________.
Extraction
________ contain measures related to the subject of analysis.
Fact tables
Queries can only come from one table?
False
Implicit changes of data warehouse requirements are permitted during:
Implicit changes of requirements are not permitted
Which of the following is true when comparing a data warehouse to a data lake?
In ETL, a data lake has no transform part
What will be the entire outcome of the following SQL statement issued in a doctorâ s database? GRANT SELECT, INSERT, ALTER, UPDATE ON specialty TO katie;
Katie can read data from SPECIALTY, change data in SPECIALTY, change the metadata of SPECIALTY, insert data in SPECIALTY
Which data types can have up to 65,535 characters?
Long Text and Hyperlink
In the case of so-called active data warehouses, the loads occur in ________ that happen continuously, ensuring that the data in the data warehouse is updated close to real time (enabling analysis of the latest data)
Micro batches
Querying and presenting data from data warehouses and/or data marts for analytical purposes is known as:
OLAP
Updating, querying, and presenting data from databases for operational purposes is known as:
OLTP
Which of the following is a Data Control Language (DCL) command
REVOKE
OLAP/BI tools are:
Read-only tools
Which of the following would NOT be found in an authorization matrix?
Recovery log checkpoint
The application development DBMS component provides functionalities to the developers of:
Security and backup procedures
Internal deployment to the members of the development team for initial testing of the functionalities of a data warehouse is known as _____________.
The alpha release
Creating surrogate key values takes place during _____________.
Transformation
Data cleansing is a part of:
Transformation
Which of the following items would tend to have the most detail:
a current sales transaction
Access to data and privileges by users is implemented by maintaining a ________________________ .
authorization matrix
Which data type is 9 digits?
autonumber
Two common abbreviations with data warehouses are BI and OLAP. What does each stand for?
business intelligence and online analytical processing
Which data type is accurate to 15 digits on the left side of the decimal point and to 4 digits on the right side
currency
_____________ tables typically have the most foreign keys.
fact tables
Granularity describes _____________________ in the fact table
level of detail
A data dictionary is a repository of the ____________________ .
metadata
Data lake repositories of big data are not created through the process of formal database __________________.
modeling
You must _________ each field, table, and other object in an Access database
name
What is the primary reason for an Access form?
to make data input easier
Which of the following data examples has the most structure?
A row in a relational table
Which of the following is an example of a subject-oriented data set.
None of these
CONSTANT DOCUMENTARIAN is an organization that receives each day a one-hour log video from 50 contributors around the world, recording mundane daily-life scenes. It also receives one daily 200-words email from its contributors. Consider the following CONSTANT DOCUMENTARIAN data sets: - Set A: collection of daily one-hour videos from the 50 contributors - Set B: collection of daily 200-words emails from its contributors - Set C: video footage of its 24/7 CCTV camera constantly recording scenes outside its headquarters - Set D: relational table containing first name, last name, phone number, and email address of each contributor. Which data set is exhibiting the highest volume?
Set A
Operational queries typically process:
Smaller amounts of data than analytical queries
The dimensional schema is often referred to as the:
Star schema
Which of the following is a part of the data warehouse requirements step?
Conceptual modeling
Due to the amount of details that have to be considered, creating _________________ is often the most time- and resource-consuming part of the data warehouse development process
ETL infrastructure
T/F Fields can be renamed in Datasheet view or Design view
True
T/F: Big data is mostly unstructured
True
The data definition DBMS component uses the SQL commands from the DDL category.
True
Deleting an extracted row from the set of extracted rows (because an identical row was retrieved from another data sourc before the data is loaded into the data warehouse, is an example of ________________.
Active transformation
OLAP/BI tools can be used for:
Ad-hoc direct analysis and creation of front-end (BI) applications
The data manipulation DBMS component is used:
By end users, either directly or via front-end applications
__________________ data marts are sourced exactly from enterprise data-warehouses.
Dependent
Production release takes place during _____________.
Deployment
The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operational databases is known as:
ETL
Big data sets, when compared with most databases and data warehouses:
Have higher possibility of different interpretations
A typical organization maintains and utilizes a number of operational data sources.
True
Which data type is 1 character only?
Yes/No
Operational queries are typically issued:
More often than analytical queries
Adding timestamp to a record to indicate when the row is valid/applicable is an example of ________________.
Passive transformation
CONSTANT DOCUMENTARIAN is an organization that receives each day a one-hour log video from 50 contributors around the world, recording mundane daily-life scenes. It also receives one daily 200-words email from its contributors. Consider the following CONSTANT DOCUMENTARIAN data sets: - Set A: collection of daily one-hour videos from the 50 contributors - Set B: collection of daily 200-words emails from its contributors - Set C: video footage of its 24/7 CCTV camera constantly recording scenes outside its headquarters - Set D: relational table containing first name, last name, phone number, and email address of each contributor. Which data set is exhibiting the highest velocity?
Set C
Big data does not easily fit into tables with rows and columns
True
Periodic backup copies of the database are made through use of recovery logs and _______________ .
check points
To relate two tables together, you must always have a _________________ field.
common
Which data type is 8 bytes?
date
________________ data marts are sourced from data captured from one or more operational systems or external data providers, or data generally locally within a different department or geographic area
Independent
Big data is not formally modeled for querying and retrieval and are not accompanied with detailed metadata
True
T/F Access does not allow duplicate values in the primary key field?
True
The data definition component is a part of every DBMS package.
True
The data manipulation component is a part of every DBMS package.
True
When you format a field, you change the way data is displayed, but not the _____________________.
actual values stored in the table
One way integrity of data in a database can be compromised is ______________ .
all of these ways
Metadata is __________________________.
data about the data
The _____________ determines what field values you can enter for the field and what other properties the field will have
data type
Choose the list that CORRECTLY has sizes of data sets from smallest to largest.
database, data warehouse, data lake
________ contain analytically useful information.
Both dimension and fact tables
The purpose of the source system is:
Both the original operational purpose and as a source system for the data warehouse
Which of the following is a Transaction Control Language (TCL) command
COMMIT
If, during the use of the database system, it becomes apparent that a new relation needs to be added to the database, the decision to add the new relation to the database will rest with the:
Database developer
Due to multiple subjects of analysis sharing dimensions, the following occurs:
A dimensional model contains more than one fact table
Data warehouse requirements can be refined and/or expanded following:
All of these
Which of the following is NOT a data administration task?
Creating a conceptual database model
Which of the following processes is typically the most time- and resource-consuming?
Creating the ETL infrastructure
Which of the following allows use of the same dimensions with multiple fact tables?
Galaxy of stars
What does BI stand for?
Business Intelligence
To create a data warehouse based on the star schema with 5 dimensions and 1 fact table, we will need ____ CREATE TABLE statements.
6
Data types are assigned automatically by ______________ or specifically by the _______________ .
Access Table Designer
Which of the following would typically be stored in source (operational databassystems?
Detailed Data
Typically, in a star schema all ________ are given a simple, non-composite system-generated primary key, also called a surrogate key.
Dimension tables
________ contain descriptions of the business, organization, or enterprise to which the subject of analysis belongs.
Dimension tables
A dimensional model cannot be based on a single source.
False
A large fact table in a data warehouse is an example of big data.
False
All sources for a dimensional model must be operational data sources from the company that is building the dimensional model.
False
SQL provides all the necessary functionalities for managing and analyzing big data.
False
T/F You cannot import data from Excel into Access due to software restrictions and formatting of the fields?
False
The application development component is a part of every DBMS package.
False
The operational data sources include the data warehouses and data marts.
False
If applicable, the archived data from operational sources is loaded in the data warehouse during the:
First load
In the process of query optimization, query cost refers to:
How long it takes to execute a query.
Where on the Spectrum of Solutions for Large Analytical Data Repositories, would the following example best fit? Data from 10 data sources is extracted. Two of those sources have the exact same structure, so the data from those two sources is pasted together before loading. There is a small overlap of data in those two sources, so the duplicates are eliminated before loading. The rest of the sources are loaded as they were.
Inside the spectrum, closer to the left edge of the spectrum (Pure Data Lake).
In a typical properly-designed star schema, the number of records/rows in any of the dimension tables is:
No typical ruleâ there is a similar number of dimension tables with larger, smaller and same number of records as in the fact table
Data time-horizon is:
Typically, shorter in operational systems than in analytical systems
Which of the following statements is true.
detailed fact tables contain the most information
Which of the following means to get more detailed data (higher granularity)?
drill down
When purchase items online, you are actually entering data for the vendor into a database. When ordering online, what does the graphic below represent in relation to a database?
form
Comparing a Data Warehouse vs a Data Lake, a common analogy is data warehouses are like agriculture while data lakes are like _____________________ .
hunting/gathering
Access responds faster to your requests for specific records based on the ______________.
primary key
Within a Data Lake, the large data pool in which the schema and data requirements are not defined until the data is ______________ .
queried
Data lakes store vast amount of __________________ data in its native format until it is needed
raw
Which field type has these characteristics: 0 to 255 characters; default is 255
short text
____________________ adds, replaces, or eliminates specified dimension attributes (or particular values of the dimension attributes) from the already displayed result
slice and dice
Which of the following is likely to be a user of a bank's data warehouse
Bank's CEO
CONSTANT DOCUMENTARIAN is an organization that receives each day a one-hour log video from 50 contributors around the world, recording mundane daily-life scenes. It also receives one daily 200-words email from its contributors. Consider the following CONSTANT DOCUMENTARIAN data sets: - Set A: collection of daily one-hour videos from the 50 contributors - Set B: collection of daily 200-words emails from its contributors - Set C: video footage of its 24/7 CCTV camera constantly recording scenes outside its headquarters - Set D: relational table containing first name, last name, phone number, and email address of each contributor. Which data set has the most structure?
Set D
Label the graphic below correctly between course and fine granularity: A) Students = 5 Classes = 10 B) Students = A. Johnson, K. Thessalonious, A. Palmer, K Walker, Z. Bundrick Classes = CIT 105, ENG 101, MAT 150, SOC 101, PHY 110, MAT 171, CIT 149, CIT 180, CIT 293, HIS 109
A = coarse, B = fine
Operational queries are typically issued:
By more users than analytical queries
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.
Type 2
The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated.
Type 3
Examples of sources generating large amounts of data
ALL OF THESE ANSWERS
CONSTANT DOCUMENTARIAN is an organization that receives each day a one-hour log video from 50 contributors around the world, recording mundane daily-life scenes. It also receives one daily 200-words email from its contributors. Consider the following CONSTANT DOCUMENTARIAN data sets: - Set A: collection of daily one-hour videos from the 50 contributors - Set B: collection of daily 200-words emails from its contributors - Set C: video footage of its 24/7 CCTV camera constantly recording scenes outside its headquarters - Set D: relational table containing first name, last name, phone number, and email address of each contributor. Which data set is exhibiting the lowest velocity?
Set D
CONSTANT DOCUMENTARIAN is an organization that receives each day a one-hour log video from 50 contributors around the world, recording mundane daily-life scenes. It also receives one daily 200-words email from its contributors. Consider the following CONSTANT DOCUMENTARIAN data sets: - Set A: collection of daily one-hour videos from the 50 contributors - Set B: collection of daily 200-words emails from its contributors - Set C: video footage of its 24/7 CCTV camera constantly recording scenes outside its headquarters - Set D: relational table containing first name, last name, phone number, and email address of each contributor. Which data set is exhibiting the lowest volume?
Set D
Which of the following column names would not appear in a fact table?
Product Name
Which way(s) can you import data into an Access database
ALL of these ways
