Transcript File and Database Design Continued

SYS364
Database Design Continued
Database Design
Definitions
Initial ERD’s
Normalization of data
Final ERD’s
Database Management
Database Models
File Access and Organization
Database Management
File processing environments where each
user/department has its own copy of files (or data
in spreadsheets) have three problems
Data redundancy
Inconsistent data
Managers require enterprise-wide information
A database is a normalized, single repository
optimized for transactions.
A data warehouse is optimized for read only data
inquiry and may not be normalized. Updated by
the transaction database at intervals.
Elements of Database
Management Systems
DBMS is a software system used to create,
access and control a database
Has five main components
Data Definition Language
Data Manipulation Language
Query language
Data dictionary
Utility programs
Data Definition Language
Used to describe the structure of the
database
Complete database description (fields,
records and relationships) is the schema or
collection
Subschema is a view of the database as seen
by a program or a user
Data Manipulation Language
Provides necessary commands for database
operations
Select
Insert
Update
Delete
Query Language
A non-procedural language used to access a
database
Non-procedural languages allows you to
specify a task without specifying how the
task will be done
QBE (Query By Example)
SQL (Structured Query Language)
Data Dictionary
Serves as a central repository for
information about the database
Schemas and subschemas are stored in the
data dictionary
Utility Programs
Most DBMS include the necessary support for
database security, backup and recovery, audit
trails, and data integrity
Most DMBS also provide utility programs for
creating a database, changing the structure of the
database, gathering and reporting patterns of
database usage, and detecting and reporting
database structure irregularities
Characteristics of Database
Management
Programs independent of Data storage
Scalability – accommodates growth
Support for client/server
Central repository:
Economy of scale – one big server
Data exists once and is shared (not copied)
DB Admin. Enforces standards, balances specific
requirements vs. overall performance
Controls DB Replication when needed
Security
Database Models
Hierarchical & Network
old, e.g. FoxPro, dBase
Programs must know relationships
Object-oriented
good for BLOBs and non-transactional data
Relational
the current standard, SQL interface
ad hoc (to this) interactive processing
Embedded in programs
ODBC, JDBC
File Access and Organization
Logical vs. Physical Records
Types of files
Control files, e.g. next numbers for
Customer code, SKU, Invoice No.
Master file, e.g. Customer, Inventory
Transaction Files, e.g. Invoices
(updated, volatile)
Audit/Journal Files, who did what and when
History Files, e.g. old Invoices
(read-only, non-volatile)
Work Files, temporary use
File Access
Sequential Access Method (all records in
sequence, usually by primary key)
Random Access Method (any one record)
Various programming techniques must be
used in non-DBMS systems
DBMS is all by key, Sequentially or
Randomly through program interfaces
and/or imbedded SQL
File Organization
File (non-DBMS) systems:
Sequential Organization (sorted records)
Direct Organization (relative record number)
Indexed Organization (simple key but no
relations)
DBMS
Tables in 3NF with Primary Keys, Views of
data across tables by relationships
Storage Media
HDD – cheap, fast
DASD –good, faster
RAID1 – mirrored redundancy: fast, good
RAID5 – striped redundancy: cheaper, fast
Floppy vs. diskette
Tape, CD-ROM, removable media
 ensure capacity for unattended backup
Hierarchical file systems