AngularJS and REST API: Part 4

• Download demo code - 284.1 KB

Introduction

In AngularJS and REST API: Part 2, I showed a database table called Airport with airport data from https://openflights.org/data.html. (The same table is also used in Part 3.) There are two issues with this table:

1. There is a lot of unnecessary data duplication because the name of the City where the airport is located is spelled out completely. San Diego, for example has three airports, so the city name "San Diego" is duplicated in the table three times. Removing this redundancy is a process called normalization.
2. The state the City is in does not appear in the data. Granted, you can often deduce the state from the name of the city, for example, most everyone knows Los Angeles is in California, but what if the city is Springfield? (There are more than 30 Springfields in the US!)

In this part 4, I will address both issues. To solve the first issue, I have created a new table called City where each row contains information for a city. Each row in the City table has a unique ID, CityId, and that ID in turn will be used to identify the city in the new airport table, AirportNew. In database parlance, the CityId is a foreign key, and the relationship is one-to-many since there can be many airports in one city (like San Diego or Sacramento), but an airport can only be in one city. How a one-to-many relationship is configured in Entity Framework is described below. To address the second issue, the state is in the City table (as well as the name of the city, county, and latitude and longitude).

Using the Code

Download the AirportDatabaseProjectPart4.zip file and extract it. It consists of a directory called AngularJS_REST_API_AirportLocator which contains the JavaScript and HTML files, six SQL script files, and the Visual Studio 2015 project AirportsAPI. Open the AirportsAPI project with Visual Studio. You will need to change the AirportEntities and AirportNewEntities connection strings in Web.config to point to your database by setting [YOUR_SERVER_NAME] and [YOUR_DATABASE_NAME] the name of your server and database, respectively. Build it by pressing F6; the project should build successfully with no errors. Press F5 to run the AirportsAPI project in debug mode. As described in AngularJS and REST API: Part 2, you will need to change 'YOUR CREDENTIALS' in ListController.js to your Bing credentials. I'm using Entity Framework's database first model so I have provided six SQL scripts to create and populate the database tables. In the CreateAirportTable.sql, CreateCityTable.sql and CreateAirportTableNew.sql script files, change [YOUR_DATABASE_NAME] to the name of your database and then execute the three scripts in SQL Server Management Studio (SSMS) to create the three tables. After the tables have been created, you can populate them by executing AirportDataBaseLosAngeles.sql, InsertIntoCityTable.sql, and InsertIntoAirportNewTable.sql scripts in SSMS.

Table Joins

Most readers are aware that table joins are one of the most powerful features of a relational database, and are used to combine rows from two or more tables based on a related column, in this case, CityId. To illustrate, here is the SQL to join the AirportNew and City tables using a left join which returns all records from the left table (AirportNew), and the matched records from the right table (City). Since every CityId in the AirportNew table has a corresponding CityId in the City table, there are no NULL rows.

select AN.Name, AN.CityId, AN.ICAO, AN.Latitude, AN.Longitude, City.Placename as 'City Name',
City.AdminName2 as County, City.AdminCode1 as State from AirportNew AN left join City
on AN.CityId = City.CityId

A right join, as you would expect, returns all records from the right table (City), and the matched records from the left table (AirportNew). However, since there are CityIds in the City table that are not referenced in the AirportNew table, there are some NULL rows. In other words, the cities 'Coalinga' and 'Huron' are in our City table with CityId of 8 and 9, but there are no rows in the AirportNew table with a CityId of 8 or 9, hence the NULL rows as shown below:

select AN.Name, AN.CityId, AN.ICAO, AN.Latitude, AN.Longitude, City.Placename as 'City Name',
City.AdminName2 as County, City.AdminCode1 as State from AirportNew AN right join City
on AN.CityId = City.CityId

Configuring One-To-Many in Entity Framework

Since I am using Entity Framework's Database First model, I added an ADO.NET Entity Data Model and Visual Studio auto-generated the City and AirportNew classes. In order to implement the one-to-many relationship, Entity Framework creates a collection navigation property public virtual ICollection<AirportNew> AirportsNew in the City class:

public partial class City
{
	public City()
	{
		this.AirportsNew = new HashSet<AirportNew>();
	}
	public int CityId { get; set; }
	(remaining properties)
	...
	public virtual ICollection<AirportNew> AirportsNew { get; set; }
}

And it creates a reference navigation property public virtual City City in the AirportNew class:

public partial class AirportNew
{
	public int ID { get; set; }
	public int VendorID { get; set; }
	public string Name { get; set; }

	public int CityId { get; set; }
	public string IATA { get; set; }
	(remaining properties)
	...

	public virtual City City { get; set; }
}

API

The API for this Part 4 is the same as Part 3, except data is fetched from the AirportsNew table, and the word 'New' is added to the API so for example, AirportsByRect is now AirportsNewByRect: curl -X GET http://localhost:55213/api/AirportsNewByRect/34.4/-119.3/33.7/-117.9/.

I've also created new HTML files in AngularJS_REST_API_AirportLocator to use the AirportNew table; the new HTML files are AirportLocaterApproach1New.html, AirportLocaterApproach2New.html and AirportLocaterApproach3New.html. Like the HTML files in the previous projects, they demonstrate the AngularJS ng-repeat, AngularJS Autocomplete, and AngularJS md-tab and md-list directives, respectively. In all three, I've added a button that displays the CityId; clicking on it displays the City information from the city table as shown in the image above.

Conclusion

The concepts of table joins, foreign keys, and one-to-many relationships are essential to working with relational databases. Entity Framework has conventions for implementing these database features.

Version 4.0.0.0