Relational Databases and Microsoft Access (McFadyen)

Relational Databases were introduced by E. F. Codd in 1969 ; Codd’s 1970 paper is considered one of the great papers in Computer Science.

We begin with a very small example: a database with one relation, the list of employees shown in Table . You should notice this looks just like a two dimensional table of rows and columns. The name of the table is Employees, each column of the table has its own title, and each row has the same structure. Each row has a value for employee number, first name, last name, and gender. As tables of data appear in so many places (newspaper articles, text books, web pages, etc.) it is very likely you have seen and used this representation for data previously.

MS Access is a relational database system for workstations that run the Microsoft Windows operating system. MS Access is typically used by individuals for data they use personally, but in some situations a single MS Access database may be used by a group of people or small department. MS Access databases are stored in a single file that has a file suffix of “.accdb” or “.mdb”. Databases created using MS Access 2007 and later have a file suffix “.accdb”, and databases created using MS Access 2003 or earlier have a file suffix “.mdb”. We will be using databases where the files have names ending in “.accdb”. You need to use MS Access 2007 or later to open these databases.

The typical MS Access database comprises several kinds of database objects such as indexes and tables. Each table represents a kind of entity (persons, places, things, events, etc.), or relationship between entities. For instance, if we are keeping track of departments and courses at our University then we should have two tables:

• Department: to keep information about departments
• Course: to keep information about courses.

For each department suppose we need to know things such as: department code, department name, location of the department (an office number), phone number for the department, and the name of the department’s chair. Suppose departments can be identified by their department code (e.g. ACS) and by their department name (e.g. Applied Computer Science); both of these fields are assigned by the University and each will be unique across departments. We will choose to use the department code as the primary key; that is, we choose to use department code as the primary identifier for departments.

Data Types

MS Access provides several data types – we will discuss Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, and Lookup Wizard.