Imagine that you have been hired to design a database service containing informa
ID: 3886020 • Letter: I
Question
Imagine that you have been hired to design a database service containing information about cities and towns in the United States, as described in Example 1.2. Suggest two possible implementations for the database. A company is developing a database system containing information about cities and towns in the United States. There are many thousands of cities and towns, and the database program should allow users to find information about a particular place by name (another example of an exact-match query). Users should also be able to find all places that match a particular value or range of values for attributes such as location or population size. This is known as a range query.]Explanation / Answer
Creation of the Address table:
It contains the following fields:
Addresses
Id int
StreetName varchar(50)
City_Id int Default NULL
State_Id int Default NULL
County_Id int Default NULL
Zip varchar(6) // Will handle all American zips
Latitude decimal(10,8) // Allows for ~1mm accuracy at equator. Default NULL
Longitude decimal(11,8) // Allows for ~1mm accuracy at equator. Default NULL
Cities
City_Id int // primary Key
name varchar(45)
State_id int // foriegn key to States table
County_Id int // foriegn key to Counties table
States:
State_Id int // primary key
State_Name varchar(50)
County_Id int // foriegn key to Counties table
Counties:
County_Id int
County_Name varchar(50)
Sytax for the creation of the tables:
Create table Counties(
County_Id int primary key not null,
County_Name varchar(50)
)
Create table States(
State_Id int primary key not null,
State_Name varchar(50),
County_Id int References Counties(County_Id)
)
Create Table Cities(
City_Id int primary key not null,
name varchar(45),
State_id int References States(State_Id),
County_Id int References Counties(County_Id)
)
Create Table Address(
Id int(P),
StreetName varchar(50),
Zip varchar(6) ,
Latitude decimal(10,8) ,
Longitude decimal(11,8),
City_Id int Default NULL References Cities(City_Id) ,
County_Id int Default NULL References Counties(County_Id))