Data Modeling
Logical Model
Relational Modeling ( or E-R Modeling ) & OO Modeling
Why Modeling is important ?
Modeling helps in visualizing the business with out being cluttered with too many details and application logic. However the rules of normalization are rigorously enforced leading to clean design. Hence it would be clearly visualized and cleanly designed model.
Without modeling( in 3NF) if the table structures are implemented it will lead to what is called insert, update, and delete anomalies i.e. for eg If you have supplier, part & supplier city data all in one table and if you want to a delete a part you delete the entire row and loose other information also. You cannot add a new city unless a you have supplier & part from that city. Updating a supplier city requires updating several rows. This is NOT common sense but a source of common errors in many organizations.
Entities and Relationships
Any real world thing is an entity eg : Cat, Bus , Customer , order etc. However the entities must be relevant to the business being modeled. The relationship between them is considered to build the model. For example a CUSTOMER must place one or more ORDERS. There are many other relationships . They are (One to Many, Many to Many etc.)
Examples
 1-M : a CUSTOMER must place one or more ORDERS.
 M-M : A Company may sell to one or more customers &
A Customer may buy from one or more companies
 Recursive : A Manager manages one or more Employees
 Arc : A bank Transaction must be of Current or
checking account
 Super Types &
Sub Types : Bus, Plane, Ship are types of Vehicle
 Identifying &
non-identifying relationships : An entity can exist independently or
with reference to other entity
eg : Pin (Zip) Code is independent of State
Order is dependent on Customer
Cardinality
Number(0,1, M) of occurrences of each of entity in a relationship.
Attributes
Each entity shall have a primary key and optional other attributes.
Primary Key
It uniquely identifies an instance of an entity (or record in a table).
For eg. A Customer is identified by a customer number.

Referential Integrity
The referential integrity rule states that every foreign key value must match a primary key value in an associated table

Rules of normalization
First Normal Form
Attributes shall be atomic
No repeating values
Second Normal Form (applicable to composite PK)
Satisfies 1NF
Every non-key attribute shall depend on the whole of primary key.
Non-key attributes shall NOT depend on part of PK.
Third Normal Form
Satisfies 1NF & 2 NF
There shall NOT be any transitive dependencies between non-key
attributes.
Non-key attributes shall depend on nothing but the PK.

Physical Model
A physical model can be generated from logical model using case tools.
A physical model has the following components most of which is
generally known and will not be elaborated here.
 Tables
 Primary Key
 Foreign Key
 Constraints
 Indexes
 Triggers & stored procedures
 Delete, Insert, Update operations on Parent/Child table can be handled in various ways in the parent/child table as follows depending on business requirement.
• Restrict/dependent
• Cascade/automatic
• Nullify
• Default
• Customized
• No effect
Generally it is recommended to implement the logical model in TNF. However some times for various reasons like performance it is brought back to SNF & this is called de-normalization.
Map Logical to Physical Model

Logical Physical Remarks
Entity Table
Attribute Column
UID(Unique Identifier) Primary Key/Alternate Key There should NOT be any intelligence built into PK. It should be a simple number
Referential Integrity Foreign Key
Data Validation Constraints
Business Rules Triggers/Stored Procedures
M-M Relationship Associative table
Super /Sub Type Table There are 3 ways of implementing this i.e. Rollup, Roll down and one table per entity ( super type & sub

12Next ››

You do not have flash or javascript support.

Average (1 vote)

Reply