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