Exam 1 BCIS 4660

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? Views Check Constraints Indexes Table Functions

Views

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 3 Type 2 Type 4

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 2 Type 3 Type 1 Type 0

Type 3

Of the following, which one is the encoding standard for string characters in SSIS/ETL? DT_Text SQL Server Data Types ASCII Unicode

Unicode

The main difference between nvarchar and varchar is _______________________. varchar is unicode and nvarchar is not varchar and nvarchar can both store unicode data nvarchar is unicode and varchar is not neither varchar and nvarchar can store unicode data

nvarchar is unicode and varchar is not

The Import and Export Wizard allows us to load data between different sources and destinations outside of SSIS. True False

True

The dimensional schema is often referred to as the: Fact table Star schema Dimension schema Dimension table

Star schema

When a 1:M relationship is mapped, the foreign key must appear in the relation mapped from the M side True False

True

Which of the following is not a database object? Table Transaction Index Views

Transaction

A data warehouse implementation is a recognition that there is potential business value in organizational data. True False

True

Operational data is known for being ________; while Data warehouse data is known for being ________________. Historical; not usually modifiable Current; granular Historical; modifiable Current; modifiable

Current; granular

Which of the following contains measures related to the subject of analysis? Fact tables Dimension tables Both dimension and fact tables Neither dimension nor fact tables

Fact tables

Which of the following is the default data source connection manager for SQL Server Database? Excel ODBC ADO.Net OLE DB

OLE DB

The Slow Changing Dimension task is part of Control Flow True False

False

There is no real difference between the development and administration of ETL True False

False

When one is designing a data transformation task, it is usually a best practice to retain the default data types that have been suggested by SSIS. True False

False

Dimensional modeling is a modeling technique used for designing data in OLTP. True False

False (OLAP)

Manipulating files in SSIS requires the use of the ____________task? FTP File system Bulk file insert File configuration

File system

Which of the following are grouping containers in SSIS? For loop For while Sequence For each loop

For loop Sequence For each loop

A report is a written document that presents business information in a structured format. True False

True

A SQL statement that alters a table is generally called _____. DDM DDL DML DCL

DDL

Match each of the following languages with the correct description. DDL Statements used for creating and altering database objects DML Statements used for manipulating data DCL Statements used for granting or revoking database privileges

DDL Statements used for creating and altering database objects DML Statements used for manipulating data DCL Statements used for granting or revoking database privileges

Microsoft Access is a multi-user RDBMS True False

False

SSIS/ETL can be used to import and export data. However, SSIS cannot be used to export data to a CSV file. True False

False

One of the following is NOT a type of index. Unique Index Clustered Index Nonclustered Index Foreign Index

Foreign Index

Which of the following can be used to maintain referential entity integrity? Foreign Key Primary Key Join View

Foreign Key

Which of the following are NOT valid values for the TinyInt data type? Choose all that apply 265 45821 30 250

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 3 4 115

3

Match each ACID acronym with the correct description. Atomicity ; All tasks in a transaction are performed or none is performed Consistency; A transaction must always keep the database in a stable state Isolation; The result of a transaction is only visible after it is committed Durability; When a transaction change is committed, the change is permanent

Atomicity ; All tasks in a transaction are performed or none is performed Consistency; A transaction must always keep the database in a stable state Isolation; The result of a transaction is only visible after it is committed Durability; When a transaction change is committed, the change is permanent

It is impossible to create multiple SSIS packages in an SSIS Solution Project. True False

False

A SQL statement that rolls back a transaction is generally referred to as a ________. DDL DML DCL JBL

DCL

A SQL statement that alters a table is generally called _____. DCL DML DDM DDL

DDL

Which of the following tasks can the File System task perform? Delete folder Copy file Create folder Delete file

Delete folder Copy file Create folder Delete file

You are designing a DW table, and desire that the [TotalPrice] column should be calculated from multiple other columns. Which of the following SSIS tasks is best suited for performing the calculation and loading the [TotalPrice] column? Derived column in the data flow tab Aggregate in Derived column in the control flow tab Aggregate in Derived column in the data flow tab Derived column in the control flow tab

Derived column in the data flow tab

BI Reports can be published or disseminated to users through the following means. Select all that apply. Email Internet Intranet Print

Email Internet Intranet Print

What is the main difference between the Execute SQL Task and the Execute T-SQL Statement? Execute SQL Task performs more SQL tasks than the Execute T-SQL statement Execute SQL Task is located in the data flow tab Execute T-SQL Statement performs more SQL tasks such as executing a stored procedure Execute T-SQL Statement only works with Oracle data sources

Execute SQL Task performs more SQL tasks than the Execute T-SQL statement

A business report can only get its source data from a Data warehouse database. 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 Implementing a statistical analysis of each division's operational data for each month Running reports from each division's operational system and then using Excel to combine them

Implementing a data warehouse system that combines the data sources from all the divisions

Data warehousing can be implemented and used in these market areas .... Choose all that apply. Logistics Financial services Retail Manufacturing

Logistics Financial services Retail Manufacturing

In which type of Cardinality relationship does one require a junction table? M:N 1:M 1:1 M:1

M:N

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 Right-click on Database, Open SSMS, Click New Database, Enter Database Name, OK Open SSMS, Click New Database, Right-click on Database, Enter Database Name, OK Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK

Open SSMS, Right-click on Database, Click New Database, Enter Database Name, OK

Which of the following is NOT a possible cardinality constraint? Optional many Mandatory many Optional zero Mandatory one

Optional zero

A scorecard is mainly used for displaying ___________, while a dashboard is used to displaying _______________. Progress, surrogate keys Performance, presentation Performance, Progress Progress, performance

Performance, Progress

A relational database is a database structure that allows objects to have relationships with other objects in the database. True False

True

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 Varchar (10) data type Use the DATETIME Use the SMALLDATE Use the Char (10) data type

Use the DATETIME Use the SMALLDATE

Match the following characteristics and modeling technique: Used in operational databases; ER model Used in data warehousing; Dimensional model Normalized tables; ER model De-normalized tables; Dimensional model

Used in operational databases; ER model Used in data warehousing; Dimensional model; Normalized tables; ER model De-normalized tables; Dimensional model

Both data flow and control flow processing is performed in memory. True False

False

Data warehouses do not reveal past trends that can help organization predict and identify new business areas. True False

False

Which of the following best describes an operational database? 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 and stores historical activities in the business 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 both the day-to-day and historical business activities

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? Hint: use your current knowledge of EUID and StudentID requirements CREATE TABLE Student ( StudentID INT (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... ) CREATE TABLE Student ( StudentID CHAR (6), EUID INT, FirstName CHAR (50), LastName CHAR (50), ..... ) 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 INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... )

Match the following SQL Statements to the appropriate type: CREATE TABLE Student DDL SELECT DML UPDATE Student DML ALTER TABLE DDL

CREATE TABLE Student DDL SELECT DML UPDATE Student DML ALTER TABLE DDL

Match the following SQL Statements to the appropriate type: CREATE TABLE Student; DDL SELECT; DML UPDATE Student; DML ALTER TABLE; DDL

CREATE TABLE Student; DDL SELECT; DML UPDATE Student; DML ALTER TABLE; DDL

Match each of the following languages with the correct description. DDL; Statements used for creating and altering database objects DML; Statements used for manipulating data DCL; Statements used for granting or revoking database privileges

DDL; Statements used for creating and altering database objects DML; Statements used for manipulating data DCL; Statements used for granting or revoking database privileges

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 both mean the same thing BI and DW are part of Microsoft Excel

DW is a subset of BI

The term "non-volatile" describes which aspect of a data warehouse? Historical data Data from non stable sources Data from different time-zones Data cannot be deleted

Data cannot be deleted

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

Business reports fulfil many functions. Which of the following is not a function of business reports? Provides infographics of the business Provides business insights Provides results of analysis Provides information to decision makers

Provides infographics of the business

Match the following PowerBI elements to their functions. Report view The canvas where visualizations is designed Data view The data in table format Relationship view A representation of the data model and their cardinalities

Report view The canvas where visualizations is designed Data view The data in table format Relationship view A representation of the data model and their cardinalities

In SQL Server, which software component is used in the development of ETL processes? SQL Server Integration Services SQL Server Reporting Services SQL Server Analysis Server SQL Server Management Studio

SQL Server Integration Services

You are teaching a junior developer how to create a database. Which of the following is not the correct way to create a database in SQL Server. Write a Create Database script Use a template script Script from an existing database Script from a SQL Agent

Script from a SQL Agent

Which of the following is the correct processing order of the DW Architecture? ETL, Sources, and DW DW, BI Apps, and ETL Sources, ETL, and DW Sources, ETL, and BI Apps

Sources, ETL, and DW

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? LastName FirstName StudentID EmailAddress

StudentID

Match the following terms with the correct concept. Subject Oriented DW data is specific to subject areas Integrated DW is created by combining data from many different sources Time variant DW identifies data by using time periods Non-volatile Data is not deleted or erased

Subject Oriented DW data is specific to subject areas Integrated DW is created by combining data from many different sources Time variant DW identifies data by using time periods Non-volatile Data is not deleted or erased

Three main types of SQL statements used for database operations are ____, ____ and ____? data definition, data manipulation, and data committing data manipulation, data control, and data commands data definition, data manipulation, and data control data commands, data manipulation, and data query

data definition, data manipulation, and data control

Three main types of SQL statements used for database operations are ____, ____ and ____? data definition, data manipulation, and data control data commands, data manipulation, and data query data definition, data manipulation, and data committing data manipulation, data control, and data commands

data definition, data manipulation, and data control

An SSIS package typically contains _____________, _____________, and __________. control flow and connection manager only data flow, control flow, connection manager data flow and control flow only data flow and connection manager only

data flow, control flow, connection manager