An I.T. System for a Video Rental Store Essay Example for Free

An I.T. System for a Video Rental Store Essay Contents * Choice of Problem * Description of Problem * Investigation and Analysis of Problem * Specification of Solution * Design of Solution * Implementation of Solution: * Resources Required * Data Collection, Capture and Input * Data Verification and Validation * Output Format * Testing of Solution * Documentation of Solution * Evaluation of Solution Choice of Problem I live in large city called London. My Grandfather runs a video shop there under the name Video Rentals LTD. Has a large and varied amount of stock that he finds hard to keep track of. At the moment he employs a card index system but this has its limitations: * Cards often get misfiled meaning that extra time has to be spent refilling and amending lost data. * On average it takes 30 seconds to search for a record, at busy times this is too long. * The shop currently has about 150 customers, which means that to find a record it may take a long time, this will delay the time that it takes to sort through the records and therefore customers may not want to wait that long and may go to another shop. Description of Problem At my grandfathers video shop the current system involves filing all the information in a filing cabinet. This is a manual system as all the data is hand written. I believe that because of this the video shop needs a computerised system, which will make the database better. There will be graphical menu System that will link all the forms and queries together. My database project will allow the shopkeeper to edit, create and delete record details of members, videos and loans. Investigation To find out more about what the database required I phoned my Grandfather. I wanted to know: * The precise objectives of the new system * The problems or weaknesses in the current manual system * The information that needs to be kept on each customer * The required output * The volume of data (e.g. number of existing customers, number of new customers each year/month) * Any hardware or software constraints. My evaluation will be based firstly on whether it works properly, secondly that it is faster in finding a particular video or client than the existing manual system and thirdly that my Grandfather finds it easy to use. Specification of Solution Objectives of the new system * It should take less than thirty seconds to establish whether a customer is already on file. * It should be possible to go directly from the Customer Details form to the entry of a video that that customer has hired, without having to re-enter the customers name, etc. * Data entry should be as fast and as easy as possible, particularly as there are several hundred existing customers and videos to be entered when the system is first installed. * It be able to show who has rented what video and when. * There must be provision for more than one video to be hired at once by the same person. * The new system is required to provide the following information: * A list of all business customers * A list of all customers interested in a certain type of video such as horror, comedy etc. * A list of customers whose numbers of rentals exceeds a given value. * A list of all rentals that are overdue and need chasing. * It should be possible to send a standard letter to selected customers, using criteria mentioned above. * The user must be able to create new letters whenever needed and perform a mail merge to selected customers. * The main menu should be displayed automatically when the database is loaded, and the whole system should be menu driven. Design of Solution A database package will be ideal for implementing the system for my Grandfathers video rental shop. He has requested that it is done in Access 97 so this is the package that must be used. It would probably be possible to implement the system using Visual Basic or another programming language but it would take a lot longer and would involve the owners having to buy more software which is not really necessary, as Access has all the capabilities required. I already have some experience of Access 97 and it is available in my house for development. Using this package it will be possible to: * Set up the necessary tables and relationships * Produce customised input screens, using Visual Basic to automate data entry wherever possible and to perform various validations * Use Visual Basic modules to enable fast searches for a particular customer and past jobs * Design reports as needed * Make and save queries to select certain customers for a mail merge * Implement a customised menu system On starting up the database, I will design it so that you are presented with a main menu that is linked to the client and video databases, which in turn are linked to each other. I will make it so that on each of the forms there are buttons that enable you to scroll between records, delete records and search for records. If you choose to search for a record then you will be prompted to enter the details of the record you want. The database will be menu orientated so I will make a separate options menu that is linked to everything else. The options menu will also contain a help menu that will cover everything that is covered in the user manual. My system will be based on two separate files, one is of the customers and the other is of the videos in stock. Each record in whichever file will only contain one video or customer, and contain fields like name, address, video name, and certificate. The implementation will begin with the design of the database, then data capture forms, entry screens and output screens. Before the database is put in to use I will test the system using a range of valid and invalid data to make sure the system works as it should. I will then create a user manual that will support the system and its functions. Implementation of Solution Resouces Required I have chosen to design and make my database in Access version 97 because this is the program that the final product will be used on. It allows me to create customized data input screens and has a wide range of validation checks available, so unless a good reason transpires for using an alternative program or package, Access will be the first choice of software. In order to run Access 97 and be able to perform a mail merge using Word, a minimum of a Pentium 133 system with 16Mb, wil be required. A faster processor may be needed if the system is going to appear slow in switching between screens and searching for records. When the system is up and running it will take up about 5Mb. The user has a Pentium 300 with 16Mb, which will be perfectly suitable, and similar machines are available for development work in my house at school. My Grandfather is familiar with Word and has good keyboard skills, so should have no problem in entering the data but may need help in doing some of the more complicated functions. In the future he would like to improve his knowledge of Access so that he can perform new queries and reports as the need arises. Data Collection, Capture and Input Three data input forms are needed: 1. Customer Details This form will be used for several purposes so it needs to be able to: * Add a new customer * Look through the current clients and see what videos they have hired. Validations will be performed automatically. The Member ID field will be an integer and letter field so that it is more personalised. Wherever possible default values will be inserted automatically (e.g. most customers are from Ludlow so the relevant fields will default to those values). Tab order will also be used to speed data entry, so each field will have a certain order in which the data is entered. The form will be straightforward and follow in a top-to-bottom order. 2. Video Details This needs basically to be the same kind of layout and make up as the customer details form except that the fields are those to do with the video. I shall use a validation rule for things such as the videos certificate which can only be one of five (18,15,12,PG,U) and have validation text that warns if the data has been incorrectly inputted. 3. Hire Details This form is designed to make the inputting of information much easier and quicker than searching for a customer and ammending the details every time. It just requires you to input the video number and the client number. Everything else is done automatically. If the wrong client or video number is given then a dialogue box comes up telling you so. You will also have the ability to go staright to a clear form so that a new customer can be added or ammended. Validations will be performed automatically. The client number field will be made up of two numbers and a capital letter, 11A for example. All text that is inputted will automatically be put into upper case lettering for ease. A sample of a data capture form is in the appendix. This is how the system will operate: The diagram shows how the menus will be linked. The hire details are entered in and stored to the various tables. Then comes th possibility of ammending a current customer or adding a new one. Data Verification and Validation Access allows you to say what type of data is included in any field, and also allows me to say the maximum length allowed for any particular field. It also gives the possibility of making input masks for the field. The First and Last Name fields will always be text and most names will be shorter than twenty letters. The House Name/ Number field allows for three digits to be put in first eg 112 Orpen Drive. In fields like Sex, Title and Form Of ID, I have coded it so that only the abreviations of the input are required, and even then the input is one of a choice. This speeds up entry time and reduces the possiblilty for error. The input masks and validation rules reduce data being incorrectly inputted, but they do not completey reduces errors. Data such as name and address, can not be validated, so it has to be verified by the user when the information is being entered. This verification involves manually checking each record to ensure accuracy. Output Format I wanted the output in my database to ocupy 3 sections, the visual display forms, the query which was a search output and the mail merge. I will now show that I have completed those task. First of all here is my Main form, which links all the other forms and functions together. Another output would be the members table I Also designed a query to find all the curent videos that are on loan. ID number of Member ID number of DVD ID number of Video Date Out Date In Surname Of Member Name of DVD Name Of Video Old Member Cost She1 dv004 1 25/7/01 27/7/01 Sheppard The Mummy The Fast one Yes à ¯Ã‚ ¿Ã‚ ½2.95 My mail merge is contained at the end of this project. User Guide To load the database click on the database file twice quickly (double click) To save the database click file, the save. Or just click on the icon that looks like this. If you require any further help try microsofts built in help progam. Then is one in Help/Microsoft assistant. And also one accesible by pressing the start button. Then on the help file. Testing of Solution I am going to test my database using a variety of correct and incorrect data. In doing this I hope to find any problems with the system, and in doing this finding the solution. The test results for the major tests are shown below. Errors which came to light during the test were corrected and all tests gave expected results. Test 1: test password When the correct password was entered the database opened. Otherwise the following message was displayed: Test 2: Entering client information Client details entered. All different options, defaults and fields were tested during data entry. The tests resulted in some minor changes of the input masks, such as the number of characters allowed in the address field was not big enough for my address. Test 3: Deleting a record A client was deleted and the result were as expected, access alowed me to delte the record, after promiting me in what I was about to do. I also needed to test whether or not my query worked, to do this I first loaded it up. I was asked to type in th date when the video was due back so I typed in 27/11/01 and the query found the following result. As the query found the right result I believe that my query works correctly. Evaluation of Solution By looking at my testing I can conlcude that my database is succesfull, as all the buttons, tables and queries work correctly. If I look at my my evaluation crtitera I can say whether or not I have completeed them. * It should take less than thirty seconds to establish whether a customer is already on file (Query member search) DONE * It should be possible to go directly from the Customer Details form to the entry of a video that that customer has hired, without having to re-enter the customers name, etc. (Relationships) DONE * Data entry should be as fast and as easy as possible, particularly as there are several hundred existing customers and videos to be entered when the system is first installed. (Data flow) DONE * It be able to show who has rented what video and when. (loan table) DONE * There must be provision for more than one video to be hired at once by the same person. IMCOMPLETE * The new system is required to provide the following information: * A list of all rentals that are overdue and need chasing. (Date due back query) DONE * It should be possible to send a standard letter to selected customers, using criteria mentioned above. (Mail merge) DONE * The user must be able to create new letters whenever needed and perform a mail merge to selected customers. (Mail merge wizard) DONE Seeing as I have completed nearly all of my evaluation points I believe that I can say that my database project is a complete success.