Transcript Chapter 5

Chapter
5-1
Chapter 5:
Organizing and Manipulating the Data in
Databases
Introduction
Normalization
Validating the Data in Databases
Extracting Data From Databases: Data
Manipulation Languages (DMLs)
Object-Oriented Databases, Multimedia
Databases, and Data Warehouses
Chapter
5-2
Normalization
Most data organized in flat files



No sequence or order
Difficult to find records
Difficult to use data efficiently
Normalization



Process of examining and arranging file data
Allows for more efficient use of data
Avoid problems accessing and modifying
Chapter
5-3
Unnormalized Data
Chapter
5-4
First Normal Form
In First Normal Form (1 NF) when:
 All
data fields are well defined
 Data can be stored in a flat file
Problems
 Data
redundancy
 Insertion anomaly
 Deletion anomaly
Chapter
5-5
First Normal Form Example
Chapter
5-6
Second Normal Form
In Second Normal Form (2 NF) when:
 It
is in 1 NF
 All data items depend on primary record key
Benefits
 More
efficient design
 Eliminates data redundancy
Chapter
5-7
Second Normal Form
Example
Chapter
5-8
Third Normal Form
In Third Normal Form (3 NF) when:
 It
is in 2 NF
 Does not contain transitive dependencies
 Data field A does not determine data field B
Ultimate Goal
 Create
database in 3 NF
Chapter
5-9
Third Normal Form Example
Chapter
5-10
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
5-11
Data Validation
Data Definition Language (DDL)
 Enables users to define record structure
 Define individual fields of each record
Example
Chapter
5-12
Tools for Data Validation
Proper Data Types for Fields
Input Masks
 Limit
data to specific formats
Default Values
 Data
fields of new records
Chapter
5-13
Tools for Data Validation
Drop-Down Lists
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
5-14
Drop-Down List Example
Chapter
5-15
Validation Rule Example
Chapter
5-16
Creating Referential
Integrity
Chapter
5-17
Study Break #1
A database is in the third normal form (3 NF) if it is second
normal form and:
A. All the data attributes in a record are well defined
B. All the data attributes in a record depend upon the record
key
C. The data contain no transitive dependencies
D. The data can be stored in two or more separate tables
Chapter
5-18
Study Break #1 - Answer
A database is in the third normal form (3 NF) if it is second
normal form and:
A. All the data attributes in a record are well defined
B. All the data attributes in a record depend upon the record
key
C. The data contain no transitive dependencies
D. The data can be stored in two or more separate tables
Chapter
5-19
Study Break #2
An example of a validation rule is:
A.
B.
C.
D.
An input value must be an integer
An input value must also have a default value
An input value must be between 0 and 40
You cannot delete parent records that have child records
associated with them
Chapter
5-20
Study Break #2 - Answer
An example of a validation rule is:
A.
B.
C.
D.
An input value must be an integer
An input value must also have a default value
An input value must be between 0 and 40
You cannot delete parent records that have child records
associated with them
Chapter
5-21
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
5-22
Creating Select Queries
Queries
 Create
customized subschemas
Dynaset
 Dynamic
subset of a database
 Created by queries
 Data Manipulation Language (DML)
Chapter
5-23
Query Example
Chapter
5-24
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
5-25
Select Query Example
Chapter
5-26
Multi-Table Select Query
Example
Chapter
5-27
Multi-Table Select Query
Example
Chapter
5-28
Creating Action Queries
Simple query Wizard
Crosstab queries
Find-Duplicates queries
Find-unmatched queries
Delete queries
Chapter
5-29
Creating Action Queries
Append queries
Sum a column
Update queries
Make-table queries
Chapter
5-30
Query Wizard Screen
Chapter
5-31
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
5-32
Extracting Data From
Databases
Structured Query Language (SQL)
Hypertext
 Hypertext
Markup Language (HTML)
eXtensible Markup Language (XML)
Chapter
5-33
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
5-34
Pivot Table
Chapter
5-35
Object-Oriented Database
Object-Oriented Database (OODB)
 Contains
text data
 Information about set of actions taken on
data field
Multimedia Databases
 Include
graphics, audio information, and
animation
Multidimensional Databases
Chapter
5-36
Data Warehouses
Pools of data from separate applications
Characteristics
 Free
of errors
 Defined uniformly
 Span longer timeframe than transaction
systems
 Optimized data relationships
Chapter
5-37
Study Break #3
All of the following are examples of action queries except:
A.
B.
C.
D.
Update query
Append query
Delete query
Find missing data query
Chapter
5-38
Study Break #3 - Answer
All of the following are examples of action queries except:
A.
B.
C.
D.
Update query
Append query
Delete query
Find missing data query
Chapter
5-39
Study Break #4
SQL is an example of:
A.
B.
C.
D.
A tool to perform online analytical processing
A database management system
A query language
A multimedia database
Chapter
5-40
Study Break #4 - Answer
SQL is an example of:
A.
B.
C.
D.
A tool to perform online analytical processing
A database management system
A query language
A multimedia database
Chapter
5-41
Copyright
Copyright 2010 John Wiley & Sons, Inc. All rights reserved.
Reproduction or translation of this work beyond that permitted in
Section 117 of the 1976 United States Copyright Act without the
express written permission of the copyright owner is unlawful.
Request for further information should be addressed to the
Permissions Department, John Wiley & Sons, Inc. The purchaser
may make backup copies for his/her own use only and not for distribution
or resale. The Publisher assumes no responsibility for errors, omissions,
or damages, caused by the use of these programs or from the use of the
information contained herein.
Chapter
5-42
Chapter 5
Chapter
5-43