The completed relational data model is shown in the previous part and the corresponding data stored in database are shown in separate tables below.
|State Code||State Name||Date TimeStamp|
|NY||New York||1/1/2005 11:23:31 AM|
|FL||Florida||1/1/2005 11:23:31 AM|
|CA||California||1/1/2005 11:23:31 AM|
|NJ||New Jersey||1/1/2005 11:23:31 AM|
|County Code||County Name||Date TimeStamp|
|NYSH||Shelby||1/1/2005 11:23:31 AM|
|FLJE||Jefferson||1/1/2005 11:23:31 AM|
|CAMO||Montgomery||1/1/2005 11:23:31 AM|
|NJHU||Hudson||1/1/2005 11:23:31 AM|
|City Code||City Name||Date TimeStamp|
|NYSHMA||Manhattan||1/1/2005 11:23:31 AM|
|FLJEPC||Panama City||1/1/2005 11:23:31 AM|
|CAMOSH||San Hose||1/1/2005 11:23:31 AM|
|NJHUJC||Jersey City||1/1/2005 11:23:31 AM|
|Emp Id||State Code||County Code||City Code||Manager Id||Emp First Name||Emp Last Name||Emp Full Name||Date TimeStamp|
|1||NY||NYSH||NYSHMA||Paul||Young||Paul Young||1/1/2005 11:23:31 AM|
|2||FL||FLJE||FLJEPC||1||Chris||Davis||Chris Davis||1/1/2005 11:23:31 AM|
|3||CA||CAMO||CAMOSH||1||Louis||Johnson||Louis Johnson||1/1/2005 11:23:31 AM|
|4||NJ||NJHU||NJHUJC||1||Sam||Mathew||Sam Mathew||1/1/2005 11:23:31 AM|
|5||NY||NYSH||NYSHMA||1||Nancy||Robinson||Nancy Robinson||1/1/2005 11:23:31 AM|
|6||FL||FLJE||FLJEPC||2||Sheela ||Shellum||Sheela Shellum||1/1/2005 11:23:31 AM|
|7||CA||CAMO||CAMOSH||3||Jeff||Bill||Jeff Bill||1/1/2005 11:23:31 AM|
|8||NJ||NJHU||NJHUJC||4||John||Burrell||John Burrell||1/1/2005 11:23:31 AM|
|Employer Id||Employer Name||DateTimeStamp|
|1001||American Bank of NewYork||1/1/2005 11:23:31 AM|
|1002||American Bank of Florida||1/1/2005 11:23:31 AM|
|1003||American Bank of California||1/1/2005 11:23:31 AM|
|1004||American Bank of New Jersey||1/1/2005 11:23:31 AM|
Employee Employer XREF:
|Employee Id||Employer Id||DateTimeStamp|
|1||1001||1/1/2005 11:23:31 AM|
|2||1002||1/1/2005 11:23:31 AM|
|3||1003||1/1/2005 11:23:31 AM|
|4||1004||1/1/2005 11:23:31 AM|
|5||1001||1/1/2005 11:23:31 AM|
|6||1002||1/1/2005 11:23:31 AM|
|7||1003||1/1/2005 11:23:31 AM|
|8||1004||1/1/2005 11:23:31 AM|
Upon discussion with business analysts, data modeler can come up with the following conclusions regarding grouping and relationship between the data. These conclusions play a vital role in designing the data model as well as expanding for future scope.
- Many cities can be in one county. City names will be unique across the country.
- Many counties can be in one state. County names will be unique across the country.
- Many states can be in USA. State names will be unique across the country USA.
- One employee can work with many branches at same time.
- For some employees, managers may not be there.
In order to implement the above decisions, relational data modeling is done in the following manner.
- To achieve normalization, relevant attributes of employee, employer lookup, state lookup, county lookup and city lookup tables should be grouped and created.
- In order to validate the data of employee table, employee table has been connected to state, county, and city lookups. Whenever state, county, city data is entered in employee table, data would be checked against respective lookup tables and correct data is stored. Hence there is no need to carry redundant data of state, county, city lookup in employee table.
- All tables are identified by primary keys(PK). So data can be uniquely identified from tables.
- Records can be inserted or updated directly in the respective lookup table. For example if a state name changes, then the change will be updated only in the state lookup, hence this change will not affect other tables like employee.
- Since one employee can work in many branches at the same time, table EmployeeEmployerXREF has been created and it resolves many to many relationships.
- Since an employee can be a manager in many occasions, column “manager identifier” has been added and becomes a foreign key to column employee identifier. The “manager identifier” column would contain the same value as of an employee identifier. Sometimes it may contain null values also. For example, Paul Young is the topmost person and doesn’t have any managers.
- A new column DateTimeStamp has been added to all tables. This column gives the information about the date and time when the row was inserted or updated.
The completed relational data model is shown in Figure below and the corresponding data are shown in separate tables in the next page.
Relational Data Modeling Example Diagram:
Next: Relational Data Modeling Example – Part 3
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||Date TimeStamp|
|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.
Next: Relational Data Modeling Example – Part 2