Database design in GIS pertains to defining relationships between data. It is arguably one of the most important concepts in GIS and essential in organizing large amounts of data. Various topics are addressed in database design and management including Entity-Relationship (ER) Diagrams, Unified Modeling Language Diagrams (UML Diagrams), Postgres Databases, etc.
Relational Database System Introduction
What is a primary key?
A primary key is the first field in each table of a database. This field auto-numbers by default so that every record in the table has its own unique number to ID it. Access uses the primary key to pull information together when running queries and/or reports.
What is a foreign key?
A foreign key is a field that is the primary field in its own table but that also shows up in another table. This may happen as certain fields are the primary key for their own tables but are considered foreign keys in others.
What is referential integrity?
Referential integrity tells Access and databases to check for certain things in reference to relationships when someone is working with your data records.
What is a database relationship?
A database relationship lists all of the tables that are selected to relate including the fields that were set up for each table. It is literally “relating” the data between these designated tables bringing together information when necessary. Access allows for several different types such as: one to one (1:1), one to many (1:M) and many to many (M:M). The relationship type that is most frequently shown is the one to many (1:M).
A primary key is the first field in each table of a database. This field auto-numbers by default so that every record in the table has its own unique number to ID it. Access uses the primary key to pull information together when running queries and/or reports.
What is a foreign key?
A foreign key is a field that is the primary field in its own table but that also shows up in another table. This may happen as certain fields are the primary key for their own tables but are considered foreign keys in others.
What is referential integrity?
Referential integrity tells Access and databases to check for certain things in reference to relationships when someone is working with your data records.
What is a database relationship?
A database relationship lists all of the tables that are selected to relate including the fields that were set up for each table. It is literally “relating” the data between these designated tables bringing together information when necessary. Access allows for several different types such as: one to one (1:1), one to many (1:M) and many to many (M:M). The relationship type that is most frequently shown is the one to many (1:M).
The 1:M relationship means that data for that field will show up a single time in one table but many times in the related table. As shown below, the primary key, “Employee Number” from Table 1 will be used once as it is specific to each individual employee; hence the identifiers listed in Table 1 include individual employee names, phone numbers, emails, etc. All of this data is specific to an employee/employee number. This is similar to the primary key “Skill Number” listed in Table 2. Each skill number applies to a particular skill once; hence skill name, description, etc. However, as seen in Table 3, each individual employee could have various skill sets exemplifying many skill numbers at various levels. Other employees could also exhibit the same skills/skill numbers. This displays a one to many (1:M/M:1), many to one relationship between the data tables.
Entity-Relationship (ER) Diagram
The ER Diagram below was created using DIA, it displays the relationships between the objects and data within the database. It shows all tables in the database and how the tables are related through primary and foreign keys.
DIA UML Diagram
The example below displays creating a UML diagram using DIA to appropriately display relationships and associations between data tables. DIA allows a user to edit new classes, table names and fields. It also displays and identifies primary (#) and foreign keys as well as types of relationships between data associations.
Data Definition Language (DDL)
From the UML Diagram, DDL SQL code was created and used to develop a schema for the database. This process created empty tables and relationships between the tables, noting primary keys and specific table and field names. The code can be found below.