A database for maintaining information about cities in the United States has the following relation schema:...

Transcribed Image Text:

A database for maintaining information about cities in the United States has the following relation schema: Info(city name, state, governor, mayor) The city_name attribute is the name of a city, state is the abbreviation for state the city is in, governor is the name of the governor of the state the city is in, and mayor is the name of the mayor of the city. For example, the tuple ('New York', 'NY', 'Andrew Cuomo', 'Bill diBlasio') gives information about New York City. Two cities may have the same name (for example there are at least two cities named Portland in the US), but two cities in the same state cannot have the same name. Two states cannot have the same abbreviation. Each city has exactly one mayor and each state has exactly one governor. 1. State whether each of these is A. a superkey, B. a superkey that is also a candidate key, or C. not a superkey: • {city_name, state} {city_name, state, mayor} • {city_name} {state, governor} 2. If there are 500 cities from NY state in a relation of this schema, how many rows will need to be updated when NY gets a new governor? 3. We will say that a functional dependency a →ß is trivial if ß is a subset of a. Give an example of a trivial functional dependency in this schema. 4. Give an example of a non-trivial functional dependency in this schema for which the left-hand side is a superkey. A database for maintaining information about cities in the United States has the following relation schema: Info(city name, state, governor, mayor) The city_name attribute is the name of a city, state is the abbreviation for state the city is in, governor is the name of the governor of the state the city is in, and mayor is the name of the mayor of the city. For example, the tuple ('New York', 'NY', 'Andrew Cuomo', 'Bill diBlasio') gives information about New York City. Two cities may have the same name (for example there are at least two cities named Portland in the US), but two cities in the same state cannot have the same name. Two states cannot have the same abbreviation. Each city has exactly one mayor and each state has exactly one governor. 1. State whether each of these is A. a superkey, B. a superkey that is also a candidate key, or C. not a superkey: • {city_name, state} {city_name, state, mayor} • {city_name} {state, governor} 2. If there are 500 cities from NY state in a relation of this schema, how many rows will need to be updated when NY gets a new governor? 3. We will say that a functional dependency a →ß is trivial if ß is a subset of a. Give an example of a trivial functional dependency in this schema. 4. Give an example of a non-trivial functional dependency in this schema for which the left-hand side is a superkey.