# 2.1: Creating Tables

$$\newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}}$$

$$\newcommand{\id}{\mathrm{id}}$$ $$\newcommand{\Span}{\mathrm{span}}$$

( \newcommand{\kernel}{\mathrm{null}\,}\) $$\newcommand{\range}{\mathrm{range}\,}$$

$$\newcommand{\RealPart}{\mathrm{Re}}$$ $$\newcommand{\ImaginaryPart}{\mathrm{Im}}$$

$$\newcommand{\Argument}{\mathrm{Arg}}$$ $$\newcommand{\norm}[1]{\| #1 \|}$$

$$\newcommand{\inner}[2]{\langle #1, #2 \rangle}$$

$$\newcommand{\Span}{\mathrm{span}}$$

$$\newcommand{\id}{\mathrm{id}}$$

$$\newcommand{\Span}{\mathrm{span}}$$

$$\newcommand{\kernel}{\mathrm{null}\,}$$

$$\newcommand{\range}{\mathrm{range}\,}$$

$$\newcommand{\RealPart}{\mathrm{Re}}$$

$$\newcommand{\ImaginaryPart}{\mathrm{Im}}$$

$$\newcommand{\Argument}{\mathrm{Arg}}$$

$$\newcommand{\norm}[1]{\| #1 \|}$$

$$\newcommand{\inner}[2]{\langle #1, #2 \rangle}$$

$$\newcommand{\Span}{\mathrm{span}}$$ $$\newcommand{\AA}{\unicode[.8,0]{x212B}}$$

$$\newcommand{\vectorA}[1]{\vec{#1}} % arrow$$

$$\newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow$$

$$\newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vectorC}[1]{\textbf{#1}}$$

$$\newcommand{\vectorD}[1]{\overrightarrow{#1}}$$

$$\newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}}$$

$$\newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}}$$

$$\newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} }$$

$$\newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}}$$

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:

Table $$\PageIndex{1}$$: Department table.

Department

deptCode

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 2L88 786-9843 James Dunn

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 comprise the primary key). We show this structure with sample data:

Table $$\PageIndex{2}$$: Course table.

Course

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