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 |
In the next page, we will discuss how to resolve these problems in order to design a good relational data model.