CS302, Section 1
Fall 2008, Due Friday at the start of class
The first assignment will familiarize you with using Access.
Part 1
The file you will need for the assignment can be found at:
DO NOT OPEN THE FILE FROM ITS CURRENT LOCATION. Save it to your computer instead.
Please note that file name at the end is STARS_XXX.accdb and this is case sensitive, if your browser says that no file can be found, check to see if you mistyped the address first.
1) Save the file to your P: drive or your USB drive if you have one.
2) Rename the database before opening it. Replace XXX with your initials. For example, if I were completing this assignment I’d name the database STARS_JSC.accdb.
3)Create the Instructor table. Click on the Create tab, then click on Table Design.
Instructor should have the following fields:
InstructorIDText(11), Primary Key
LastNameText(32)
OfficeText(12)
PhoneText(13)
DepartmentText(6)
Text(6) indicates that the data type of the field should be Text, and that the field size should be changed to 6.
Don’t forget to set InstructorID as primary key for the table. (Select that field, click on Primary Key button in Design view)
Save the design of the Instructor table. Click on Save, name the table Instructor.
4) Create the relationship between Instructor and Class, enforcing referential integrity.
Click on the Database Tools tab, then on the Relationships button.
Add the Instructor table by clicking on Show Table and selecting Instructor.
Drag from InstructorID in the Instructor table to InstructorID in the Class table.
When the dialog box opens, check Enforce referential integrity and cascade update options
Click on OK.
5) Add 3 students to the Student table. You should use made up names for the database. ABSOLUTELY DO NOT enter real student ID numbers. Make those up as well.
6) Open Quick Look from STARS in a browser window. Look up 3 classes you are taking this semester or have taken in the past.
Using this information, enter 3 real instructors and 3 real classes to the database. Make up the InstructorID for the instructors.
Assign the instructors to teach the classes.
7) Enroll students in classes in your database by adding their student IDs and the corresponding STAR numbers to the enrolled table. Enter enough enrollments to have 15 records in this table.
8)Create a form based on the Student table using the Form wizard.
Select Create->More Forms…->Form Wizard.
Choose the student table and then click on next.
Add all the fields to the right side and then click finish.
9)Create a report based on the Instructor table using the report wizard.
Select: Create->Report Wizard.
Add all the fields from the Instructor table and click on Next.
Group by Major1 and click on Finish.
10)Create a query by clicking on Create->Query Design
Add the Class table.
Double click on Department, Number, and Section fields.
For the criteria for the Department field type “CS”
Run your query.
Save it as Computer Science classes
11)Createa new query in design view. (Repeat the first step above)
Add the Instructor and Class tables.
Double click on Instructor LastName, Class Department, Number, and Section.
Run the query.
Save it as Classes taught.
Part 2
This section will have you create a new file and then perform similar actions as in Part 1 to reinforce using Access.
1) Create a new database.
Click on the Office button, then select new.
Click on Blank database.
Type in a new name for the database in the textbox shown; name it Library_XXX where XXX will be replaced by your initials.
Click on the browse button to change the location for the new file to either you P: drive or your USB drive where you save your file from Part 1.
Click on the Create button.
2) Create the Student table below:
StudentID, Text(11), Primary key
FirstName, Text(25)
LastName, Text(32)
Major1, Text(6)
Major2, Text(6)
Save the table as Student.
3)Create the Book table below:
CallNumber, Text(50), Primary Key
Title, Text(100)Title of the book
Author, Text(32) Just the last name of the Author
StudentID, Text(11)
Save the table as Book.
4) Create a relationship between Book and Student thorough StudentID. Enforce referential integrity and set the cascade update option
5) Copy records from your other database.
Open your STARS_XXX database.
Open the Student table.
Select all the records by clicking on the white triangle in the upper left hand corner of the table.
Click on the copy button in the ribbon.
Go back to your Library_XXX database and open the Student table.
Select the entire first (empty) record by clicking on the record selection button to the left of the first field.
Select Paste from the ribbon.
You may now close the STARS_XXX database again.
6) Using the form wizard, create a form for the book table. Save it as Book.
7) Enter 5 books into the book table using the form you just made. We’ll use the WIU library to get real data.
Open the WIU library website at
Click on the link that says “Find Books, Videos, More”
Search for books you might be interested in, that you’re had for class, or books you have read before
Select the books you find and enter the appropriate data in that table.
8) Modify the records above to have 3 of the books borrowed by students in our database.
9) Create a report showing all books currently borrowed from the library. Hint: You can still use the wizard here, just select fields from both Student and Book and Access will use the relationship to only show books borrowed. Save it as Books currently out.
10) Create a query showing the books not currently borrowed from the library. Show all of the fields in the table in the result. You’ll set the criteria for StudentID in the book table to Is NULL. Don’t include the Student table or this won’t work! Save the query as Available Books.
Part 3
Submit the files via email. Make sure you have attached the files before sending.
Email is inherently unreliable. You must CC yourself the files as well in your email to me (I want to see your email address in the CC field). This is a receipt of your submission. If you have no receipt, you have no proof that you sent it. Keep this copy until I have graded the files.
My email address is .