Final Exam BCIS 4660

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

false

Match the following problems with the right data modeling technique. - You have users who are not interested in writing queries with lots of joins - You support customer service representatives who need access to current data

- Dimensional modeling (OLAP) - Entity Relationship Modeling or ERD (OLTP)

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 SMALLDATE - Use the DATETIME

If an entity has three unique attributes, how many primary keys will its mapped relation have?

1

How many entities are depicted by the following requirements? School XYZ keeps track of its 100 students, 10 teachers, and 5 classrooms.

3

Which of the following best describes an operational database?

An operational database is a system that captures all elements of the day-to-day activities of the business

Match each ACID acronym with the correct description. Atomicity Consistency Isolation Durability

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

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 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 ALTER TABLE - DDL SELECT - DML UPDATE student - DML

A SQL statement that rolls back a transaction is generally referred to as a

DCL

A SQL statement that alters a table is generally called

DDL

Match each of the following languages with the correct description. DDL DML DCL

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

The term "non-volatile" describes which aspect of a data warehouse?

Data cannot be deleted

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

Which of the following data warehouse components contains descriptions of the business that the subject (e.g., sales, inventory) belongs to.

Dimension models

One of the following is NOT an objective of business intelligence solution.

Easy access to data that is current detailed and always modifiable

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

ETL is an abbreviation for which of the following processes?

Extraction, transformation, loading

Which of the following contains measures related to the subject of analysis?

Fact Tables

A database table can have a maximum of 1026 columns

False

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

False

Microsoft Access is a multi-user RDBMS

False

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

False

Which of the following are grouping containers in SSIS?

For loop, For each loop, Sequence

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

In which type of Cardinality relationship does one require a junction table?

M:N

Data warehousing can be implemented and used in these market areas ....

Manufacturing, Retail, Logistics, Financial Services

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?

No, she is not wrong. You need an operational database system to build a data warehouse system

Which of the following is NOT a possible cardinality constraint?

Optional zero

In SQL Server, which software component is used in the development of ETL processes?

SQL Server Integration Services

Match the following concepts to the correct description. SSDT/SSIS SSMS

SSDT/SSIS - ETL development environment SSMS - ETL administration environment

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.

Script from a SQL Agent

Which of the following is the correct processing order of the DW Architecture?

Sources, ETL, and DW

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

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

false

Which of the following is not a database object?

Transaction

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

True

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

True

You are designing a table to store demographic information for a genealogy company in the US. The table must store information for the past 250 years. What data types should you use for the [BirthDate] field?

Use the DATETIME

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

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

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

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

false

Operational data is known for being ________; while Data warehouse data is known for being ________________.

current ; granular

Three main types of SQL statements used for database operations are ____, ____ and ____?

data definition, data manipulation, and data control

Which of the following tasks can the File System task perform?

delete file, create folder, copy file, delete folder

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.

false

Manipulating files in SSIS requires the use of the ____________task?

file configuration

The main difference between nvarchar and varchar is

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

Of the following, which one is the encoding standard for string characters in SSIS/ETL?

unicode