Database Systems Final Exam

encryption key

information scrambling algorithm

Delete Set-to-Null

option allows a record to be delete if its primary key value is referred to by a foreign key value returns it to a null value

Update Restrict

option does not allow a record to be updated if its primary key value if its primary key value is referred to by a foreign key

Not Null Constraint

is only a column constraint and not a table constraint; if you don't specify a _____, the column will accept null values by default

Select Statement

is the basic building block of any data query request. It is command that pulls specific data from a particular table within the database highlighted

derived column

is the result of a calculation and is created with a SELECT --- clause expression that is something other than a simple reference to the column

Order By

is used to sort by data in a particular column or columns

Limit Statement

it limits the number of rows that are shown

authentication

login procedure using user ID and password

recovery log

logs database update & ensures against loss of updates

Big Data

massive volumes of diverse and rapidly growing data that are not formally modeled 3 V's: volume, velocity, and variety

Cross Joins

returns all rows from the first table in which each row from the first table is combined with all rows from the second table

Left (or Right) Join

returns all rows from the left (or right) table, even if there are no matches in the right (or left) table

Left

returns left n characters of a string

NOW

returns the current date and time

current_date/time

returns the current date or current time

age

returns the difference between two date/time columns

max

returns the maximum value in a column

min

returns the minimum value in a column

count

returns the number of times a column appears

count distinct

returns the number of unique columns in a array

Position

returns the position of a given string

decryption key

reverts the informations to its original state

rollback

rolls back all the updates since the last commit

ILIKE

same as LIKE statement but not sensitive to capitalization

Extract

same as date_part

view materialized

saving a view as an actual physical table

backup

saving additional physical copies of the data

encryption

scrambles data so that information becomes unreadable unless someone has an encryption key

Inner Join

selects all rows from both tables as long as there is a match between the columns in both tables

Full joins

selects all rows from both tables even if there is no match

centralized approach

single central copy of the master data is used by operational information systems

natural joins

special case of inner join; compares all the columns in one table with corresponding columns that have the same name in the other table

hybrid approach

central master data copy while individual operational systems may contain their own copies of the master data as well

CAST

changes the data type of a column in the query

Concatenating Strings

combining strings together

make_date/time

creates a date or time

Substring

is any sequence of contiguous characters from the source string

%

wildcard placeholder for text used with LIKE or ILIKE statements

SQL is used for:

-creating databases -adding, modifying and deleting database structures -inserting, deleting and modifying records in databases -querying databases

query optimizer

DBMS feature that identifies the possible ways to execute the query and chooses the fastest query plan

Intersect

combines the results of two SELECT statements that are union compatible by listing every row that appears in the result of BOTH the SELECT statements

Except

combines the results of two queries into a single result that has the rows that belong to only the first query

commit

causes all the updates to be recorded on the disk

Where

It's possible to use the WHERE syntax instead of ON

Mid

Returns the characters n from the left and o from the right

Length

Returns the length in characters of a string

Keywords

These have a very specific meaning in the language. The statement must have at least one. These often define the operation that is performed

Identifiers

These refer to the names of the tables and columns that are called or manipulated

Set Operations

UNION, INTERSECT, & EXCEPT used to combine the results of two or more SELECT statements that are union compatible

Mapreduce

a computation framework for dealing with Big Data

master data management

a data governance initiative

retrieval of analytical information

a data warehouse is developed for the retrieval of analytical information and is not meant for direct data entry by the users

corrective data quality actions

actions taken to correct the data quality problems

preventive data quality actions

actions taken to preclude data quality problems

sum

adds the values in a column

Check Constraint

allows for limiting a particular column based on a particular value rules These include: minimum or maximum value specified value range of values

Like

allows to find similar text, but is case sensitive

Joins

allows you to combine data from two relational tables for additional insights

access privileges

assigned to the database user account

master data

authenticated quality version of the key data that provides a common point of reference for the organization's information systems

avg

calculates the average of a column

Where

can be used in combination with boolean comparators for columns that are numeric or integers

data manipulation forms & search forms

can be used to delete and update items as well

Between

can be used to find a range instead of using > and <

NOT

can negate IN, Between, LIKE or conditional statement

NOSQL

databases that is not only based on relational model

registry

dispersed master data connected via a central master data registry

Lower

displays column as lower case

INITCAP

displays column as proper case

Upper

displays columns as UPPER case

Select Distinct

eliminates duplicate values

data entry forms

enable data input and retrieval for end users

complete mirrored backup

ensures against complete database destruction

IN

equivalent to multiple OR statements

query optimization

examining multiple ways of executing the same query and choosing the fastest option

Unique Constraint

forces each value of a column or table to be unique

data governance

formally regulating how, when and by whom the organizational data and metadata are created, stored, updated and archived

Having

if you want to use a filter that contains an aggregate function, you can't use WHERE

authorization matrix

implements the access privileges provided by the DBMS but managed by the DBA

referential integrity constraint

in each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null

Enterprise Resource Planning

integrated information system

SQL

is a domain-specific programming language designed for managing and communicating with data held in a relational database management system

Null Value

is a marker for data that has not been entered or is missing, unknown, or inapplicable

nested subquery

is a type of subquery where the outer query uses the results of the inner query to select data

detailed and/or summarized data

may include the ____ or ____ or both a data warehouse that contains the data at the first level of detail is the most powerful

Index

mechanism for increasing the speed of data search and data retrieval on relations with a large number of records

Delete Cascade

option allows a record to be deleted if its primary key value is referred to by a foreign key value

Delete Set-to-Default

option allows a record to be deleted if its primary key value is referred to by a foreign key value allows you to pick the number

Update Cascade

option allows a record to be updated if its primary key value is referred to by a foreign key value

update set-to-default

option allows a record to be updated if its primary key value is referred to by a foreign key value allows you to set the number

Update Set-to-Null

option allows a record to be updated if its primary key value is referred to by a foreign key value sets value to null

Delete Restrict

option does not allow a record to be deleted if its primary key value is referred by a foreign key

query hint

overrides the default behavior of the query optimizer

checkpoint

part of a recovery log indicates a point when updates are written on the disk

reports

present the data and calculations on the data from one or more tables from the database in a formatted way

ALTER table

provides the ability to: -add/drop a column -alter a column's data type -rename a column -rename a table -add, alter or drop a column's default value or null ability constraint -add, alter or drop column or table constraints such as primary key, foreign key, unique and check constraint

Date_Part

pulls out a specific date part from a date

Right

pulls the right n characters of a string

recovery

recovering the content of the database after a failure

time variant

refers to the fact that a data warehouse contains slices or snapshots of data from different periods of time across its time horizon with the data slices, the user can create reports for various periods of time within the time horizon

enterprise wide

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

subject orientated

refers to the fundamental difference in the purpose of an operational database system and a data warehouse A data warehouse is developed to analyze specific business subject areas whereas an operational database supports specific operations

Trim

removes trailing and leading blank spaces

COALESCE

replaces NULL values with another value

replace

replaces value 1 with value 2 in a column

uniqueness

requires each real-world instances to be represented only once in the data collection

data steward

responsible for the proper use of the data in database

data custodian

responsible for the technical aspects of data management and use, such as protection, transport, and storage of data

Group By

returns a single value for each value in the column(s) specified in the ____ clause

catalog

the data dictionary created by the DBMS

integrated

the data warehouse integrates the analytically useful data from the various operational databases refers to this process of bringing the data from multiple data sources into a singular data warehouse

structured repository

the data warehouse is a database containing analytically useful information any database is a structured repository with its structure represented in its metadata

completeness

the degree to which all the required data is present in the data collection

timeliness

the degree to which the data is aligned with the proper time window in its representation real world

accuracy

the extent to which data correctly reflects the real-world instances it is supposed to depict

conformity

the extent to which the data conforms to its specified format

consistency

the extent to which the data properly conforms to and matches up with the other data

analytical information

the information collected and used in support of analytical taks

operational information

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

query cost

the time length of execution

Date_Trunc

truncates date/time to specified interval

Data Control Language (DCL)

used for data access control

Transaction Control Language

used for managing database transactions

Union

used to combine the union compatible results of two SELECT statements by listing all rows from the result of the first SELECT statement and all rows from the result of the other SELECT statement

Data Definition Language (DDL)

used to create and modify the structure of the database example: CREATE ALTER or DROP

application development component

used to develop front-end applications

Data Manipulation Language (DML)

used to insert, modify, delete and retrieve data example: INSERT INTO UPDATE or DELETE

Data Retrieval Language (DRL)

used to query or retrieve data from a database example: SELECT

Subqueries

when you embed one SELECT statement inside another