Real World: SAGE Software Data Flow Diagrams Database Normalization

1
Real World SAGE SoftwareData Flow Diagrams /
Database Normalization

• Andy Spruill
• Vertex Systems, Inc.

2
DFD - Quick Review

• Network representation of a system
• Analogous with a roadmap
• Cornerstone of system analysis and design
• Used to discover data in processes, NOT model the
  process

3
DFD Quick Review

• DFDs help to represent and communicate
• Information and data flows
• Processes that change or handle data
• Stored data
• System boundaries
• Sources and recipients of information and data

4
DFD Quick Review

• Benefits of DFDs
• Provide a pictorial, non-technical representation
• Easy to understand
• Easy to produce
• Limited number of symbols
• Simple, top-down expansion

5
DFD Quick Review

• Process of representing different detail levels
  are known as leveling or partitioning
• Highest level is the Context Diagram
• Proceeds then to Level 1 (DFD-1), Level 2
  (DFD-2), etc
• Processes are expanded until sufficiently
  detailed
• Lowest level may be Elementary Process
  Descriptions
• Narrative English, Mathematical Formulae, etc

6
Software Development LifecyclePure Waterfall
Model
Software Concept
Data Flow Diagramming takes place
Requirements Analysis
Architectural Design
Detailed Design
Coding Debugging
System Testing
7
Funnel Effect
Discovery
DFD
Normalization
Logical E-R Diagram / Detailed Design Documents
8
Sage Enterprise Suite v4.1

• Sage Enterprise Suite is a highly reliable,
  robust and integrated series of applications
  covering all areas of enterprise e-business,
  including accounting, budgeting, distribution,
  human resources, payroll, financial reporting and
  electronic commerce. Natively created on
  Microsoft compatible tools, technology and
  architecture, Sage Enterprise Suite is flexible,
  scalable and full-featured to deliver a total
  e-business management solution.

9
Schema Browser (E-R Diagram)
10
Detailed Design Document

• Describes the detailed design for a specific part
  of a system
• Placed under Change Control after its
  corresponding design has passed review
• Most companies keep these under the same control
  as source code
• No formalized structure to document

11
Detailed Design Document
12
Database Normalization

• Normalization is the process of organizing data
  in a database. This includes creating tables and
  establishing relationships between those tables
  according to rules designed both to protect the
  data and to make the database more flexible by
  eliminating redundancy and inconsistent
  dependency.

13
Database Normalization

• There are a few rules for database normalization.
  Each rule is called a "normal form." If the first
  rule is observed, the database is said to be in
  "first normal form." If the first three rules are
  observed, the database is considered to be in
  "third normal form."

14
Zero Form

• Commonly referred to as a flat-file database
• Level of data redundancy is high

15
First Normal Form (1NF)

• Eliminate repeating groups in individual tables
• Create a separate table for each set of related
  data
• Identify each set of related data with a primary
  key (PK)

16
Second Normal Form (2NF)

• Create separate tables for sets of values that
  apply to multiple records
• Relate these tables with a foreign key (FK)

17
Third Normal Form (3NF)

• Eliminate fields that do not depend on the key

18
Other Normalization Forms

• Fourth Normal Form
• In a many-to-many relationship, independent
  entities can not be stored in the same table
• AKA Boyce Codd Normal Form (BCNF)
• Fifth Normal Form
• The original table must be reconstructed from the
  tables into which it has been broken down
• Esoteric and usually not required to get the
  necessary functionality

19
Normalization Exceptions

• Adhering to the 3NF, while theoretically
  desirable, is not always practical.
• In theory, normalization is worth pursing.
  However, many small tables may degrade
  performance or exceed open file and memory
  capacities.
• It may be more feasible to apply 3NF only to data
  that changes frequently.

20
Real World Sage Enterprise Suite Database
Normalization

• Good Examples
• AR Invoices
• Cash Transactions
• Bad Examples
• Lets find some

21
Excellent Web Links

• SQL by Design Why You Need Database
  Normalization
• Microsoft TechKnowledge Database Normalization
  Basics
• Database Normalization And Design Techniques

22
Contact Info

• AndyS_at_VertexSystems.com
• [phone] x247
• http//www.vertexsystems.com