Week 5 - Databases Flashcards
What is a database
-structured collection of non-redundant data which can be shared by different application systems.
models in order of increasing abstraction
- Reality
- Conceptual model
- Logical Model
- Physical Model
what is a DBMS
software package that allows for creation, storage, maintenance, manipulation and retrieval of large datasets
-in tabular form
Goespatial database management system
- step up from DBMS additionally containing specific geographic information about each data point (line, polygon) such as identity, location, shape and orientation.
- powerful tool to visualise and answer spatially explicit questions.
Types of DBMS
- File management system (flat database)
- Hierarchical Database system
- Relational DBMS
Flat file Database
-spredsheet
Hierarchical Database
- organisational chart in hierarchies
- easy to understand and conceptualise
- efficient and fast access to data via key attributes
- Not ideal for large data sets as it is difficult to modify.
Relational database
- collection of tables that are connected by unique identifiers.
- each column represents a particular attribute (e.g area, soil type) FIELDS
- rows–> records
- e.g subject info and student info tables both have school column
Primary key
attributes of a table (column) used to uniquely identify each of the rows in a table.
- can’t be an attribute that produces duplicate values in the rows.
Foreign key
primary key that corresponds to an identical attribute in a secondary table.
-means of interconnecting data stored in different tables.
SQL
natural query language
Relational DBMS criteria
- flexible in design and queries
- simple and intuitive
- minimize duplication
- uses SQL
a query selects rows in a table if ..
a condition is true
Boolean operators
combine conditions with logical expressions: AND, OR, NOT. Conjunctions to combine or exclude keywords in a search.
-given results are true or false
features in a Geodatabase are stored in which format?
relational database
what does a geodatabase contain?
- Table (rows all contain the same fields)
- Feature class (tables with shape fields (point, line polygon)
- raster dataset
- USI for each feature
Joins
linking separate tables in relational DBMS
- Joins: appends fields from one table to another based on a common attribute. Non-spatial data tables
- Spatial join: combines feature information based on spatial location.
Joins vs. relates
- joins for suitable for 1-1 or many-1 relationships
- joins present data from two two tables in readily available single output table.
- relates are suitable for all relationships (1-many, many-many)
- relate produced very large table.
2 spatial query methods
- Coordinate: distance from a feature
2. topologyical relation (contained in, overlap, intersect)
Join e.g
PARCEL table: multiple Parcel attributes (LotPlan ID, Code, Adress)
combined with
PARCEL OWNER table outlining parcel owner (1 attribute).
Many-1
Centralised database
-physically confined to one location under the control of a single computer
Distributed databse system
several systems at local sites connected by communication lines
-multiple databses
Stage of increasing abstraction (which database model)
- Proceeds conceptual model to a logical database model
