CS 105 Lab 5 - Access Part2

 

Summary:

In this lab you will maintain and analyze the database that you used for your previous lab. This will include adding records, updating existing records, adding forms, and generating additional reports. You will also use MS word to discuss and summarize the data and answer questions that you are given.

If you do this lab the hard way, (without any of the shortcuts we have worked on in recent days in class) I expect it to take you several hours. If you make use of the short cuts that you have been given, you will finish it much more quickly.

 

Tasks:

Maintaining the database:

Do the first two steps in order to avoid referential integrety errors. The others may be done in any order you find effective.

The file \\webhost\jsantore\cs105\More-Students.xls contains many new students. Add this data to the students table. (Note that these new students don't have a county field. Thats ok, the county field is not manditory in the student table.)

The file \\webhost\jsantore\cs105\New-Registrations.xls contains several dozen new registrations for these students. Add this data to your database.

Create a form for the Registrations table to allow the clerks in the registrars office to enter new registrations without having to look at the rest of the database. This form should have the StudentID and CoursId fields visible, but not the registration ID since that is not something that they could edit anyway.

The administration wants to keep the students year code in the student table. Possble codes are FRESH, SOPH, JUNIOR, SENIOR. Add a column for this in the student table between the student's Last name and address. Make sure that only the above code can be entered. Make the default code FRESH, but give the existing students the following codes: All of the students should get the code SOPH, except:

Johan Straus: SENIOR
Janet Style SENIOR
Blair Keen FRESH
Timothy Brookes JUNIOR
Than Vyun JUNIOR

Data Analysis:

Your supervisor, an administrator at the college, has asked you to work up an analysis of the data to answer some questions asked by the Deans of the college. You are asked to write up a quick summary report in Microsoft word that answers the questions in brief, and then gives the name of the query or report in the database to use in case the supervisor needs to see more information. You need to answer the following questions:

  1. Which courses are least popular (which courses have the lowest enrollment) if there is more than one course with the same low enrollment, list them all.
  2. Which courses are the most popular, list at least the top 3, including any ties. (feel free to cut and paste these name if you like.)
  3. Which courses make the college the most money this semester. Show at least the top 3 money makers and any ties.
  4. According to the post-september 11 international student rules, the college must keep a list of all foriegn exchange students. Please make a report including all such students, ordered by last name, and mention it in the summary document.
  5. Since the registrar's office is busy, you have been asked to help generate some of the class lists. Please generate the class lists for the following courses (using the actual course names, so that the Deans can read the report:

If your first name begins with:

When you have finished your analysis, send the word document to your instructor by email, and use the blackboard digital dropbox to submit your access to the instructor.

 

The lab is due Wednesday Dec 15th. However, as always, you are welcome to turn it in early if you like.