Introduction to Relational Databases
through Microsoft Access
Creating
tables for data storage
1. Create
the Database Structure
2. Identify
the Important Entities and Relationships
6. Breaking
One Table Into Two
Reading
to be done for this lab:
·
Chapter 13, pp.231-top
242 of Fluency with Information Technology:
Foundations and Fundamentals of Information Technology
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 also include a mechanism to create user interfaces and to program those interfaces to work with the data. These additional layers are known as Logic and Presentation services. 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 “Blank Access database” option 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.
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 in each cell. |
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 “tbl” at the front is a naming convention so that you know you
are dealing with a table and not, as we will do later, dealing with queries
or forms The
Student table will hold data about, what else? Students!
This table (Entity) should contain attributes for Student ID,
Last Name, First Name, Major, 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
(1 to Many)
The Student/Advisor Database
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 you should 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 data about a single entity 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
should be removed from the Student table? ·
What attribute will
we put in its place? ·
Are there
additional themes represented in the Advisor table that should be removed?
(You are not required to remove them, 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 in tblStudent as the foreign key field 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.
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.)