BCIS 4660 Midterm 2020
The term "non-volatile" describes which aspect of a data warehouse? Data from different time-zones Historical data Data cannot be deleted Data from non stable sources
Data cannot be deleted
When a 1:M relationship is mapped, the foreign key must appear in the relation mapped from the M side True False
True
A SQL statement that rolls back a transaction is generally referred to as a ________. DDL DCL DML JBL
DCL
In normalization, there are four main normal forms True False
False
The Star Schema is NOT the default schema for dimensional modeling True False
False
You work as a data analyst at an IT company with several divisions ( e.g, Servers, Desktops and laptops, Accessories). Each division has its own operational system comprising of marketing, sales, and revenue. The CEO and top leaders of the organization want to see last year's monthly sales of all the organization's divisions. Which of the following solutions is best suited to allow this type of report? Implementing a data warehouse system that combines the data sources from all the divisions Restoring backups of each of the division's database and then querying and combining the results Running reports from each division's operational system and then using Excel to combine them Implementing a statistical analysis of each division's operational data for each month
Implementing a data warehouse system that combines the data sources from all the divisions
Which of the tools in SQL Server Management Studio allows one to visualize an ER Model or relationships between tables? Database Tables Power BI ER Model Diagram Database Diagram
Database Diagram
Which of the following data warehouse components contains descriptions of the business that the subject (e.g., sales, inventory) belongs to. Surrogate keys Neither dimension nor fact tables Fact tables Both dimension and fact tables Dimension tables
Dimension tables
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: OLAP ETL BI OLTP
ETL
In which type of Cardinality relationship does one require a junction table? M:1 M:N 1:M 1:1
M:N
A relational database is a database structure that allows objects to have relationships with other objects in the database. True or False
True
The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated Type 0 Type 2 Type 3 Type 1
Type 3
Choose all that apply. The following are examples of attributes of a fact table. StoreID Units Sold Amount sold Quantity DiscountID
Units Sold Amount sold Quantity
The following are attributes in a fact table. Select all that apply. Product Weight Units Sold Product Color Unit Price
Units Sold Unit Price
Which of the following is the correct processing order of the DW Architecture? Sources, ETL, and DW Sources, ETL, and BI Apps ETL, Sources, and DW DW, BI Apps, and ETL
Sources, ETL, and DW
Three main types of SQL statements used for database operations are ____, ____ and ____? data manipulation, data control, and data commands data definition, data manipulation, and data committing data definition, data manipulation, and data control data commands, data manipulation, and data query
data definition, data manipulation, and data control
You are designing a {Student} table for a large university in Texas that will contain more than 60,000 student IDs and EUIDs. This table should keep track of the student ID, EUID, name and other related fields. From your knowledge of the [Student] table at this university, you recognize that the EUID column must be non-numeric, while StudentID column must be numeric. Which T-SQL command should you run? CREATE TABLE Student ( StudentID CHAR (6), EUID INT, FirstName CHAR (50) NOT NULL, LastName CHAR (50) NOT NULL, ..... ) CREATE TABLE Student ( StudentID INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... ) CREATE TABLE Student ( StudentID CHAR (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... ) CREATE TABLE Student ( StudentID INT (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... )
CREATE TABLE Student ( StudentID INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... )
One of the following is NOT an objective of business intelligence solution. A consolidated historical data repository that supports strategic decision making A consolidated historical data that facilitates decision making Easy access to data that is current detailed and always modifiable Easy access to data that provides managers with the ability to conduct analysis and make decisions
Easy access to data that is current detailed and always modifiable
A database table can have a maximum of 1026 columns True False
False
Anna was just hired as a consultant to design and build a data warehouse system. She makes an assumption that the organization has an operational database system. Is Anna wrong in making this assumption? Yes, she is wrong. In general you do not need an operational database to build a data warehouse system No, she is not wrong. You need an operational database system to build a data warehouse system
No, she is not wrong. You need an operational database system to build a data warehouse system
You have designed a [Student] table for the Human Resources department. The table has 4 columns (StudentID, LastName, FirstName, EmailAddress). The StudentID column will be used a lot (90% of time) to retrieve student information, followed by the EmailAddress column (50% of time), and then, LastName column ( 20% of time). Which of the columns is the most appropriate to add an index? StudentID FirstName LastName EmailAddress
StudentID
If an entity has three unique attributes, how many primary keys will its mapped relation have? 3 1 0 2
1
Which of the following are NOT valid values for the TinyInt data type? Choose all that apply 265 250 45821 30
265 45821
How many entities are depicted by the following requirements? School XYZ keeps track of its 100 students, 10 teachers, and 5 classrooms. 114 115 3 4
3
Which of the following best describes an orphaned record. A column whose foreign key points to a non-existing primary key value A record whose foreign key points to an existing primary key value A column whose foreign key points to an existing primary key value A record whose foreign key points to a non-existing primary key value
A record whose foreign key points to a non-existing primary key value
A function that returns the number of items in a group, usually in integer is known as _______. Min Count Sum Avg
Count
Operational data is known for being ________; while Data warehouse data is known for being ________________. Historical; modifiable Historical; not usually modifiable Current; granular Current; modifiable
Current; granular
You are designing a [Grades] table in a school database. A few of the attributes (fields) in this table include: GradeID, CourseID, SemesterID, StudentID, GradeNumber.... Which of the following data types is better to use for the attribute, GradeNumber? Integer Decimal Bit Money
Decimal
Which of the following is NOT a possible cardinality constraint? Mandatory many Optional many Optional zero Mandatory one
Optional zero
You are a data warehouse analyst, who is mentoring a junior analyst. In trying to explain the function of surrogate key to the junior analyst, how would you best describe it? The surrogate key, a meaningless part of the fact table serves as a PK The surrogate key, a crucial part of the dimension table serves as a unique key The surrogate key, a meaningless part of the dimension table serves as a PK The surrogate key, a crucial part of the dimension table is used to ensure entity integrity
The surrogate key, a meaningless part of the dimension table serves as a PK
Which of the following is not a database object? Index Views Transaction Table
Transaction
A data warehouse implementation is a recognition that there is potential business value in organizational data. True or False
True
You are designing a database that requires users to write their own SELECT queries against the database from different applications. However, management has determined that users must not directly access the database. As a database engineer, you are tasked to create database objects that are based on tables that will allow the users to write their queries. What database objects should you use? Table Functions Indexes Check Constraints Views
Views
Which of the following best describes an operational database? An operational database is a system that captures and transforms data from different data sources An operational database is a system that captures all elements of the day-to-day activities of the business An operational database is a system that captures and stores historical activities in the business An operational database is a system that captures both the day-to-day and historical business activities
An operational database is a system that captures all elements of the day-to-day activities of the business
A SQL statement that alters a table is generally called _____. DDL DDM DML DCL
DDL
In the DW/BI Architecture, which of the following components represents data cleaning and loading? ETL Data warehouse BI Application Operational data sources
ETL
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 BI OLTP OLAP
ETL
Data warehouses do not reveal past trends that can help organization predict and identify new business areas True or False
False
Dimensional modeling is a modeling technique used for designing data in OLTP. True False
False
One of the following is NOT a type of index. Nonclustered Index Foreign Index Clustered Index Unique Index
Foreign Index
Which of the following can be used to maintain referential entity integrity? View Primary Key Foreign Key Join
Foreign Key
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 1 Type 2 Type 4 Type 3
Type 2
Which of the following correctly describe the relationship between Data warehouse and Business intelligence DW is a subset of BI BI is a subset of DW BI and DW are part of Microsoft Excel BI and DW both mean the same thing
DW is a subset of BI
In the DW/BI Architecture, in which of the following stages or components does dimensional modeling occur? BI Application Data warehouse ETL Operational data sources
Data warehouse
You work as a data analyst at an IT company with several divisions ( e.g, Servers, Desktops and laptops, Accessories). Each division has its own operational system comprising of marketing, sales, and revenue. The CEO and top leaders of the organization want to see last year's monthly sales of all the organization's divisions. Which of the following solutions is best suited to allow this type of report? Restoring backups of each of the division's database and then querying and combining the results Running reports from each division's operational system and then using Excel to combine them Implementing a statistical analysis of each division's operational data for each month Implementing a data warehouse system that combines the data sources from all the divisions
Implementing a data warehouse system that combines the data sources from all the divisions
To create a simple Database in SQL Server 2019 involves the following steps. Right-click on Database, Open SSMS, Click New Database, Enter Database Name, OK Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK Open SSMS, Click New Database, Right-click on Database, Enter Database Name, OK Right-click on Database, Open SSMS, Click New Database, Enter Database Name, OK
Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK
Which of the following can be used to maintain entity [i.e., table] integrity? Alter Table Constraint Index Constraint Primary Key Constraint View Constraint
Primary Key Constraint
You are designing a database for a healthcare software in Canada. The healthcare software has a field [AdmitDate] that will be used to store the date and time that a patient is admitted to a hospital. What data types should you use for this field? Choose all that apply. Hint: there are 2 correct answers Use the Char (10) data type Use the DATETIME Use the SMALLDATE Use the Varchar (10) data type
Use the DATETIME Use the SMALLDATE
You are designing a database that requires users to write their own SELECT queries against the database from different applications. However, management has determined that users must not directly access the database. As a database engineer, you are tasked to create database objects that are based on tables that will allow the users to write their queries. What database objects should you use? Indexes Check Constraints Views Table Functions
Views
Dimensions that contain attributes that can change are called ____________________. type changing dimensions attribute changing dimensions fast changing dimensions slow changing dimensions
slow changing dimensions
