Categories: Data Modeling Types

Relational Data Modeling Example

The sample source data shown in the table below provides the information about employees, their residential state, county, city and their employer names and manager names. It also describes employees working for an “American Bank” that has got many branches in several states. From data modeler point of view, analysis of the source data raises following questions.

How to group and organize the data?
How to avoid de-normalization since employee’s residential data like state name, county Name, city Name are repeated in most of the records.
What sort of relationship is between employer and employee?
What sort of relationship is between the employee and state, city, county?

Sample Source Data:

State Name	County Name	City Name	Emp First Name	Emp Last Name	Emp Full Name	Manager Name	Employer Name	DateTimeStamp
New York	Shelby	Manhattan	Paul	Young	Paul Young		American Bank of New York	1/1/2005 11:23:31 AM
Florida	Jefferson	Panama City	Chris	Davis	Chris Davis	Paul Young	American Bank of Florida	1/1/2005 11:23:31 AM
California	Montgomery	San Hose	Louis	Johnson	Louis Johnson	Paul Young	American Bank of California	1/1/2005 11:23:31 AM
New Jersey	Hudson	Jersey City	Sam	Mathew	Sam Mathew	Paul Young	American Bank of New Jersey	1/1/2005 11:23:31 AM
New York	Shelby	Manhattan	Nancy	Robinson	Nancy Robinson	Paul Young	American Bank of New York	1/1/2005 11:23:31 AM
Florida	Jefferson	Panama City	Sheela	Shellum	Sheela Shellum	Chris Davis	American Bank of Florida	1/1/2005 11:23:31 AM
California	Montgomery	Shelby	Jeff	Bill	Jeff Bill	Louis Johnson	American Bank of California	1/1/2005 11:23:31 AM
New Jersey	Hudson	Jersey City	John	Burrell	John Burrell	Sam Mathew	American Bank of New Jersey	1/1/2005 11:23:31 AM