Introduction to
Microsoft's Access

by K. Yue

1. Introduction to MS Access

• MS Access is a relational database management system.
• MS Access is a very 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 (or relation)
  2. row
  3. column (or field, attribute)
  4. column value
  5. data type
  6. data subtype
  7. primary key
  8. foreign key
  9. relationship (between relation/table)
  10. relationship diagram (not to be confused with the entity relationship, or ER, diagram).
  11. query
  12. SQL
• As a GUI tool, there are various important views. MS Access includes the followings:
  1. Datasheet: allows insert/delete/update of individual rows.
  2. Query: design queries to answer questions using a Query by Example approach (Based on Domain Relational Calculus)
  3. Design: define tables, columns, keys, constraints, etc.
• There are also numerous database tools, e.g. relationship diagram.
• Our course requires a small percentage 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 forms
  2. create reports
  3. Programming in MS Access
• In order to use MS Access, you may:
  • Use a computer in the department labs or UHCL labs with MS Access installed.
  • Gain MS Access license and install it in your computer. (However, Office 365 via UHCL does not include MS Access)
  • Use UHCL virtual lab, Apporto: https://www.uhcl.edu/computing/labs/virtual-lab.

2. Access Architecture

The general database architecture uses a client-server architecture:

1. A DB server, S, listens to a port.
2. A DB client, C, connects to the DB server and set up a section.
3. C sends a SQL command to S.
4. S executes the SQL command and send the result back to C.

MS Access contains both the DB client and DB server:

1. MS Access Users use the Graphical User Interface (GUI) to develop queries.
2. SQL commands are generated from the GUI queries.
3. SQL commands are executed by the MS Access DB engine.
4. Results are displayed in Access GUI.

3. Brief Introduction to 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, records) and columns (attributes, fields)
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 mean of a foreign key of R1 and a primary key of R2 (known as the parent table).

Example:

Consider toyu.accdb:

The database toyu has eight tables:

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

The student table contains 10 rows at the moment:

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 (primary key).
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. Major may not be declared, and thus a null value is acceptable.
5. minor: the department code (deptCode) of the minor of the student. Minor may not be declared, and thus a null value is acceptable.
6. ach: the number of accumulated credit hours, including transferred credits.
7. advisor: the faculty id (facId) of the faculty who serves as the advisor of the student. A student may have no faculty advisor.

There are three foreign keys in the student table:

1. major references department(deptCode)
2. minor references department(deptCode)
3. advisor references faculty(facId)

The table faculty:

Classroom exercise:

Provide the data dictionary for the table faculty.

4. 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 relationships between tables.

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

Classroom Demonstration or Practice Exercise

(1) List the primary keys and foreign keys of all tables.

(2) 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.

(3) New table: Create a new table Semester with the following columns:

1. SemesterId: auto number
2. Semester: 30 char string
3. Description: 255 char string

Insert the following four rows into the newly created table.

(4) Foreign key:

In the class table, change the data type of "semester" to number. Rename the field to "SemesterId". Replace the field values of "Fall" and "Spring" to 1 and 2 respectively. Create a foreign key for the class table: SemesterId references Semester(SemesterId).

We will demonstrate how to construct queries to satisfy data problems in the class.