Introduction to
Microsoft's Access

by K. Yue

1. Resources

• MS Access is a relational database management system.
• MS Access is a mature product and there are plenty of tutorials, examples, and resources.
• It is based on the relational model.
• To start, it is necessary to become familiar with the following basic concepts:
  1. table
  2. row
  3. column
  4. column value
  5. data type
  6. data subtype
  7. primary key
  8. foreign key
  9. relationship
  10. query
  11. SQL
• As a GUI tool, there are also various important views.
  1. Datasheet: allows insert/delete/update of individual row.
  2. Query: design queries to answer questions using a Query by Example approach
  3. Design: define tables, columns, keys, constraints, etc.
• There are also numerous tools, e.g. relationship diagram.
• Our course only requires less than 1% of the features provided by MS Access.
• As a starter, make sure you know how to:
  1. create a table
  2. populate a table
  3. create a simple query
• Many features not covered in Access in this course, such as:
  1. create form
  2. create report

The Relational Model

The basic relational data model in layman terms:

1. A database is composed of a collection of tables (relations).
2. A table contains many rows (tuples) and columns (attributes)
3. Each row contains many column values.
4. Every row of a table has the same columns.
5. Values of the same column have the same data type.
6. A table has a primary key to uniquely identify a row.
7. Data from two tables R1 and R2 can be linked by a foreign key of R1, for example, a column A, which has a value in a column B, the primary key of the table R2 (known as the parent table).

Example:

Consider toyu_init.accdb:

The database toyu has six tables:

1. student(stuId, fname, lname, major, minor, credits, advisor)
2. department(deptCode, deptName, schoolCode, numFaculty)
3. faculty(facId, fname, lname, deptCode, rank)
4. course(courseId, rubric, number, name, credits)
5. class(classId, courseId, semester, year, facId, room)
6. enroll(stuId, classId, grade, n_alerts)

The student table contains 10 rows:

Student has seven columns:

Columns should be defined such as in a data dictionary. For example: for the table student:

Student: a row in the student table stores the information of a student.

1. stuId: a unique id for the student
2. fname: the first name of the student
3. lname: the last name of the student
4. major: the department code (deptCode) of the major of the student. It may not be declared.
5. minor: the department code (deptCode) of the minor of the student. It may not be declared.
6. credits: the number of completed credits of the student.
7. advisor: the faculty id (facId) of the faculty who serves as the advisor of the student. A student may have no faculty advisor.

The table faculty:

Classroom exercise:

Provide the data dictionary for the table faculty.

Keys

Consider the stuId 100000 in the student table:

(stuId: 100000, fname: 'Tony', lname: 'Hawk', major:'CSCI', minor: 'CINF', credits:40, advisor:1011)

The column 'advisor' is a foreign key in the table student. Its value, 1011, must appear in the column 'facId', which is the primary key of the table faculty (the parent table).

A relationship diagram in Access shows foreign key relationship between tables.

A primary key may be a composite key (containing more than one columns) and a table can have multiple foreign keys.

Classroom exercise:

List the primary keys and foreign keys of all tables.

Classroom Demonstration

(1) Insert/Update/Delete

(a) Using toyu.accdb, add a new student:

(stuId: 100100, fname: 'Stephanie', lname: 'Smith', major:'MATH', minor: 'CINF', credits:33, advisor:1012)

(b) Lillian Johnson now has no minor.

(c) The class with id 11004 is canceled and its information should be deleted.

(2) New table: Create a new table School with columns:

1. schoolCode: short Text, primary key
2. schoolName: short Text

Insert the following four rows into the newly created table.

(3) Foreign key:

Make schoolCode a foreign key of the table department (referring to schoolCode of the table School).