GISP - Geographic Information Systems Professional Database Design and Management Questions and Answers

Question 1

A GIS analyst receives a single table of property data containing parcel ID, owner name, owner address, zoning code, and zoning description. They observe that for every parcel owned by the same person, the owner's name and address are repeated. Furthermore, the zoning description is repeated for every parcel with the same zoning code. This design violates which principle of database normalization?

Accepted Answer

Third Normal Form (3NF), because it contains transitive dependencies.

Answer

Third Normal Form (3NF) is violated. A table is in 3NF if it is in 2NF and has no transitive dependencies. In this scenario, 'Zoning Description' is functionally dependent on 'Zoning Code', which is a non-key attribute. This transitive dependency (Primary Key -> Non-key Attribute -> Another Non-key Attribute) leads to data redundancy and update anomalies. To achieve 3NF, the zoning information should be moved to a separate table.

Question 2

A GIS administrator needs to enforce a rule for a 'Water_Pipes' feature class that restricts the 'Material' attribute to a specific list of values: 'Ductile Iron', 'PVC', or 'Cast Iron'. This rule must apply to all new features and ensure data consistency across the geodatabase. Which geodatabase component is the most appropriate and efficient tool for this task?

Accepted Answer

A coded value domain applied to the 'Material' field.

Answer

A coded value domain is specifically designed to enforce data integrity by providing a predefined list of acceptable values for an attribute field. While subtypes can categorize features and have their own default values or domains, creating a subtype for each material is inefficient for simply constraining a single field's value. A relationship class links separate tables/feature classes, and a topology rule governs spatial relationships, neither of which is the primary tool for constraining attribute values.

Question 3

A multi-user enterprise geodatabase is used to manage a city's utility network. To accommodate long-running projects and daily maintenance tasks, multiple editors must be able to work on the data simultaneously without locking features or directly editing the production database. Edits need to be isolated until they can be reviewed and merged. Which database functionality is designed to support this workflow?

Accepted Answer

Versioning, which allows multiple, concurrent states of the database.

Answer

Versioning is the core functionality in enterprise geodatabases that allows multiple users to edit data simultaneously in their own isolated, logical 'version' of the database. This facilitates long transactions, as edits do not affect the production version until they are explicitly reconciled (checked for conflicts) and posted (merged). Archiving tracks history, replication is for distributed databases, and indexing is for performance.

Question 4

Which of the following SQL queries would correctly select all cities from a 'cities' table that have a population greater than 1,000,000 and return only the 'city_name' and 'state_name' columns?

Accepted Answer

SELECT city_name, state_name FROM cities WHERE population > 1000000;

Answer

The correct SQL syntax involves three main clauses: `SELECT` specifies the columns to be returned (`city_name, state_name`), `FROM` specifies the table (`cities`), and `WHERE` filters the rows based on a condition (`population > 1000000`). The other options use incorrect syntax or clauses (`SELECT *` returns all columns, `HAVING` is used with aggregate functions, and `LIST` is not a standard SQL command).

Question 5

In a relational database design, two tables, `Parcels` and `Owners`, need to be linked. The `Owners` table has a primary key `OwnerID`. To correctly establish a one-to-many relationship where one owner can have many parcels, what must exist in the `Parcels` table?

Accepted Answer

A foreign key field that references the `OwnerID` in the `Owners` table.

Answer

A foreign key is a column (or a set of columns) in a table whose values correspond to the values of the primary key of another table. To implement a one-to-many relationship, the table on the 'many' side (`Parcels`) must contain a foreign key that references the primary key of the table on the 'one' side (`Owners`).

GISP - Geographic Information Systems Professional Practice Test

GISP - Geographic Information Systems Professional Practice Test

GISP - Geographic Information Systems Professional Database Design and Management Questions and Answers