Data Cardinality

Problem: 

I am given the results of an industrial extension jobs survey and asked to report to each state senator and house member the exact number of jobs created in their district.  State senators and house members also want to see how neighboring districts have performed.

Analysis Procedures: 

In order to display the jobs in the House districts and Senate Districts on the map, I need to review the job survey excel data first and select only NC zip code jobs or employee and summarize the jobs per Zip code. Then I extracted only NC zip code point layer and made a tabular join with summarized job data. and finally the House and Senate districts layer spatially joined with joined job points layer and projected two choropleth maps.

After downloading an Excel sheet about job survey, NC House districts and Senate districts shape file from NC one map and as well as US zip point shape layer file from Arcgis.com. in my work folder, I added all three types of data in Arc Map. By choosing select by attribute, I selected only NC Zip code points and exported data as a shape file for NC zip code points. After reviewing the excel sheet, I found that some data belongs to outside of NC. Therefore, by choosing select by attribute, I exported the data as only NC job survey tabular data. I found that total employees need to be aggregated per zip code. After aggregating employee sum per zip code, I needed to join with NC zip code points data with the tabular employee data with common zip fileld. Then after reviewing the join table in Zip code point shape file, by select by attribute in Quary builder, a layer including more than 0 jobs in zip code points has been made. I graduated the quantity of points of Job. Finally, the House districts data spatially joined with this new point layer specifying summed jobs and a new layer has been created with graduated color of House districts on the basis of number of jobs per house district. In this way the Senate districts data spatially joined with this new point layer and a new layer has been created with graduated color of Senate Districts according to number of jobs per senate district. Now I can check with the data to make sure that whether or not graduated points and graduated polygons have been matched or superimposed.

                                  Workflow Diagram

Results:

Application & Reflection:

Database Cardinality is very important process where one to one, one to many, many to one and many to many recorded data relationships we can process. In this project, while I was summarizing the tabular data, I was processing many to one relationship by aggregating total jobs per zip code.

While I joined the Zip code point data with aggregated tabular data, I used data cardinality one to one relationship, like number of jobs related to per zip code points.

During spatial joining time of House or senate districts with Zip code point or job points layer, one to one data cardinality relationship worked, because I found one job points in one polygon.

Any profit and nonprofit organization as well as Govt organizations’ employees or customers data can be projected in Zip code points as well as spatially joined with any districts and county boundaries or zonal boundaries.

Through data cardinality attributes are transferred to the feature class like point, line or polygon and project or locate the data spatially by using spatial location.

For example, if we work with number of Doctors in hospitals of a state, I need to know number of Doctors over the state. First we need to aggregate the Doctors in zip codes of the state, then tabular join with Zip code point layer and finally the county layer spatially join with Zip code point layer containing the number of Doctors.

Back to top