ISM Test 2

OLAP Operations type

*Cube slicing*-come up with 2-D view of data *Drill-down*-going from summary to more detailed views

How to create a table in SQL with DDL

CREATE TABLE tablename (column_name1 data_type, column_name2 data_type,...)

True or False: In an SQL statement, you use the AND, OR and NOT operators for customizing conditions in the WHERE clause.

True

True or False: Structured Query Lanaguage (SQL) is a programming language used to manage data in relational database management systems (RDBMS).

True

True or False: The ALTER TABLE statement allows you to change column specifications.

True

True or False: The CREATE TABLE SQL statement defines a new table and its columns.

True

True or False: When joining more than two tables, each pair of tables requires a joining condition, matching primary keys against foreign keys.

True

True or False: if you want to join three tables you will need to have two join condition (i.e., equality check) statements.

True

How to do an update statement

UPDATE table_name SET column1 = new value WHERE column1 = "X"

Journalizing Facilities

Audit trail of transactions and database updates i.e. transaction logs and database change logs

primary users of informational systems

BA's and managers

SQL Language Types

1. Data Definition Language (DDL) 2. Data Manipulation Language (DML) 3. Data Control Language (DCL)

What are the clauses of the select statements

1. FROM: Indicate the table(s) or view(s) from which data will be obtained 2. WHERE: Indicate the conditions under which a row will be included in the result 3. GROUP BY: Indicates categorization of results 4. HAVING: indicates conditions under which a category will be included 5. ORDER BY: Sorts the results according to specified criteria

Steps in a table creator

1. Identify columns that are unique (candidate keys) 2. Select the primary key 3. Identify data types for attributes 4. Identify columns that can/cannot be null 5. Identify primary key - foreign key mates 6. Create the table

data mart

A 'mini' data warehouse that is limited in scope, i.e., a slice of the data warehouse.

Relational Database Management System

A DBMS that manages data as a collection of tables in which all relationships are represented by common values in related tables

An inner join is:

A query that compares each row of one table with each row of another table to find all rows of data which satisfy the join condition.

A union join is:

A query that includes all columns from each table in the join, and an instance for each row of each table.

Structured Query Language (SQL)

A special-purpose programming language designed for managing data in RDBMS

Data Warehouse

A subject-oriented, integrated, time-variant, non-updatable collection of data used in support of management decision-making processes

Dynamic SQL

Ability for an application program to generate SQL code on the fly, as the application is running which makes it more flexible

Join

An operation that causes two or more tables with a common field to be combined into a single table or view

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data i.e. grant, add, and revoke

Data definition language: [ Choose ] Commands that control a database. Commands that define a database (creating, altering and dropping tables). Commands that maintain and query a database.

Commands that define a database (creating, altering and dropping tables).

Data Definition Language

Commands that define a database, including creating, altering, and dropping tables and establishing constraints i.e. create and drop and FK's

CIA

Confidentiality, Integrity, Availability

Referential Integrity

Constraint that states that foreign key values of a table must match primary key values of a related table

Multidimensional OLAP (MOLAP)

Cube structure

Recovery Manager

DBMS module that restores the database to a correct condition when a failure occurs and then resumes processing user requests

Which SQL language times are part of physical design

DDL

Which SQL language types are part of maintenance

DDL & DCL

Which SQL language types are part of implantation

DML and DCL

how to do a drop statement for a table or for a row

Drop Table "X" or Drop from Table_X where Y = Z or Delete from X

Logical access controls

Hiring practices, employee monitoring, security training, separation of duties

How to do an insert statement

INSERT INTO TABLE VALUES (x,y,z) *OR* INSERT INTO TABLE (attribute 1, attribute 2, attribute 3) VALUES (x, y, z)

Union Join

Includes all columns from each table in the join, and an instance for each row of each table i.e. all the info in both tables

Embedded SQL

Including hard-coded SQL statements in a program written in another language such as C#.Net or Java Can create a more flexible, accessible interface for the user Possible performance improvement (but not always) Database security improvement; grant access only to the application instead of users

A database schema can be described by all of the following EXCEPT: It's the structure that contains descriptions of the objects created by the user. It's a special purpose programming language. It's the organization of data. It's stored in the data dictionary.

It's a special purpose programming language.

what is data mining and what are the goals

Knowledge discovery using a blend of statistical, AI, and computer graphics techniques Goals: Explain observed events or conditions Confirm hypotheses Explore data for new or unexpected relationships

how to do a select statement

SELECT x FROM table_name

Routines

Program modules that execute on demand

A constraint that states that the foreign key values of a table must match the primary key values of a related table is called:

Referential Integrity

Outer join

Rows that do not have matching values in common columns are still included in the result Can use left outer join or right outer join

Primary Purpose of Operation System

Run the business on a current basis

An example of Explicit Join Notation is: SELECT * FROM employee, department "WHERE employee.DepartmentID = department.DepartmentID SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Neither are

SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID

How to do a count function

Select Count(X) from Y

Database mangement system

Software that handles the storage, retrieval and updating of data in a computer system

Views

Subset of the database that is presented to one or more users (groups) User can be given access to view without allowing access to underlying tables

Primary purpose of Information System

Support Managerial decision making

Physical access controls

Swipe cards, equipment locking, check-out procedures, screen placement, laptop protection

True or False: Explicit Join Notation uses the JOIN keyword to specify the tables to join and the ON keyword to specify the joining condition.

True

of the following statements about an inner join are true EXCEPT: The keyword SELECT must be included in the SQL statement. Rows must have matching values in order to appear in the result. The query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join condition.

The query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join condition.

Inner/Natual Join

The query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join condition. Rows must have matching values in order to appear in the result.

Online Analytical Processing

The use of a set of graphical tools that provides users with multidimensional views of their data and allows them to analyze the data using simple techniques

True or False: In SQL, the INSERT statement adds one or more rows to a table.

True

Relational OLAP (ROLAP)

Traditional relational representation

True or False: Any query that you build can be saved as a view.

True

True or False: In an SQL statement, the wildcard (*) retrieves all rows and columns in a table.

True

When writing SQL queries, identifying the entities (tables) that contain the desired attributes tells you: What to put in the ON or WHERE clause. What to put in the SELECT clause. What to put in the EXECUTE clause. What to put in the JOIN or FROM clause.

What to put in the JOIN or FROM clause.

Data Administration

a high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards; more managerial

Informational system

a system designed to support decision making based on a historical point-in-time and prediction data for complex queries or data-mining applications

Operational system

a system that is used to run a business in real time, based on current data

Database Administration

a technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery; more technical

What does the and, or, not functions do

allow you to include certain conditions in your select statements

What must be true for Referential Integrity to hold

any field in a table that is declared a foreign key can contain only values from a parent table's primary key or candidate key

scope of usage of informational systems

broad and complex queries and analysis

Time-variant

can study trends and changes over time

Data Manipulation Language (DML)

commands that maintain and query a database i.e. insert, update, and select

Integrated

consistent naming conventions, formats, encoding structures; from multiple data sources

volume of usage in operational System

constant updates/queries or a few tables or rows

Dimension tables

contain descriptions about the objects (e.g., products and stores) of the business

Authorization rules

controls that are incorporated into the data management system; authorization rules grant access, specifying which actions and constraints govern the user (or user groups) access.

Type of data in operational System

current state of business

Subject-oriented:

customers, patients, students, products

design goal of informational systems

ease of flexible access and use

Data Warehouse Administration

emphasis on integration and coordination of metadata/data across many data sources, supports decision support applications; build a stable architecture; manage data warehouses and data marts

why are company wide views not common in databases

inconsistent key structures, synonyms item names, and inconsistent data

Boolean data type

int stores truth values from 0 to 1

Implicit Join Notation

lists the tables to join in the FROM clause using commas to separate them, and the WHERE clause specifies the joining condition. ex. SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID

DATABASE RECOVERY

mechanisms for restoring a database quickly and accurately after loss or damage

scope of usage in in operational System

narrow planned and simple updates/queries

design goal in operational System

performance and availability

volume of informational systems

periodic batch updates and queries requiring many or all rows

type of data in informational systems

point in time and predictions

Database Security

protection of the data and database against accidental or intentional loss, destruction, or misuse

Fact table

provides statistics for sales broken down by product, period and store dimensions

Non-updatable

read-only, periodically refreshed

Procedures

routines that do not return values; extensive or complex processing that requires execution of several SQL statements is often moved into a stored procedure, and all applications call the procedure.

Triggers

routines that execute in response to a database event (INSERT, UPDATE, or DELETE)

Functions

routines that return values

Primary users in operational System

sales people, clerks, and administrators

Text data type

stored as char() or varchar() and it Stores string/text characters; fixed length

Number data type

stored as int(whole number) or decimals(exact)

Temporal data type

stores date

Explicit Join Notation

uses the JOIN keyword to specify the tables to join and the ON keyword to specify the joining condition. ex. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID