Data is a collection of numbers, letters, or even symbols.

Once we understand what the data means, it becomes information.

A collection of related data stored in a specific format to simplify retrieval is called a database (db or DB).

Database Management Systems (DBMS) are programs that help in the storage and retrieval of information from databases. (MS Access and Oracle are examples.)

Data in a relational database is organized in rows (also called records) and columns (called fields).

A list of related data organized in fields and records is called a database Table.

Queries - question structures to sort, filter and select specific information.  A query is not the data that results but a set of instructions specifying how specific records or combinations of records should be extracted… it is the process of asking the database for specific information.  A query is a question about the data in the database.

Forms - structures for screen views of data.  A form is not data that results but a set of instructions specifying a screen view format of the data.  These forms are designed to simplify data display, inputting and editing.

Reports - structures for written/printed output of data.  A report is not the data that results but a set of instructions specifying the format of written output.

Program Modules - program code to perform specific actions

Relational Databases:

• All records in a table have the same fields
• Fields must state what type of data they contain
• Field types include Text, Number, Currency, Date as well as many others
• Fields can have additional properties that define their display format, rules for data validation, if data is required for that field, the size, a default value if any, and much more.

DBMS  verses Spreadsheet

Database systems:	Spreadsheets:
Store and retrieve data	Store and analyze data
Provide support for organization and easy selection/retrieval of data	Provide support for complex calculations
Can do simple calculations	Can do simple data organizing and selecting
Efficient data handling	Inefficient data handling
Multi-user	Single use
Handle lots of data well	Size limited by memory

Primary key

• A field, or combination of fields, which uniquely identifies a record in a database
• The primary key is indexed
• The primary key does not allow duplicates
• The primary key does not allow Null values

Foreign key

• One or more fields (columns) that refer to the primary key field in another table.
• A foreign key indicates how the tables are related.
• The field names on each table do not have to match as long as the information is the same. 
• The related fields must be the same data type (number, text etc)
• Fields having the same name does not necessarily mean they are related (foreign) keys

Writing queries involves:

1. Choosing the required tables: Where is your information coming from?
2. Choosing the output fields: What information do you want to list?
3. Choosing the criteria fields: What  selection criteria do you want, which records to “choose”?

QBE verses SQL

• The language called   SQL (Structured Query Language) has become the industry standard for querying relational databases.
• Access supports SQL, but has a user friendly interface to querying called QBE (Query By Example).

Relational operators which can be used in queries:   = , <= , >= , <> , < , > 

For example:

<= 10               returns records where field value is less than or equal to 10

< “G”               returns records where field text begins with letters A through F

> “Jones”         returns records where field text is after Jones (alphabetically)

Boolean Operators used in queries:   And, Or, Not.   For example:

“BE”  Or  “BA”  returns records where field value is either (any of) “BE” or “BA”

Not “BE”            returns records where field value is not equal to “BE”

>5 And <10        returns records where field value is greater than 5 and less than 10

Wild cards characters for use in queries:  Asterisk ( * ) and question mark ( ? )

An asterisk ( * ) replaces any number of characters.  For example:

Like “C*”           returns records where field value starts with “C” no matter its length

Like “*cookie*” returns records including the word cookie anywhere in the field value

A question mark ( ? ) replaces a single character.  For Example:

Like “B?”         returns records where field value has “B” as its first character and only one other character after the “B”

Null values:

• If you do not have data entered in a field, the field is said to contain a Null value. 
• Null means there is no value.
• Calculations which include a Null value will result in a Null value.