BCIS 4660 Activity Quizzes

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

Foreign Key

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

TRUE

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

*DDL (CREATE, ALTER)

*DML (SELECT, UPDATE) -manipulate data

What are some T-SQL Clauses? *allows you to filter/customize queries

-construct queries ex. FROM, WHERE, HAVING, ORDERBY, GROUPBY

DDL (Data Definition Language)

-creating and altering database objects -CREATE, ALTER, DROP

What are some T-SQL Functions?

-does calculations ex. COUNT, MIN, MAX, SUM, AVG

DCL (Data Control Language)

-grant or revoke database privileges

DML (Data Manipulation Language)

-manipulate data -UPDATE, SELECT, INSERT

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

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

Dimension tables

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

ETL extract, transform, load

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

Easy access to data that is current detailed and always modifiable

In normalization, there are four main normal forms

FALSE

A database table can have a maximum of 1026 columns

FALSE -the maximum number of tables that a database can have is 1024

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

DateTime -goes back 268 years -1753-9999

SmallDateTime -goes back 121 years -1900-2079

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

TRUE

The what, who, when, and where are questions that help us determine the dimension tables

TRUE

Examples of Database Objects:

Tables, Views, Indexes

What are some T-SQL Predicates?

-results in a boolean(T/F) ex. BETWEEN, IF EXISTS, IN, LIKE, CONTAINS, NULL, NOT NULL

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

COUNT

[AdmitDate] that will be used to store the date and time

SMALL DATE DATETIME

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

Sources, ETL, and DW

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(*ROW*) whose foreign key points to a non-existing primary key value

ACID

A-atomicity *all works or nothing works C-consistency I-isolation *results are only visible after completed D-durability *changes are permanent

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

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 MANY TO MANY

Microsoft Access is NOT a multi-user RDBMS

MySQL is a multi-user RDBMS

Which of the following is NOT a possible cardinality constraint?

Optional zero -if zero it is not an option

Which of the following is NOT a database object?

Transaction

Char -fixed number of strings ex. social security format

Varchar -variable character strings ex. names

-write their own SELECT queries -users must not directly access the database. What database objects should you use?

Views

Business Intelligence/ DW...

are NOT modifiable

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

data definition(DDL), data manipulation(DML), and data control(DCL)

The dimensional schema is often referred to as the:

Star schema

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;

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

DW is a subset of BI

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 can be used to maintain entity [i.e., table] integrity?

Primary Key Constraint

The following are examples of attributes of a fact table.

amount sold quantity units sold

Which of the following are NOT valid values for the TinyInt data type?

anything over 255 TinyInt = 0-255

Dimensions that contain attributes that can change are called...

slow changing dimensions