Article Surfing Archive

Entity-relationship analysis comprises of three major abstractions to describe data. These are entities, relationships and attributes.

Here entities are the distinct things in the enterprise. Relationship is the relation between the entities and attributes are the nature or properties of the entities.

So in a software design scenario we assimilate similar objects in sets and call these sets as entities. We then model all interactions between the objects within the entity sets by relationships or relationship sets. Relationship sets are more difficult to perceive than entity sets. We can see the entities but relationships are always underlying and we cannot see them. This makes data analysis difficult because it is necessary to create models of things that do not physically exist as single objects.

There are two major parts in entity-relationship scenario. They are Conceptual data modeling and Physical data modeling.

The Physical Data Model specifies the physical implementation of the database.

With the Physical Data Model, we consider the details of actual physical implementation. It takes into account both software or data storage structures. We can modify the PDM to suit our performance or physical constraints.

The Physical Data Model fills the following roles:

Represent the physical organization of data in a graphic format

Generate database creation and modification scripts

Define referential integrity triggers and constraints

Generate extended attributes

Reverse engineer existing databases

Regenerate a Conceptual Data Model

Physical Data Model creation

There are several ways to create a Physical Data Model:

Generate a Physical Data Model from a Conceptual Data Model

Create a Physical Data Model from scratch

Reverse engineer a database into a Physical Data Model

Objects in a Physical Data Model

A Physical Data Model graphically represents the interaction of the following objects:

Table : Collection of rows (records) that have associated columns (fields)

Column : Data structure that contains an individual data item within a row (record), model equivalent of a database field

Primary key : Column or columns whose values uniquely identify a row in a table

Foreign key : Column or columns whose values depend on and migrate from a primary key in another table

Index : Data structure that is based on a key and that speeds access to data and controls unique values

Reference : Link between the primary key and the foreign key of different tables

View : Data structure that results from a SQL query and that is built from data in one or more tables.