Overall system design

My system will not be entirely automatic – some manual processed will obviously be required. However, the manual processes should work together with the computerised processes should work together efficiently to provide a full working solution. I will construct the system to allow data flow in this pattern:

Modular structure of system

The system as shown in the system flow chart can be thought of as being made up of several different modules, each with its own section of coding. These are:

• Validation Modules: There are two independents validation modules which are the ones which validate the entry of data about members and tracks. However, there a further validation module will be “built in” to the Process Booking module, to ensure that only valid bookings are made. For more information on the validation process, see the validation section.
• Process Bookings Module: This will take data from both the Member and Music files in order to build up and store a valid booking.
• Search Modules: These will have their own algorithms to search the databases for the relevant information. For more information on the algorithms, see the algorithms section.

Other parts of the system, which cannot be truly referred to as “Modules”, are also necessary. These manual processes are:

• Collection of Track Details: This involves the typing in of details about each track. More information in the Data Capture and Entry section.
• Collection of Member Details: This involves the typing in of details about each member, and keeping these details up to date. More information in the Data Capture and Entry section.

Data Requirements

Because of the many-to-many relationship involved in this data, a separate “link” table containing the fields Date, Time and Track ID will be necessary to normalise the database. The E-R model can then be constructed as shown in the Database Design: E-R Model.

Storage media and format

The data will be stored as Access Database files, and will be stored on the computer’s hard drive, and backed up regularly onto Zip Disc. Storing the files as Access Database files is the most practical solution, since I will be accessing the files from Visual Basic via Microsoft Access. Since these files are of a recognised and widely used file format, they will also be easy to use with other programs if the system is changed in the future.

In terms of a volumetric calculation:

• The “Music” table could have up to about 70 characters => 70 bytes per record and will hold approximately 2000 records => 140kB
• The “Members” table could have up to about 150 characters => 180 bytes per record and will hold approximately 30 records => 4.5kB
• The “Bookings” table could have up to about 150 characters => 150 bytes per record and about 20 records will be added per week => 3.6kB added per week => 187.2kB added per year

Therefore, the database cannot be expected to grow over about 350kB over a year, or 2.5MB over ten years. Therefore, the 20Gb hard drive is ample.

Algorithms

The main algorithms used in the project will be those for the searches. The search algorithms will be so similar that it only seems worth including one of them at this stage, since they will be fully documented in the program listings. Therefore, I have only included the algorithm for the search of the member database:

Dim sql As String

sql = “SELECT * From Client WHERE (((UCASE(Client.ClientId)) Like ‘%?%’));”

sql = Replace (sql, “?”. Ucase(txtSearchFor.Text))

adoClients.RecordSource = sql

adoClients.Refresh

Validation

The validation procedures will validate the data as detailed in the data dictionary. I will leave the error handling to Access, as I do not feel that there is any way in which I can improve upon the error handling provided. However, to reassure the user, I will intercept the default error message with my own, as new users may not understand those provided by Access. I will also put the incorrect field in focus so that the user can correct it.

The error message will be a VB message box, and so will look something like this:

User interface design

In order to navigate the system, menus will be used. Before looking in detail at the form design of the menus, it is logical to look at the menu structure as a whole. This is shown below. I have designed it to be as logical and intuitive as possible, so that each part of the program is easy to find. The menu structure is shown here:

Clicking on the large command buttons to navigate the system will take the user directly to the editing dialog as relevant. This will mean that the less computer-literate users will have access to all the data without becoming confused over more complex features such as reports.

The menu forms will look like this:

The Member Details form will be designed like this:

When the form loads, the first member’s details will be displayed in the text boxes. Changing the details as displayed on the screen will edit the database. This system will also be used in the music database:

The bookings display will need to be slightly different, but the same principle will apply in that the first booking will be displayed when the form is loaded. The form will be designed to look like this:

In order to maintain consistency throughout the program, all of the search dialogs will be designed to appear similar. They will all look like this:

The reports will appear in Visual Basic’s default report window. The reports themselves are discussed in “Output”.

Data capture and entry

For the data capture of the member details, a new member data capture form, which has been tried and tested over several years is already in place, and I see no good reason to change this. This form is given to all new members, so that their personal information can be recorded. They are required to inform the secretary of any changes to their information, and so the secretary can then update the new system in the same way that he currently updates the system in place at the moment. The data will be entered through the system which I have designed by navigating to the Member details screen and clicking “Add”. The relevant information can then be entered via the keyboard.

The data for the music database can be found on the cover of the Minidisks, and is currently entered into the old system by support staff. There seems no valid reason to change this system, so the support staff can continue to enter the information by typing it into the new system. To do this, they will simply need to navigate to the “Music Database” form and click “Add”. They will then be able to enter the data for the new track to add it to the database.

For the bookings system, the presenter who wishes to book the studio can simply enter the relevant information into the system when they are at the studio. Alternatively, they could phone the studio and someone could enter the booking information for them.

Record / Database structure

The database will be made up of three tables: Music(ID, Title, Artist, Length), Members(ID, Name, Address, Status) and Booking(ID, BookerID, Tracks, Time, Date).

For more information on these, see the data dictionary under “Data Requirements”.

Output

The hard output from the system will consist of several reports which can be printed if the user so wishes, or alternatively just viewed on the screen. The printed output will look like this:

Security and integrity of data

The only data which needs to be secured is that of the members, since this is particularly sensitive. Any problems with the integrity of the other data will be detected and easily corrected during system usage. The Member information, however, only needs to be accessed by executives, and so will be password protected. The password protection will be built into VB. This will obviously not make the system impossible to get in to, but it should make it a little more difficult for opportunists. The password will be stored as a separate database table.

System security

System security will be rather important in this system, since there will be a lot of data stored in it, and nobody would be pleased if the data became corrupt and had to be re-entered. Therefore, regular backups should be made. Since data is not often added to the system, weekly backups should be sufficient. The database files are the ones which should be backed up. The database can be backed up with the rest of the system as is the present system, and the backup is stored at an external location (the secretary’s house). This should make the data secure enough. Password protection, as discussed in Security and Integrity of Data, should help to prevent accidental deletion or corruption of the more sensitive data in the system.

Test strategy

My Testing strategy will make sure that all parts of the program are able to handle typical and extreme data, whilst rejecting erroneous data. Particular parts of the program which will be subject to testing will be those with validation processes, such as the Music IDs and track lengths in the Music Database. Of course, other parts of the program will also need to be subject to testing in order to ensure that the system created works fully.

For further details on the testing actually carried out during the project, see the section “System Testing”.