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
