Video Rentals Shop Database

Elliot Hunt GCSE I.T.

Video Rentals Shop Database

Description of chosen problem

For this project I am going to design and make a database to fit the requirements of a local video rentals shop. The video shop I used for research was STRIKE video rentals, and unfortunately for them they are still on a manual system, this is due to the cost of the computer and other requirements like soft and hardware required to set such system. I need to create tables that can be extended and upgraded as the years tick over, with new videos coming into stock and new customers gaining membership. I will be required also to make a table of rentals, for the manager and workers use, so they can check which customers have currently rented which videos. It will also be important to have the customers address so if encase a customer has a video over their allowed rental time, a letter can be sent to their home indicating that their video is overdue and that they will have to pay a small fine which will obviously vary depending on the length of time the particular customers video is overdue.

The current system under use is far from faultless. With files all being kept together they can infact be prone to getting misplaced which can cause a delay in the running of the shop. If a video is overdue, it could be incredibly time consuming to write out a letter to every customer who is overdue and, with the files in the filing cabinets being all kept together, it is infact prone to error in that the letter can be sent to the wrong address or the wrong details could be contained in the letter. Taking a stock check would be made worlds easier as instead of having to close the shop for one or two days (loosing a lot of business) to count every video and compare them to the records, you could simply click a button and the computer would do the stock check for you in seconds, allowing a stock check to be done on a far more regular basis making any loss of videos known within one or two days of it happening as well as showing which customer has the video.

Description of Problem: The current system under use in the video shop is infact a set of filing cabinets that is only maintained by all the employees who also have to concentrate on serving customers as well taking stock checks etc. A photocopy of a pre written letter is used to mail customers who have rented videos, which are overdue. This is a serious inconvenience because the employee who is working at a particular time must take time to get a photocopy of the pre written letter and fill in the required in formation, Name, Time film has been overdue, price of fine which has been enforced etc. Following the employee doing this, he or she would have to put every letter into an envelope, and write the customers address on the envelope, apply a stamp and leave it for the postman to pick up, or even more time consuming, have to take the letter down to the post office themselves. All these actions are error-prone, the employee could put the incorrect name on the envelope or the wrong address the wrong fine could be put also, particularly if the employee has more than one letter to write. If a customer wishes to enquire about weather or not the video shop has a video in stock, a manual check has to be done, which is also prone to error. A manual check is not only prone to error but it is a manual check prone to error; it is also incredibly time consuming which can result in the possibility of a customer losing interest in renting the particular film or even an extreme, customers joining another video rentals shop because they may consider the service to be poor.

From looking at the above reasons it is made relatively clear that a computerised system would be hugely beneficial to the video rentals shop and although it would be relatively expensive to set up the system, I will be regarded as almost essential in the next few years to keep up with the demands and competition of other video shops. By using a computer-based system, it would allow the rentals shop to expand in the future, for example it would enable the shop to expand and have other outlets in the future and if the computer possessed a CD re-writer ...

This is a preview of the whole essay

Investigation and analysis and specification of solution: After visiting STRIKE video rentals, I asked if it would be possible for them to show me how their current system is operated, following this a time was arranged when it was convenient for the system to be shown and explained to me.

When a new customer wishes to gain membership, the customer is required to fill out a data collection form containing various details (Name, Address, Phone number etc.) after the customer has filled out the form they are given a file in the filing cabinet and a membership number. Into the file also goes their rentals card which is an A5 size card which has the customers details across the top and a table printed on it, when a customer wishes to rent a video, the employee takes the customers card out of their folder and puts the date in the box on the empty row on the right hand side of the table and to the right of the box containing the date is a long box where the title of the film is entered, below is a copy of the table:

As you can see, the table and method is rather basic. The system of hand writing in the date, film title, and the date the film is returned, along with the form having to be taken out of the filing cabinet manually is that the employee could always get the wrong card, or enter the wrong information into the table. If the system was computer based then input masks could be used to make certain that correct information is inputted to prevent error. Various information would be required if the computer based system was to be put into practice, the information which are needed for the system would mainly already be on record, that information is as follows:

For the customers.

First Name
Surname
Other names
Date of birth
Home telephone number
Mobile telephone number
First line of address
Town of residence

For the films.

Film title
Date of release
Genre (film type)
Director
Film Company (Warner bros., Touchstone, Paramount Etc.)
Film code (V004 Etc.)
Number of copies
Staring Actor / Actress

To actually make my designed solution relatively successful I am going to have to make three tables, one for the videos in stock in the video rentals shop, another for all the members on record at the video rentals shop and finally I will be required to have a table showing which customers have rented which particular videos, a rentals table. With minimal information actually being required about the actual customer he or herself, the tables will actually be quite brief and relatively simple. The tables will contain the fields mentioned above.

Design of Solution: Having completed my research, I have decided that the information, which is to be entered into the computer, could be done straight from the current files in the filing cabinet. Therefore, a Code must be given to each video and also to each customer, to ensure that the correct number of figures is imputed, I am going to use input masks. For the customer codes I am going to use C***, The C obviously standing for customer, and by using three figures after the letter, it would enable the shop to have a maximum of 999 customers. With the videos table, I am going to go along the similar lines as the customer codes except I am going to add an extra digit to the code, to enable the video shop to have 9999 videos as their maximum number of videos in stock, because video shops obviously have a larger number of videos than members.

I am going to construct my tables using the fields mentioned previously, input masks included so that information cannot be inputted incorrectly. Once all my tables are completed I am going to connect them using macros to connect my forms and tables together. A diagram into how the forms and tables are to be linked is displayed below:

Data collection, capture and input: Because the customer wishing to open a membership with the video shop does not wish to be stood arranging their membership details with the employee behind the counter, their view would be that the less information required for them to give the better, as this is time consuming, we will try to keep the required information required to a minimal for many reasons, one being to save room on the database another simply to keep the data-capture form relatively simple as possible and the final reason is not to ask the customers for unnecessary information to save their and our time. In more detail, the fields, which are going to be used, are as follows:

For customers:

Customer’s Surname- For identification and to be used when putting customers in to Alphabetical order.
Customer’s First name- Merely for identification and for use if there is more than one customer whit a particular name of the same spelling.
Other names- Any other names the customer possesses.
First line of address- The first line of the customers address, (e.g. 25 Hazel drive)
Post code- The postcode of the customers address
Home telephone number- The customers home telephone number.
Mobile telephone number (If owned) – The customers mobile telephone number if he or she possesses one.

For videos:

Film title- The title of the particular film.
Date released- The date the particular film was released on video.
Genre (film type)- The type of film, Action, Comedy, Romance etc.
Director- The Director of the particular film, Steven Spielberg, John woo etc.
Film Company- The particular company which produce the film, Warner brothers, Touchstone, Paramount Etc.
Number of copies- The number of copies that shop has in stock at the current time.
Film code- The code which has been given to the particular film.
Staring actor/actress- The actor or actress who stars in the film.

Data verification and validation:

For Customers:

For Videos:

Although there are input masks in place, it doesn’t make the process faultless, after a new members details have been entered, the new member should read through it to make sure there are no errors. Or if it is a film, the employee who entered the details should ask a fellow employee to check over it, ensuring no errors have occurred.

Data and program structures: I tried to lay out all the forms as simple and straight forward as possible to try and be user friendly. Below is a picture of my opening form, which contains buttons, which can take you to all parts of my database.

From the other pages you can go to various pages in my database by using the FORWARD and BACK buttons situated at the top of the screen.

Output Format: My Database possesses several ways of outputting. The first output feature is the letter, which is sent to members who have currently got overdue videos and have forced the rentals shop to enforce a fine because the film is overdue, the a letter which is printed out and sent to the members look like this below:

The second feature which can be outputted are my tables, they can be printed out if the manager or any particular person required them for any reason.

Any information, which is in tabular form, can be printed out as a graph therefore allowing employees and managers to see weather business has risen of fallen, so he/she can make changes accordingly.

Testing: For the testing of my solution, I started out by inputting false information into it, information I had made up, to allow me to try and perform searches and stock takes etc. one my three tables were full of at least 25 pieces of information (25 videos, 25 members etc.) I was able to try my forms, which were very successful.

Initially I came across a problem, with the 5 digit area codes, as my input mask was only designed to allow a 4 digit area code, I was told that the problem could easily be solved, all that was required was a validation rule. The validation rule was a form of explanation, which explained about the four and five digit area codes, once I had inputted a validation rule; I had no problem inputting four or five digit area codes.

Above is a screen shot of the tables whilst I was inputting my information to allow me to carry out the various tests.

On the Previous page is a screenshot of a completed table for my videos, to which I have added a search engine, which can allow me to search for a particular video at any particular time.

Following this I tested my form of search engine and it was successful, it allowed you to search by film title or customer name or by code.

For the loaning a video, the procedure was relatively straight forward, in that you type in the date of the rental and the customer and video codes and the computer does the rest for you.

For security reasons, I felt it would be beneficial to the rentals shop to have a password feature to prevent any unauthorised personnel using the computer, because they could cause a problem to the whole system. Therefore I added a password to the database to enable each employee to have his or her own username and password.

Setting up a password was also successful, as I managed to make two different passwords and username, which were both valid.

Documentation of Solution: The best way to explain to a new user how the program works would be to send a document of instructions along with the program to explain to the new user how the software and it’s features work. Ideally I would like to create a basic wizard which can explain to the new user how all the buttons and features work and take the new user through the particular tasks step-by-step showing them also how various techniques can be achieved. By adding a query to the input masks will no doubt help any person who did not understand what information they were being asked to obtain.

Evaluation: When we first were assigned this particular project, after doing my research and investigation I felt that to make my database a good on I should include the following points:

I was to make a user-friendly which did not require any specific specialist training.
A relatively medium sized database, which does not take up too much space on the computers hard drive.
An expandable database so in the future the database can be expanded, new members and videos added and old/un needed ones deleted.
A form of search engine so members and videos can be found by using the search.
To contain a mail merge feature so members can be informed about news like new and up and coming rentals, new shops opening etc. and to inform members who have overdue videos that fines have been enforced and to return the video.

After completing my database, I now can say that it is relatively user friendly, I feel it would be a good idea to include a user manual included to help new users overcome various problems when they are new to the system. By including input masks it enabled an element of security to the input of new customer information into the database or the input of a new video title and it’s information, without minimal errors, the only errors that could infact occur are if a employee copies down a telephone number or postcode incorrectly, and therefore I realised it would be a good idea for the new member to read through the imputed information to ensure there are no errors other than the way just suggested, there is no other real way to get around the problem.

I checked the mail merge system, the system which sends letters to members and that appears to be running all okay and the only comment I could make to that particular area is that I would like to make my letter slightly more attractive, by adding company logos and possibly a more attractive colour writing. In the future I would like to do something, which I as yet have not been able to do, and that is to have my system tested by STRIKE video and gain their opinions on it, I realize that they would not use my system as their actual new filing system, but I would appreciate it if an employee could just try my system and I could gain his or her opinions and criticisms.

As a final conclusion to the whole project I feel that the new method, which I have developed, is a lot faster and user friendly than the old manual method for many reasons. Those being:

Search engine.
No lost memberships or videos.
No damaged forms or records
Input masks enable there to be fewer errors when inputting information.
Fast stocktakings and alphabetic ordering.
System allows expansion for the future.

In my final opinion I feel I have accomplished the final task relatively well, although if my system was to be put into practice in an actual video rentals shop, I feel it would require some modifications.