The end user Mr Lane is an experienced user of Word and has a basic knowledge of the operation of Access. He is also quite good at using Excel. His keyboard skills are standard. He has used Publisher for the making of cards but never on a professional basis.
Design
Comparison of Possible Solutions
A number of possible solutions could be considered:
- Use of Excel 97 Spreadsheet
- Use of Access 97 Database
Excel 97 is a spreadsheet of considerable power including database facility. However the relationships required would be difficult to implement in the time scale available. The final solution might also be difficult for the end user to understand and maintain.
Access 97 has all the capabilities required and is the preferred option for the end user. I have experience of using Access and some Visual Basic knowledge that can be used for enhanced customisation.
Using Access 97 it will be possible to:
- Create and edit tables
- Create and edit relationships
- Create forms and reports based on tables
- Create customised input screens and using Visual Basic facilitate searches and validation procedures
- Create queries to find specific data
- Create a menu driven system
- Implement security provision.
Database Design
The Database contains FOUR entities:
- Staff
- Videos
- Members
- Rentals
These are related as shown below (in normalised form illustrating many to one relationship from rentals table to the other three).
These relationships will be confirmed in the database relationship facility.
Data Requirement Definition (partial data dictionary)
Video
Staff
Customer
Rentals
Design of Input Forms
Four Data Input Forms are needed
- Customers
- Staff
- Videos
- Rentals
Customers Form
This will be needed to input and update the Members of Video World. Customers are automatically numbered. For this reason automatic incrementation is used. This form will be accessed from the editing form, which is reached from the main form. There will be a button to add customers and delete customers and also a find button. In addition the end user wished for a sub form that detailed the videos that the customer had already rented out. There will be a button to re-enter the editing form.
The Staff Form
This will be needed to input and delete the staff of Video World. Staff are given automatically incremented ID numbers. This form will also be accessed from the editing form. There will be a button to add and delete staff. There will be a button to re-enter the editing form.
The Video Form
This will be needed to input and delete the videos in Video World. Staff are given automatically incremented ID numbers. This form will be accessed from the editing form. There will be a button to add videos and a button to delete videos and a list box will be used to view details of videos already allocated. There will also be a find button to locate any video that needs editing or deleting. There will be a button to re-enter the editing form.
The Rentals Form
This will be needed to input the rented videos. This form will be accessed from the Main Menu. There will be a button for return videos and a button to delete videos and a list box will be used to view details of videos already allocated. There will also be three subforms that will show data once the IDs of staff, customers and videos are entered. There will also be a button to re-enter the Main Menu.
Menu Design
The menu structure will be based upon the following plan
Menu Design Explanation
Report Design
The design of the reports shall be generated using Access facilities to provide a professional look. Gary Lane would like the reports to show a lot of detail in a small space and so I think that a columnar report will be best suited.
Queries
Queries are an integral part of the operation of any Access Database. The queries implemented will enable reports to be formed, which will enable the specification required by the end user to be fulfilled:
Security
A password will be attached to enable Mr Lane and his staff to have immediate access to the Database. There is no need for different levels of security.
Schedule of Activities
Test Plan
Implementation
System Overview
This Video World rentals system is designed to replace the current paper based system.
The system is designed to run on the Video World PC on the basis that all of the staff can access and edit the Database.
Input of data into the system is through four purposely-constructed input forms:
- Staff Input
- Video Input
- Customer Input
- Rentals Input
You can input data at any time and the information that arises from these forms, comes in the form of queries. And so the end user can make use of the information.
The queries can be turned into reports for an overall analysis.
Tables and Relationships
This system is designed so that the mechanics behind all of the forms and reports will not be seen. The end user will only be using forms and reports.
Every required input table has been repeated as a form. The requirements of the tables have been discussed in the design section.
The relationships between tables is shown in the design section and confirmed in the relationship facility within the Database. A screen shot is shown below:
The relationships between the tables are shown. The entities and their attributes used in the table constructed are clearly detailed in the design section.
Forms
The Menu Structure was constructed as shown in the Design Section. I have decided to leave all of the toolbars and scrollbars in.
Main Menu
This form was selected as the start-up form (using Tools, start-up). This means it automatically loads when the database is opened.
All buttons placed on this form were allocated using wizards. This included the buttons for opening other forms and closing the Database.
A macro called Maximise opens the window to a maximum size when the database is opened.
Other forms navigated from this Main Menu are:
- The Edit Data Menu
- Reports Menu
- A rentals Menu
A screen shot is shown below:
The Reports Menu
All buttons were placed using wizards. Maximise macro carries on running when you open the Reports menu.
Reports Menu allowed navigation to the following reports and forms
- Customers
- Videos Rented
- Customers
- Staff
- Staff Progress
A main menu button provided by button wizard is also in this form
A screen Shot is shown below:
Edit Data Menu
All buttons were allocated using Access Wizards. Maximise continues on opening.
The Edit Data Menu allows navigation to the following Forms:
- Add Staff (Input Form)
- Add Video (Input Form)
- Add Customer (Input Form)
There is also a Main Menu Button added by button wizard
A screen shot is shown below:
Rentals Menu
This menu is accessed from the main menu. All the buttons were allocated using wizards. The macro Maximise continues when opening this form.
This menu provides access to the Rental form (Input Form). There is also a Main menu button.
Input Forms
Add Staff Form
This is a simple input form, constructed from the staff table.
To add a staff member you must click on the New Staff button provided by button wizard. This will make all of the entities blank for you to enter the correct data. The Staff ID is automatically incremented. There is also a delete record button for fired staff (ie staff no longer employed). There is also a back to editing menu button generated by button wizard.
Edit Customers Form
This form is used to add customers and also delete them. There are buttons for both of these functions generated by button wizard. There is also a find button generated by button wizard to find particular customers. There is a subform that shows what (if any) videos that particular customer is currently renting out, and when it is due back. It can do this because the forms are both related by their customer IDs.
A back to editing menu button is also generated using button wizard.
Rentals Input form
This form is used to input and delete the rentals from Video World. There are three subforms (frmStaff, frmCustomer and frmVideo) these are all related to the main form with their respective IDs. So when you type an ID into the main form the subform shows you which customer, video or staff member you have typed the ID in for. There are also buttons provided by button wizard for returning certain videos and for ging to the main menu.
Queries
Queries are listed in the design section.
All of the queries have been generated without the use of any wizards and have been produced from scratch by myself.
Screen shots:
Reports
The reports take information from the queries and turn them into a more user friendly vision.
I have reports for the customers, customers in Oakwood, videos currently being rented out and by whom, the staff employed, the videos that staff have rented to customers, and the videos currently owned by Video World. The reasons for these reports have been explained in the query section of the design section.
All the reports were produced and then modified to the specifications of Gary Lane the end user.
Screenshots are shown below
Macros
The macros I have used are
Maximise This macro maximises the form that I set it to. I have set macro maximise to the Main Menu and it takes place as soon as somebody opens the Video World database.
Overdue This macro tells the end user if a person has not brought their video back on time. If the condition of the date being a day after the date of return is valid, then a text box shall appear saying the customer owes a video.
Test Results
I have gone through with my test plan and here are my results. I have provided screen shots for all of my tests as proof that they are successful or not.
Test 1- Test Password
This test was successful as the password entry screen came up and only “november6” opened the main menu.
Test 2- Test main menu option to open “Editing” menu form
This test was successful as when you press the Editing button it opens up the data entry menu for customer staff and videos.
Test 3- Test main menu option to open “Reports” menu form
This test was successful as when you press the Reports button it opens up the reports menu.
Test 4- Test main menu option to open “Rentals” menu form
This test was successful as when you press the rentals button it opens the rentals menu.
Test 5- Test main menu option to Exit database
This test was successful as when you press the Exit button the database closes.
Test 6- Test editing option to open Customers form
This test was successful as when you press the customers button the customers input form opens.
Test 7- Test editing option to open Videos form
This test was successful as when you press the videos button the videos input form opens.
Test 8- Test editing option to open Staff form
This test was successful as when you press the staff button the staff input form opens.
Test 9- Test add customer button in the customer form
This test was successful as when you press the add record button a new record appears.
Test 10- Test delete customer button in the customer form.
This test was successful as when you press the delete record button the record selected is deleted.
Test 11-Test Find button in customer form
This test was successful as when I pressed the find button the find box appeared and the criteria selected was found.
Test 12- Test relationships between customer form and rental subform.
This test was successful as the Customer ID for the form was related to the subform’s Customer ID.
The customer had not rented out any videos so the other data is irrelevant.
Test 13- Test customer form’s combo boxes.
This test was successful as when you press on the down arrow a list of titles and towns are available to choose from.
Test 14- Test video form’s find button
This test was successful as when I pressed the find button the find box appeared and the criteria selected was found.
Test 15- Test the add video button in the video form.
This test was successful because when you press the add video button a new empty record is visible.
Test 16- Test the video form’s combo boxes.
This test was successful because when you press on the arrow of the combo box a list of genres certificates and prices are available.
Test 17- Test the staff form’s add staff button.
This test was successful as when you press the new staff button a new empty record is visible.
Test 18- Test the staff form’s fired staff button
This test was successful as when you press the fired staff button the record selected is deleted.
Test 19- Test the rentals button in the rentals menu.
This test was successful because when you press the button the rentals form opens.
Test 20- Test the subform’s relationships.
This test was successful because all the relevant subform IDs were related to the main form.
Test 21- Test the combo box for the price entity.
This test was successful because when you click on the arrow a list of prices appears.
Test 22- Test macro Overdue
This test was successful because when a video has not been returned by the date it is supposed to be, a message box appears.
Test 23- Test the returned button in the rentals form.
This test was successful because when you press the button the record is deleted.
Test 24- Test all the menu options in the reports menu.
This test was successful because all of the reports opened. To see screenshots look at the reports section of the implementation section.
Test 25- Test all the back to main menu buttons.
This test was successful as all of the buttons worked correctly.
Test 26- Test recovery after power failure.
This test was successful as when there was a power failure only the current changes were lost.
Test 27- Acceptance test by end user.
This test was successful as he could do all of the necessary tasks without any trouble.
Evaluation
The system has been installed on the Video World computer where the end user works. The system was downloaded from floppy disk and was easily installed. The system has been stable since implementation.
Qualitative
Data entry should be as intuitive as possible.
The data entry forms are provided with combo boxes wherever possible to facilitate entry. The end user understands the fields that provide the rest of the data. The movement around each input screen is logical in progression. The colours, fonts and groupings have been chosen for easy use.
The input screens should be self-explanatory.
Due to the design features introduced such as combo boxes many of the input fields are obvious. The navigation around the input screens is fast and logical in progression. The use of logical text box object input names aids in the input of information
Data entry should be quick.
Most of the fields require only one word, which speeds the inputting process up. Also the combo boxes require only a click of a button. The design of the screens of general layout makes them easy to read and navigate quickly.
The mechanics of the application chosen should not be apparent to the user.
There is no need for the end user to go into the inner workings of the database. No obscure data formatting is required. Error messages make it obvious when input is of an incorrect type. The use of Menus which seamlessly blend, make navigation around the whole system intuitive.
A main menu should be loaded automatically when the database is loaded, and the whole system should be menu driven.
When the database is loaded the first screen is the main menu. This menu allows fast and efficient access to other menus. All of the menus are of similar design and layout. You are able to navigate through the whole database using only menu buttons.
Quantitative
It should be possible to rent a video without referring to video or member input screens.
By using combo boxes and sub forms supplying appropriate information, the input of rented videos is quick and easy.
It should be possible to input new videos, staff members, and customers in less than thirty seconds.
The average times to input are:
Videos 15 seconds
Staff 25 seconds
Customers 30 seconds.
These times have been verified by the end user who is a proficient user of a keyboard.
It should be possible to provide reports in less than 60 seconds in each of the following areas:
- Videos rented
- The staff that rented the videos out
- Staff currently employed
- Videos owned by the store
- Customer details
This has been achieved by using queries and also reports based upon these queries. The access times for these reports are very short indeed. The end user simply has to click on two buttons and the reports are generated.
Test Plan Results
All of my tests ran correctly and there were no problems.
Areas for Improvement and Development
In retrospect my Query “Late” should have been labelled “Rentals” and my “Fired Staff” button in my Staff form should be labelled “Delete Staff Member” because the staff may have left rather than being fired.
My rentals menu only has a button leading to the rental form and therefore has no purpose, I could have just had a button leading from the main menu to the rental form.
I should also have had a delete button for videos no longer held in stock.
User Manual
Introduction
This database is for the keeping track of video rentals in a video shop.
Features include the ability to keep track of customers,videos in stock and staff details. There is also the ability to keep track of when videos are due back and how much money is owed.
Getting Started
When the datbase is loaded, a password screen will come up. Enter the preset password, press the enter key and you will be taken to the main menu.
From here you can go to the edit data menu, the rentals menu and the reports menu. There is also an exit button to close the database.
Editing Menu
From the main menu press the editing button. This will take you to the editing menu. From where you can see buttons for Staff, Video and Customer input forms. This menu is where you can input and edit the data for the database.
Staff Input Form
Press the staff button and you will be taken to the staff input form. In this form you can add and delete staff and also edit data.
To input your staff members into this form simply click on the new staff button.
The data is easy to input all you have to do is put the relevant data into each attribute. You don’t have to type in a staff ID because the database will do it for you.
To delete a staff member that no longer works for your company, click on the fired staff button. A box will ask you if you are sure you want to delete that particular staff member, click yes.
To go back to the editing menu press on the button, with an open door with an arrow pointing to it (back button).
Video Input Form
From the editing menu press on the video button. You will be taken to the video input form. This is the form for adding the videos that your business owns.
To add a video click on the add video button a new record will appear and you have to type in the video name. As before the video ID will not have to be inputted as the computer will do it for you. For the price, certificate and genres there is a combo box that you can bring down and it will give you a list to choose from instead of you having to type it in.
After you have put in all of your videos you can search for a particular video by pressing on the binoculars button. A box will appear asking what you would like to find, type in the word you would like to look for and the computer will find it. If the computer finds more than one you can click on next to see the next record it has found. Click the close button to exit the find box.
To go back to the editing menu click on the back button.
Customer Input Form
From the editing menu click on the customers button and you will be taken to the customers input form. From here you can add and delete customers, find customers and later you can see what videos that customer has rented.
You can add customers by clicking the add record button and then enter the data the same way in which you would add staff and videos. There are combo boxes for title and town to save time on inputting the data.
To delete a customer click the delete record button and a box will appear aking if you are sure you want to delete that customer, click yes.
When all the customers are added you can find specific ones by pressing on the binoculars button. A box will appear asking what you would like to find, type in the word you would like to look for and the computer will find it. If the computer finds more than one you can click on next to see the next record it has found. Press the close button to exit the find box.
Also when a customer has rented a video out you can see which one from this form. Search for the customer you want to view and the rental subform will show you which video that person has rented.
To exit the form press the back button.
Rentals Form
From the main menu click on the rentals button, you will be taken to the rentals menu. Click on rentals again and you shall be taken to the rental form.
From this form you will be able to rent out the videos to customers.
To enter a rental you need to know the Staff ID, the Customer ID and the Video ID. Enter these ID numbers in the relevant attributes. To check if you have the right ID numbers the subforms will show the relevant data. Then enter today’s date in the date rented entity. Then enter the date it is to be returned in the date of return entity. Enter the cost of rental in the relevant space, (if you are unsure of how much the video costs look in the video subform and it will tell you).
When a video is returned click on the returned button and the record will be deleted.
If the date passes the date of return a message box will appear saying that the person owes a video. Click OK on the box and it will dissappear.
To go back into the rentals menu click on the back button.
Reports
From the main menu click on the reports button and you will be taken to the reports menu. From here you can view reports on videos, videos rented, customers, staff and staff’s progress.
To view a report simply click on the button with the report you would like to see.
The Videos report shows all of the videos that you have entered in the video input form. It shows all the videos you own.
The videos rented report shows what videos are rented and who by. It also shows when the video was due back and the price of the rental.
The customer report shows a list of all the customers that are in the customer form.
The staff report shows the members of staff currently employed by your business.
The staffs progress shows what videos the staff have rented to people and when they served the customer.
To go back to the main menu press the back button.
Exiting
When you have finished using the database go to the main menu and click on the exit button.
Ashan Arslan Video World Database Project