200 likes | 529 Views
Database Design Steps. Conceptual DesignDefining the projectEstablishing the methodologyGathering the informationLogical DesignProducing the database documentationPhysical DesignCreating and testing the database. Conceptual Design Goals. Scope the projectEstablish goalsEstablish limitsHard
E N D
1. Database Design Design and Documentation
2. Database Design Steps Conceptual Design
Defining the project
Establishing the methodology
Gathering the information
Logical Design
Producing the database documentation
Physical Design
Creating and testing the database
3. Conceptual Design Goals Scope the project
Establish goals
Establish limits
Hardware
Software
Evaluate resources
People
Existing documentation
4. Laying the Groundwork Set Project Standards and Guidelines
Select a design methodology
Define documentation standards for:
ERD symbols and naming conventions
Transaction Analysis sheets
Metadata Dictionary
5. Database Design Documentation Metadata Dictionary
Transaction Analysis sheets
Database ERD
CRUD diagram
6. Metadata Dictionary What is the Metadata Dictionary?
A “living document”
Contains “all information” on the project
“One-stop shopping” place for information
Repository and clearing house for all information
7. Conceptual Design What is IN the Metadata Dictionary?
Project Standards and Guidelines
Statement of goals and scope
Design methodologies used
As they are uncovered include:
List of the known Business Rules
Terms, definitions and formulas
8. Conceptual Design Eventually the Metadata Dictionary will include:
“Always current” database ERD
CRUD diagram
Detailed transaction sheets
Table/column definitions and datatypes
A table of contents and index for the MDD
9. Gather Project Information Examine existing system
Training Manuals
Interviews
Determine key decision makers
Determine knowledgeable (accurate) sources
Determine receptive (accessible) sources
Observation
Determine enhancements
Incorporate new project goals
10. Starting the Metadata Dictionary Establish and collect Business Rules
Document the rule, the date and the source
Organize the business rules appropriately into the metadata dictionary
Document the business “workflow”
DFDs, Workflow diagrams, Use Cases
11. Starting the Logical Design Phase Input:
Project goals
Business work flow
Business rules
Output:
Well documented Database design
Used as a blueprint for Physical Design
12. ERD and Transaction Sheets Once the business “workflow” is established:
Start a “rough” ERD
Use Normalization techniques based on the business rules
Start “rough” Transaction Analysis sheets
13. Transaction Analysis documentation For each Business Function uncovered, start a “rough” transaction analysis sheet.
Document the transaction as best as possible
As the ERD evolves, evolve the transactions as well
Some initial transactions may be split and others may be combined depending on business needs
14. What information should a Transaction Analysis have? Identifier
A number and short name,
Full verbal description
Detailed “transaction code” description
Relevant data
Time of day, priority, frequency, security issue and user access
List of affected tables
Pertinent business rules necessary to understand the transaction
15. A “CRUD” diagram CRUD stands for:
Create
Read
Update
Delete
Merges the database design (as shown in the ERD) and the business functions/transactions
16. Purpose of the CRUD The CRUD diagram will show on one sheet:
All transactions and the tables they interact with
Potential locking conflicts between transactions
Table activity
17. Using the CRUD, ERD and TA sheets the DBA can... Estimate, eliminate or minimize potential locking conflicts
Prioritize or maximize disk I/O throughput utilization
Estimate correct indexing strategies
Establish database security
18. What’s left? Table Analysis Assessing initial table disk sizes and future growth potential and file placement
Assessing table structures and indexing options
Table/Index maintenance plans
Logical guesses can be made from the ERD, TAs and CRUD
19. Documentation Resources and Depth? Fit the documentation to the project
Small designs:
Spiral notebook and folders
Larger designs:
On-line documentation and files
Large “Mission Critical” systems:
Perhaps a separate database to store the documentation
Full-time “librarian” to maintain the documentation
20. The End