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

Connection

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

          Password

 

Column

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

          FLOAT,DOUBLE,BOOLEAN,INT, BIGINT

          CHAR[n],VARCHAR[n] and BLOB

Table or Relation

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

Database

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

Query

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

Index

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.

Permissions

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.