Transcript Santa R. Susarapu Ph.D. Student - Information Systems

L3
Database Management
Santa R. Susarapu
Ph.D. Student
Virginia Commonwealth University
Database Management
3-1
Objectives




Understand why databases
are important to modern
organizations
Understand how databases
work
Demonstration of
Database Creation using
Oracle/SQL*Plus
Understand how
organizations can
maximize their strategic
potential with databases
Database Management
3-2
Database Management for
Strategic Advantage

Use databases to:





Create a book
Track book sales
Set salaries and wages
Pay employees
Database – a collection of related attributes about
entities and organized in a way to facilitate data
searches
 Entity = Table
 Record = Column in a Table
Database Management
3-3
Database Management
3-4
Database Management for
Strategic Advantage
 The
Database Approach: Foundational
Concepts
DBMS is a software to create, store,
organize, and retrieve data from a single
database or several databases
 Example
 Microsoft Access
 Oracle

Database Management
3-5
Database Management for Strategic
Advantage

Traditional way to deal with electronic data
manipulation
 File Processing Approach
 Each department will have a separate
database
 Decentralized approach
 Difficult to modify
Database Management
3-6
Database Management for
Strategic Advantage


Database Approach - Centralized Approach
Advantages









Program-data independence
Minimal data redundancy
Improved data consistency
Improved data sharing
Increased productivity of application development
Enforcement of standards
Improved data quality
Improved data accessibility
Reduced program maintenance
Database Management
3-7
Database Management for Strategic
Advantage

Database Approach – Disadvantages
 Ownership conflict
 Backup and recovery issues
 Complex management
 Specialized personnel & training
 Conversion costs
Database Management
3-8
Database Management for
Strategic Advantage

Effective Management of Databases

The database administrator (DBA) :
 Works with programmers and analysts to
design and implement the database
 Works with users and managers to establish
database policies
 Implements security features and establishes
database permissions
Database Management
3-9
Key Database Activities
Entering Data
 Online forms
 Preprinted forms
 Telephone conversations
 Querying Data – Retrieval technique

Structured Query Language (SQL)
 Query by example (QBE)
 Demonstration using MS Access Database

Database Management
3-10
Key Database Activities

Creating Database Reports
Report – a compilation of data that is organized
and produced in printed format
 Report Generators
 Demonstration using MS Access Database

Database Management
3-11
Key Database Activities

Database Design
Must be organized
 Few or no redundancies
 Data model – a map of entity relationships
 Keys




Primary key
Combination primary key
Foreign key
Database Management
3-12
Database Management
3-13
Key Database Activities

Entity-Relationship Diagramming (ERD)
Commonly used when designing databases
 One draws entities (tables) as boxes and lines
between entities to show relationships
 Example: Mountain Animals ERD
 Handout

Database Management
3-14
Key Database Activities

Database Associations
One-to-one relationship
 One-to-many relationship
 Many-to-many relationship

Database Management
3-15
Key Database Activities

The Relational Model of Databases
Entities linked by a common key field
 Records = rows
 Fields = columns
 Other models exist




Hierarchical
Network
Object-oriented model
Database Management
3-16
Key Database Activities

Normalization
A technique for making complex databases
more efficient and more easily handled by the
DBMS
 1st Normal Form (1NF)
nd Normal Form (2NF)
2
 3rd Normal Form (3NF)
 Boyce-Codd Normal Form (BCNF)
 Eliminates data redundancy and modification
anomalies

Database Management
3-17
Database Management
3-18
Database Management
3-19
Key Database Activities

Data Dictionary a.k.a. metadata

A document that explains each piece of
information in the database




Field name
Data type
• Numeric, text, date/time
• Useful for sorting and allocating storage
Is this field a key field?
Business rules
• Update authority
• Valid data values
Database Management
3-20
Creation of Databases using
Oracle/SQL*Plus
Oracle Enterprise Manager
 SLQ*Plus
 iSQL*Plus

Database Management
3-21
How Organizations Get the
Most from Their Data

Linking Web Sites to Databases

Example: Amazon
 2.5 million titles
 Managing online data effectively
Database Management
3-22
How Organizations Get the
Most from Their Data

Data Mining
A method for better understanding data
 Information on customers, products, markets, etc.
 Drill down: from summary to more detailed data
 Sort and extract information
 Trends, correlations, forecasting, statistics

Database Management
3-23
How Organizations Get the
Most from Their Data

Data Mining

Online Transaction Processing (OLTP)
 Immediate automated responses to user
requests
 Multiple concurrent transactions
 A big part of interactive Internet e-commerce
Database Management
3-24
How Organizations Get the
Most from Their Data

Data Mining

Online Analytical Processing (OLAP)
 Graphical software tools that provide complex
analysis of data stored in a database
 Drills down to deeper levels of consolidation
 Time series and trend analysis
 “What if” and “why” questions
Database Management
3-25
How Organizations Get the
Most from Their Data

Data Mining

Merging Transaction and Analytical Processing
 Real-time OLAP diminishes performance
because the database must be “locked” during
execution time
 Solution: replicate transactions on a 2nd
database server
Database Management
3-26
How Organizations Get the
Most from Their Data

Data Mining

Merging Transaction and Analytical Processing
 Operational Systems
• Interact with customers and run a business
in real time
• Examples: Order processing, reservation
systems
 Informational Systems
• Support decision making based on stable
point-in-time or historical data
Database Management
3-27
How Organizations Get the
Most from Their Data

Data Warehousing
Integrating multiple large databases into a
single repository
 Queries, analysis, and processing
 Purpose: put key business information into
the hands of decision makers
 Cost: millions

Database Management
3-28
How Organizations Get the
Most from Their Data

Data Marts
Instead of one large data warehouse, many
organizations create multiple data marts
 Each contains a subset of the data
 Example: finance, inventory, personnel
 Each data mart is customized for particular
DSS applications
 Cost: typically less than $1 million

Database Management
3-29