Transcript 8. MANAGING DATA RESOURCES

LEARNING OBJECTIVES
• Explain problems of traditional file
environment
• Describe how database management
system organizes data
*
LEARNING OBJECTIVES
• Identify 3 database models (principles of
database design)
• Analyze managerial, organizational
requirements for creating database
environment
*
File Organization
• BIT: Binary Digit (0,1;Y,N;On, Off)
• BYTE: Combination of BITS which
represent a CHARACTER
• FIELD: Collection of BYTES which
represent a DATUM or Fact
• RECORD: Collection of FIELDS
which reflect a TRANSACTION
*
File Organization
• FILE: A Collection of Similar
RECORDS
• Database: An Organization’s
Electronic Library of FILES
• Database Management System
(DBMS): The application software
that maintains the database
*
File Organization in terms
of “Data Modeling”
• ATTRIBUTE: Description of a
Particular ENTITY
• ENTITY: Person, Place, Thing, Event
about Which Data Must be Kept (a
collection of attributes)
• KEY FIELD: Field Used to Retrieve,
Update, Sort RECORD
*
Use the term “attribute”
rather than “variable”
• Must be attributable to some
outcome, (i.e., are important factors).
Guard against frivolous expansion
• Each attribute is a new “dimension,”
and a person in 3D world can
forecast for 2D world
Key Field (Primary Key)
Field in Each Record that can be used
to uniquely identify the record
For RETRIEVAL
UPDATING
SORTING
*
Foreign Key
• A non-primary key attribute in the
table that is a primary key in the
other table.
Student (sid, name, aid)
Apartment (aid, address)
aid is the primary key for Apartment
table but not so in Student table
Primary Key Creation
• Hashing Function: an algorithm to
generate a unique number/key
• function: for every x there is only one
y. This implies the uniqueness of the
key (enter your SS# and YOUR
records will show up).
• use rotation
• use addition
TRADITIONAL FILE
ENVIRONMENT (FLAT FILE)
•
•
•
•
•
DATA REDUNDANCY
PROGRAM / DATA DEPENDENCY
LACK OF FLEXIBILITY
POOR SECURITY
LACK OF DATA SHARING &
AVAILABILITY
*
7.11
DATABASE
• ORGANIZATION’S ELECTRONIC
LIBRARY
• STORES & MANAGES DATA
• IN A CONVENIENT FORM
*
DATABASE MANAGEMENT
SYSTEM (DBMS)
 Software to create and maintain data
 Enables business applications to
extract data
*
DBMS
COMPONENTS OF DBMS:
• Data Definition Language (DDL):
– Defines Data Elements in Database
• Data Manipulation Language (DML):
– Manipulates Data for Applications
• Data Dictionary:
– Formal Definitions of all Variables in Database;
Controls Variety of Database Contents
• Process Specification:
– Application procedures used to generate
needed information
*
DBMS
TWO VIEWS OF DATA
• Physical View: Where is data stored physically?
– Drive, disk, surface, track, sector, Record
– Tape, block, record number (key)
• Logical View: What data is needed? How are they
presented to users?
– Organize factors into tables for application use
– Provide a way for tables to cross-reference
*
Advantages of DBMS (by
Modular Combination)
• Complexity now is in the “combination” of
fewer key tables, and not the “sheer
number” of data files
• Enhances system flexibility
• Reduces data redundancy / inconsistency
• Reduces program / data dependence
*
DBMS
Advantages of DBMS (by
centralization)
• Central control of data creation /
definitions
• increases access / availability of
information
*
DBMS
Hierarchical Database
May have more than one offspring, but only
one parent
ROOT
FIRST
CHILD
2nd
CHILD
Employer
Compensation
Ratings
Salary
Job
Assignments
Pension
Benefits
Insurance
Health
Relationship
how data are used together
ONE-TO-ONE (1:1)
STUDENT
ID
Father
ONE-TO-MANY (1:m)
child
A
MANY-TO-MANY (m:n)
child
B
CLASS
1
STUDENT
A
child
C
CLASS
2
STUDENT
B
STUDENT
C
Network Data Model
• May have more than one “parent”
• Useful for m:n relationships
*
CLASS
1
STUDENT
A
CLASS
2
STUDENT
B
STUDENT
C
Relational Data Model
• “Relation” in mathematics means a 2D
table, so in Relational Model there are
tables, all tables, nothing but tables (so
help me Codd).
*
HOURS
RATE
TOTAL
ABLE
$ 40.50 $ 10.35 $ 419.18
BAXTER $ 38.00 $ 8.75 $ 332.50
CHEN
$ 42.70 $ 9.25 $ 394.98
DENVER $ 35.90 $ 9.50 $ 341.05
Relational Data Model Terms
• Relation: table
• Tuple: row or record in table (y-axis)
• Attribute: column or field in table (x-axis)
*
HOURS
RATE
TOTAL
ABLE
$ 40.50 $ 10.35 $ 419.18
BAXTER $ 38.00 $ 8.75 $ 332.50
CHEN
$ 42.70 $ 9.25 $ 394.98
DENVER $ 35.90 $ 9.50 $ 341.05
Structured Query Language
(SQL)
SELECT -- attributes
FROM -- tables
WHERE -- rows, based on specified conditions
Since Relational Model has only 2D tables,
identify attributes (x) and rows (y) can locate
any data from tables.
Every report is a modular combination of
tables.
Diagrams as Communication
Aid
• Data Flow Diagram (DFD): emphasis
on process management
• Entity-Relationship Diagram (ERD):
emphasis on how data are used
together
• State Transit Diagram (STD):
emphasis on decision and branching
*
Diagrams as Communication
Aid
• Pictorial Diagram in IFPS is a
modified form of DFD and ERD
*
Entity-Relationship Diagram
(ERD)
ORDER
ORDER: #, DATE, PART #, QUANTITY
1
CAN
HAVE
1
PART: #, DESCRIPTION, UNIT PRICE,
SUPPLIER #
PART
M
CAN
HAVE
11
SUPPLIER
SUPPLIER: #, NAME, ADDRESS
Normalization
The process of eliminating operational
errors of a database. Usually by
breaking larger tables down to
smaller ones
Normalization
Using ER approach
1:1 relationship -- merge
1:m relationship -- the primary key on
the 1 side becomes the foreign key of
the m side
m:n relationship -- create a third table
using primary keys on both tables
Database Trends
• Distributed Processing: Multiple
Geographical / Functional Systems
Connected with Network (to reduce overall
processing time by delegation / parallel
processing)
• Distributed Database: Data Physically
Stored in more than one Location (to
reduce transmission time)
*
DATABASE TRENDS
• Hypermedia: Nodes Contain Text,
Graphics, Sound, Video, Programs.
Organizes Data as Nodes. (to enhance
comprehension and effectiveness)
• Internet related WebDB: Common user
interface, centralized control, wider
accessibility and availability.
*
Database Trends
• Data Warehouse: Organization’s
Electronic Library Stores Consolidated
Current & Historic Data for Management
Reporting & Analysis
• Data Mart: small data warehouse for
special function, e.g., focused marketing
based on customer info (7-11)
• Data Mining: Business Intelligence
*
Database Trends
• On-line Analytical Processing
(OLAP): ability to manipulate,
analyze large volumes of data from
multiple perspectives
(quote.yahoo.com)
*