|
Database Design Document
Need an information management database
design document checklist?
What is data warehouse
architecture?
A data warehouse is the foundation for information management and
involves:
- Extracting data from operational systems;
- Moving it into data warehouse structures;
- Reorganizing and structuring the data for
analysis purposes; and
- Moving it into reporting structures called data
marts.
What are data warehouse
structures?
Data warehouse database design is based on two key design concepts:
- Relational database design; and
- Dimensional, or star schema design.
What is a database design
document?
Database design documentation may be contained in a series of documents.
- Enterprise data model is an entity relationship
diagram which builds on the conceptual data model and adds additional
details;
- Logical
data model is a
fully attributed entity
relationship diagram (ERD), which shows
each
entity, its relationship to other entitles and specifies the applicable
business rules;
- Dimensional
model, represents facts and dimensions,
which are required to store dimension data in an efficient manner for
reporting and analysis purposes; and
- Physical data model, is the final
representation of the relational database design structures that will
be
generated from the model.
What conceptual data
model documentation is required?
A conceptual data model should be a one page diagram identifying key
entities. Documentation required to support this model is generally
produced by the data model tool and should include:
- Entity name; and
- Entity definition.
What enterprise data
model documentation is required?
An enterprise data model may be a series of models showing entities and
key relationships. Documentation required to support this model is
generally produced by the data model tool and should include:
- Model definition, i.e a description of what the model
contains;
- Subject area definition (or diagram definition) e.g.
this subject area defines Customer;
- For each subject area, or diagram, document ion
should include:
- Entity name;
- Entity definition;
- Relationship definition and verb phrase e.g.
- each customer may own one or many accounts; and
- each account must be owned by one and only one
customer.
- An optional level of documentation might include:
- Attribute name; and
- Attribute description.
What logical data model
documentation is required?
A logical data model is a fully attributed model showing all entities
and attributes and all relationships, Documentation
required to support this model is
generally produced by the data model tool and should include:
- Model definition, i.e a description of what the model
contains;
- Subject area definition (or diagram definition) e.g.
this subject area defines Customer;
- For each subject area, or diagram, documentation
should include:
- Entity name;
- Entity definition;
- Attribute name;
- Attribute definition (with example);
- Domain name;
- Class name;
- Datatype;
- NULL/NOT NULL;
- Valid values (if any);
- Default values (if any);
- Primary key definition;
- Foreign key definition;
- Alternate key definition;
- Initial size (row count); and
- Expected growth
- Relationship definition and verb phrase e.g.
- each customer may own one or many accounts; and
- each account must be owned by one and only one
customer.
What physical data model
documentation is required?
A physical data model is usually generated by the data model tool.
Documentation required to support this model is
generally produced by the data model tool and should include:
- Model definition, i.e a description of what the model
contains;
- Subject area definition (or diagram definition) e.g.
this subject area defines Customer;
- For each subject area, or diagram, documentation
should include:
- Table name;
- Table definition;
- Table space definition;
- Index definition;
- Partitioning definition
- Column name;
- Column definition (with example);
- Domain name;
- Class name;
- Datatype;
- NULL/NOT NULL;
- Valid values (if any);
- Default values (if any);
- Primary key definition;
- Foreign key definition;
- Alternate key definition;
- Initial size (row count); and
- Expected growth and
- Constraint definition.
This documentation is generally stored within the data model but
reports can be generated as needed.
Summary...
Data modeling tools such as Oracle Designer,
ERwin and PowerDesigner are used to define database design. They can
create reports as need to form the basis for the database design
document.
|
