Background and investigation for designing a database for a DVD rental shop

JenJams is a family run convenience store which also supplies DVDs for rental, located off Wilstead high street. The shop is owned and run by Ashley Bradshaw who is accompanied by his wife Mrs Bradshaw and his son who are part of the shops staff. He set up the business 7 years ago and has stuck with the old system. Mr Bradshaw's wife and son work in the shop on a part time basis. It is a reasonably sized shop that provides entertainment to roughly 300 members who live in and around Wilstead.

People who want to become members have to be 18 and over and during the membership process identification has to be shown. Ashley is my client but the users will consist of him and the other members of staff.

Description of the Current System

The shop is signed up to Choices UK which supply the DVDs'. The shop receives 4 new DVDs on a weekly basis. When the films arrive they are assigned a video number, catalogued and put out on display.

Get quality help now

Bella Hamilton

Verified writer

Proficient in: Data

5 (234)

“ Very organized ,I enjoyed and Loved every bit of our professional interaction ”

+84 relevant experts are online

Hire writer

The shop also sends back 25 films of their choice, usually on the fact that they aren't popular anymore; they then receive 25 DVDs in return. The shop is entitled to request a certain film from the 25.

Ashley currently stores all membership forms in a pink folder. The amount of membership forms is unknown and Mr Bradshaw isn't shore whether all the people within these forms are still members. This means that JenJams may be storing expired data. When a customer decides to become a member the initial procedure involves filling out a membership form, they are then issued with a membership card; when a member wants to hire a DVD they choose the film they want, by choosing the assigned DVD case from the shop floor, all DVD cases on the shop floor are kept empty.

The case is then handed in to the reception along with the membership card, which is placed in the empty DVD case and stored under the counter. The rental is then recorded onto a rental sheet along with the members' details, the DVDs assigned number, how long the rental is for and the overall cost. The DVD is then given to the member in an empty case.

During a visit to Mr Bradshaw's shop he could locate at least 3 DVD cases kept under the counter which had membership cards missing, he explained this happens on a regular basis and they then have to chase up which member rented the DVD from the rental form, which takes up a lot of time. On returning a DVD, the member hands the rented film in and the member of staff that accepts it will place the DVD back into its original case. The member of staff then has to back track through the rental forms to find the rentals details, which is then signed off with the date returned. The membership card that is in the case is then returned to the member. Read justification report example

The old paper filing system is very inefficient in notifying when members are overdue with bringing the DVDs back, staff have to search through the records which is extremely time consuming, when a person is found to still be in possession of a DVD and past the due date, a member of staff then has to locate the members membership form. When an overdue DVD is returned they are charged �2.99 for each days the film is overdue. All in all the overdue process is overly inefficient and time consuming.

Mr Bradshaw explained he had used a computer system to manage his rentals in the past but it was just not fast enough to carry out simple tasks, it was also full of errors and wasn't designed for Mr. Bradshaw's skill level. Ashley Bradshaw possesses limited IT skills but is keen to introduce a computer-based system again for the rentals of his DVDs if it meets the requirements.

Identification of client and potential users

Client

The client is Mr Bradshaw who is the owner and the manager of JenJams. A paper based system has been implemented for the majority of the time the shop has been running, a computer based solution was trialed for a few months but Mr. Bradshaw didn't take a shine to it. Mr Bradshaw is the client because he is the individual who asked me to provide an ICT based solution and he will be providing me with all the information I require to design and make the proposed system.

Users

Ashley Bradshaw devotes most of his time to the running of the shop and this deems him the main user. Ashley currently selects all the DVDs that need to be returned, this means he will be using the systems quires to select DVDs to return back to choice UK. Anything that that needs to be removed or deleted has to be authorised by MR Bradshaw, in the proposed system only Mr. Bradshaw will be able to delete members, DVDs and rentals.

Amy Bradshaw has the same level of ICT skills as her husband. Mrs Bradshaw also spends long hours in the shop and her abilities will affect the overall design of the proposed system. Her main role consists of dealing with customers and making rentals. She will be adding and editing members, DVDs and rental in the proposed system.

The final user of the system will be Jason Bradshaw, Mr Bradshaw's son. He currently works on a part time basis, as he will not be using the proposed system as much as his parents, the new system will be more tailored for Mr and Mrs Bradshaw's level of abilities. Jason obtains larger knowledge on using an ICT based system as he is currently a student and has to deal with computers on a daily basis. Jason will help with adding and editing members, DVDs and Rentals. He has insisted that he will be able to assist his parents with using the proposed system to the best of his abilities.

Justification for required solution

I have generated a few points which highlight reasons for implementing a new ICT based solution, replacing the current paper based system.

Keeping track of when videos are due back

Because all the data is kept in files you must continuously check if people are not bringing DVDs back. Obviously this can be very time consuming and errors are likely to be made, resulting in lost DVDs, lost assets and revenue. It is also not immediately obvious when a date is overdue because there is so much writing on the forms and so you cannot always single out an overdue DVD.

Stock levels

As JenJams receives new DVDs on a regular basis it is difficult to know the number of DVDs within the shop without counting each one by one.

The amount of time it takes to document a new member

With the manual system it can be very time consuming, just entering data for the new member. The form has no tick boxes and the staffs have to write it all in full by hand.

Bad Handwriting

The current system stores all details on paper forms that are arranged in separate files. Due to the different writing styles of the staff and the haste with which employees sometimes write the data on to paper, the data tends to become hard to read. This can lead to incorrect readings of the data, which can lead to incorrect decisions being made.

There are no links

All the different files are all kept separate from each other and so there are no links between: The video the person who rented the video and the staff member who rented the video out. If these files were linked relevant data and information could be retrieved. When Mr. Bradshaw wants to select 25 films to return to choices uk, he uses his own knowledge and judgement on whether a movie is not being rented or is unpopular. There are no measures to monitor how many times a movie has been rented unless Mr. Bradshaw decides to go through the rental forms which is unpractical.

Concluding Remarks

Mr Bradshaw the end user is used to his position and is suffering from a paper based management system that is inefficient. Ashley Bradshaw realises his problems and is very willing to become more efficient in his workplace.

The computer on which Mr bradshaw will operate within Video World is a Windows xp system and the workstation has a specification of a 1200 MHz processor with 2028 MB RAM and 64 MB graphics cards. He has a Microsoft Office 2003 professional suite that includes Microsoft Word, Access, Excel, Publisher and PowerPoint. He is very keen to have a solution implemented, which makes use of the applications within Microsoft Office. His initial ideas were centred on the use of Excel and Word, with which he is very familiar, although he is willing to learn how to use Microsoft Access.

Investigation Techniques

Primary Interview with client

These are the set of questions that i will ask during my interview that I will be holding with Ashley, the manager and owner of JenJams. If I feel that something interesting arises from a response to one of my questions, I will pursue that line of enquiry but that is the only circumstance under which I will digress from my main list of questioning. I will take a notepad with me during the interview to record his answers to my questions.

Interview Questions and answers

1. How many personnel are currently in your employ?

We have three members of staff. Two work on a full time basis and one works part-time.

2. What details do you hold about your employees?

As all employees are family members not much information is kept but for emergency reasons there name, contact and address is held.

3. Do your employees have any qualifications in ICT?

Me and My wife do not have any qualifications in ICT but my son has a GCSE in ICT.

4. Do your employees have any past experience working with computerised systems?

I briefly tried to run a computer based system but the idea was dropped quickly. Me and my wife hadn't gained much experience. However my son uses ICT based systems at school and has average knowledge on using ICT systems.

5. Do you expect staff to welcome a computerised system or are you expecting some resistance to such a system?

Me and my son are open to the idea but my wife will struggle with the introduction of a computerised system.

6. Do you store information about your customers? If so, how and what is it stored in?

Yes we do. When a member form is filled we obtain information on the customer's name, age, contact details and address.

7. Do the customers have unique customer identification numbers? If they do not, would you like the new system to do this?

Customer are given the next available 'Member Number' when they fill out a membership form

8. On average, how many customers do you have a week?

We roughly have 100 customers on a weekly basis

9. What time of year is the busiest for your business?

Winter is the busiest time of year for us as people spend more time indoors. This means they rent more DVDs.

10. How many DVDs does the business have?

We roughly have 250 DVDs in stock. This number fluctuates allot as new DVDs are added and removed.

11. Where do you store your DVDs?

All DVDs are stored under the counter for quick access and they are kept in number order. Their original cases are kept on the shop floor for display.

12. What details do you hold on your DVDs?

When new DVDs are introduced they are given a number and there Title, genre, date released and certificate information is held.

13. How do you introduce new DVDs?

I have to decide using my own knowledge which DVDs need to be sent back to choice UK. This usually involves me picking out what i think are the least popular DVDs. the new DVDs are then given new numbers and placed in order.

14. What details are recorded when a rental is made, and where is this data stored?

When i new rental is made we record the date of rental, price of rental, due date, customer number, members name and contact details along with information on the DVD being rented.

15. Do the videos have unique identification numbers? If they do not, would you like the new system to do this?

Yes we do currently have 'DVD Numbers' for each DVD.

16. What firms supply the business with the DVDs?

We are signed up to 'Choices UK' who provide all our DVDs. They also take our least popular DVDs.

17. What is the average rental price for a DVD?

As we tend to keep the latest and most popular DVDs we have a rental fee that is the same for all the DVDs. this fee is �2.99 per night.

18. What is the lowest rental price, and the highest rental price?

All rental fees are the same

19. Do you have an overdue fee system in place for late rentals?

An overdue fee of �2.99 is charged for each day the customer is overdue.

20. What type of information would you like to be extracted from the new system?

Currently task such as selected what dvds to send back isn't a reliable process as i use my own knowledge to choose dvds. I would like the system to automatically pick out the 25 least popular dvds in stock. I would also like to be able to print and display information on members, dvds and rentals.

21. How long would you like the system to hold records before they are deleted?

I would like the system to notify me of customer that have been inactive for 6 months and then give me the choice of deleting them.

22. Under what circumstances is a letter sent out to a customer?

Currently we do not send out letters as finding customers contact details involves searching for their membership form which is an inefficient task. However I would like the new system to give us the ability to produce letters to be sent to members.

23. What would you say the main problems are with the current system?

The current system is very time consuming and many tasks are avoided because searching through large amounts of documents in unpractical. We sometimes have problems with handwriting and incorrect data that has been stored on forms.

24. Will I be able to observe the current system in operation from within the offices for a few hours?

Yes you are more than welcome to observe the current system.

25. Will I be allowed access to all of the documents and records your business use to store and transmit information?

Yes you will be allowed to view any documents you will need to design the proposed system.

26. Do you have a back-up strategy for your data?

No we do not currently have a back up strategy.

27. Are there any other factors that are taken into consideration when calculating the total price of the DVD rentals?

I would like the proposed system to automatically calculate rental fees and overdue fee, then give me an overall charge for the member to pay.

Direct Observation Aims

Providing that question 24 in the interview with Ashley yields a positive result and I am permitted to do so, I will observe the office environment of JenJams for a few hours one day in order to gain some understanding into the way in which procedures operate. During my observation in the office, I will take care not to behave in any way that might distract staff members to ensure that the working environment being observed is as close to a naturalistic situation as possible. Things that I hope to observe include:

* The work load on the employees

* If there are any unnecessary delays between the performance of various processes

* The methods of working

* The nature of the tasks being performed by staff

* The frequency at which these tasks are performed

* The interactions between the different staff members

* The flow of information in the system and the efficiency of this flow

General Findings from interview and direct observation

JenJams currently has three members of staff, consisting of the manager, and floor staff. The employees have little experience in ICT. One of the floor staff members has a GCSE in the subject and Mr Bradshaw has little experience working with a computerised system that had been trialed in the store. The winter months are the busiest times of year, as this is when the time of year people spend larger amounts of time indoors, which leads people wanting to DVDs out on a regular basis.

One important discovery is that all of the storage and manipulation of data within the office is conducted by hand. For example, when a new customer enters the shop, they are asked to fill out a form by the manager. Once they have completed this form, it is filed away in a folder which includes other membership forms. All such data regarding the business is stored on paper, in folders, including staff details, DVD details and Rental details. I get the impression that all of the paperwork that needs to be completed each time data needs to be recorded or manipulated can be a time-consuming process.

Keeping all of the files updated and accurate can be a hassle, as a member of staff has to search through a folder full of records before locating the required one. This wastes unnecessary time. Also, when making amendments to a record, the employees have to manually erase the previous information on the record using either tip-ex or a rubber, before they can transcribe updated information to the document. Upon viewing these corrected records, I noticed that they look very messy and in some cases illegible due to these corrections. In some cases, Ashley noted that staff members just fill out a new record rather than correct the original to avoid such messy-looking records, which is obviously very time-consuming.

Another issue that was raised by the manager during the interview was that of human error occurring within the system. Due to the heavy amounts of paperwork involved, the employees sometimes make typographical errors during the recording or editing of data, which can be something as small as a mis-spelt name but can on occasion result in something a little more serious such as an incorrect telephone number or the date at which a DVD is due to be returned.

A similar issue is the way in which the differing writing styles of the staff can lead to confusion and erroneous readings of records. During my inspection of the files and records, I noticed that the handwriting appeared to be barely readable in certain instances. And it wasn't just I who thought so. The manager could recall an incident when the business were trying desperately to get into contact with one of their customers regarding an overdue DVD, but their efforts to contact him via telephone all yielded no answer on the customer's end of the line for days. Due to the messiness and illegibility of the handwriting on the customer's record, the employee that was trying to contact the customer read one of the digits wrongly on the telephone number, and thus it turned out was dialling the wrong telephone number the whole time!

Information that Ashley would like to be able to extract from the new system include an easy way to monitor and produce reports on the rental status of each DVD, as this will save time when a customer wants to make a rental. He would also like to be able to produce a report of all the DVDs of a certain category, so he can immediately bring up a list of all of the available DVDs available in the category the customer wants search for a DVD. He comments that he often gets customers that would like a particular genre of DVD, so it would be very helpful to be able to bring up a list of all the available DVDs of that type to help them make their decision.

In the current system rental fees and overdue fees are calculated manually, Mr. Bradshaw informed me of cases where staff members wrongly calculated overall fees resulting in charging less than what was owed, so a formula that would calculate this value automatically in the new system would be a good addition to reduce the likelihood of human error.

Document analysis

I have decided to examine the existing documents as these will help provide me knowledge of what fields are required within the forms in the proposed system. I also hope to find out what information is required to be help in the new system.

Document 1 - Rental Form

The first document is a rental form. This form allows rentals to be recorded, holding information on when the rental was made, the film no, title of film, membership number, due date, when the DVD was received and the amount due. It is clear that each field is extremely small which means writing needs to be tiny, which can be difficult to read. With The current style of recording information, Mr. Bradshaw has been forced to limit the number of fields he can have on this form. This in turn results in him not being able store customers' names and contact details and he has to rely entirely on the membership number which in some cases has been entered incorrectly, causing huge problems for Ashley. When the form has been filled out it is a stressful task to pick out all the overdue rentals as the number of days a customer rents a DVD is not consistent, so a system cannot be put in to place to find the overdue DVDs apart from manually searching the forms. From this document I know what fields need to be included in the computerised form and what extra fields i need to include.

Document 2 - Membership form

This is the membership form that needs to be filled out by customers upon signing up to JenJams. This form has an attractive colour scheme and this will be used as the theme for the proposed system. Each form will consist of these colours. Each field is clearly labelled and arranged systematically, this is a quality that the computerized form will need to convey. From this form i will know what fields i will need for the proposed form.

Document 3 - Terms and Conditions (On reverse of Membership Form)

This is the companies terms and conditions that are written on the reverse on the membership form. This will be kept in the current system and Mr. Bradshaw will need to be able to print this to give to customers.

End-User Requirements

During the interview with Ashley, and my hours of observing the working environment of JenJams, a number of user requirements have been identified, which I shall now list below. The system must:

UR1: enable DVD details to be added/edited/deleted/ displayed onscreen/printed;

UR2: enable staff details to be added/edited/deleted/ displayed onscreen/printed;

UR3: enable returns details to be added/edited/deleted/displayed onscreen/printed;

UR4: enable rental details to be added/edited/deleted/ displayed onscreen/printed;

UR5: enable customer details to be added/edited/deleted/ displayed onscreen/printed;

UR6: enable the return date of the rental to be calculated automatically;

UR7: enable old customer information to be moved to an archive file;

UR8: enable old rental details to be moved to an archive file;

UR9: enable old staff details to be moved to an archive file;

UR10: enable the total price of Video rental to be calculated automatically;

UR12: enable the age of a customer to be calculated automatically

UR14: enable overdue fees to be calculated automatically, exponential to the number of days overdue;

UR15: display/print a list of DVDs on rental;

UR16: display/print a list of all DVDs to be returned that day;

UR17: display/print a list of all overdue DVDs;

UR18: display/print a list of customers' contact details;

UR19: enable user to search for a particular customer's details;

UR20: enable user to search for DVD details;

UR21: enable user to search for a particular rental details, such as the number of dvds rented in a month;

UR22: enable the above user requirements UR15-18 to be displayed onscreen or printed;

UR23: enable user to create a letter;

UR24: allow customer details to be added into a letter;

UR25: be user-friendly;

UR26: be menu-driven;

UR27: Decrease the amount of paperwork needed;

UR28: reduce data duplication;

UR29: increase the level of security;

UR30: comply with the Data Protection Act 1998;

UR31: have a user guide for the instruction of users on how to use the system;

UR32: hold data of a high level of accuracy by using data validation methods;

UR33: be speedier than the previous system;

UR34: use a password system for security;

UR35: be easy to understand for the user.

UR36: be able to back-up data

UR37: have a disaster recovery plan management strategy