Chapter 2: Relational Database Management Systems
2.2. Relational Databases
Introduction:
In our experience teaching Introduction to GIS courses, it has become clear to us that many or most students in programs outside of Computer Science have had little exposure to Databases and, in particular, the idea of Relational Databases or Database Management Systems. In these assigned readings, we are trying to give you that exposure. Moreover, as you will see, in many GIS situations the GIS data are stored in relational database systems.
For example, in the proprietary world of ArcGIS, the ‘Geodatabase’ is a method for storing geospatial data in a database management system or DBMS. The desktop Geodatabase uses the relational database package Microsoft Access to store its data. In ‘enterprise,’ larger scale GIS situations where multiple users are using the GIS at the same time, other DBMS packages are used to store the Geodatabase, such as IBM DB2, Microsoft SQL Server, Oracle or PostgreSQL (the database system you will be using in this course).
For this pre-class assignment, we are asking you to do some readings on Databases, Database Management Systems, and the idea of Relational Databases.
The readings we are assigning are Open Educational Resources – that is, Open Access and available freely (as in cost) over the Web. You can read the individual chapters online, or can download the entire book as a pdf and read it on an e-reader.
Assignment:
Read Chapters 1-4, 7-8, 15-16 (approximately 50 pages).
The entire book (pdf) is available at:
Database Design - 2nd Edition by Adrienne Watt and Nelson Eng. 2012. Open Educational Resource book available at: http://opentextbc.ca/dbdesign01/open/download?filename=Database-Design-2nd-Edition-1420760605&type=pdf
Individual chapters (web-page versions) are available here:
Chapter 1: Before the Advent of Database Systems (pp 1-5) http://opentextbc.ca/dbdesign01/chapter/chapter-1-before-the-advent-of-database-systems/ Chapter 2: Fundamental Concepts (pp 6-8) (http://opentextbc.ca/dbdesign01/chapter/chapter-2-fundamental-concepts/
Chapter 3: Characteristics and Benefits of a Database (pp 9-12) - http://opentextbc.ca/dbdesign01/chapter/chapter-3-characteristics-and-benefits-of-a-database/
Chapter 4: Types of Database Models (pp 13-15) - http://opentextbc.ca/dbdesign01/chapter/chapter-4-types-of-database-models/
Chapter 7: The Relational Data Model (pp 24-29) – http://opentextbc.ca/dbdesign01/chapter/chapter-7-the-relational-data-model/
Chapter 8: The Entity Relationship Data Model (pp 29-44) – http://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/
Chapter 15: SQL Structured Query Language (pp 83-93)– http://opentextbc.ca/dbdesign01/chapter/sql-structured-query-language/
Chapter 16: SQL Data Manipulation Language (pp 93-112) - http://opentextbc.ca/dbdesign01/chapter/chapter-sql-dml/
NOTE: The below are the key terms WE (Charlie, Andy, Jon, Walt) think are most important for you to understand for the work we are doing in the Web-GIS class. These are useful to understand for the quiz on these readings.
Chapter 2 – key terms
database: a shared collection of related data used to support the activities of a particular organization
database management system (DBMS): a collection of programs that enables users to create and maintain databases and control all access to them
table: a combination of fields
Chapter 3: Key terms
concurrency control strategies: features of a database that allow several users access to the same data item at the same time
data type: determines the sort of data permitted in a field, for example numbers only
metadata: defines and describes the data and relationships between tables in the database
view: a subset of the database
Chapter 4: Key terms
relation: another term for table
relational model: represents data as relations or tables
Chapter 7: Key Terms
Attribute: principle storage unit in a database
column: see attribute
field: see attribute
record: contains fields that are related
relation: the technical term for table or file
row: see tuple
structured query language (SQL): the standard database access language table: see relation
tuple: a technical term for row or record
Chapter 8: Key Terms
candidate key or primary key: a simple or composite key that is unique (no two rows in a table may have the same value) and minimal (every column is necessary)
composite key: composed of two or more attributes, but it must be minimal
dependent entities: these entities depend on other tables for their meaning
derived attributes: attributes that contain values calculated from other attributes
entity: a thing or object in the real world with an independent existence that can be differentiated from other objects; tables are entities in a relational database
entity relationship (ER) data model: also called an ER schema, are represented by ER diagrams. These are well suited to data modeling for use with databases.
entity relationship schema: see entity relationship data model
foreign key (FK): an attribute in a table that references the primary key in another table OR it can be null
key (sometimes called a primary key): an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set
null: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank
relationships: the associations or interactions between entities; used to connect related information between tables
Chapter 15: Key Terms
Structured Query Language (SQL): a database language designed for managing data held in a relational database management system
Chapter 16: Key Terms
DELETE statement: removes rows from a record set
JOIN: connects two tables on a column with the same data type
INSERT statement: adds rows to a table
SELECT statement: used to query data in the database
UPDATE statement: changes data in existing rows either by adding new data or modifying existing data
wildcard: allows the user to match fields that contain certain letters.