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