CS 330
Homework 1
Fri September 5, 1997
Due: 9/10/97 by midnight.
There are two parts for the homework.
Part 1 is to do the Tutorial in Access.
Part 2 is to apply what you learned in the tutorial
and create a small
databases in Access.
Part 1 should be done before Tuesday's
class. The second part will be due Wednesday Sept 10, by
midnight. For part 2, you answer the questions by email
(sent to streinu@cs.smith.edu with subject line: 330 homework 1) and
hand in a diskette (in class, at 9:00 on Thursday) with the required database.
Note that the time stamp on the Access file should be earlier than
Wednesday midnight.
Part 1: The Access Tutorial
How
Choose start/programs/MS Office/MS Access. A prompt box appears, close
it. Go to help/MS Access help topics/visual introduction to MS Access.
This is the menu that appears:
- What is a database?
- Tables: What they are and how they work
- Customizing a table
- Working in table Datasheet view
- Queries: What they are and how they work
- Customizing a query
- Working in query Datasheet view
- Forms: What they are and how they work
- Customizing a form
- Working in Form view
- Reports: What they are and how they work
- Customizing a report
- Macros: What they are and how they work
- Modules: What they are and how they work
What to do
Go over all the modules, but if you do not have time, you can omit the
Macros for the time being. We might get back to them later. If you need more
details, use the Help menu to find the topic on which you want more
information.
I expect questions on Tuesday.
Part 2: A simple Database in Access.
Create a database in Access with four tables:
- Computers: with fields Computer_id, Model
- Software: with fields Software_id, Name
- Administrators: with fields Id, Name, Email
- Installed: with fields Transaction_id, Computer_id, Software_id,
Installer_id, Date
For the first id field in each table, choose Automatic (i.e., a number is
generated automatically when a new record is added to the table).
Question 1
Do you see a reason why we do not generate the numbers
automatically for the other id fields in the transactions table?
When you want to save the table definition, you will be asked if you want
to create a primary key for the table. Answer yes. Access will choose (I
think, correctly!) the field to be the primary key. I know I have not
defined yet what this is, but try it - and then you will understand in
class on Tuesday when I will define it.
Question 2
Can you guess what is the role of the primary key for each table?
Create four forms for entering data in each table.
Initialize the tables with a few entries in each table (4 computers, 4
software, 2 administrators and 4 transactions).
The ultimate goal is to create a report for listing all the transactions,
with full name (not just the id) given for each computer, software and
administrator involved in a transaction.
Question 3
You may not be able to create it directly from the tables, but
I want you to try and see what kinds of reports you can create, and where
is it that you run into trouble - and why?
From the help menu, choose the topic "creating relationships between
tables". Read it.
Question 4
Do you see a way these tables should be related? If so,
explain what they should be.
And, of course, use the instructions in Help to create those
relationships in Access.
Further, I want you to (at least try) to create a query which will
collect the data I need for the final report. Use the help to read about
how to create a query. Ask me in class on Tuesday, if you want more details.
If you went so far and did everything, and asked the right questions, you
did very well.
For extra-credit do create the query and the associated report
of all transactions listed with the names, not the id's, of the objects
involved (computers, software and administrators).
Last updated Sept 5, 1997
Ileana Streinu