ISM 4212 Exam 2 - Armstrong
What are the threats of DB security?
1. Accidental losses (human error, software/hardware failure) 2. Theft and fraud 3. Loss of privacy/confidentiality (personal and corporate data) 4. Loss of data integrity (Remaining intact and unaltered) 5. Loss of availability (sabotage, upgrades, disaster recovery)
What are the tips for good queries?
1. Be familiar with data model 2. Understand desired result (what question am i asking) 3. Know what attributes you want (what to put in the SELECT) 4. Identify which entities contain the attributes (what to put in the from clause) 5. Construct a joining condition (what you put in the where phrase)
What are the components of the environment?
1. CASE tools 2. Repository 3. DBMS 4. Application programs 5. User interface 6. Data/DB Administrators 7. System developers 8. End users 9. Actual DB
What are the benefits of embedded SQL?
1. Can create more flexible, accessible interfaces 2. Possible performance improvement 3. DB security improvement (grant access only to application instead of users)
What are the characteristics of business rules?
1. Declarative 2. Precise 3. Atomic 4. Consistent 5. Expressible 6. Distinct 7. Business oriented
What are the goals of data mining?
1. Explain obseved events or conditions. 2. Confirm hypotheses 3. Explore data for new or unexpected relationships.
What are the requirements of an entity?
1. Must have many instances 2. Must have many attributes 3. Must matter to firm
What are the different forms of business intelligence?
1. Operational reports 2. Ad hoc reporting 3. OLAP Analysis 4. Data visualization 5. Dashboards (high level view)
What are some data mining techniques?
1. Regression 2. Clustering and signal processing 3. Decision tree 4. Affinity 5. Sequence association 6. Case-based reasoning 7. Rule discovery 8. Fractals 9. Neural nets
What are the characteristics of a data name?
1. Related to business 2. Meaningful 3. Self-documenting 4. Written in standard syntax 5. Unique 6. Readable 7. Repeatable
What are the database software security features? (7)
1. Views 2. Integrity Controls 3. Authorization Rules 4. Authentication Schemes 5. Encryption 6. Access Controls 7. Backup, journalizing, and check pointing
What is a relational database management system?
A DBMS that manages data as a collection of tables in which relationships are represented by a common values in related tables.
What is referential integrity?
A constraint that states that foreign key values of a table must match primary key values of the related table.
What is a back up?
A copy utility that produces a backup copy of the entire database or subset
What is a data administrator?
A high-level function (manager) responsible for overall management of data resources in an organization including maintaining corporate-wide definitions/standards.
What is a data mart?
A mini data warehouse that is limited in scope. (like a slice of the warehouse)
What is a recovery manager?
A module that restores the DB to a correct condition when a failure occurs and then resumes processing user requests.
What is a relation?
A named, two dimensional table of data.
What is SQL?
A special-purpose programming language designed for managing data in a RDBMS.
What is a data warehouse? Why do we use them?
A subject-oriented, integrated, time-variant, non-updatabale collection of data. To help in support of management decision making processes.
What is journalizing?
A trail of transactions and DB updates.
What function changes tables?
ALTER
How do you add a FK to a table?
ALTER TABLE tblname ADD FOREIGN KEY (blankid) references tblname2(blankid)
How do you add a primary key that's composite?
ALTER TABLE xyz ADD CONSTRAINT xxxxxID primary key (pkname1, pkname2)
What do authorization rules restrict?
Access to data and the actions that people can take on data.
What is an inner join?
Also called a natural join, it compares each row of table A w/ each row of table B to find all pairs of rows which satisfy the condition. Rows must have matching values.
What is data visualization?
An analytical tool used to help people understand the significance of data by placing it in visual forms.
What are triggers?
An integrity control that prevents inappropriate actions, invokes special handling procedures, and writes to log files
What is a domain?
An integrity control that sets allowable values
What is a join?
An operation that causes two or more tables with a common field to be combined into a single table or view.
What is a database?
An organized collection of logically related data.
What are some recovery facilities?
Backup, Journalizing, Recovery Manager, Chekpoint
how do you create a database?
CREATE DATABASE dbname
Give the basic form for making a table in SQL.
CREATE TABLE tblname (columnname1 datatype not null primary key,.....)
Data warehouse have ___________.
Centralized logic.
Assuming all 1st normal form requirements are met and you do not have a ______ you are in 2nf.
Composite PK
What are CASE tools?
Computer aided software engineering. Used to design DB and application programs.
What is a relationship?
Connections between entities used to retrieve desired information from multiple entities.
What are authorization rules? What do they do?
Controls that are incorporated into the DBMS. They grant access, specify which actions and constraints govern the user access.
How do you delete all rows from a table?
DELETE FROM tblname
How do you delete certain rows from a table?
DELETE FROM tblname WHERE condition.
How do you delete a whole table from a DB?
DROP TABLE tblname
What does DCL do and what does it stand for?
Data control language. They are commands that administer privileges and execute data.
What does DDL do and what does it stand for?
Data definition language. They are commands that define (create) and establish constraints.
What are the different types of SQL?
Data definition, data manipulation, and data control.
What is information?
Data in context. Data processed to increase knowledge in the user.
What does DML do and what does it stand for?
Data manipulation language. Includes commands that maintain and query a database.
What does the database approach consist of?
Data models, entities, relationships, and relational databases.
What is data mining?
Examining DBs using a blend of statistical techniques, Algorithmic, and computer graphic techniques to generate new information.
What is the difference between explicit and implicit join notation?
Explicit uses the words "join" and "on" while implicit uses the phrases "from" and "where" when joining tables.
What is a data model?
Graphical system capturing the nature and relationship of the data (ERD).
What is embedded SQL?
Hard-coded SQL statements in a program written in another language such C++ or Java
What are logical controls? (examples)
Hiring practices, employee monitoring, security training, separation of duties.
What does a relationship cardinality show?
How many of each entity type is allowed in a relation.
How do you insert a partial row of data?
INSERT INTO tblname (columnname1, comulmnname2) VALUES ('no',1)
How do you insert a full row of data into a table?
INSERT INTO tblname VALUES (1, 'Dodgers', '01/02/1980')
What is business analytics?
Identifying business needs and determining solutions to business problems.
What SQL types are used for implementation, maintenance, physical design?
Implementation: DML, DCL Maintenance: DCL, DDL Physical design: DDL
If a firm is not happy with their business intelligence, where is the issue usually lying?
In the data warehouses or data marts.
What is a union join?
Includes columns from each table in the join and an instance for each row of a table.
What is knowledge?
Information put into action. what we KNOW.
What are assertions?
Integrity controls that enforce database conditions.
What is wisdom?
Knowledge and experience needed to make sensible decisions.
What is a checkpoint?
Mechanism where all the previous logs are removed from the system and stored permanently in a storage disk.
What is DB recovery?
Mechanisms for restoring a DB quickly and accurately after loss or damage.
What is a database administrator?
More technical. Responsible for phsyical DB design and for dealing with technical issues like security enforcement, DB performance, and backup and recovery.
What are the requirements for a table to be in 2NF?
NO partial dependencies. (PK usually not composite but if it is, check for dependencies)
What is an informational system?
One designed to support decision-making based on a historical point-in-time and predict data for complex queries or data-mining applications.
What is an operational system?
One that is used to run a business in real time, based on current data.
What is OLAP?
Online Analytical Processing. Graphical tools that provide users with multidimensional views of their data allowing them to analyze the data using simple techniques.
What is a routine?
Program modules that execute on demand.
What is the purpose of integrity controls?
Protect data from unauthorized use.
What is a data warehouse administrator?
Responsible for: 1. the integration and coordination of metadata/data across many data sources 2. Supporting decision-support applications. 3. Building a stable architecture 4. Managing data warehouses and data marts.
What are procedures?
Routines that do not return values.
What is a trigger?
Routines that execute in response to a database event (i.e. INSERT, UPDATE, or DELETE)
What are functions?
Routines that return values.
What is an outer join?
Rows that do not have matching values in common columns, are still a result. Can use left or right outer join.
Which phrase narrows columns?
SELECT
What is a database management system?
Software that handles the storage, retrieval and updating of data in a computer system.
What are application programs?
Software using the data to manipulate the DB and produce information to users.
What are views?
Subset of the database that is presented to one or more users. (users can be given access to views w/o access to data)
What are physical access controls? (EXAMPLES)
Swipe cards, equipment locking, screen placement, etc.
What are the requirements for 1NF?
Table meets all criteria for relation 1. Unique name 2. No multi-valued or composite attributes 3. Every row is unique 4. Attributes have unique name 5. Order of rows/columns are irrelevant.
What is the user interface?
Text and graphical displays to users.
What is range control?
The allowable value limitations.
What is encryption?
The coding or scrambling of data so that humans cannot read the data.
What is business intelligence?
The process of truing raw data into legible information.
What are the requirements of 3NF?
There must be no transitive dependencies.
What are foreign keys?
They're identifiers that enable a dependent relationship (on the many side) to refer to its parent relation (on the one side)
What are the goal of OLAP?
To minimize the amount of on the fly processing
What is the best solution to authentication?
Two or three factors
What function do you use to change the value of a field of data?
UPDATE
How do you update data in a table?
UPDATE tblname SET column1 = new value WHERE condition.
Which phrase(s) narrows rows?
WHERE, AND, OR, NOT
What is a transitive dependency?
When a non-key attribute identifies another attribute. 3nf.
What is a partial dependency and what normal form does this become an issue?
When one part of a PK identifies another attribute. 2nf
Which terms are used alongside of select statements?
from, where, group by, having, order by
What is authentication?
obtain a positive identification of the user.
