Building Conceptual Data Model in DBMS

The tasks involves in building conceptual data model are:

• Identify entity types
• Identify relationship types
• Identify and associate attributes with entity or relationship types
• Determine attributes domains
• Determine candidate, primary, and alternate key attributes
• Consider use of enhanced modeling concepts (optional step)
• Check model for redundancy
• Validate conceptual model against user transactions
• Review conceptual data model with user

Identify relationship types

• Use Entity-Relationship (ER) diagrams
• Determine the multiplicity constraints of relationship types
• Used to check and maintain data quality
• Results in better representation of the data requirements of the enterprise
• Check for fan and chasm traps
• Document relationship types

Identify and associate attributes with entity or relationship types

• Simple/composite attributes
• Single/multi-valued attributes

             - Two approaches, separate entity or as a multi-valued

• Derived attribute
• Document attributes

Determine attribute domains

• A domain is a pool of values from which one or more attributes draw their values e.g. staffNo, sex etc.
• Document attribute domains

Determine candidate, primary, and alternate key attributes
Primary key guidelines

• The CK with the minimal set of attributes
• The CK that is least likely to have its values changed
• The CK with fewest character (strings)
• The CK with smallest maximum value (numerical)
• The CK that is easiest to use from the user's point of view

Strong and weak entities
Document PK and AKs

Consider use of enhanced modeling concepts (optional step)

• As a useful 'rule of thumb' when considering the use of these concepts, always attempt to represent the important entities and their relationships as clearly possible in the ER diagram.
• Therefore, the use of advanced modeling concept should be guided by the readability of the ER diagram and the clarity by which it models the important entities and relationships.

Check model for redundancy

• Re-examine one-to-one (1:1) relationships
• Remove redundant relationships
• Consider time dimension

Validate conceptual model against user transactions

• We examine two possible approaches to ensuring that the conceptual data model supports the required transactions:
• Describing the transactions
• Using transaction pathways

Review conceptual data model with user

• To review the conceptual data model with the users to ensure that they consider the model to be a 'true' representation of the data requirements of the enterprise
• If any anomalies are present in the data model, we must make the appropriate changes, which may require repeating the previous step.
• We repeat this process until the user is prepared to 'sign off' the model as being a 'true' representation of the part of the enterprise that we are modeling