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