Mis 431 Exam 1 Study Guide

MIS 431 EXAM 1 STUDY GUIDE

data vs information - Answer -- data constitute building blocks of information
- information produced by processing data
- information reveals meaning of data

database is shared, integrated computer structure housing: - Answer -- end user data
- metadata

database management system (DBMS) - Answer -- manages database structure
- controls access to data
- contains query language

importance of DBMS - Answer -- makes data management more efficient and effective
- query language allows quick answers to ad hoc queries
- provides better access to more and better-managed data
- promotes integrated view of organizations operations
- reduces the probability of inconsistent data

importance of good design - Answer -- poor design results in unwanted data
redundancy
- poor design generates errors leading to bad decisions

practical approach - Answer -- focus on principles and concepts of database design
- importance of logical design

data - Answer -raw facts

field/attribute - Answer -group of characters with specific meaning

record/row/tuple - Answer -Logically connected fields that describe a person, place, or
thing

table - Answer -collection of related records

DBMS functions - Answer -- data dictionary management
- data storage management
- Data transformation and presentation
- Security management
- Multiuser access control
- Backup and recovery management
- Data integrity management
- Database access languages and application programming interfaces
- Database communication interfaces

,database models - Answer -collection of logical constructs used to represent data
structure and relationships within the database

conceptual models - Answer -logical nature of data representation

implementation models - Answer -emphasis on how the data are represented in the
database

relational database model - Answer -- Perceived by user as a collection of tables for
data storage
- Tables are a series of row/column intersections
- tables related by sharing common entity characteristics

creating table structure - Answer -- tables store end-user data
- may be based on data dictionary entries
CREATE TABLE <table name>
(<attribute1 name and attribute1 characteristics, attribute2 name and attribute2
characteristics, attribute3 name and attribute3 characteristics,
primary key designation,
foreign key designation and foreign key requirements>);

SQL integrity constraints
- adherence to entity integrity and referential integrity rules is crucial - Answer -- entity
integrity enforced automatically if primary key specified in CREATE TABLE command
sequence
- referential integrity can be enforced in specification of FOREIGN KEY
- other specifications to ensure conditions met:
1. ON DELETE RESTRICT
2. ON UPDATE CASCADE

2 types of integrity - Answer -1. entity
2. referential

data entry & saving - Answer -- enters data into a table
INSERT INTO <table name>
VALUES (attribute 1 value, attribute 2 value...etc.);
- saves changes to disk
COMMIT <table names>;

listing table contents and other commands - Answer -- allows table contents to be listed
SELECT <attribute names> FROM <table names>;
- UPDATE command makes data entry corrections
UPDATE <table name> SET <attribute name> = value where <identifying attribute
name> = value;
- ROLLBACK command restores database back to previous condition if COMMIT hasn't
been used

, - DELETE command removes table row
DELETE FROM <table name> WHERE <attribute names> = value;

table structure changes - Answer -alter table <table name> add <attribute name and
attribute characteristics>
alter table <table name> drop <attribute name>
- alter table = altering structure

queries - Answer -- creating partial listings of table contents
SELECT <column(s)>
FROM <table name>
WHERE <conditions>;

examples - Answer -- mathematical operators
SELECT PartNum, Description, Unit_price
FROM Part
WHERE Unit_price <> 24;
- mathematical operators on character attributes
SELECT PartNum, Description, Unit_Price
FROM Part
WHERE PartNum < 'BV06';
- mathematical operators on dates
SELECT PartNum, Description, Unit_Price
FROM Part
WHERE OrderDate < '10-20-2012';
(numerical data does not have quotes, dates DO)

computed columns - Answer -- new columns can be created through valid
computations or formulas
1. formulas may contain mathematical operators
2. may contain attributes of any tables specified in FROM clause
- alias is alternate name given to table or column in SQL statement
SELECT PartNum, Description, Unit_Price*OnHand as TOTVALUE
FROM Part
WHERE Unit_price <> 24;

logical operators - Answer -- AND, OR, NOT
SELECT *
FROM Part
WHERE PartNum = 'AT94' OR Partner = 'CD52';

rules of precedence - Answer -- conditions within parentheses executed first
- Boolean algebra

BETWEEN - Answer -defines limits