Lab
Activity 12: Introduction to Relational
Databases
through Microsoft Access
Creating
tables for data storage
.pdf version of this document
Reading
to be done for this lab:
·
Chapter 20 of Fluency
with Information Technology:
Foundations and Fundamentals of Information Technology
Create the Database Structure:
Identify the Important Entities and
Relationships:
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:
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).
One of the attributes (columns) of the table is almost always a number
data type that is used as a key. A key
is used to uniquely identify the data in a particular row. Key values must be
unique and not null. A primary key is
just the attribute(s) that have been designated as the main key for the table.
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, view and manipulate the data. Queries are also called views because they are a user’s “view” of the data in the database. A query that selects certain data from one or more tables and returns results is also called a virtual table.
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. In Access, the language used in the code is VBA, Visual Basic for Applications. Your interaction with logic services for this project will be minimal. You will use simple queries 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 the next Project. Forms are a way of entering data into and viewing data from one or more tables.
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. Open Microsoft Access
Access 2000 -------------- Access XP
You are creating your first database today in order to hold/track 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 in 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:
Advises Student
(1 to Many)
Advisor
2.
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
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 indicate a
String value)
Fname Text
Department Text
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.
File>Save
3.
Repeat steps 2A-2D, but this time create the Student table
with the following attributes and data types:
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 AdvisorID
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.)
4. 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.
5. Go to the Tables Window and open tblAdvisor.
6. 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.
Notice that a number appears once you put your cursor in the Lname column and start typing “Dickey”. 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 attribute does not exist that is known to be unique and not null.
7.
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:
What is the view of the table? It is the view you first saw when creating
the attributes of the entity.
8.
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.
In order to enforce referential integrity among tables, and to be able to create forms based on information from more than one table (as you will for Project 3) 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.
9. After you have created the structures for your tables, go to the Relationships window by right-clicking on the database window:
You will then see a Show Table window:
Use the drag and drop
method to show where the tables are associated
When you have created all the proper
relationships, your window should look like this:
The infinity symbol
indicates the “many” side of the relationship
10. 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.
11. 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)?
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.
12. Currently your database consists of two tables. The Student table includes in it textual 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?
13.
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.
14.
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.
Refer to step 2 to create this table if needed.
15.
Save the table as tblMajor and close it
16.
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.
17. Open the tblStudent table in Design view. Do this by clicking once on the Student table object and then click the Design icon above,
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.
B. When
asked if you want to “permanently Delete the selected fields….”, click
Yes.
18.
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.
19. There are now 3 table structures created to hold instances of the entities Student, Advisor and Major.
20.
Close the database completely and SSH your .mdb file to your
Dante account. You will use it again in
the next lab.
NOTE: If your database is open
when you upload, you may end up loading an empty file. (Depends on the version of Access and the
OS)