Management of Information Systems and Technology
MGT 186
Relational Database System using MS Access
This exercise is for you to practice using MS Access in the lab, and to provide you
with help as to how to work with access. In many regards, it is similar to your for-
grade project. The goal withthis project is to learn how to create and use an MS
Access database, not to rush through the exercise.
PART I THE FILE STRUCTURES
Our database shall be to keep track of salespersons in a company. The
SALESPERSON table
shall be made up of the following fields:
There should be a table of customers who are Customers of the salespersons, the
CUSTOMER
table. It should be made up of the following fields:
PART II STEP BY STEP, CREATING THE SALESPERSON TABLE
1. First open up MS Access. Click in the Create a New Database boxs use a Blank
Database. Then click OK.
2. Youll be asked to save the new database. Down below, click in the File name
box, and call this Employee.mdb. Remember this database will have two tables.
Click Create.
3. The blank database will be presented. The Tables tab will already be selected
for you. Note that you cant select anything but New on the right. Click on it.
4. A new dialog box is opened. Click on Design View, and then OK. You will do this
for both tables.
5. Now youll start setting up your table. In the Field Name column, give the field its
identifying name. You may use the suggested field names above. Then use the Tab
key to tab over to the Data Type. When you do, a drop-down arrow will be
displayed, as well as the default of
Text.
6. Once you do this, the General tab under Field Properties below will be
selected. It is here that you give the field its properties, such as field width, the >
sign in Format for the Office and State fields, which makes them uppercase, and you
create the Validation Rule and
Validation Text, as described in the previous class session.
7. Go ahead and complete all the fields. When youre done with each table, before
you leave
Design View, you will need to set up the Primary Key before you go to Datasheet
View. (Actually, you need to do this before formatting the input mask for the SSN as
a Social
Security Number)
_ to do this, place your cursor anywhere on the row containing the field to be
designated as the Primary Key.
_ now click on the little key icon on the standard tool bar. The Primary Key will
be assigned.
8. Now click on the Datasheet view icon. Youll be prompted to save the table. Call it
Salesperson and click OK. After the save is complete, the Datasheet view will be
displayed.
9. Now enter in all of your data for the Salesperson Table. Enter about 6 records.
PART III CREATE THE CUSTOMER TABLE
1. Now that you have created the Salesperson Table, and entered the data, youre
ready to create the Customer Table.
2. Following Steps 1 through 9 in PART II, create the Customer Table and enter the
data, using the same steps for corresponding fields.
3. MAKE SURE THAT THE CUSTOMER RECORDS CONTAIN SALESPERSON ID#s
WHICH EXIST IN THE SALESPERSON TABLE.
4. Enter about 5 records.
PART IV CREATE THE RELATIONSHIP
1. Exit your table after youve entered all the data.
2. Click on the Tools drop-down menu from the menu bar. Click on the Relationships
command.
3. The Show Table dialog box is displayed:
Click on each table and then click on Add
Click on the common field in the Salesperson table, and drag it to the same
common field in the Customer table.
When you do, the Relationships dialog box will be displayed. Confirm the
fields displayed and their tables. Then click in the Enforce Referential Integrity
check box.
Click on the Create button. The relationship has been created.
4. Close out of Relationships. If asked to save it, click "Yes.".
PART V CREATE THE REPORT USING THE REPORT WIZARD
1. Click the Report tab. Click New.
2. Click the Report Wizard. Then click on the drop-down arrow to choose the table to
report from. Choose the Salesperson table.
3. Click on the fields you wish to report, and then the right arrow, to place the field
in the
Selected Fields box. To place ALL the fields in the box, click the double-arrow.
4. After youve done this, if you want to remove a field, click on the field in the
Selected Fields box and then the left-pointing single or (to remove all) double-
arrow buttons.
5. To rearrange the fields, you may have to remove them from the right, and replace
them from the left in different sequences.
Follow each step in the wizard process to complete your report. At the end of the
process, youll be prompted to give the report a name. Do that, and click Finish.
The report will be previewed for you. After previewing it, you may exit the report. If
you were to print it, you would click on the Print icon.
PART VI - CREATING QUERIES (simple)
1. Click the Queries tab. Click New.
2. Click on Design View and click "OK"
3. From the "Show Table" dialog box, click on the Salesperson Table and click "Add".
The table will be added to the view table area above the grid in your query design
view. Now click on "Close".
4. In the Query grid, first column, click on the drop-down arrow on the Field row.
Choose the
Salesperson ID field. Now do the same for each column's field, selecting an
Salesperson
Table field to display. Don't select all the fields - the idea here is to select only the
fields you want.
5. When you've selected your fields, save the query by clicking on the diskette icon
and giving your query the name Salesperson Query. Now run the query by clicking
on the red exclamation point icon in the toolbar at the top of the screen. Your data
will be displayed.
6. Once you've done that, click on the Design View icon in the top left of the toolbar.
Experiment by changing your query, and running it each time, in the following ways:
- Click on the Sort row in a column other than the Salesperson ID, and
select Ascending.
Run the query and view the results. Now change back to Design View and
select
Descending for the same field and run it. View the results. Try this with
several fields.
- Click on the Show button (in Design View) for a field (should be
checked - so "uncheck" it) and run the query. That field should not be
present. Check the field back again.
- If you haven't chosen the State field to be in your query, click on the
next empty field's drop-down arrow and choose it. Now you're going to
select, or filter out, records. Click in the Criteria row for the State field
and key in =GA. (obviously, you will have to have some employees in
GA, and you should have some in other states too) Tab out of the field.
Now run the query.
- Try the same selection with Sex being M and then F. You should have
some records of each sex so that this filter works, displaying only the
male records (and then, the female records)
- You can do the same thing with the Company Car Yes / No field, using it
to filter only those Salespersons who drive a company car.
Dont forget to Turn in your lab activity to our Google Classroom to avoid late
submission.
Activity No. 2
Overview
This another lab activity will introduce you to database concepts using Microsoft Access.
Run MS Access
Under Start -- Programs, choose MS Access.
Create a UNIVERSITY Database
Choose "Create a new database using Blank Access database" - click OK
Save it on the desktop with the name "UNIVERSITY.mdb"
Create DEPARTMENT table
Double-click on "Create table in Design view"
Fill in the following fields and types to create the table:
FIELD NAME DATA TYPE
DEPT ID TEXT
DEPT NAME TEXT
BUILDING TEXT
PHONE TEXT
Choose the DEPT ID as primary key
o right click on the row and choose "Primary key"
Save the table (click on the save icon)
name it "DEPARTMENT"
Enter data into DEPARTMENT table
From the Window menu, choose "University:Database" to view choices
Double-click on "DEPARTMENT"
Fill in the following data, using tab to move from one cell to the next
DEPT ID DEPT NAME BUILDING PHONE
CSC COMPUTER SCIENCE TYLER 874-2701
MTH MATH TYLER 874-2809
PHY PHYSICS PASTORE 874-2232
ENG ENGLISH INDEPENDENCE 874-3202
BIO BIOLOGY BIOLOGICAL SCIENCE 874-4330
Save the table by clicking on the save icon
Create Faculty table
Go back to University Database window
Choose "Create table in Design view"
Create table with the following fields
FIELD NAME DATA TYPE
FAC ID TEXT (PRIMARY KEY)
LAST NAME TEXT
FIRST NAME TEXT
DEPT TEXT
BDATE DATE
RANK TEXT
HIRE DATE DATE
Save the table as "FACULTY"
Create Faculty-Department relationship
On the Tools menu, choose Relationships...
Highlight dept and click "Add"
Highlight faculty and click "Add"
Icons for each table appear on the screen
Click and drag from "DEPT ID" in dept table to "DEPT" in faculty table.
In the dialog box, click on Create
Save the relationship by clicking on the save icon
Add data to faculty table
Insert the following data into the faculty table -- notice that each faculty entry has a dept
that corresponds to an existing dept entry
FAC ID LAST NAME FIRST NAME DEPT BDATE RANK HIRE DATE
00303 SMITH JOE CSC 09/23/1965 ASST 01/23/1998
00322 JONES ALICE MTH 03/11/1972 ASST 12/23/2001
00223 FRIDAY JENNIFER ENG 12/12/1970 ASSOC 08/02/1994
00332 LANE LANCE CSC 06/02/1959 FULL 01/01/1995
00433 JUNIPER LUCY MTH 03/21/1965 ASSOC 03/03/1998
Ask some questions (queries)
On the University:Database window, click on the Queries option (left side column)
Double-click on "Create query in Design view"
1. Show all departments in descending order by name
click on the first Field: box and choose dept.name
click on the first Sort: box and choose Ascending
click on the second Field: box and choose dept id
On the Query menu, choose Run
2. Show the names and ranks of all faculty members in the CSC department
Create a query in Design view as above
Add both FACULTY and DEPT to the query by double-clicking on the
names
Icons for both tables will appear in the top part of the query
In the first three Field: boxes, choose FACULTY.LAST NAME,
FACULTY.FIRST NAME, and FACULTY.RANK. Be sure to check the
"Show" box to indicate that you want these fields to show in the result.
In the fourth Field: box, choose DEPT.DEPT ID. In the Criteria: box, type
= "CSC". This will only choose those rows where the DEPT ID = "CSC".
Note that the two tables are automatically linked by DEPT ID because of
the relationship that was set up above. Double-click on the link between
the tables to see the properties of the Join. You will see that the default is
to "Only include rows where the joined fields from both tables are equal".
This means that the query will only show results where the DEPT ID is the
same in both tables.
Use the steps above to create a new table for students
Here is the structure of the student table
FIELD NAME DATA TYPE
LAST NAME TEXT
FIRST NAME TEXT
STUD ID TEXT
MAJOR TEXT
BIRTH DATE DATE
GRAD DATE DATE
ADVISOR TEXT
Be sure to create a primary key
Create a relationship from the student's department (MAJOR) to the department table
Create a relationship from the student's advisor (FAC ID) to the faculty table
Insert data representing at least 10 students
Create the following queries:
List all students, sorted by graduation date.
List all students advised by Dr. Smith.
For every student in the CSC major, list his/her advisor.
Challenge Problem:
Add another table representing classes. The fields should include at least the class
code, the name of the class, the number of students taking the class, the semester the
class is offered, the department offering the class and the faculty teaching the class.
Link the classes to department (providing the class) and faculty (teaching the class).
Fill in interesting data (at least 10 classes).
Create the following queries:
1. List the names of all classes offered by the Computer Science department.
2. List the faculty members teaching classes with more than 50 students.
3. List the class with the most students.
4. List the departments teaching classes in Fall 2005.
Dont forget to Turn in your lab activity to our Google Classroom to avoid late
submission.
