Lab 15: Introduction to
Relational Databases
through Microsoft Access
Creating
tables for data storage
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:
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. You will use simple queries to gather
data from tables. 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. 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 3.
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.
A. Open Microsoft Access
Programs>Microsoft Access
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 defining/creating a database
structure and that space will be the only way to access the tables and
queries created in it.
B. Select the radio button that says “Blank Access database” and click OK
C. When the window called “File New Database” shows up, name the database as YourName.mdb and save it to the Desktop or to your disk.
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. 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. |
|
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
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
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.)
You now have 2 tables holding data related to 2 different entities. One is the Student entity. The other is the Advisor entity.
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
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. In order to ensure data integrity, allow Access to enforce the relationships as you create queries and forms.
· Right click while on the database window OR ·
Go to Tools> · Find the icon on the Tool Bar |
The Show Table window box
should come up: |
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
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. 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?
Normalization
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.
To use the data in tblMajor how will we associate it with the tblStudent? 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? ·
Are
there additional themes represented in the Advisor table that should be
removed? (Not required, just identify them)
Domain Integrity: There is a rule in database
design that any attributes that are part of a relationship between 2 tables
must have the same data type. This
is called “maintaining domain integrity”.
By creating a new table for Majors
with a primary key that is a number data type, we can no longer use the
attribute Major because it is a text data type. The data type of any attribute
holding foreign key information from the Major table will have to
match. Since you already have a
couple rows of data in that column, it will be easier to delete the
attribute and then create another field called MajorID,
this time with a number data type.
F.
When asked if you want to “permanently
Delete the selected fields….”, click Yes.
This attribute will hold the key
numbers that corresponds with particular instances of the Major
entity. It is placed in the Student
table as a way to indicate the major that the student has declared. (The
attribute AdvisorID is used in the same manner.)