BCIS Exam 1 Review

Pataasin ang iyong marka sa homework at exams ngayon gamit ang Quizwiz!

5 major constraints

1. Not Null (NN) 2. Unique (U) 3. Primary Key (PK) 4. Foreign Key (FK) 5. Check (CHK)

Purpose of constraints

A constraint is a rule placed on a table or column that restricts operations and data values allowed▪ Enforces rules at the table level▪ Enforces integrity▪ Prevents deletion of tables, if dependencies exist▪ Can be defined during or after table creation

Database management system

A database system is an information or computer system that manages the collection, storage, retrieval of data

File system tasks

Allows different file system tasks (e.g., copy, move, delete file and folders)

PowerBI Filters

Allows you to further edit and streamline your report by adding columns to the filters

A RDBMS processes transactions using the ACID property. What does ACID stand for

Atomicity Consistency Isolation Durability

A in ACID

Atomicity. All tasks performed or none at all

T-SQL Predicates

BETWEEN• EXISTS• IN• LIKE• CONTAINS• IS [NOT] NULL

Database design process

Conceptual design, Logical database design, Physical database design

Fact Tables

Contain measures related to the subject of analysis and the foreign keys (associating fact tables with dimension tables) record the measures of business events (e.g., sales) occurring in an enterprise

SSIS Package

Control Flow(workflow) + Data Flow (Data Transformation) + Connection (Connection to source,destination) = Package

Logical database design

Converting ERDs from previous step to logical relational tables; Normalizing and designing tables

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?

DATETIME

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

What is the difference between delete and drop

Drop is ddl and delete is dml

ER or Dim models are used for operational databases?

ER

The design of operational data is based on...

ER Model

First Normal Form

Every column is atomic, single valued: the column cannot be further divided into sub-columns All attributes are functionally dependent on all or part of the key

Control Flow tasks

Executes SQL Statements, stored procedures Different from Execute T-SQL Statement

E in ETL

Extract. Get data from several different sources (e.g., Excel, SQL Server, Oracle, text files)

T-SQL clauses

FROM • WHERE• GROUP BY• HAVING• ORDER BY

Logical Design for Many to Many relationship

First, a new junction table is created Second, the primary keys from each related table is placed as a concatenated primary key in the new junction table Third, related attributes are added to the junction table

Physical database design

Implementing or physically creating the database, the relational tables, using T-SQL

What is a data warehouse?

In general, a DW is a part of the overall business intelligence system

What data type is the primary key?

Integer

Exact Numerics

Integer, SmallInt, TinyInt, decimal, numeric

I in ACID

Isolation. Result only visible once committed

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

SSIS Architecture. Slide 12 Lec 8

Package: Control Flow Task Data flow

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

Sources, ETL, DW

3 SSIS constraints

Success Complete Failure

Data Visualization

The process of transforming data and metrics into visual charts, graphs, that make is easier for users to understand, make sense of, and gain new insights

Primary key constraint

This constraint ensures that the column is the PK for the table and will not have duplicate values

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

True

T/F decimal and numeric are synonyms?

True

Changing Attribute SCD

Updates column is the source value changes

Normalization

a process of creating database schema. the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database.

the design of data warehouse is based on...

dimensional model

D in ACID

durability. When committed change permanent.

ER or Dim model have normalized tables

er

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

foreign key

Surrogate Key

have no meaning or purpose except to give each dimension a new column that serves as a primary key within the dimensional model instead of the operational key

What kind of data is Acct #

integer

Slowly Changing Dimension

is a dimension that stores and manages both current and historical data over time in a data warehouse

In a star schema, a fact table is on the _____ side of the relationship

many

3 types of cardinalities

one to one, one to many, many to many

To convert a 1:1 relationship:

place the primary key of the one table into the second table as a FK

SQL

structured query language

Which of the following is not a database object?

transaction

What is business intelligence?

umbrella term that combines architectures, tools, databases, analytical tools, applications and methodologies used for decision making

When to use CHAR

when the sizes of the column data entries are consistent (e.g., EUID)

T-SQL Functions

• COUNT• MIN• MAX• SUM• AVG

Features of data warehouse

Subject oriented Integrated Non volatile Time variant

when to use VarChar?

Used when the sizes of the column data entries varies (e.g., FirstName)

What kind of data is FirstName

Var char

what kind of data is address?

Var char

Nonclustered Index

can be defined on a table and the data rows are not in any particular order. Uniqueness is a property of nonclustered indexes

what kind of data is phone number

char

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

data definition, data manipulation, data control

What values does Bit hold?

0, 1, null

Steps for dimensional modeling

1. Op database 2. ER modeling 3. DW database 4. dim model

What does Decimal (5,2) look like?

123.00

Completed SSIS

A linked task executes if the prior task completes, irrespective of whether task succeeds or fails (BLUE)

Success SSIS constraints

A linked task executes only if the prior task completes successfully (GREEN)

Failure SSIS

A linked task executes only if the prior task fails to complete. Typically used for notifications (RED)

Historical Attribute SCD

A new record (row) is created when a source value changes and the old record is preserved

Which of the following best describes an orphaned record.

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

Scorecard business report

A report that shows business goals versus performance measures. Example, financial, customer, and growth indicators Provides managers with information on how well they are doing (performance)

WHAT IS A BUSINESS REPORT?

A written document that presents business information in a structured format It is used for business decision making, gets its data from a DW or other sources, its structure of the report consists of tables, charts, dials, graphs

Second Normal Form

Achieve 1NF ▪ All non key attributes are functionally dependent on the key. This eliminates any attribute that lacks complete dependency to PK

Third Normal Form

Achieve 1NF, and 2NF ▪ Non key attributes must not depend on another non key attribute

Visualizations

Allows you to select visualizations, add values to the visuals, and add columns to the axis

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

Slowly Changing Dimension 1

Changes the value in the dimension's record ▪ Old values are lost ▪ Simple to implement ▪ No history is preserved

what kind of data is SS#

Char fixed

Dimension Tables

Columns in dimension tables contain descriptive information that is often textual Dimensions represent the who, what, where, and when of these business events

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data. Grant revoke callback

Data definition language

Commands that define a database, including creating, altering, and dropping tables and establishing constraints. Create Alter Drop

Data Manipulation Language (DML)

Commands that maintain and query a database. Select Insert, Update, Delete

DML CRUD operations

Create, Read, Update, Delete

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

Current: granular

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

DW is subset of BI

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 warehousing

Article Data Warehouse Design for E-Commerce Environment Talks about?

Data warehousing and e-commerce are two of the most rapidly expanding fields. Paper goes into detail about how to build DW for e-commerce environments

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

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

Easy access to data that is current detailed and always modifiable

Unique key constraint

Enforces unique constraint on a column. This means that there can be no duplicate values for this column of data

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

False

Microsoft Access is a multi-user RDBMS

False

T/F Views store data

False. They create a layer of abstraction between the table and the user

One of the following is NOT a type of index.

Foreign Index

Dashboard

Graphical presentation of several performance indicators in a single page Provides managers with information on what they are doing (progress) A combination of several report types

Conceptual database design

Identifying data elements; planning the metadata, structure; creating Entity Relationship Diagrams (ERD); conceptually connecting the data elements

Logical Design for 1 to 1 relationship

If all attributes of an entity do not logically belong to one entity, consider creating a separate table that corresponds to the second entity Then place the primary key of the one table into the second table

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

L in ETL

Load the data into a target location

Fixed Attribute SCD

No changes are made. ETL ignores changes or generates an error

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 to build a data warehouse

Difference between primary and unique key

PK does not allow NULL Unique key can allow a NULL• Table has only 1 PK Tables can include multiple Unique Keys

PowerBI Fields

Provides the list of tables that were loaded at the onset. Allows you to see and select table fields/columns for report design

PowerBI Benefits

Quick and simple to create reports Data Visualization Report Interactivity Various Data Source Connections Excel (demo)

What shape are entities

Rectangles

PowerBI Views

Report view - This is where you use data and/or queries to build visualizations Data view - This is where you see the data in your report in table format. You can add new measures and create new columns. Relationships view - This shows a graphical representation of the relationships and cardinalities in the data model

SSIS

SQL Server Integration Services Runs in the background

SSIS vs ETL

SSIS is an ETL tool and ETL is an technology in this we Extract the data and transform as per business need and then load it into a destination.

Which of the following is not the correct way to create a database in SQL Server.

Script from a SQL Agent

datawarehouse/bi architecture

Sources(operational) -> extraction, transformation&loading -> data warehouse -> BI/ analytics applications -> BI analysts

Not Null key constraint

This constraint is an unknown value. It restricts a column from being an unknown value. Please note that a NULL value is not blank, zero, empty.

Foreign key constraint

This defines a column as a foreign key (reference). It references to the primary key column of another table. Ensures referential integrity

Operational Datawarehouse

Transactional, day-to-day operations data ▪ Known as Online Transaction Processing (OLTP) Fulfils ACID

T in ETL

Transform. Clean and perform calculation on data into a desired format

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

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

Logical Design for 1 to Many relationship

We place the primary key from the One side as a foreign key to the Many side.

To convert a 1: Many relationship:

We place the primary key from the One side as a foreign key to the Many side.

Regional database management system

a database structure that allows database objects to have relationships with other objects in the database

Entity

anything that captures data

What shape are attributes

circles

C in ACID

consistency. transaction must always keep database in a stable state

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

count

Date V DateTime

date time has 24hr clock

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

Grain

determines lowest level of detail of each activity. determines fact measures is atomic EX one row is sales transaction one is product item

What shape are relationships

diamonds

ER or Dim model have denormalized tables

dim

ER or Dim models are used for data warehousing

dim

Slowly Changing Dimension 2

▪ Creates an new additional record using a new value for the surrogate key every time a value in a dimension record changes ▪ Preserves current and previous value ▪ Used in cases where history should be preserved

To convert a M:N relationship

▪ First, a new junction table is created▪ Second, the primary keys from each related table is placed as a concatenated primary key in the new junction table▪ Third, related attributes are added to the junction table

Slowly Changing Dimension 3

▪ Involves creating a "previous" and "current" column in the dimension table for each column where changes are anticipated (or version number) ▪ Applicable in cases in which there is a fixed number of changes possible per column of a dimension, or in cases when only a limited history is recorded.

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

SMALLDATE and DATETIME would work

Destination Connections

Specifies the location of the destination storage in the Data Flow Task OLE DB Destination (Default for SQL)▪ ADO.Net Destination▪ SQL Server Destination▪ Excel Destination▪ ODBC Destination▪ Raw File Destination

Source Connections

Specifies the location of the source data in the Data Flow Task OLE DB Source (Default for SQL)▪ Excel Source▪ Flat file Source▪ ADO Net Source▪ XML Source▪ Raw File Source

A regional databse consists of what 3 things

Structure(Defined database objects used for storing and accessing the database) Operations(Defined actions that allow users to manipulate the data and the data structures) Rules (Rules that govern the operations performed on the data and data structure)

CamelCase vs PascalCase

pascal case requires the first letter of the first word to also be capitalized so every word starts with an uppercase letter. ie MiloCat

Clustered Index

sorts and stores the data rows of the table in order based on the clustered index key. Uniqueness is a property of clustered indexes

The dimensional schema is often referred to as the:

star schema

Datawarehouse compared to operational dw

▪ Analytical, stores historical data ▪ Known as Online Analytical Processing (OLAP) Used for decision making


Kaugnay na mga set ng pag-aaral

الفصل الثاني / حضارة بلاد الرافدين (العراق)

View Set

lesson 1 module 2: trade-offs and opportunity costs

View Set

Med Surg GI/GU Diabetes Questions

View Set

Insurance and Risk Management Midterm

View Set