###BeginCaseStudy###
Case Study: 1
Scenario 1
Background
You are developing a flight information consolidation service. The service retrieves flight
information from a number of sources and combines them into a single data set. The
consolidated flight information is stored in a SQL Server database. Customers can query
and retrieve the data by using a REST API provided by the service. The service also offers
access to historical flight information. The historical flight information can be filtered and
queried in an ad hoc manner. The service runs on a Windows Azure Web Role. SSL is not
used.
Business Requirements
• A new data source for historical flight information is being developed by a contractor
located on another continent.
• If a time zone is not specified, then it should be interpreted as Coordinated Universal
Time (UTC).
• When you upgrade a service from a staging deployment to a production deployment,
the time that the service is unavailable must be minimized.
• The default port must be used for HTTP.
Technical Requirements
The existing sources of flight information and the mechanism of exchange are listed below.
• Blue Yonder Airlines provides flight information in an XML file.
• Consolidated Messenger provides flight information in a Microsoft Access database
that is uploaded every 12 hours to the service using SFTP. The company uses port 22 for
SFTP.
• Margie’s Travel provides and consumes flight information using serialized ADO.NET
DataSets. Data is periodically synced between the service and Margie’s Travel.
• Trey Research provides data from multiple sources serialized in proprietary binary
formats. The data must be read by using .NET assemblies provided by Trey Research. The
assemblies use a common set of dependencies. The current version of the Trey Research
assemblies is 1.2.0.0. All assemblies provided by Trey Research are signed with a key pair
contained in a file named Trey.snk, which Trey Research also supplies.
• The application specification requires that any third-party assemblies must have
strong names.
Application Structure
###EndCaseStudy###
Historical flight information data will be stored in Windows Azure Table Storage using the
FlightInfo class as the table entity. There are millions of entries in the table. Queries for
historical flight information specify a set of airlines to search and whether the query should
return only late flights. Results should be ordered by flight name. You need to specify which
properties of the FlightInfo class should be used at the partition and row keys to ensure that
query results are returned as quickly as possible. What should you do? (Each correct
answer presents part of the solution. Choose all that apply.)
A.
Use the WasLate property as the row key.
B.
Use the Airline property as the row key.
C.
Use the WasLate property as the partition key
D.
Use the Arrival property as the row key.
E.
Use the Airline property as the partition key.
F.
Use the Flight property as the row key.
Correct answer should E and F.
Airline must be the partition key and Flight must be the row key
E and F is the right answer in similar questions.
E and F are true
E and F can’t be, cause would duplicate the key (partitiion + row)… flight can repeat in airline…
I think the answer should be E and D for PartitionKey and RowKey accordingly.
Logic
——
1. Let’s look at https://msdn.microsoft.com/en-us/library/azure/hh508997.aspx
– The RowKey property stores string values that UNIQUELY identify entities within each partition. The PartitionKey and the RowKey together form the primary key for the entity.
– The primary key for an Azure entity consists of the combined PartitionKey and RowKey properties, forming a single clustered index within the table.
– Clustered index usually implies quicker search on underlying values.
Now take a look at the available answers. The only unique property for RowKey corresponds to Arrival.
– As mentioned above in the previous comment, Flight number is not unique since it usually repeats.
–> RowKey = Arrival
2. Query requirements of this question imply “…Queries for historical flight information specify a set of airlines to search and whether the query should return only late flights…”
So we should probably search for Airline and narrow results by late flights that can be almost instantly evaluated from Arrival (time greater than).
3. Of course, the sorting requirement “…Results should be ordered by flight name…” would make Flight as a viable option for PartitionKey.
– This would be also quite easy to get Airline out of Flight number as first two characters of the latter (see the code of of HistoricalDataLoader.GetAirline).
– Part of query would look like “SUBSTRING(Flight, 0, 2) in (…)”
BUT… Flight in not present in the answers for PartitionKey.
Combination of WasLate and Arrival does not fit in the stated search query.
–> The residual choice is PartitionKey = Airline
So logical answers are:
Airline
Arrival
This corresponds to DE (or ED to be in correct order).
Do you mean to say that Arrival time is unique and doesn’t repeat? Clearly E and F combination represents the unique value as a primary key.
No it doesn’t represent. Flight is usually repeated each week. You can find the same Flight each Sunday for example.
In fact only Flight+Arrival provide the 100% correct unique key.
(Arrival is a DateTimeOffset type so it also contains the time zone).
However since the Flight field as Partition key is absent among the answers – we should use Airline instead of the Flight. It will also speed up the desired query.
And then we should hope that within the same time zone there is never the case when two different flights of the same airline land at the same moment of time (e.g. in different airports). But it is quite low possibility I beleive.
An airline can have multiple flights arrive at the same time? Based on your logic, then Airline/Arrival should not be the partition/row key partition since it is not unique.
“Results should be ordered by flight name.”
what about this condition in the question??
Since only 1 option is allowed for each key, then I believe it is Airline/Flight for Partition/Row keys respectively.
However, if it were an option, I may even do: Airline for partition, and combination of Flight-Arrival as the row key for a truly unique identifier, similar to the example presented in the 70-487 reference book.
As flight is unique for the flight across airlines e.g. BA715, BA represents the airline (Ignore weekly flights) then partition should be flight, row should be arrival to make the whole thing unique across time. However we aren’t offered these options. Nothing else works not even Airline, Arrival as big airlines many more than 60 flights an hour landing in a timezone. My guess is the question is not correct.