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 ManagementFile processing environments where each user/department has its own copy of files (or data in spreadsheets) have three problems

Data redundancyInconsistent dataManagers 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 databaseHas five main components

Data Definition LanguageData Manipulation LanguageQuery languageData dictionaryUtility 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 storageScalability – accommodates growthSupport for client/serverCentral repository:Economy of scale – one big serverData exists once and is shared (not copied)DB Admin. Enforces standards, balances specific requirements vs. overall performanceControls DB Replication when neededSecurity

Database ModelsHierarchical & Network

old, e.g. FoxPro, dBasePrograms must know relationships

Object-orientedgood for BLOBs and non-transactional dataRelational

the current standard, SQL interface ad hoc (to this) interactive processingEmbedded in programsODBC, JDBC

File Access and Organization

Logical vs. Physical RecordsTypes of files

Control files, e.g. next numbers for Customer code, SKU, Invoice No.Master file, e.g. Customer, InventoryTransaction Files, e.g. Invoices (updated, volatile)Audit/Journal Files, who did what and whenHistory 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 systemsDBMS 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)

DBMSTables 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