Database Design and Management Flashcards

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?

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

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.

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?

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

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.

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?

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

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.

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?

Answer: SELECT city_name, state_name FROM cities WHERE population > 1000000;

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).

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?

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

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`).

Which statement best describes a fundamental difference between the georelational data model (e.g., shapefile) and an object-based data model (e.g., geodatabase feature class)?

Answer: The object-based model stores spatial and attribute data as an integrated unit, allowing for advanced behaviors like validation rules and relationships.

The object-based model, used by geodatabases, treats a feature as an object that encapsulates both its geometry (spatial data) and its attributes within a single system. This integrated structure allows the object to have behaviors, such as subtypes, domains, and relationship rules. In contrast, the georelational model stores geometry and attributes in separate but linked files (e.g., .shp and .dbf) and does not natively support these advanced behaviors.