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 NameCounty NameCity NameEmp First NameEmp Last NameEmp Full NameManager NameEmployer NameDate TimeStamp
New YorkShelbyManhattanPaulYoungPaul YoungAmerican Bank of New York1/1/2005 11:23:31 AM
FloridaJeffersonPanama CityChrisDavisChris DavisPaul YoungAmerican Bank of Florida1/1/2005 11:23:31 AM
CaliforniaMontgomerySan HoseLouisJohnsonLouis JohnsonPaul YoungAmerican Bank of California1/1/2005 11:23:31 AM
New JerseyHudsonJersey CitySamMathewSam MathewPaul YoungAmerican Bank of New Jersey1/1/2005 11:23:31 AM
New YorkShelbyManhattanNancyRobinsonNancy RobinsonPaul YoungAmerican Bank of New York1/1/2005 11:23:31 AM
FloridaJeffersonPanama CitySheelaShellumSheela ShellumChris DavisAmerican Bank of Florida1/1/2005 11:23:31 AM
CaliforniaMontgomeryShelbyJeffBillJeff BillLouis JohnsonAmerican Bank of California1/1/2005 11:23:31 AM
New JerseyHudsonJersey CityJohnBurrellJohn BurrellSam MathewAmerican Bank of New Jersey1/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

Relational Data Modeling Tutorial

Relational (OLTP) Data Modeling:

Relational Data Model is a data model that views the real world as entities and relationships.

Entities are concepts, real or abstract about which information is collected. Entities are associated with each other by relationship and attributes are properties of entities.

Business rules would determine the relationship between each of entities in a data model.

The goal of relational data model is to normalize (avoid redundancy)data and to present it in a good normal form.

While working with relational data modeling, a data modeler has to understand 1st normal form through 5th normal form to design a good data model.

Following are some of the questions that arise during the development of entity relationship data model. A complete business and data analysis would lead to design a good data model.

  • What will be the future scope of the data model? How to normalize the data?
  • How to group attributes in entities?
  • How to name entities, attributes, keys groups, relationships?
  • How to connect one entity to other? What sort of relationship is that?
  • How to validate the data?
  • How to normalize the data?
  • How to present reports?

Next: Relational Data Modeling Example