IT 775 Exam 3 UNH

what is not a purpose of a view?

A convenient way to hold intermediate results for a session

What is the purpose(s) of a view?

A convenient way to invoke frequently used complex queries A convenient way to implement external schemas for end users A layer of security by granting users access only to views, but not underlying tables

A table that is persistent, permanent.

Base

SQL recognizes several types of tables. What are the table(s) below it would recognize?

Base Tables, Temporary (working) Tables, Views

The following is correct syntax for a defining a View...

CREATE VIEW viewname [(attrlist)] AS SELECT ...

What is it called when a Transaction succeeds and database reaches a new consistent state?

Commit

DBMS software is used for:

Creation of databases Manipulation of the data in the databases (i.e. insertion, storage, retrieval, update and deletion) Maintenance of databases Creating front-end applications (in some DBMS packages)

Used to create the components of the database e.g. database tables, referential integrity constraints connecting the created tables

Data definition component

Used to insert, read, update, and delete information in a database

Data manipulation component

multiuser systems

Data manipulation component used by multiple users at the same time

Used to provide access to the data warehouse for users who are engaging in indirect use

Data warehouse front-end (BI) applications

reverts the information to its original state

Decryption Key

The infrastructure that facilitates the retrieval of data from operational databases into the data warehouses

ETL Infrastructure

The main goal of Database Normalization is to restructure the logical data model of a database to...

Eliminate redundancy Organize data efficiently Reduce the potential for data anomalies

The term enterprise-wide refers to the fact that the data warehouse provides an organization-wide view of the analytically useful information it contains

Enterprise-wide

When two columns (or sets of columns) that functionally determine each other determine other columns

Equivalent

ETL includes the following tasks:

Extracting analytically useful data from the operational data sources Transforming such data so that it conforms to the structure of the subject-oriented target data warehouse model (while ensuring the quality of the transformed data) Loading the transformed and quality assured data into the target data warehouse

True or False: In general, database relations are normalized to 7NF in order to eliminate unnecessary data redundancy and avoid update anomalies..

False

True or False: Normalization to should be done judiciously and pragmatically, however if done correctly you will never need to deliberately denormalize a database

False

Phase 1 - transaction acquires all necessary locks...

Growth Phase

The term ________ refers to the larger time horizon in the data warehouse than in the operational databases

Historical

Analytical Data Technical Differences

Large Amounts used in a Process Low/Modest frequency of Access Read (and Append) Only Redundancy not an Issue

Lock holder with exclusive access wants to update to have shared access

Lock Demotion

Lock holder with shared access wants to update to have exclusive access

Lock Promotion

Recovery log

Logs database updates Ensures against loss of updates

What is it called when Successfully completed update is overridden by another user?

Lost update problem

A temporary (working) table is...

NOT permanent and will NOT be there for next session.

the information collected and used in support of day to day operational needs in businesses and other organizations

Operational information (transactional information)

Checkpoint

Part of a recovery log Indicates a point when updates are written on the disk

During Normalization, which Functional Dependencies are used?

Partial functional dependency Full key functional dependency Transitive functional dependency

Securing the database against unauthorized access

Preventing unauthorized access to data Using methods such as: Authentication Access privileges Encryption

Lock holder shares access with other lock holders, may not modify data (Shared Lock)

Readlock

Phase 2 - transaction releases lock as it finishes with item...

Shrinking Phase

Operational Data Technical Differences

Small Amounts used in a Process High frequency of Access Can be Updated Non-Redundant

are operational databases and other operational data repositories (in other words, any sets of data used for operational purposes) that provide analytically useful information for the data warehouse's subjects of analysis

Source Systems

Data warehouse components

Source systems Extraction-transformation-load (ETL) infrastructure Data warehouse Front-end applications

Independent data mart

Stand-alone data mart, created in the same fashion as the data warehouse Independent data mart has its own source systems and ETL infrastructure

A table that is used to hold intermediate results for a session. It is useful for debugging SQL queries. It is transient and does not persist beyond the session so there is no need to drop tables after use to cleanup.

Temporary (working)

Every operational data store that is used as a source system for the data warehouse has two purposes:

The original operational purpose As a source system for the data warehouse

Transactions use locks because...

They deny access to other transactions and so prevent incorrect updates It is the most widely used approach to ensure serializability They prevent another transaction from modifying an item or sometimes even reading it

When an attribute (or a set of attributes) functionally determines itself or its subset

Trivial

Operational Data Makeup Differences

Typical Time-Horizon: Years Detailed Current

Analytical Data Makeup Difference

Typical Time-Horizon: Years Summarized (and/or Detailed) Values over time (Snapsho

Occurs when one transaction can see intermediate results of another transaction before it has committed?

Uncommitted Dependency Problem

A table that is virtual (derived). It is created by executing a named stored query (called a NAMED SELECT). It is automatically created or updated for each reference and materialized whenever DBMS wants (at query or update) often used to support external schemas for various specific users.

View

Lock holder has exclusive access, can modify data (Exclusive Lock)

WriteLock

The data warehouse is...

a structured repository of integrated, subject-oriented, enterprise-wide, historical, and time-variant data

You can use a view name like...

a table name

Does not have its own source systems The data comes from the data warehouse

dependent data mart

A data warehouse can store

detailed and/or summarized data

information scrambling algorithm

encryption key

Source systems can include...

external data sources

An Update Operation can be a collective term for...

insert, delete and modify operations

Update anomalies are anomalies in relations that contain redundant (unnecessarily repeating) data, caused by update operations, They occur when?

manipulating data about one real-world entity requires the same manipulation of data about another real-world entity

Analytical information is based on...

operational (transactional) information

When a column of a relation is functionally dependent on a component of a composite primary key. Only composite primary keys have separate components, while single-column primary keys do not have separate components, so this dependency can occur only in cases when a relation has a composite primary key

partial functional dependency

A view is...

permanent and will be there for next session

recovering the content of the database after a failure

recovery

The operational data sources include...

the databases and other data repositories which are used to support the organization's day-to-day operations

An Update Operation can be a synonym for...

the modify operation

The purpose of the data warehouse is...

the retrieval of analytical information

A "Functional Dependency" occurs when...

the value of one (or more) column(s) in each record of a relation uniquely determines the value of another column in that same record of the relation

The term _________________ refers to the fact that a data warehouse contains slices or snapshots of data from different periods of time across its time horizon

time variant

When creating a view, if an attribute list is not included, the default is...

to use the names of the attributes from the select list

A view can be used for...

updates and inserts (but there are restrictions)

What is it called when transaction reads several values but second transaction updates some of them during execution of first?

Inconsistent Analysis Problem

Data anomalies are inconsistencies in the data stored in a database as a result of an operation such as update, insertion, and/or deletion. Such inconsistencies may arise when have a particular record stored in multiple locations and not all of the copies are updated.Such anomalies can be prevented by implementing...

9 different levels of normalization called Normal Forms (NF)

What is it called when a Transaction fails and database must be restored to the consistent state before it started. Such a transaction is rolled back or undone, it can be restarted later?

Abort

a Transaction...

Action, or series of actions, carried out by user or application, which reads or updates contents of database Logical unit of work on the database Transforms database from one consistent state to another, although consistency may be violated during the transaction

the information collected and used in support of analytical tasks

Analytical information

Used to develop front-end applications

Application development component

A functional dependency that contains an existing functional dependency. It does not add new information to what is already described by the existing functional dependency

Augmented

The Normalization Process involves which steps?

Examine each table and verify it satisfies a particular normal form If a table satisfies a particular normal form, then verify if that relation satisfies the next higher normal form If a table does not satisfy a particular normal form, actions are taken to convert the table into a set of tables that satisfy the particular normal form

True or False: When converting from 1NF to 2NF you Identify the Primary Key for the 1NF Relation, Identify the Functional Dependencies in the Relation, and if Partial Dependencies exist on the Primary Key you remove them by placing them into a new Relation along with a copy of their determinant

False

Two main reasons for the creation of a data warehouse as a separate analytical database?

The performance of operational day-to-day tasks involving data use can be severely diminished if such tasks have to compete for computing resources with analytical queries It is often impossible to structure a database which can be used in an efficient manner for both operational and analytical purposes

When nonkey columns functionally determine other nonkey columns of a relation. The nonkey column is a column in a relation that is neither a primary nor a candidate key column

Transitive Functional Dependency

When "Streamlining Functional Dependencies", not all functional dependencies need to be depicted. Which of the following types of functional dependencies can be omitted?

Trivial functional dependencies Augmented functional dependencies Equivalent functional dependencies

True or False: When converting from 2NF to 3NF you Identify the Primary Key for the 2NF Relation, Identify the Functional Dependencies in the Relation, and if Transitive Dependencies exist on the Primary Key you remove them by placing them into a new Relation along with a copy of their determinant

True

Login procedure using user ID and password

authentication

saving additional physical copies of the data

backup

A view is used just like a...

base table

Database Systems manage concurrency using locks on DB resources (e.g., tables, rows, columns, etc.), where the granularity of the locks is a tradeoff. Many small specific locks maximize?

concurrency and fewer coarse locks minimizes transaction management overhead Table or page level locking is a common compromise

Used for technical, administrative, and maintenance tasks of database systems

data administration component

A data store based on the same principles as a data warehouse, but with a more limited scope

data mart

When converting from UNF to 1NF you...

nominate an attribute or group of attributes to act as the Primary Key for the unnormalized table, identify the repeating group(s) in the unnormalized table which repeats for the key attribute(s), and then remove the repeating group by either entering appropriate data into empty columns of rows containing the repeating data ('flattening the table') or by placing the repeating data along with a copy of the original key attributes into a separate relation

A typical organization maintains and utilizes a number of?

operational data sources

A temporary (working) table does NOT automatically...

reflect updates to source tables (at least limited updates).

A view automatically ..

reflects updates to source tables (at least limited updates).

As normalization proceeds, the relations become progressively more...

restricted (stronger) in format and also less vulnerable to update anomalies

A data warehouse is developed for the _________________________ and it is not meant for direct data entry by the users

retrieval of analytical information

A data warehouse is created within an organization as a?

separate data store whose primary purpose is data analysis

In the "Two Phase Locking Protocol", Phase 1 is the _____ phase and Phase 2 is the ______ phase

shrinking, growth

Data manipulation component used by one user at a time

single user systems

When working with Functional Dependencies, particularly during "Streamlining", all dependencies DONT need...

to be depicted/listed

When a primary key functionally determines the column of a relation and no separate component of the primary key determines the same column

Full Key functional dependency

Locking - Basic Rules...

If transaction has shared lock on item, can read but not update item If transaction has exclusive lock on item, can both read and update item Reads cannot conflict, so more than one transaction can hold shared locks simultaneously on same item Exclusive lock gives transaction exclusive access to that item Some systems allow a transaction to upgrade read locks to an exclusive lock, or downgrade an exclusive lock to a shared lock

Analytical Data Functional Differences

Used by a narrower set of users for decision making Subject Oriented

Operational Data Functional Differences

Used by all types of employees for tactical purposes Application Oriented

Normal Forms are...

additive and progressive, You can NOT jump to any level desired without complying to the prior lower levels