CSE1IS Information Systems
Tutorial - Week 8
Extending the Access Database Application

La Trobe University, Bendigo campus Department of Computer Science and Computer Engineering
 

In this tutorial we will create three extra database tables - Result, Assessment and Tutor

Remember that the datatypes and field sizes need to be consistent across linked fields in different tables throughout your database. ie if tutor_no is a numeric field in Tutorial table then it should also be numeric in the Tutor table.

The Tutor Table

Table description for the Tutor table
Field Name Data Type Field Properties
tutor_no
(Primary Key) 		Number Field size:Integer
Required: Yes
Indexed: Yes[No Duplicates]
t_name text Field size: 20
Input Mask: >CCCCCCCCCCCCCCCCCCCC
Required: yes
Allow Zero Length: No
Indexed: Yes[Duplicated OK]
t_office text Field size: 4
Input Mask: >?999
Allowed Zero Length: No
Indexed: No
Enter the following sample data for the Tutor table
tutor_no t_name t_office
1 MR TOM BURNS C345
2 MS JOAN RIVERS F780
3 DR RIVIERA C112
4 PROF SIMPSON C768

The Assessment Table

Table description for the Assessment table
Field Name Data Type Field Properties
assess_no
(Primary key) 		number Field size: Integer
Required: yes
Indexed: Yes[No duplicates
(specify as the key value)
a_desc text Field size: 25
Input Mask: >CCCCCCCCCCCCCCCCCCCCCCCCC
Required: Yes
Alow Zero Length: No
Indexed: No
max_mark Number Field size:Integer
Required: No
Indexed: No
weight Number Field size: Integer
Required: No
Indexed: No
Enter the following sample data for the Assessment table
assess_no a_desc max_mark weight
1 Assignment 1 (theory) 50 10
2 Assignment 2 (prac) 30 20
3 Presentation 10 10
4 Exam 100 60

The Result Table

Table description for the Result table
Field Name Data Type Field Properties
stud_id
(Primary key) 		text Field size:6
Input Mask: 999999
Required: Yes
Allow Zero Length: No
Indexed: Yes[Duplicates OK]
(specify as part of the key - see below)
assess_no
(Primary key) 		Number Field size: Integer
Required: Yes
Indexed: Yes[Duplicates OK]
(specify as part of the key -see below)
mark Number Field size: Integer
Required: Yes
Indexed: No
Note:
In this table both stud_id and assess_no will be specified as the key field. You will need to select both fields together as the key. To do this, select the top most of the two and while holding the shift key down select the other. Release the mouse button and then click on the key icon.

Before entering sample data into the Result table we will update the table relationships.

From the Database Window select Relationships... from the Tools menu (or click the Relationships button in the Toolbar).

Add the new tables to the relationship layout using the Relationships → Show Table option.

Establish the following relationships (enforce Referential Integrity, Cascading Update and Cascading Delete in all cases).

The relationship layout should resemble something like:

Entity - Relationship diagram showing a one to many link between the Tutor Table and the Tutorial table, one to many between the Tutorial table and the Student table, one to many between Student and Result, and one to many between Assessment and Result

Now enter the following sample data in the Result table
Note:

  1. the stud_ id numbers appearing here must be present in Student table.
  2. the mark must less than or equal to max_mark for the assess number in the Assessment table.
Sample data for the Result table
stud_id assess_no mark
900543 1 45
900543 2 20
898265 2 15
898265 1 40
904372 1 35
904372 2 22
902514 1 36
902514 2 28
895601 2 25
895601 1 33
909533 2 19

Some extra queries

Can you create the following queries? Think about what the answers should be and compare your output to what you expect. Write down a diagram of the query solution.

  1. A query based on the Student and the Result table - showing student names (in alphabetical order), the assessment number (in ascending order) and the marks they have received for each piece of assessment - save as StudentResultQRY.

  2. A query based on the Student, Result and Assessment tables - showing student IDs (in ascending order), names, the assessment number (in ascending order), the assessment description and the marks received - save as FullStudentResultQRY.

    Note: In this query the assessment number is not to be displayed in the query. (hint: click on the show box under assess_no to de-select it).

  3. Modify the query, FullTutorialListQRY (created in an earlier tutorial), to show the tutor’s name next to the tutorial details.

  4. Create a new query using the result table that first lists all of the student ids and their marks. Then use the View → Totals option in the query design to reveal the Total row. Try to group the students so that you can get an average mark for each student. Hint: this will involve the Group By option and the Avg function. Try some other functions such as counting the number of subject results per student. Experiment with some of the other functions too!

  5. Using the Tutorial and Tutor tables, construct a query that counts the number of tutorials taken by each tutor. You are free to experiment further with other options available when the Total row is viewable.

Valid XHTML 1.0 Transitional