Access and databases. Creating a Database.

A database is an organized collection of data for one or more purposes, usually in digital form.

A Database Management System (DBMS) is a program (set of programs) that stores and organizes data. One such organization method is relational where data is organized by means of related tables called relations and hence the name Relational Database Management System (RDBMS). RDBMS stores information in related relations (tables, subjects) which can be related using common fields.

The term relational database was originally defined by and is attributed to Edgar Codd at IBM Almaden Research Center in 1970. Relational database theory uses a set of mathematical terms, which are roughly equivalent to SQL database terminology. The table below summarizes some of the most important relational database terms and their SQL database equivalents.

Picture 1. Set of terms.

A relationship is an association that is established between columns (fields) in two tables.

Three types of relationships can be distinguished:

• One-to-one relationship: when one record in a table can have only one matching record the another table and vice versa. This type of relationship is used normally when we want to store information that only have some records in a table.

• One-to-many relationship: When a record in a table can have only one matching record in the other table, and a record from the second table can have many matching records in the first table.

• Many-to-many relationships: When a record in a table can have many matching records in a second table and when a record in the second table can have many matching records in the first table. A many-to-many relationship is only possible by defining an intermediate table between the two other tables.

Picture 2 Three types of relationships

MS Access is a Relational Database Management System (RDBMS). Like all database systems MS Access stores database information in a database file (MS Access uses .mdb extension), MS Access .mdb files store information about your database objects such as tables, queries, forms, macros, reports, access web pages, and modules. An RDBMS is used to store, retrieve and update data via facilities and mechanisms that are supported from within the database system, these mechanisms include tools to:

• view the data.

• update the data.

• facility to store data (Tables).

• present data (Forms, Reports, Access Web Pages).

• generate reports from the database dynamically (Wizards).

• Security utilities – only authorized users can access the database.

• retrieve subset of the data based on a defined selection criteria (Queries). MS Access uses Structured Query Language (see query section) to support query services.

• backing up and restoring data.

MS Access 2003 supports all these features and provide objects that are used to implement each tool. The relational database objects that are supported by MS Access 2003 are organized in a database file.

A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system (RDBMS).

A Microsoft Access database is made up of several components including:

Table 1. Microsoft Access components.

Tables are collection of records about a particular subject (i.e., Student). Each record in the table is uniquely identified by the primary key of the table. Primary Keys are fields in the table that uniquely identifies a row (record). Access supports the following data types:

Text	You may type in any alphabetical/numerical data that you desire - up to a maximum of 255 characters. As indicated, this is a text field, so you can't do mathematical calculations. Examples of Text data are: names, addresses, stock numbers, room numbers, etc.
Memo	This field is for lots of text. You can have up to 32,000 characters.
Number	This field is for numbers where you want to add, subtract, multiply, divide, average, and do numerical calculations.
Date/Time	Dates and Times. .
Currency	Dollars ($).
AutoNumber	This field is an "automatic" counter that assigns a number each time you put data into a new field.
Yes/No	This is a "True/False" or "Yes/No" type of field.
OLE Object	This means "Object Link Embedding" which indicates you can insert a graphic, picture, sound, etc.

Also table fields have some properties. Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.

Table 2 Field properties

Practice. Creating a database «Dean's office»