FIT 100

Lab 15:  Introduction to Relational Databases
through Microsoft Access

Creating tables for data storage

Autumn 2001

Reading to be done before beginning this lab:

·         Chapter 16 of Fluency with Information Technology:  Foundations and Fundamentals of Information Technology

 

Introduction:

The last part of the FIT 100 Course is a study of databases, and in particular, relational databases.  We will use Microsoft Access as a way to demonstrate concretely the various layers of complete database architectures.  Relational Database Management Systems usually consist of Data Services:  Data storage, the storage engine and the query processor.  Many RDBM systems do not include a Logic or Presentation layer in the application.  However, Microsoft Access is an application environment that allows you to provide Data Storage, Logic Services and Presentation Services all from the same application:

 

          Data Services

The main unit of data services (data storage) is a Table.  Data storage allows for persistent data (the data stays even when the power goes off).  A table is a collection of data about a specific topic. 

 

Tables organize that data in columns (called attributes) and rows (called records or tuples).  One of the attributes of the table is almost always a number data type that is used as a primary key.  A primary key is used to uniquely identify the data in a particular row. Primary key values must be unique and not null.  Primary keys are also indexed by database to reference specific rows of information.

 

Data Services also includes the storage engine and the query engine which deal with the saving, accessing and manipulation of the data.  A Query is used to access and manipulate the data. Queries are also called views because they are a user’s “view” of the data in the database.

 

Logic Services

This area deals with the processes that control the more complex logic determining a user’s manipulation of data in the Data Services level. Logic is usually implemented in the code module of an application.  Your interaction with logic services will be minimal.  Instead, you will use simple queries and the query processor to gather data from tables.

 

Presentation Services

The interfaces created to allow users of the Database application the ability to enter data and to view output of data.  A Form is the typical interface used for input and output of data that goes through logic services and talks to the Query Engine.  There are other interfaces in Access such as Reports and Data Access Pages, but you will only be dealing with Forms for the first part of Project 4.

 

 

In today’s lab the main focus will be on Data Storage:  creating tables to hold data instances (rows of information) of the entity represented by the table.

 

Objectives:

  1. To connect the abstract concepts of Entities, Attributes and Tuples in the Relational Database Model with the corresponding objects in Access that are concrete representations of those concepts.
  2. To become familiar with MS Access, one of many different Relational Database Systems that can create complete database applications.
  3. To create tables that are associated with each other based on a specific type of attribute, a key attribute.

 

TO DO: 

1.      Open Microsoft Access

Programs>Microsoft Access

 

2.      The first thing you have to do is save the database.  This may seem weird, since other applications allow you to add text or create objects and then save, but this is what Access requires.  You are saving a database space (or shell) and that space will be the only way to access the tables and queries created in it.

 

3.      Select the radio button that says “Blank Access database” and click OK


 

4.      When the window called “File New Database” shows up, name the database Lab15.mdb and save it to the Desktop or to your disk.

 

5.      After you create the database, the next window lists the database objects available on the left and the methods available for creating them on the right.

         

6.      Creating a table in Design view will let you build the table from scratch, adding attributes (the names of the columns) and deciding what data types will be allowed for stored values.

 

The Student/Advisor Database

You will create your first database today in order to hold information about two different entities:  the Student entity and the Advisor entity.  In layman’s terms - you’re going to create two tables:  tblStudent and tblAdvisor 

 

The Student table will hold data about, what else?  Students!  This table (Entity) should contain attributes for Student ID, Last Name, First Name, Major, year in school and the student’s advisor.

 

The Advisor table will hold data about the Advisor.  This table should contain attributes for Advisor ID, Last Name, First Name and Department.

 

These tables are associated with each other is the following manner: 

 

A student, when they first enter school, is assigned an advisor to help them plan out the courses they will take each quarter.  That student may change advisors- for example, if they change majors- but they will only have one advisor at a time during their college career. 

 

An advisor, on the other hand, may have many students that they advise over the duration of a quarter or a year. 

 

We indicate the association between the Advisor and the Student in the following manner:

An Advisor may have many students that they advise, but a student will have one and only one advisor at any given time. 

 

The formal way to say this when talking about the relational database model is:

          There is a one-to-many relationship between the Advisor table

and the Student table.

A one-to-many relationship is the most common type of relationship used in the Relational Database Model. In a one-to-many relationship, like the one between Advisor and Student, a record in the Advisor table can be associated with many matching records in the Student table, but a record in the Student table has only one matching record in the Advisor table. 

Here is how you can indicate this relationship in graphical terms:

Student
 

Advisor
 
                             (1 to Many)

 

7.      Double Click on “Create table in Design view” to start building your Advisor table.  The screen that comes up will look like this:

Place notes to yourself about what each attribute contains in the Description column.  These notes are only available in this screen.

 

Indicate the data type allowed for the value of an attribute in the Data Type column
 

Place the names of the table attributes in the Field Name column
 

 

8.      The Advisor table should have the following attributes:

 

Field Name                Data Type

AdvisorID                AutoNumber   (use AutoNumber so that Access

will produce a unique number to be

used as a Primary Key)

          LName                         Text             (Text is one data type that Access uses to

          Fname                    Text                       indicate a String value)

          Department             Text            

 

 

9.      The last thing to do after adding the attributes and data types (and maybe some notes to yourself), is to indicate the attribute that will be used to hold the unique key value in this table: AdvisorID

Highlight the AdvisorID row in table design view by clicking the area just to the left of the AdvisorID.  Then click on the key icon on the tool bar.

 

    

 

Once you do that, a small key icon should appear just to the left of AdvisorID.

10.  When you have finished establishing the Primary Key, save your table with the name tblAdvisor:

File>Save

 

11.  Close the table.

12.  Repeat step 7, but this time create the Student table with the following attributes and data types:

Field Name                Data Type

StudentID               AutoNumber   (use AutoNumber so that Access

will produce a unique number to be

used as a Primary Key)

          LName                         Text

          Fname                    Text

          Major                      Text

          Year                       Number

AdvisorID                Number         (This is the column where you will

indicate the Advisor ID number that

matches the student’s assigned advisor name. 

It is known as the foreign key when used here

because it is a primary key sitting in a table not

defined as its own.)

13.  Repeat step, but make StudentID the primary key for this table.

 

14.  Save your table with the name tblStudent.

 

15.  Can you explain to the person sitting next to you how these two Entities (tables) are associated with (related to) each other?

 

You now have 2 tables holding data related to 2 different entities.  One is the Student entity.  The other is the Advisor entity.

 

16.  Double click on the Advisor table in the Tables window.

 

 

17.  Notice that the table opens up in Datasheet View.  This view allows us to see all instances of the advisor entity that have been entered so far.

 

Right now, the datasheet view is empty.  Add the following 2 instances, or rows, so that there is some data in the table:

 

 

Notice that a number appears once you put your cursor in the Lname column and start typing “Levy”. This is the primary key for each row and it is generated by Access so that every row is unique and at no time will a null value be allowed in the AdvisorID column.  Autonumber is not always required when establishing a primary key in Access.  It is simply a convenient way to assign one if an existing number attribute does not exist that is known to be unique and not null.

 

18.  After you add the data, switch to the design view of the table by clicking the design icon in the upper left corner of the database screen:

 

19.  What is the view of the table?  It is the view you first saw when creating the attributes of the entity. 

Switch back to Datasheet View by clicking the appropriate icon now in the left corner of the database screen:

 

 

Close the table.  You do not have to save tables when you enter data in the datasheet view.  It is automatically saved for you.  IF you change the way the table “looks” (the display/format), then you need to save it in order to preserve the look you have created. 

 

Establishing Relationships

 

In order to enforce referential integrity among tables, and to be able to create linked forms (as you will for Project 4, part 2) you must establish relationships between your tables.  They might be one-to-one or one-to-many or many-to-many.  In order to ensure data integrity, allow Access to enforce the relationships as you create queries and forms.

 

20.   After you have created the structures for all of your tables, go to the Relationships window by right-clicking on the Lab 15 database window:

 

 

You will then see a Show Table window:

 

 

Add each table to the Relationship form by selecting it and clicking Add.  Do not add each table more than one time.  IF the Show Table window does not appear when you open the Relationship form, right click to find it.


Close the Show Table window.

 

Use the drag and drop method to show where the tables are associated
 
    

Drag the keys that correspond to each other from one table to the next.  Once you have placed them, an Edit Relationships window comes up.  Check the Enforce Referential Integrity window and Create the relationship:

 

    

 

When you have created all the proper relationships, your window should look like this:

This symbol indicates the “many” side of the relationship
 

 

Save and close the Relationships Window. 

 

21.  Open up the Student table.  Add 4 records of student information for yourself and 3 friends.  Assign them each an advisor and place the ID number that corresponds with the advisor in the correct column:

 

 

In order to maintain data integrity, do not try to assign an Advisor ID that does not correspond to an actual advisor.  Because you have established relationships between these two tables that enforce referential data integrity, you will not be allowed to do so.

 

22.  Repeat steps 18 and 19 for the Student table.

 

23.  You now have two entities in your database.  They are populated with instances (rows of data).  Take a look at each of your tables.  What attribute in each of our tables could have been separated out, new tables created, and a key number put in its place to reduce data redundancy (the number of times we had to type in the same information)?

 

Normalization

 

Normalization is the process of efficiently organizing data in a database.  Two main goals of the normalization process are to eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).  This process has many levels involved in it.  Let’s look at one of the most basic: revising a table structure when it has more than one entity described in it.

 

Let’s start to normalize one of the two tables you created today.  This process is usually done before creating table structures, but we are going through it on a database that has already been created so that you get an idea of how it is done. 

 

24.  Currently the Lab15 database consists of two tables.  The Student table includes in it semantic information about another potential entity.

 

This means there is the possibility that values in some of the columns may

be filled with redundant information.  Can you identify the column that

holds semantic (textual, meaningful) information about a separate “thing”

in the student table? 

 

If you are thinking of the Major field, you are correct!

 

25.  Add a table called Major to hold the list of majors that a student can declare. 

For our database, a student will only be allowed to declare one major.

This will maintain a one – to - many relationship between the Student and Major entities.

 

26.  Create a table in design view.  This table will be called tblMajor.   The table should have the following attributes: MajorID as the Primary Key and MajorName as a text attribute.

 

 

27.  Set the primary key for the Major table and save the table as tblMajor.

28.  Close the table.

29.  Use the data in this table and associate it with the Student table.  To do this requires removing one attribute and adding another attribute to the Student table.  What attribute will we add to the Student table?  What will we remove?  Could we also normalize the Instructor table and pull out the Department information into another, separate table?

30.  Open the tblStudent table in Design view.  Do this by clicking once on the Student table object and then click the Design icon above,

 



31.  We will no longer use the attribute Major as a text data type.  Since you already have a couple rows of data in that column, it will be easier to delete the attribute at this point and then create another new field called MajorID, this time with a number data type.

Select the field Major, right click the mouse. From the menu that pops up, select Delete Rows,

 

When asked if you want to “permanently Delete the selected fields….”, click Yes.

32.  Now the old attribute Major and all the corresponding data in your table have been removed.  Insert a new row right above the field Year.

Select the Year row, right click and select Insert Rows



A new row will be inserted between FName and Year.

33.  Add a new field (attribute) to the table Student where you inserted the row. 

 

34.  Save and close the table.

35.  You now have the table structures created to hold instances of the entities Student, Advisor and Major. 

Before you begin making forms to view and alter the data in these tables, add the Major Table to your relationship window. 

Associate the Major table with the Student table based on the MajorID.  Create the relationship and enforce referential integrity.

 

36.  Show your relationship window and your Student table in design view to your Instructor.

 

37.  FTP your Lab15.mdb to your Dante account.  You will use it again in the next lab.  The lab for the Monday/Tuesday after Thanksgiving will be posted early so you can work on it and your Project at the same time.