In this series of practical tutorials you will build a simple prototype information system using the database management system, MS-ACCESS. The system is based on a lecturer's student record system.

Important:
Initially you will create the data files with the fields and their definitions (ie. types, width, etc). Then you will create relationships between the tables. This allows for more efficient usage and storage of the data in the database. We then finally populate the tables with the data. Note: you are sometimes restricted to entering certain data because of the relationships that are in place.

Creating and Maintaining Database Files (Tables)

Creating and Maintaining Database Files(Tables)

• Load MS-ACCESS
  
  
• Select File → New → Blank Database from the file menu.
  
  
• Select a suitable directory (folder) to store your database. You may even create a new folder called, say, INFOSYS. Name the database studrec and click on the Create button.
  
  
• A database window, studrec, should now open.

Note: When you wish to work on this database in future tutorials, you will select Open from the File menu, find your account in the network directory structure and select studrec from the Open window.

The first prototype system will consist of two tables (files) - a student table and a tutorial table.

Creating the student table

From the database window ensure the Table tab is selected, then :

• select New
• then Design View

Enter the following

The field, stud_id, will be specified as the key field (unique identifier) for this table. To do this select the stud_id row and click on the Primary Key icon (a small yellow key on the tool bar). The key icon will then be displayed in the stud_id row.

Save the table as student

Question
Use the help facility in MS-ACCESS (Press F1 or click on help) to determine the meaning of the following special characters when used to specify an Input Mask (you will need to highlight the Input Mask field before hitting F1)
>   C   9   0   <   ?

Creating the tutorial table

From the database window again create a New table in Design View.

The following table summarises the characteristics of the tutorial table. Specify tute_no as the key field and save the table as tutorial.

Creating a relationship between the Tutorial and Student tables

To enable MS-ACCESS to maintain the integrity of the stored data, it is sensible to create a relationship between the two tables. This will be based on the common field, tute_no, present in both tables. The relationship will show that one tutorial can have many students and that the tute_no for a student in the student table must be present in the tutorial table.

Select Relationships from the Tools menu or use the appropriate button.

Add both the student table and the tutorial table and close the Show Table window.

Select tute_no in the Tutorial table and, while holding down the mouse button, drag the cursor to tute_no in the Student table - release the mouse button.

In the window that opens (the relationships window) select the following:

• Enforce referential integrity
• Cascade update related fields
• The Relationship Type should be a One-To-Many. Click on the Join Type button to see what this means.
• Then Create the relationship.

Now close the Relationships Window and save the Relationship Layout.

Adding data to the tables

To add data to the tables, highlight the table name in the database window, select open and begin adding the data into the datasheet. Add the following data - due to the enforcement of referential integrity, you must add data to the tutorial table before the student table.

Question: why?

To find out why, try adding data to the Student table. What error message does Access display?

Enter the following test data into the tutorial Table:

Enter the following data into the student table:

Adding, modifying & deleting records

To add a new record to a database table, select the table name in the database window and then select open. New records can be added at the bottom of the datasheet.

To modify an existing record, simply open the datasheet, and modify the appropriate record.

To delete a record, simply open the datasheet and select the appropriate record row by clicking in the leftmost column (ie. the grey column). The record should now be highlighted. To delete, press the delete key.

However remember that referential integrity may not allow deletions in the tutorial table if there are related records in the student table.

Attempt each of the following exercises.

1. Add a new student, 891221 MICHAEL CHANG to tutorial group 2
2. DIANNE GATESBY is recently married and has become DIANNE JONES
3. Attempt to add a new student, 901076 JAMES JONES, to tutorial group 1. What happens and why? Make a necessary modification to allow the addition.
4. Delete the student, JAMES JONES
5. Attempt to delete Tutorial group 1 from the tutorial table. Why is this not permitted? What has to occur before it is permitted?
6. From the database window choose the Tutorial table and click on Design. Click on tute_time and then click on the Input Mask option.
   
   Enter the follow Input Mask for tute_time:
   >LLL\   00\.00\   LL.
   Hit F1 whilst on the Input Mask option to find out what this group of characters do. As we continue through the exercises you may wish to enforce the entry of some data by inserting your own Input masks if they do not exist or if they do you may improve on them!
7. Add a new tutorial group - group 4 on Thursday at 11 AM in room R54 with tutor number 2. Notice how the Input Mask comes into action now. Add the following students to this new tutorial group.
   
   891111   GRAEME SMITH
   934573   JAMES WHELAN
   931134   MARY ROBERTS
   937619   JAMES HARDIE
   921457   LUCY LAUDIE
8. Return to the relationships option and draw a diagram in your lecture notes of the relationship between Student and Tutorial. Ensure you include all the fields in your diagram.