A report

Case: Ashland-Baldwin College (ABC)

Analyzing Course Registration

Objectives

In this case you will learn how to:

· Create an Access database

· Create a table

· Design a table

· Import data from Excel

· Specify the primary key

· Create a relationship between tables

· Create a report

· Calculate fields

· Use aggregate functions

· Use query summary features

Ashland-Baldwin College (ABC) is a small liberal arts college in rural Michigan. ABC prides itself on its small class sizes and the one-on-one attention that students receive from the faculty. They call this the “ABC Experience” and argue that no larger institution can match it. While ABC may provide students with individualized attention in the classroom, it still needs to improve other aspects of the student experience. For example, students have long complained that registering for classes at ABC is a serious problem.

When registering for classes, students often find that the courses they need are not offered or, if they are offered, the sections fill up quickly with extra students being placed on wait lists. The problem has become something of a joke on campus but ABC’s President, Dr. William Grawn, is not laughing. Dr. Grawn has publicly stated that the registration issue is his highest priority. To prove the point, Dr. Grawn personally approved the hire of a new Registrar for the College.

Pam Smith is ABC’s first new Registrar in nearly 20 years. The previous Registrar retired after Dr. Grawn made it clear that changes needed to be made. Upon assuming her new role, Pam’s first act was to thoroughly review the existing registration process. She was shocked to find that much of the process was actually done by hand. Past efforts to automate the process had always been resisted by the previous Registrar who frequently claimed “machines can’t provide the ABC Experience”. Even when ABC purchased its current registration system, over a decade ago, little actually changed. While the system allows ABC to do computer-based registration, it is too simple to provide anything beyond basic “data dump” reporting. The system has no real data analysis functionality which makes it difficult for the Registrar to plan properly. Without demand data, the Registrar doesn’t know what courses to offer or when to offer them. The lack of data also causes the Registrar to assign sections to rooms that are often too big or too small. This then leads to some sections having lots of empty seats (a waste of resources) while other sections are overflowing and have to use wait lists (unhappy students).

Based on the limitations of the existing registration system, Pam has decided to replace it with a new database. Pam’s hope is that this new database will make registration data more accessible and easier to analyze. This should, in turn, improve the registration experience for students while helping ABC better utilize it classroom and lab resources.

Pam asked ABC’s Information Systems department to design the new database. After much discussion, it was determined that the database should contain four tables: Course, Section, Room, and Registration. The Course table will store data about the courses in the ABC catalog. This data will include the course designator, title, description, and credits. The Section table will store data about specific course offerings. This will include data such as the course ID, the day/time of the section, and the section’s assigned room. The Room table will store data about all the classrooms and labs on campus. Specific data will include the room’s building and number, as well as, the room’s seating capacity. Finally, the Registration table will store data about the sections that students have registered for.

The design for the new database is modeled in the Entity Relation Diagram (ERD) (see Figure 1). The ERD indicates:

· A course can have many sections, but each section is for a single course.

· A section is assigned to a single room, but a room can be used for many sections.

· A section can have many student registrations, but each registration is for a single section.

Figure 1: Entity-Relation Diagram (ERD)

Based on the ERD, the Information Systems department designed the tables to contain specific data. The description of each table’s data is detailed in Figures 2-4. It is not necessary to type in the “Field Description” entry. That is for informational purposes only.

FIELD NAME	FIELD DESCRIPTION	DATA TYPE	FIELD SIZE
CourseID	Identification number uniquely designating each course	Number	Integer
Designator	Course’s designator	Short Text	8
CourseTitle	Course’s title	Short Text	40
Description	Course’s description	Short Text	100
Credits	Course’s number of credits	Number	Integer
Department	Department that teaches the course	Short Text	20
College	College where the course’s department belongs	Short Text	20

Figure 2: Course Table Data Structure

FIELD NAME	FIELD DESCRIPTION	DATA TYPE	FIELD SIZE
SectionID	Identification number uniquely designating each section	Number	Integer
CourseID	Identification number of the course the section belongs to	Number	Integer
RoomID	Identification number of the room where the section is assigned	Number	Integer
ClassTime	Day/Time when section will be held	Short Text	20

Figure 3: Section Table Data Structure

FIELD NAME	FIELD DESCRIPTION	DATA TYPE	FIELD SIZE
RoomID	Identification number uniquely designating each room	Number	Integer
BuildingName	Building’s name	Short Text	20
RoomNbr	Room’s number within the building	Number	Integer
RoomName	Room’s name	Short Text	40
RoomType	Room’s type (C=classroom, L=lab)	Short Text	1
Capacity	Number of seats in the room	Number	Integer

Figure 4: Room Table Data Structure

FIELD NAME	FIELD DESCRIPTION	DATA TYPE	FIELD SIZE
RegistrationID	Identification number uniquely designating each student registration	Number	Integer
SectionID	Identification number of the section the student is registering for	Number	Integer
StudentID	Identification number of the student registering for the section	Number	Integer

Figure 5: Registration Table Data Structure

Now that the design is complete, Pam has asked you to build the database and complete an analysis of the registration data. When finished, submit your database in Blackboard.

Task 1: Launch Access and create a new database that you name ABC.accdb. Use Table Design view to create a new table, that you name Course, which contains the fields from the Course table data structure (Figure 2). Specify the CourseID as the primary key for this table. Save your database with your newly defined table. Copy the records from the ABC_Course.XLSX file and paste them into the Course table. Adjust column widths as needed to make the data readable.

Task 2: Add the Section table to your ABC database using the External Data → Import Excel tool. The Section data is stored in the ABC_Section.XLSX file. Be sure to specify SectionID as the primary key of the table. After you have imported the Section table from Excel, use the Design View to specify the data types as indicated in the Section Table Data Structure (Figure 3).

Task 3: Create a new table, that you name Room, which contains the fields from the Room Table Data structure (Figure 4). Specify the RoomID as the primary key for this table. Copy the records from the ABC_Room.XLSX file and paste them into the Room table.

Task 4: Add the Registration table to your ABC database using the External Data → Import Excel tool. The Registration data is stored in the ABC_Registration.XLSX file. Be sure to specify RegistrationID as the primary key of the table. After you have imported the Registration table from Excel, use the Design View to specify the data types as indicated in the Registration Table Data Structure (Figure 5).

Task 5: Create the relationships between the Course, Section, Room, and Registration tables as shown in the ERD (Figure 1). Enforce referential integrity for each relationship.

Task 6: Create queries to help Pam answer the following questions. “List” means to show these fields as output.

a. Which courses are offered by the Business College? List the Department, Designator, CourseTitle, and Credits. Sort the results by Department and Designator in ascending order. Name the query Task6A.

b. Which students registered for Elementary Education 1-3 on MWF 3:00-3:50? List only the StudentID. Sort the results in ascending order. Name the query Task6B.

c. Which classrooms have a capacity between 50 and 60 (inclusive)? List the BuildingName, RoomNbr, RoomName, and Capacity. Sort by BuildingName and RoomNbr in ascending order. Name the query Task6C.

d. How many sections are offered by each department? List the Department and the count of SectionID. Sort the results by the count in descending order. Name the query Task6D.

e. Which sections are taught on Tuesday-Thursday? List the SectionID, Designator, CourseTitle, BuildingName, RoomNbr, and ClassTime for all Tuesday-Thursday (TTH) sections. Sort the results by Designator in ascending order. Name the query Task6E. Hint: You will need to use wildcards to find class times that begin with TTH.

f. What is the average capacity of a classroom? List only the average capacity (do not include labs). Name the query Task6F.

g. How many students are registered by section? List the SectionID, Designator, ClassTime, RoomID, and count of RegistrationID. Name the count fields as Registered. Name the query Task6G.

For the remaining tasks, you will need to use the Task6G query as a data source to complete the queries (other tables may also be required).

h. When more students register for a section than the room can hold (capacity), they go on the Wait List. Which sections have students on the Wait List? List the SectionID, Designator, ClassTime, and Wait List (Registered – Capacity). Name the calculated field Wait List. List only the sections where Wait List is greater than zero. Sort the results by Wait List in descending order. Name the query Task6H.

i. Which sections have empty seats? List the SectionID, Designator, ClassTime, and Empty Seats (Capacity - Reserved). Name the calculated field Empty Seats. List only the sections where Empty Seats is greater than zero. Sort the results by Empty Seats in descending order. Name the query Task6I.

j. Which courses averaged more than 25 registered students? List the Designator and the average Registered. Sort the results by the average in descending order. Name the query Task6J.

Task 7: Pam wants you to create a Room Usage Report. The report should include the BuildingName, RoomNbr, ClassTime, Designator, and CourseTitle. Group the report by BuildingName and sort the output in ascending order by RoomNbr. Figure 6 shows a design sketch for the report. Follow the details shown in this figure. Use a function for the current date.

Ashland-Baldwin College Room Usage Report (current date)

Building Name Room Nbr Class Time Designator Course Title

Kettinger 254 MWF 4:00-4:50 HIST101 American History II 456 MWF 3:00-3:50 HIST 200 History of Ancient . . .

Figure 6: Room Usage Report Design Sketch

Task 8: Pam wants you to create a Couse Registration Report. The report should include the Designator, CourseTitle, SectionID, ClassTime, and count of RegistrationID. Group the report by Designator and Course Title in ascending order. Figure 7 shows a design sketch for the report. Follow the details shown in this figure. Use a function for the current date.

Ashland-Baldwin College Course Registration Report (current date)

Designator Course Title Section ID Class Time Registered Students

ACCT200 Managerial Accounting 17 MWF 2:00-2:50 44 18 TTH 11:00-12:15 48 19 TTH 3:30-4:45 46 . . .

Figure 7: Course Registration Report Design Sketch

Access Features

Feature	Help Lookup Key Word	Help Lookup Topic
Aggregate functions	Aggregate functions	Display column totals in a datasheet → Understand Sum and the other aggregate functions
Calculate fields	Calculate fields	Calculated fields
Create database	Create blank database	Create a new database → Creating a database without using a template
Create relationship	Create relationship	Create, edit or delete a relationship
Create table	Create table	Create a table
Enter data in table	Enter data in table	Add one or more records to a database → The basics of adding records → Add records directly to a table in datasheet view
Import data from Excel	Import Excel	Import data from Excel to a new table in Access
Primary key	Primary key	Create or remove a primary key
Report Wizard	Report Wizard	Create a simple report → Create a report by suing the Report Wizard

5