Information Technology in a Global Society

Databases

What is a database?

A database is information stored on the computer and organized into lists. Each list we call a table of information.

Examples of lists include As a database designer you are setting up the organization of the lists (tables) in your database. For each table you have to define how you will structure each item in your list. An item in the list is called a record. So a table is a list that contains a bunch of rows or records.

Designing a Record

A table is defined by the records stored in the table. You need to specify how each record should look. For example, a list of students might contain the following information about each student: 
  Name
  ID Number
  address
  GPA
  phone
  guardian
The table of students contains many records that fit the above format, something like: 
  Viviani    23422334   30 E Elm        3.2    234-2342    Maria Quintana
  Jonathan   45645242   120 S Racine    2.8    563-2342    Bill Watson
  Yanqiu     46456234   3410 S Archer   4.0    567-3453    Mei Zhang

To completely design the record you really need to specify the type of each field in the record. The student record shown above actually has six fields that we might design as 
  studentName char(10)
  IDNumber    char(8)
  address     char(40)
  gpa         number(2,1)
  phone       char(8)
  guardian    char(30)
The numbers in parentheses after the word "char" indicate the length of the field. So for instance, the address field is defined to be a bunch of characters up to 40 characters long.

Try this in Access

Follow these steps to create your first Access database:
  1. Run Microsoft Access.
  2. Create a New--Blank Database (on the right)
  3. Save the database on your network drive.
  4. Double click on "Create table in Design View"
  5. Enter each field name as shown above for the student table.
  6. Select "Text" as the Data Type of each field. Text is just like the "char" type. Notice that the default field size is 50. Change the field size to the sizes shown above.
  7. When you get to Student ID RIGHT-CLICK on that row and select Primary Key. A little picture of a key should show up next to that row. A primary key is a field that is different in each record. It helps you to find a unique record in the table.
  8. When you get to GPA change the data type to Number. Look in the "General" tab and set the Decimal Places to 1, the scale to 1 and make sure to change the "Field Size" to Decimal! It should look like this
  9. When you are done close the window and save the table. Name the table Student.
  10. Now you should see the student table in the list. Double-click on the student table and start entering information. Notice how the database keeps you from entering data that should not be there. For instance, try to enter more than 8 characters in the IDNumber field.

Relationships between Tables

The real power of databases come from the ability to relate one table to another. For instance, let's say you want to store information about each grade that a student takes. You could create another table in your database called grade. In the grade table you could have all the grades ever given at the school. Create a table now called grade that looks like this: 
  studentID    Text  8
  course       Text  40
  year         Text  4
  grade        Text  1
BEFORE YOU SAVE YOUR TABLE make sure to set the primary key. In this table the primary key is actually (studentID, course, year) since all three fields are needed to find a particular grade. What you are saying is that if you know the studentID, the course and the year, then you know the grade the student got. Otherwise, you can not say for sure (since a student can take a course more than once). To set the primary key like that you need to highlight all three fields and then right-click. That is kind of hard to do so watch how I do it.

Relating One table to Another

Now that you have two tables, one called student and the other called grade, you can create a relationship between the two tables.

Remember, in the student table the primary key is IDNumber. In the grade table the primary key is (studentID, course, year). Notice that part of the primary key in the grade table is the student id. We are going to use that to relate one table to the other. This relationship is called a one-to-many since one student can have many grades.
Follow these steps in Access to create the relationship
  1. Click Relationships... in the Tools menu.
  2. Select each table (grade and student) and click Add so that both tables show up in your diagram.
  3. Now the tricky part. Clink on IDNumber in the student table and drag to StudentID in the grade table. Note that "drag" means to hold the mouse button down while moving the cursor.
  4. You will see this box
  5. Click on the check box for Enforce Referential Integrity.
  6. Then click Create.

Relationships Defined

Relationships defined in databases help to give the database its integrity and strength. Notice some terms from the exercise above that you should understand

Referential Integrity

When you checked that box you told the database to make sure that every grade relates bake to a real student. If you try to enter a studentID in the grade table that does not exist in the student table the database will not let you do that. This insures the integrity of the data. Otherwise you could end up with grades that don't relate back to any student.

Relationship Type

The Access database system automatically figured out that you were defining a One-to-Many relationship. That is because the primary key in the student table is only part of the primary key in the grade table. In that case one student in the student table can have many grades in the grade table.

This is the most common relationship, but you can also have a many-to-many and a one-to-one.

Foreign Key

The primary key defines how you identify a record in a table. If that primary key is part of a relationship it has to be found in the related table too. So in the related table it is called a foreign key. In the student table the primary key is called IDNumber. That same field is found in the grade table but there it is called studentID. Notice one important fact. In both tables the type and size of the field is the same, a Text size 8.

Open your student table again. You will see a feature of Access that shows the related grade records for each student.

Next Steps

In our next lecture we will discover how to create forms for people to use to enter data into our database.