FIT 100

Lab 15: Introduction to Microsoft Access and Relational Databases

Creating tables for data storage

Spring 2001

Reading to be done for Labs 15 and 16:

· Chapter 15 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 concepts of the relational database model. The three main tiers of the structure of a Relational Database Management System are:

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). One of the attributes (columns) 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.

Logic Services

The processes by which a user can view, use, add and change data in the Data Storage level. A Query is the vehicle for this process. Queries are also called views because they are a user’s “view” of the data in the database.

Presentation Services

The interfaces created to allow users of the Database system 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 is retrieved by a Query. 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.

Some Databases do not include a Presentation layer in the application. However, Microsoft Access is an environment that allows you to provide Data Storage, Logic Services and Presentation Services all from the same application.

In today’s lab you will receive an overview of all of these layers, but the main focus will be on Data Storage: creating tables holding different types of data that are related to each other in some manner.

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 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. I know this seems weird, since other applications allowed you to create a bunch of things and then save, but this is what Access requires.

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 either to C:/Documents or to your disk.

5. After you create the database, the next window you see will list the database objects available on the left and the methods with which you can create 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 valued stored.

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: Student and Advisor

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 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 an Entity Relationship diagram:

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

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 Advisor:

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.)

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

14. Save your table with the name Student.

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 “Snyder”. 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.

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 once you enter data in the datasheet view. It is automatically saved for you. IF you change the way the table “looks”, then you need to save it in order to preserve the look you have created.

20. 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 assign an Advisor ID that does not correspond to an actual advisor. You will learn how make Access enforce this rule in later labs.

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

22. 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, a new table 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)?

23. Show your table in design view to your TA. (See step 18 if you don’t remember how to get to design view)

24. FTP your Lab15.mdb to your Dante account if you don’t already have it on disk. You will use it again in Lab 16.