Embedded SQL

Database description

• Each county (i.e., each grid cell) contains the following information. A name. A list of between 1 and 4 cities located within that county. There is one of the cities in each county designated as the county seat. Each city has associated with it its population size.
• There is a list of highways between pairs of cities. (Obviously the pair of cities do not have to be in the same cell or in neighboring cells. However a highway should not be longer in Taxi-Cab distance than 6.) The graph of highways needs to be connected. Each highway has a name, a distance, and an estimated duration to travel between the two cities linked by the highway. Certain highways carry the designation ``scenic.''

Tasks

• Specify a conceptual and physical design for a relational database to model this (very, very simple) geographic database. The conceptual design would typically be done as an ER-diagram or as an object-oriented design. In the physical design, your conceptual design needs to be mapped to relations (with possible keys, indexes etc.)
• Define using SQL's data definition language your physical design into a SYBASE database.
• Populate your database with random data. (This random data will be supplied to you in the very near future as an ascii file.) A county is called CountyIJ if the county is located at the x-coordinate I and the y-coordinate J. Call the cities CityIJN, IJ denotes the name of the county, N the name of the city in that county. The county seat is always city 1. Call the highway HighwayIJKLMN, if the highway connects CityIJM to CityIJN. Distances and duration are set realistically but with some randomness built in.
• Solve the following problems in embedded SQL. (REMARK: you are not allowed to load the entire database in main memory and make this a simply data structures assignment.)
  • Write an application that takes as input two cities (for simplicity I will refer to these cities as cite1 and city2) and determines
    • The shortest path from city1 to city2. You are to write out this path and give its distance and duration. Also give a list of counties through which the path runs.
    • Whether there exist scenic paths between city1 and city2, and if so, writes out the shortest of these paths.
  • Lists the pairs of neighboring counties that are not directly connected by a highway.
  • List the pairs of county seats that are connected by a scenic highway.
  • Determines the population size in the North Eastern counties.
  • List the quadrant which have the maximum distance of scenic highways.
  • Given as input a city C and a distance D, list all the cities that have a distance to C smaller than D.
  An interesting online site to learn more about SYBASE is available on the web at http://www.dgsys.com/~dcasug/sybintro/intro.html.