CS302, Section 1
Fall 2008
Assignment 5
Part I: Update the data in your STARS2_XXX database
Deleting the existing records
We will be deleting the existing classes, students, instructors, and enrollments from the database. We do want to keep the table structure so that we can import the new data.
The following deletions must be made in the order listed. Do not delete the tables; delete the records in these tables.
Delete the records from your enrollment table (see figures below)
First select all the records by clicking on the small triangle in the upper left hand corner of the table. You should see all the records selected as below:
Now click on the delete button. Access will ask you to confirm the deletion. Click on the Yes button. Note that you cannot undo this action. Always use caution when deleting records.
Your records are now deleted. You should see the following:
Why do we have to delete these records first? Because of Referential Integrity
If we try to delete Student first, we’ll have records in this table that refer to StudentIDs that don’t exist.
Here’s what you’ll see if you try to delete the tables out of order:
Repeat the above steps for the following tables in the order given:
Delete the records from your Student table
Delete the records from your Class table
Delete the records from your Instructor table
Copying existing records
Now we’re going to copy records from a new database into your tables. (We’re doing this so that everyone has the same data in their tables.)
Save the following database to disk, do not open it from the class website.
Save the STARS_DATA.accdb database to your desktop. (Click on the STARS DATA database link)
Open the copy on your desktop AND open the STARS2_XXX.accdb that you have been saving from previous assignments.
We also have to copy the data in a particular order due to referential integrity. This is the reverse order from above.
Open the Instructor table in STARS_DATA.accdb and again select all the records by clicking on the small triangle. You should see an image like below (81 records):
Click on the copy button or use CTRL-C to copy.
Open the Instructor table in STARS2_XXX. Click on the small star on the very left column of the table.
This selects the entire records area. If you try to paste the records here without selecting this, you will be attempting to paste all the records into one field. That simply won’t work. You should see the following:
Paste the records. Click on the paste icon or use CTRL-V. You’ll be asked for confirmation as in the dialog box below. Answer yes.
Repeat the steps above to copy records for the tables listed below in the order given.
Copy records from the Class table
Copy records from the Student table
Copy records from the enrollment table
Attempting to copy out of order will give you an error dialog box similar to the following:
Open the Instructor table and look at the data entered.
Note that your classmates have entered the data in different ways. Some capitalized the names of faculty members, so did not. Some entered full phone numbers, while others only entered the last few digits.
This illustrates how different people entering data can lead to inconsistent formats. We can avoid this somewhat by designing forms that limit the ways data can be entered or that explain clearly to the person entering data what is desired. We’ll probably never get perfect data entry though, so data sets must often be “cleaned” so that they have a uniform appearance.
We’ll look at performing some of this automatically in later exercises.
We have also picked up some other, more bothersome errors in the database. For example, there are 18 duplicated instructors in the database. Fixing this problem is involved because we can’t just delete the duplicates. We also need to move their classes to correct ID numbers. We’ll temporarily leave this problem alone and fix it later in the class when we have better tools to deal with it.
Note: if you find offensive names in the student list, please point this out. I caught one or two that might have been objectionable. If you see more, I’d prefer to remove them from the database.
Look at the existing forms and queries
If you open current enrollments, you’ll see we have quite a few more students enrolled in our classes than with our initial database.
If you open the View Current Classes form, you see that estimated tuition now has much larger numbers. Overall this extra data will help us make sure our database is working well.
Part II: Create the following queries in QBE
All of the following queries should be created in Design view, which is also called query by example, or QBE. The query wizard can’t do these.
1)Select all the fields about the students who are LEJA majors. Save the query as LEJA majors.
2)Select the IDs, first names, and last names of the students who are CS majors. Save the query as CS majors.
3)Select all fields about the students who have no major assigned. Hint: you will have to specify the criteria as Null to find a blank field. Save the query as No major assigned. Change Albert’s major to ECON and then rerun the query.
4)Select all fields about the students who have are in the following majors: ENG, ANTH, HIST,ANTH. You must use the In() operator. (See p. 235 of the text) Save the query as CAS majors.
5)Using the LIKE operator, return all fields for students whose name begins with a C. (See p.235 of the text and notes from last Friday.) Save the query as C Students.
6)Using the LIKE operator, return all fields for students whose name ends with “son”. Save the query as Ends with son
7)Using the LIKE operator, return all fields for students whose name contains a “b”. Save the query as Contains B
8)Select all the fields for classes that have not been assigned to be taught by an Instructor yet. (This is similar to 3 above.) Save the query as Unassigned Classes
9)Select the Department, Number, and Section for all CS400 level classes. Save the query as CS400 level classes.
10)Select the Department, Number, and Section for all Econ 200 level classes and below. Save the query as ECON Freshman and Sophomore classes.
Again save your file. We’ll be doing one more assignment associated with this file on Friday then we’ll submit it. Make sure you’re caught up.