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

License

Share This Book