2. CREATING TABLES
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. We show Department with some sample data:
| deptName | deptLocn | deptPhone | chairName | |
| ENGL | English | 3D05 | 786-9999 | April Jones |
| MATH | Mathematics | 2R33 | 786-0033 | Peter Smith |
| ACS | Applied Computer Science | 3D07 | 786-0300 | Simon Lee |
| PHIL | Philosophy | 3C11 | 786-3322 | Judy Chan |
| BIOL | Biology | 288 | 786-9843 | James Dunn |
Figure 2.1: Department table
Suppose the Course table keeps track of courses offered by the University and includes the fields: course number, title, short description and credit hours. At the University, what is a course number? The ways of identifying courses varies from one institution to another, but a common way is to give the department code followed by the course number, such as “ENGL-2221”; “ENGL-2221” comprises two parts: a department code and a course number (the dash is just there for formatting purposes). We will use this convention and so we must include department code as a field in the Course table, and the combination of department code and course number serve as a unique identifier (i.e., together they form a composite primary key). We show this structure with sample data:
| deptCode | courseNo | title | description | creditHours |
| ACS | 1453 | Introduction to Computers | This course will introduce students to the basic concepts of computers: types of computers, hardware, software, and types of application systems. | 3 |
| ACS | 1803 | Introduction to Information Systems | This course examines applications of information technology to businesses and other organizations. | 3 |
| ENGL | 2221 | The Age of Chaucer | This course examines a selection of medieval poetry and drama with emphasis upon Chaucer’s Canterbury Tales. | 6 |
| PHIL | 2219 | Philosophy of Art | Through reading key theorists in the history of esthetics, this course examines some of the fundamental problems in the philosophy of art, including those of the definition and purpose of art, the nature of beauty, the sources of genius and originality, the problem of forgery, and the possible connection between art and the moral good. | 3 |
| BIOL | 4451 | Forest Ecosystems Field Course | This is an intensive three-week field course designed to give students a comprehensive overview of forest ecology field skills. | 2 |
| BIOL | 4931 | Immunology | Immunology is the study of the defence system which the body has evolved to protect itself from external threats such as viruses and internal threats such as tumour cells. | 3 |
Figure 2.2: Course table
