BCIS 4660- Exam 1 Guide

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

DATETIME and SMALLDATE

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

DCL

A SQL statement that alters a table is generally called

DDL

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

False

In normalization, there are four main normal forms

False

Microsoft Access is a multi-user RDBMS

False

The Slow Changing Dimension task is part of Control Flow

False

One of the following is NOT a type of index

Foreign Index

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

Foreign Key

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

Current; granular

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

False

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

265, 458221

How many columns will Student Entity have?

3

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 orphaned record

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

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 ACID acronyms

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 transition is only visible after it is committed Durability: When a transaction 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 SQL statements

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

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

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

Data cannot be deleted

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

Data warehouse

Operational database design is based on ___, and data warehouse design is based on __.

ER Modeling, Dimensional Modeling

In the DW/BI Architecture, which of the following components represents data cleaning and loading?

ETL

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

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 the default data source connection manager for SQL Server Database?

OLE DB

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

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

Which of the following is NOT a possible cardinality constraint?

Optional zero

Match

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

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

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

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

True

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

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

Choose all that apply. The following are examples of attributes of a fact table

Units Sold, Amount Sold, Quantity

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

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

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

Match the 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)

Three main types of SQL statements used for database operations are.....

data definition, data manipulation, data control

An SSIS package typically contains ___, ____, and ____.

data flow, control flow, connection manager