Systems Analysis & Design: Topic 11
Database and File Design


Introduction


Approaches to Data Storage

 


Conventional Files

Sequential Access

Random Access

Advantages of Conventional Files:

Disadvantages of Conventional Files:


Databases

Advantages of Databases:

Disadvantages of Databases:


Data Concepts



Entity-Relationship Diagrams

 

 

 


Relational Databases


Normalization

Normalization Example
SALES-REPORT (SALESPERSON-NUMBER, SALESPERSON-NAME, SALES-AREA, 
      (CUSTOMER-NUMBER, CUSTOMER-NAME, WAREHOUSE-NUMBER, WAREHOUSE-LOCATION, SALES-AMOUNT) )

* the inner set of parentheses represents the repeating group.

First Normal Form (1NF)


SALESPERSON (SALESPERSON-NUMBER, SALESPERSON-NAME, SALES-AREA)

SALESPERSON-CUSTOMER (SALESPERSON-NUMBER, CUSTOMER-NUMBER, CUSTOMER-NAME, WAREHOUSE-NUMBER, WAREHOUSE-LOCATION, SALES­AMOUNT)

Second Normal Form (2NF)

SALES (SALESPERSON-NUMBER, CUSTOMER-NUMBER, SALES-AMOUNT)

CUSTOMER-WAREHOUSE (CUSTOMER-NUMBER, CUSTOMER-NAME, WAREHOUSE-NUMBER, WAREHOUSE-LOCATION)

Third Normal Form (3NF)

CUSTOMER (CUSTOMER-NUMBER, CUSTOMER-NAME, WAREHOUSE-NUMBER)

WAREHOUSE (WAREHOUSE-NUMBER, WAREHOUSE-LOCATION)

SALESPERSON (SALESPERSON-NUMBER, SALESPERSON-NAME, SALES-AREA)

SALES (SALESPERSON-NUMBER, CUSTOMER-NUMBER, SALES-AMOUNT)

CUSTOMER (CUSTOMER-NUMBER, CUSTOMER-NAME, WAREHOUSE-NUMBER)

WAREHOUSE (WAREHOUSE-NUMBER, WAREHOUSE-LOCATION)



Guidelines for Database Design

  1. Do not combine two distinct entities in one file.
  2. A specific data field should exist only in one master file.
  3. Each database relation should have programs to Read, Add, Modify, and Delete records.

Data Storage

Controlling Redundancy

Planning for Performance

Building in Flexibility

Insuring Integrity and Security