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