BCIS 4660 - Exam 1

One of the following is NOT a type of index.

Foreign Index

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 2

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

Use the DATETIME

Microsoft Access is a multi-user RDBMS

False

Data warehousing can be implemented and used in these market areas .... Choose all that apply.

-Manufacturing Logistics -Retail -Financial services

The following are attributes in a fact table. Select all that apply.

-Unit Price -Units Sold

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

Match each ACID acronym with the correct description.

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

Match the following SQL Statements:

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

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

DCL

Operational database design is based on ___________, and data warehouse design is based on __________________.

ER Modeling, Dimensional Modeling

A database table can have a maximum of 1026 columns

False

To create a simple Database in SQL Server 2019 involves the following steps.

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

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

True

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

True

A SQL statement that alters a table is generally called _____.

DDL

In normalization, there are four main normal forms

False

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

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

1

Which of the following best describes an orphaned record.

A record whose foreign key points to a non-existing primary key value

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 INT NOT NULL , EUID CHAR (6) NOT NULL, FirstName VARCHAR (50) NOT NULL, LastName VARCHAR (50) NOT NULL, ..... )

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

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?

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

Which of the following is the default data source connection manager for SQL Server Database?

OLE DB

Match the following concepts to their associated descriptions.

SCD Type 1- Changes the value in the dimension 's record with no preserved history SCD Type 2- Creates a new record using a new value SCD Type 3 - Creates a new column using previous and current

Match the following concepts to the correct description.

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

Some objective of business intelligence solutions are:

-Easy access to data that provides managers with the ability to conduct analysis and make decisions-A consolidated historical data repository that supports strategic decision making-A consolidated historical data that facilitates decision making

A database table can have a maximum of ____ columns

1024

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

265 45821

How many columns will Student Entity have? (image0)

3

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

3

Observe the ER Diagram for a purchase order database application. How many tables will the relational database that is created from this ERD have? (image1)

5

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

A function that returns the number of items in a group, usually in integer is known as _______.

Count

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

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?

Decimal

Match the following 4 step processes to their correct descriptions.

Define the Business Process - Action verbs derived from the business activities Define the grain - this determines the lowest level of detail of each activity Define the dimensions - It answers the question, "How do we describe the business process event?" Define the fact - It asks the question, "How do we measure the business process?"

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

Which of the following is NOT a possible cardinality constraint?

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 dimension table serves as a PK

Which of the following is not a database object?

Transaction

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

True

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

True

An SSIS package typically contains _____________, _____________, and __________.

data flow, control flow, connection manager

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 databased privileges

Which of the following correctly describe the relationship between Data warehouse and Business intelligence?

DW is a subset of BI

The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operation databases is known as:

ETL

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

Easy access to data that is current detailed and always modifiable

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

False

In dimensional modeling, a table that contains descriptions of a Product, Store, or Customer is known as fact table.

False

Which of the following can be used to maintain referential entity integrity?

Foreign Key

Which of the following column names would not appear in a fact table?

ProductName

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

Match the following:

Subject Oriented: DW data is specific to specific area Integrated: DW is created by combining data from many sources Time Variant: DW identifies data by using time periods Non-volatile: Data is not deleted or erased

The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated

Type 3

You are a database developer for Pharagon Inc. You have been asked to discard and drop a table[StudioRate] that is no longer of any use. Write the SQL Syntax that completes this task.

DROP TABLE StudioRate

In the DW/BI Architecture, in which of the following stages or components does dimensional modeling occur?

Data warehouse

Which of the tools in SQL Server Management Studio allows one to visualize an ER Model or relationships between tables?

Database Diagram

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

Dimension tables

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

data definition, data manipulation, and data control

Dimensions that contain attributes that can change are called ____________________.

slow changing dimensions

The Slow Changing Dimension task is part of Control Flow

False

The Star Schema is NOT the default schema for dimensional modeling

False

Which of the following can be used to maintain entity [i.e., table] integrity?

Primary Key Constraint

Match the following Data Modeling Technique:

You have users who are not interested in writing queries with lots of joins: Dimensional modeling (OLAP) You support customer service representatives who need access to current data: Entity relationship model (OLTP)

The dimensional schema is often referred to as the:

Star schema