Transcript Remembering the Past With Organizational Memory

Part 2
Remembering the Past
With Organizational
Memory - Database
Copyright © 2001
by Harcourt, Inc.
All rights reserved
Organizational
Memory
Remember the Past
(organizational memory)
People and
Technology
Handling the
Present (transaction
processing system
2001
Decisions
Preparing for the
Future (decision
support systems)
Organizational Memory
Data
Information
Numbers
Text (tables & reports)
Organizational Culture
Characters
Hypertext
Social Networks
Graphics (incl. charts)
Problem Solving Models
Images
Audio
2001
Knowledge
Video
File Organization
• Sequential
• Direct (Random)
• Indexed Sequential
2001
Database
• Organized collection of related files
– To relate, have common field
– Shared across applications and functional areas
– Reduced Duplications
2001
Data Hierarchy
Database
(collection of related files)
File
(table)
Record
(row)
Field
(column)
Character
(byte)
Bit
2001
Database - Advantages
• Data Redundancy - less duplication between
applications causing inaccuracies
• Data Dependence - data not linked to
application
• Data Integration -shared resource
• Flexibility -access and view many ways
• Better Security
2001
Data Storage
Structured and Semistructured Organizational Memory
Storage Type
2001
Data
Information
Knowledge
Structured
Database, data
warehouse
Indexed Web site, Groupware,
reports, charts,
expert systems,
manuals
FAQs,
newsgroups
Semistructured
Unorganized list
Web pages,
e-mail
Bulletin boards,
chat groups
DBMS Software Components
• Data Definition Language
• Data Manipulation Language
• Data Dictionary
2001
Data Definition Language
• Structure of Database
– Schema
• Field Name, Length
• Type
– Alphanumeric, Numeric, Date, Logic, Memo
• Properties
2001
Data Manipulation Language
• For use of database
– Add / Change data
– View data
• Structured Query Language (SQL)
• Report Generator
– Organize records
• Index or Sort
– Delete records
2001
Data Dictionary
•
•
•
•
•
•
Actual File
Stores Definitions (Properties)
Ownership
Security
Usage
Data Element
2001
Views
Logical
• As it is viewed by an
end user
• The user is unaware of
the fact that data is
physically resident in
two different files
2001
Physical
• The actual
organization of data
within the computer
storage device
• The actual
arrangement of data
into entities, attributes,
tables and databases
Views
• Logical
–
–
–
–
–
–
–
–
2001
Customer #
Order #
Product #
Order Quantity
Price
Ship-to-Address
Supplier
Salesman #
• Physical Entities
Customer
Order
Product
Tables for Special Orders at
fareastfoods.com
2001
Customer Table
Special Table
Customer ID
Last Name
First Name
E-Mail Address
(other columns not
shown)
Product ID
Product Name
(other columns not
shown)
TYPES OR RELATIONS
ONE-TO-ONE:
STUDENT
CLASS
ONE-TO-MANY:
STUDENT
A
MANY-TO-MANY:
2001
ID
STUDENT
B
CLASS
1
STUDENT
A
STUDENT
C
CLASS
2
STUDENT
B
STUDENT
C
NETWORK DATA MODEL
• Variation Of Hierarchical Model
• Useful For Many-to-many Relationships
CLASS
1
STUDENT
A
2001
CLASS
2
STUDENT
B
STUDENT
C
RELATIONAL DATA MODEL
•
•
•
•
Data In Table Format
Entity: Table
Tuple: Row (Record) In Table
Field: Column (Attribute) In Table
*
2001
2001
Relational DBMS
Transaction
processing
systems
Snapshot data
*Extraction
*Transformation
*Cleaning
*Loading
2001
Creating
Warehouse
* Decision
Queries
Data
support
warehouse Data
systems
*Data mining
Using Data
Warehouse
Relational DBMS
Relationships in Fareastfoods.com Database
Customer
Customer ID
Last Name
First Name
E-mail Address
2001
Request
Request Number
Customer ID
Product ID
Special Order
Products
Product ID
Product Name
Fareastfoods.com Data Model
2001
Customer
Request
*Customer ID
Last Name
First Name
E-mail Address
*Request Number
Customer ID
Product ID
Special Order
Products
*Product ID
Product Name
Structured query language
(SQL)
• SQL queries
• Update
• Delete
• Add
2001
Sample SQL Query
SELECT fields FROM tables
WHERE fields match query
condition
SELECT LASTNAME, FIRSTNAME, EMAIL
FROM CUSTOMER, REQUEST
WHERE CUSTOMER.CUSTID = REQUEST.CUSTID
ORDER BY LASTNAME;
2001
Properties of Relations
• An entry at the intersection of each row and
column is single-valued
• Entries in columns are from the same
domain and relate to the same attribute
…name, address
• Each row is unique and has a unique key.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.
2001
Relational Databases
• Mainframe...
– DB2 - IBM
– Oracle (mini, micro)
– SQL Server - MS
• Microcomputer
–
–
–
–
2001
Access - MS
FoxPro - MS
Paradox - Borland
dBASE IV - Borland
Normalization
• The process of creating small
data structures (tables) or
entities from complex groups
of fields (attributes).
– minimize redundancy
– easily allows insertion, deletion, and
modification without inconsistencies.
2001
Normalization Guidelines
• Remove all repeating attributes (1NF)
– one to many relations
• All non-key attributes are dependent on
full-key attribute(s) and not just part of it.
(2NF)
– compound key entities
• Non-key attributes not dependent on any
other non-key attribute. (3NF)
2001
Designing NEW Database
•
•
•
•
Determine Attributes (fields)
Determine Entities (tables)
Normalize Entities
Determine Keys
– Unique Key (_____)
– Foreign Key (_ _ _ _)
• Show Relationships (Lines between keys)
2001
Object-Oriented Databases
• Data and Procedures Stored Together; can be
Retrieved, Shared
• Data/Procedures is Object
–
–
–
–
–
Document
Graphic
Video
Text
Sound
• Used in Multimedia
2001
DATABASE TRENDS
• HYPERMEDIA:
– Text, Graphics, Sound, Video, Programs.
– Nodes Connected by Links
• HYBRID - Object and Relational
– Can store Graphics, Video, Sound…
*
2001
Distributed Databases
• A database which is accessed and
maintained at multiple locations.
• To avoid failure:
– Replicated - central database is duplicated at
remote location(s).
– Partitioned - database is subdivided and only
portion used is available at remote sites. All at
central.
2001
Security
• Who has access to data?
– Read, Write, Change, Delete
• Length of Retention
– 1 month, Until paid, Historical
• Back-up
– On-site, Off-site, How often
• Audit Trail
– Record of update, who, when, log
2001
Data Warehouse - Advantages
• Enterprise-wide view
of business
• Executive and
Management planning
and decision making
• Access, not change!
2001
DATABASE ADMINISTRATION
• Defines & Organizes Database Structure
And Content
• Develops Security Procedures
• Develops Database Documentation
• Maintains DBMS
2001
Information Management
• Electronic imaging
–Scanner
–Portable document
format (PDF)
2001
Problems with Paper Storage of Information
Problem
2001
Explanation
Storage costs
Paper documents are expensive to store in filing
cabinets and storage cabinets
Time costs
A large amount of time can be required to find
documents
Multiple access
(sharing)
Paper documents (or microfiche/microfilm) must be
copied to be available to multiple people
Transmission
delay
Paper documents can take a long time to be sent from
one place to another
Information
manipulation
Information on paper (or microfilm/microfiche) must be
manually extracted; also, it is not easily searchable
Backups
Backups of paper documents are seldom kept due to
space problems
Knowledge Management
• Groupware
• Group support systems
• Videoconferencing
• Document-based
groupware
2001
• Expert systems
–Knowledge base
–Inference engine
–Domain database
–Rule database
–If-then rule
–Logic trace
2001
Elements of Expert Systems
Knowledge
Facts
Knowledge
Base
Inference
Engine
User
2001
Preparing for the Future
with Decision Support
NEXT SLIDE
PAST SLIDE
5 - 41
Copyright © 2001
by Harcourt, Inc.
All rights reserved
Database Case Exercise
• Determine Attributes (given)
• Determine Entities (tables)
• Determine Keys
– Unique Key (_____)
– Foreign Key (_ _ _ _)
• Normalize Entities (3 rules)
• Show Relationships (Lines between keys)
– One to Many
2001