Introduction To (Relational) Data Bases

A table is a matrix of rows and columns. Each column has a name and each row has a number. Rows correspond to objects or cases and we call them RECORDS, while columns correspond to types of information about the case and we call them FIELDS.

Suppose I have information on three students:

Alex is 21 years old, female, 68" tall, does not drive, and has a cat. She is taking SOC128 with Professor Ryan.
Bella is 19, female, 64" tall, drives a Honda, hasca dog. She is taking SOC128 with Professor Ryan.
Chris is 24, female, 69" tall, drives a VW, has a dog. She is taking SOC116 with Professor Ryan.
Dar is 18, female, 63" tall, drives a Toyota, has no pet. She is taking SOC055 with Professor Hunter.

CaseID Name Age Sex Height Car Pet Course Professor
1 Alex 21 female 68 does not drive cat SOC128 Professor Ryan
2 Bella 19 female 64 Honda dog SOC128 Professor Ryan
3 Chris 24 female 69 VW dog SOC116 Professor Ryan
4 Dar 18 female 63 Toyota no pet SOC055 Professor Hunter

Fields always have a specified DATA TYPE. We'll learn more about the options later — for now, just text or number:

CaseID number
Name text
Age Number
Sex text
Height number
Car text
Pet text
Course text
Professor text

By convention, every table must have a field (or set of fields) that uniquely identifies each record. This field is called the KEY. Here it would be CaseID.

Notice that the table above has in it repeated information. If a new instructor were to arrive for SOC128, we would have to go in and edit a large number of records. In addition, we are using up valuable storage space writing "Professor Ryan" over and over again.

STUDENT TABLE

CaseID Name Age Sex Height Car Pet CourseID
1 Alex 21 female 68 does not drive cat 1
2 Bella 19 female 64 Honda dog 1
3 Chris 24 female 69 VW dog 2
4 Dar 18 female 63 Toyota no pet 3

COURSE TABLE

CourseID DeptCourse Instructor
1 SOC128 Professor Ryan
2 SOC116 Professor Ryan
3 SOC055 Professor Hunter

Joining Tables

The basic operation

JOIN STUDENT_TABLE COURSE TABLE on CourseID

CaseID Name Age Sex Height Car Pet CourseID CourseID Course Professor
1 Alex 21 female 68 does not drive cat 1 1 SOC128 Professor Ryan
2 Bella 19 female 64 Honda dog 1 1 SOC128 Professor Ryan
3 Chris 24 female 69 VW dog 2 2 SOC116 Professor Ryan
4 Dar 18 female 63 Toyota no pet 3 3 SOC055 Professor Hunter

But I can go farther.

Student Table

CaseID Name Age Sex Height Car Pet CourseID
1 Alex 21 female 68 does not drive cat 1
2 Bella 19 female 64 Honda dog 1
3 Chris 24 female 69 VW dog 2
4 Dar 18 female 63 Toyota no pet 3

COURSE TABLE

CourseID DeptCourse InstructorID
1 SOC128 1
2 SOC116 1
3 SOC055 1

INSTRUCTOR TABLE

InstructorID InstructorName
1 Professor Ryan
2 Professor Hunter

But I can go farther.

Student Table

CaseID Name Age Sex Height CarID PetID CourseID
1 Alex 21 female 68 0 1 1
2 Bella 19 female 64 1 2 1
3 Chris 24 female 69 2 2 2
4 Dar 18 female 63 3 3 3

COURSE TABLE

CourseID DeptCourse InstructorID
1 SOC128 1
2 SOC116 1
3 SOC055 1

INSTRUCTOR TABLE

InstructorID InstructorName
1 Professor Ryan
2 Professor Hunter

Car Table

CarID Car
1 does not drive
2 Honda
3 VW
4 Toyota

Pet Table

PetID Pet
1 cat
2 dog
3 no pet

Many to One and One to Many

Example: Mining Pits and Metals Samples.

Geographic Tables and Non-Geographic Tables

A feature class is basically a table of (barenaked) geographic objects (points, polylines, polygons). Start with a table of points.

POINTS TABLE

FeatureID FeatureType Long Lat
0001 Point -123.435837 48.09383
0002 Point -123.435847 48.09363
0003 Point -123.435857 48.09363
0004 Point -123.435857 48.09343
0005 Point -123.435857 48.09323

LINES TABLE

FeatureID FeatureType FromPT ToPT
0001 Line 0001 0002
0002 Line 0002 0003

Joining Data to Geography

What is an index and why is it useful?

Query as stand-alone object

What is a relate as opposed to a join