Transcript AIS PowerPoint Presentations

Chapter
4-1
Database Management
Systems
Overview
 Not a database
 Separate software
system
Functions
 Enables
users to utilize database information
more efficiently
Examples
 Access,
Alpha 5, dQuery, Lotus Approach
Chapter
4-2
Introduction to Microsoft
Access
A popular relational database
Used by many businesses and individuals
Used for small database applications
Chapter
4-3
Microsoft Access – Opening
Screen
Chapter
4-4
Creating Database Tables –
Defining Record Format
Field Name

Names assigned to the data fields
Data Type

Specified for each data field
 Identifies how to store the data – field properties
Description

Optional field
 Defines record structures
Chapter
4-5
Creating Database Tables –
Opening Screen
Chapter
4-6
Creating Database Tables –
Record Format
Chapter
4-7
Creating Relationships
Purpose
 Link
tables together
 Enable users to create multi-table reports
Steps in Creating Relationships
 Select
tables
 Link the tables
Chapter
4-8
Creating Database
Relationships – Linking Tables
Chapter
4-9
Creating Relationships –
Multitable Relationships
Chapter
4-10
Creating Records
Utilize datasheet view to input data
Chapter
4-11
Data Validation
Data Definition Language (DDL)
 Enables users to define record structure
 Define individual fields of each record (text,
date, number, etc.) by size and if required
Example
Chapter
4-12
Tools for Data Validation
Proper Data Types for Fields
 Only
accept letters or numbers or dates
Input Masks
 Limit
data to specific formats (e.g. SSN XXXXX-XXXX or telephone number XXX-XXXXXXX)
Default Values
 Data
fields of new records (city resident must
have zip of 99324)
Chapter
4-13
Tools for Data Validation
Drop-Down Lists (e.g. alumni database,
type of industry in which alum works)
Validation Rules
 Create
rules than limit range of values that
may be entered
Referential Integrity
 Deleting
of information disallowed when it
would disrupt references
Chapter
4-14
Drop-Down List Example
Chapter
4-15
Validation Rule Example
Chapter
4-16
Creating Referential
Integrity
Chapter
4-17
Study Break #1
All of the following are examples of DBMSs except:
A. Access
B. Oracle
C. DB2
D. SQL
Chapter
4-18
Study Break #1 - Answer
All of the following are examples of DBMSs except:
A. Access
B. Oracle
C. DB2
D. SQL
Chapter
4-19
Study Break #2
An example of a validation rule is:
A. An input value must be an integer
B. An input value must also have a default value
C. An input value must be between 0 and 40
D. You cannot delete parent records that have child records
associated with them
Chapter
4-20
Study Break #2 - Answer
An example of a validation rule is:
A. An input value must be an integer
B. An input value must also have a default value
C. An input value must be between 0 and 40
D. You cannot delete parent records that have child records
associated with them
Chapter
4-21
Tips for Creating
Database Tables and Records
Design first
Create
tables and records last
Name tables systematically

Use conventional tbl prefixes
Use mnemonic names for data fields
Assign correct data types to data fields
Chapter
4-22
Tips for Creating
Database Tables and Records
Data fields that link tables must be the same
data type
Limit the size of text data fields to reasonable
lengths
Use input masks
Chapter
4-23
Extracting Data From
Databases
Schema
 All
information in a database
 All relationships of the tables
 Map of entire database
Subschema
 Subset
of the schema
Chapter
4-24
Creating Select Queries
Queries
 Create
customized subschemas
Dynaset
 Dynamic
subset of a database
 Created by queries
 Data Manipulation Language (DML)
Chapter
4-25
Query Example
Chapter
4-26
Creating Select Queries
One-Table Select Queries
 Creates
a dynaset
 Based on:


Criteria determining which records to include
Criteria determining which fields to include from
those records
 Single
or Multiple Criteria
Chapter
4-27
Select Query Example
Chapter
4-28
Multi-Table Select Query
Example
Chapter
4-29
Multi-Table Select Query
Example
Chapter
4-30
Creating Action Queries
Delete queries
Append queries
Sum a column
Update queries
Make-table queries
Chapter
4-31
Query Wizard Queries
Simple query Wizard
Crosstab queries
Find-Duplicates queries
Find-unmatched queries
Chapter
4-32
Query Wizard Screen
Chapter
4-33
Guidelines for Creating
Queries
Spell accurately and be case sensitive
Specify AND and OR operations correctly
Tables must be joined properly
Name queries systematically
Choose data fields selectively
Chapter
4-34
Extracting Data From
Databases
Structured Query Language (SQL)
Example of SQL Instructions
Chapter
4-35
Extracting Data From
Databases
Online Analytical Processing (OLAP)
 Complex,
multidimensional data analysis
 Pivot tables
Data Mining
 Utilize
a set of data analysis and statistical
tools
 Identify relationships, patterns, or trends
Chapter
4-36
Data Mining
Analytical tools for detecting trends or
relationships among seemingly
uncorrelated data
Used in:
 Marketing
(promotions, ads, etc.)
 Auditing & criminal/terrorist investigations
(connecting the dots)
Software examples: Darwin (Oracle);
Intelli Miner (IBM)
Chapter
4-37
Cloud Computing
Form of Internet-based Computing
 Software
provided through the Internet
 Processing occurs on a Web of computers
 Expands IT capabilities
Database-As-A-Service (DAAS)
 Outsourcing
of databases
Backup Services
Chapter
4-38
Data Warehouses
Multiple databases stored in one system
Enables efficient use of data to pinpoint
customer needs, etc.; helps generate ROI
Difficult to collect and store diverse info in a
consistent and useful way; can be very costly
Characteristics needed

Free of errors (scrub or clean data)
 Defined uniformly (normalize & standardize)
 Span long timeframe (> than transaction system)
Chapter
4-39
Study Break #3
All of the following are examples of action queries except:
A. Update query
B. Append query
C. Delete query
D. Find missing data query
Chapter
4-40
Study Break #3 - Answer
All of the following are examples of action queries except:
A. Update query
B. Append query
C. Delete query
D. Find missing data query
Chapter
4-41
Study Break #4
SQL is an example of:
A. A tool to perform online analytical processing
B. A database management system
C. A query language
D. A multimedia database
Chapter
4-42
Study Break #4 - Answer
SQL is an example of:
A. A tool to perform online analytical processing
B. A database management system
C. A query language
D. A multimedia database
Chapter
4-43