A tutor represents one of the entities within this database. The tutor as an entity has been defined using a Tutor table that consists of the following fields:
The Modcoord and StudentID fields are the primary and secondary keys respectively for this Tutors table.
The tutor and student tables are related via the module coordinator table. A tutor also qualifies as a module coordinator and hence the primary key (unique identifier) of any of the tutors is the ModCoord.
The primary key in the module coordinator table is ModCoord.
From this setup, we can identify that the student, module coordinator, and tutors tables are related. The association between the module coordinator and the student table is a one-to-many type. This implies that a module coordinator has one or more students to handle for a particular module. Similarly, the relationship between the module coordinator and tutor is a one-to-many type that implies that one module can be coordinated by one or more tutors.
The screenprint below shows these relationships:
Tutors require information related to the student as well as the module. Typical information required will include the StudentID, ModCode, Name, Address, County, Postcode, Email, Telephone number and Group. The tutor details form captures the tutor details. Typical fields available on the tutorial details form include ModCoord, StudentID, and Name. The tutors may also want to know the performance of their students for assessment purposes.
Button and macro actions have been utilized to link the tutorials form to the student and coordinator forms. This further illustrates the relationship that exists between the tutors, students, and module coordinator tables.
The University of London is one of the reputable academic institutions that seek to benefit from integrating a relational database application to take care of the student course coordination requirements. The relational database for this university includes; students and tutors among other users. This study is going to focus on the tutor as one of the user groups found within the university database. The tutor in this setup will also double up as a module coordinator and will therefore need information relating to a module, degree, student, schedule, and campus. The university students undertake various modules for their degree program. These courses may be offered at different campuses and therefore a proper lecture schedule is necessary to cater to these student requirements. The tutor should therefore know those students taking the respective courses for which they are module coordinators. This requires that a student is assigned to a course or module by the module coordinator. It may be important to understand that the tutors are the module coordinators. Looking at the student-tutor query, we can see the details of the students assigned to various tutors. The two main entities student and the tutor are used by the queries. The two entities have one to an association.
The student_tutor query furnishes the tutor with the details of students assigned to them. Alternatively, the students identify their tutors using these queries.
In Microsoft Access, the form object can provide an interaction point for the user with the tables. This form object enables a user to update the tables containing data used by both queries and reports.
Based on the tutor as one of the user groups, we can describe their interface based on the tutorial details form.
The form is divided into three sections; the schedule section which indicates the module coordinator, student identification, name, room, and campus.
The next part of the form covers the other entities related to the tutor. These are the schedule, student, and coordinator each of which has a form that can be opened by clicking the respective button.
Subsequently, the third section of the form contains the buttons that activate the reports related to the entities defined in the middle section of the interface.
The menu design is such that all associated entity details can be accessed from any of the main forms. While at the tutor details interface, the schedule, coordinator, and student interfaces can be easily accessed just by clicking on the respective buttons to open the forms via macro actions.
The menu structure is simple and centralized allowing a user to access all the information from one location. This is an important and fundamental interface design principle, proposing that the menu should make available to the user all the options and a simple navigation method back and forth between the various interfaces. The menu structure remains consistent in the naming and navigation to ensure the user is not confused. The diagram below shows the general menu structure of this application
Application
The GetDay query as one of the parameter queries accepts a day of the week as input at the prompt to display the records of all the modules undertaken during that day of the week. The screens below show this query when it is run.
After submitting a parameter as M, for Monday as one of the days of the week and clicking on the modules for the day button, the following screen in displayed:
Other queries include the Student and Student campus parameter queries added to the database based on the requirements. A parameter query is a select query type that retrieves data from the database based on a user-defined parameter. This exercise demonstrates two parameter queries created based on student and campus data.
The student parameter query is a simple query that will return the details of a specified student whose details are submitted in the ‘enter StudentID ‘ prompt box as shown in the screens below.
The student parameter query design is shown below.
Running this query results in the details of the student whose ID was submitted as the parameter.
The following screen shows the prompt box with a valid StudentID (12456)
On clicking the ok button, the student parameter query will retrieve the details of StudentID (12456) as shown below:
It is therefore possible to quickly retrieve the name and module coordinator for any of the thirteen students in the university database using the student parameter query by simply submitting a valid student ID.
It may be necessary to know which campus each of the students is at and this can be achieved by running the Student_campus parameter query. The search criterion is based on the StudentID submitted in a prompt box.
The Student_campus parameter query design is shown below. The tables used here are the Module coordinator, Student and Tutors.
The defined relationship between the three tables student, module coordinator, and tutor enables the query to pick data from the three tables at the same time.
When this query is run with StudentID 12456, the result is shown below.
Table structure documentation
This university database has a number of tables. They include Campus, Degree, Student, Tutor, Module Coordinator, Room Number, and Schedule.
The Campus table consists of the campus name, county, postcode, address, and telephone. The primary key is the field Campus.
The Degree table consists of ModCode, Campus, SingleHons, and Combined as the fields. The primary key for this table is the ModCode.
The Student table consists of StudentID, Name, Address, County, Postcode, Email, Telephone Number, Group, ModCoord, and ModCode.
The Tutor table consists of CoordID, ModCoord, StudentID, Name, Room, and Campus.
The Module coordinator table consists of ModCoord, Email, Room, Telephone, Campus, and ModCode.
The Room Number table consists of ModCode, Module, Semester, Type, Group, Campus, Room, and RoomID.
The Schedule table consists of ModCode, ModCoord, Semester, Day, Type, Group, StTime, EndTime, RoomID, Campus, and ScheduleID:
The student_degree query has an inner join as indicated in the SQL statements below:
- SELECT Student. [Student ID], Student.Name, Degree.ModCode, Degree.Combined
- FROM Degree INNER JOIN Student ON Degree.ModCode = Student.ModCode;
The following is the data in each of the tables captured in the datasheet view:
Relationships
Access and other relational databases have a way of connecting the various tables in the database to enable other database objects like the queries and reports to retrieve consolidated data from these different tables. The simplest database relationship is a one –to –one relationship meaning that each record in one of the tables has only one related record in another table. Alternatively, a one –to – many relationships is where a record in one table has one or more associated records in another table. In order to enable the retrieval and consolidation of data to be meaningful, on how data will be added, updated, and deleted. This process involves setting rules from these tables.
The university database has a number of tables that are related with some having enforced referential integrity.
The relationship between the module coordinator student and module coordinator tutor and is a one- to – many relationship meaning that one record in the module coordinator table refers to one or more records in the student and tutor tables respectively.
The Campus and Degree tables have a one –to – many relationship between them as well with referential integrity enforced.
The relationship between Degree and Student and Schedule and Room Number is indeterminate with the join property along rows where the joined fields from the two tables are equal.
The relationship between the Tutors and Room Number is indeterminate with the join property including all records from Tutors and only those from Room Number where the fields are equal.
User Guide
This application has been developed in the Microsoft Access 2003 and later environment and therefore will run in this environment.
The application requires the following to successfully run A windows operating system platform.
The Microsoft Office 2003 or later suite that includes the Microsoft Access application.
The application also requires a minimum hard disk capacity of 45 Mb.
The application will run well using RAM of 256Mb and above.
On opening the application, the main switchboard is activated from where the user can call and run other modules of the application. The screen below illustrates the main switchboard with the following options:
- Student details: Used to open a student form in edit or add mode
- Tutor details: Used to open a Tutor form in edit or add mode
- Schedule details: Used to open a Schedule form in add or edit mode
- Coordinator details: Used to open the Module coordinator form in add or edit mode
- Exit database: Used to exit from the application
Modules overview
This application consists of a number of modules. The main one are described here below:
Tutor: This module handles all the tutor details in this application. A typical screen for this module is shown below:
Schedule: This module handles all the schedule details in this application. A typical screen for this module is shown below:
Coordinator: This module handles all the module coordinator details in this application. A typical screen for this module is shown below:
The application also contains four main reports namely:
- Module coordinator report
- Student report
- Tutor report
- Schedule report
An entity relationship diagram (E-R) of the University of London Relational database.
The summary of the attributes for the ER diagram above are shown in the table below: