There are several stages of a Data Model's life cycle and we need to understand what they are


Stage #1: Gathering Business Requirement(s)

The Data Modeler needs to interact with the business analysts to get the functional requirements.  They also need to work with the end user to out the reporting needs and service level requirements.  


Stage #2: Conceptual Data Model (CDM)

The data model includes entities at a high level.  Since this is the initial phase of what the model will look like,  this model will not show much detail.  This will allow the user not to get confused and understand what the system is doing.

Highlight(s):

• The CDM is the first step in constructing a data model in a top-down approach.  It is a clear visual representation of the organizations business needs.
• CDM displays the overall structure of the database and provides a high level of information on the subject areas of the organization
• CDM will start with the main subject area and then continue through all the different entities of each subject area and understand it in detail.
• CDM contains data structures that have not been implemented in the database.
• Within this phase, technical and non-technical teams can present their ideas for building a sound data model.
• This model contains entity types and relationships (one to one, one to many, and many to many).

Stage #3: Logical Data Model (LDM)

This stage will take the high level entities that were created in stage #2 and enhances the model to show the organization's business requirements. This includes changing the entities to tables and adding details to the tables.  These details are shown by adding attributes (columns), relationships between tables (primary & foreign keys), and business requirements (constraints).

Component	Description
Entity	Object, Event or phenomenon about what data is stored in the database.  This represents a collection of similar objects.  This can represent a collection of people, places, things, events or concepts.  Entities only contain data. Examples include customers, orders, items, taxes.
Attribute	Each entity will have one or more data attributes.  An Attribute is a particular description that defines a property of an entity.
Primary Key (PK)	This is a key that is used to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced.
Candidate key	This is a field or combination of fields that can act as a primary key field for that table to uniquely identify each record in the table.
Alternate Key	An Alternate key is an candidate key with is not selected to be the primary key.
Rule	Business Rules, this is how each organization runs their business.  Also how attributes are attached to entities.
Relationship	This is a named connection or association that links entities together.
Definition	A description of the attribute or entity

Stage #4: Physical Data Model (PDM)

This is the complete model to be implemented for the organization.  This includes tables, columns, relationships, and constraints needed to create a physical implementation of the model.

This can also be designed for a specific vendor.

Component(s)	Description
Table	A Collection of rows (records) that have associated columns (fields) that help in describing each row.
Column	A data structure that contains individual data item that helps describe a row.
Primary Key (PK) Constraint	This is used to uniquely identify each row in a table.
Unique Constraint / Unique Index	This creates a requirement that a row will be unique compared to all the other rows in the table.  The difference between a this and a primary key is that this can have a null value. A primary key (PK) does not allow for a NULL value.
Non-unique Index	This allows for more than one row in the table to have the same data.  This is used on date fields, foreign key constraint fields, etc...
Check Constraint/Default Value	These are implementations of business rules.  Example could be that a column can not be NULL, a piece of data could be enter in if the value is NULL.
Foreign Key (FK) Constraint	This is a constraint in which this field is a primary key in a different table.  This can be used to link parent (PK) and child (FK) tables together.
Comment	The is needed to make remarks on the table(s) or column(s) in the database.

Stage #5: Database

The Physical model is generated into vendor specific SQL Code and executed against the database server to create the target database.


NOTE: It is important to have the physical model generated to SQL Code.  With human intervention comes mistakes.  We use the script to that the creation process is the same each time.  The database script will be executed more than once and may even have different versions.  By having the script available the DBA can know what has changed from version to version by running a "Diff" tool on the different scrips.