Database Concepts

June 18, 2010


Install the MySQL query browser and connect to the DLS2009a database on matilda. 

Browsing around in this database will give you a feel for how databases work.


Some concepts that  you need to know about:



Database Engine

A network server technology for storing and retrieving data.  Most of the technologies in use are called “relational”, referring to the data organization into data-typed tables


Examples:  MySQL, Oracle, SQLServer


A typically authenticated communication channel between a user and a database server.  Users have usernames and passwords, and also sometimes hostnames.  Connection information includes:


          Server Name

          User Name

          Database Name




A named, typed, and ordered set of data, such as all of the ra coordinates belonging to objects in a survey.  Types include:


          CHAR[n],VARCHAR[n] and BLOB

Table or Relation

A named set of columns, usually grouped together (clustered) on disk


A collection of tables, functions, and other objects.  You can “USE” a database or explicitly name objects, such as DLS2009a.PhotoObj


A statement which allows a user to create, update, and fetch data from a table or collection of tables


A sort of one or more of the columns from a table, saved on disk.  Indexes are very important for understanding why some queries are fast and some are not.


The rights you have as a user to see data, run programs, and create objects on the database.  These permissions may be very fine-grained, such as the right to update a particular column of a particular table

Functions and Procedures

Programs which may either perform functions, return values, or both.  The may be used inside queries or called by themselves.