Moviezone video rental system.

The current system causes the following problems:

• Members sometimes forget or lose their membership cards;
• The video details cards often go missing from the cases and new ones have to be written out;
• Video details cards can’t hold much information and need to be replaced regularly.
• Video details cards are often put in the wrong place in the loans box and it takes time to find them when a member returns a video;
• If a particular video isn’t in the right place on the shelves its difficult to tell a customer if its on loan without searching through the loans box;
• Staff spend a lot of time answering questions about which types of films are available to rent.
• It takes a long time to look through the loans box to find out which videos are overdue.
• It takes a long time to write out reminder letters by hand which are sent to customers who have videos that are overdue for return.

Possible Solutions

MovieZone Video Rental could employ someone to organise his record collection, choose records for each disco and write their details down for him. If this were someone’s job they would have more time to choose records and make sure that the lists didn’t get lost. This won’t really solve the problem because it doesn’t really change the existing system, which is basically just very inefficient anyway.  Another disadvantage of this is that MovieZone Video Rental can’t afford to pay someone else a wage.

Another way of solving the problem might be to use a computer to store information about all videos, members and loans. Computers are ideal for storing large amounts of data and searching through it quickly.  There are two ways that a computer could be used to do this. The first is to have a program specially written to do everything MovieZone Video Rental wants. This might be the best way because the program would be tailor made to solve all of MovieZone Video Rental’s problems. The disadvantage of this method is that it can be very expensive and MovieZone Video Rental’s business isn’t really big enough to afford it. The next best alternative is to use a software package like a spreadsheet or a database to set up a new system. The software package could be set up to store information about the record collection, search for suitable records for different discos and print out lists.

Chosen Solution

I think that the best solution will be to set up a new system on a computer using a software package. I need to decide whether to use a spreadsheet or database package.

Spreadsheets are good for solving problems that involve a lot of numbers and calculations. This problem is about storing and searching through information and doesn’t need any calculations so a spreadsheet won’t be the best type of program to use.

I think that using a database package will be better because databases are designed to store large amounts of information and let users search through it quickly and easily. I have asked Hollywood Video Rental if he thinks this is a good idea and he agrees with me. He has also suggested that it might be a good idea if the new system could be used by his customers to choose records for their own discos. To find out more about this I am going to design a questionnaire and interview some of his customers to see what they think. I will also interview Hollywood Video Rental to make sure that I find out exactly what he wants.

System Objectives

The system must be able to perform the following tasks:

• allow member details to be input, looked-up, edited and stored quickly, easily and accurately;

• allow video details to be input, looked-up, edited and stored quickly, easily and accurately;
• allow loan details to be input, looked-up, edited and stored quickly, easily and accurately;
• allow video details to be searched to answer customer enquires quickly and easily;
• produce a list showing the videos due back each day;
• produce a summary report showing the member and video details for videos still overdue at the end of each week;
• produce reminder letters to members with overdue videos at the end of each week.

---

Performance Criteria

The following criteria will be used to evaluate the success of the new system:

• the system must be able to store at least 10 member records;
• the system must be able to store at least 20 video records;
• the system must be able to store at least 10 loan records;
• it must take no longer than 30 seconds to find and display a member, video or loan record;
• it must take no longer than two minutes to answer customers’ enquiries by entering search criteria and finding matching video records;
• it must take no more one minute to find and delete a member, video or loan record;
• it must take no more two minutes to find and edit a member, video or loan record;
• it must take no more two minutes to create a new member, video or loan record;
• on screen data entry forms must be clearly laid out to make data entry;
• on screen data entry forms must use automatic data validation to reduce data errors on input;
• it must take no longer than 2 minutes to search for videos due back on a certain date and produce a list that can be printed out;
• it must take no longer than 5 minutes to search for all the members with overdue videos at the end of the week and produce a summary report on screen;
• reminder letters to customers with overdue videos should be clearly laid out and include all the details of videos that need to be returned;

---

Current Resources

Mr and Mrs Marshall already have a computer with the following specification:

• Windows 95 operating system        
• A Pentium II 200 MHz processor
• 16 Mb of RAM                
• A 2 Gb hard disk with 821 Mb of free space
• A floppy disk drive                
• A CD-ROM drive                
• MS Office 97 Standard (this includes Word 97 and Excel 97)

Mr and Mrs Marshall can spend up to £1000 on new hardware and software if necessary.

Choice of Software

The database packages that I could use to set up the new system are PinPoint and Access 2000. These packages are available on the computers at school and my PC at home. Mr and Mrs Marshall don’t have these packages on their computer but they are willing to spend up to £1000 on new hardware and software.

PinPoint offers the following features:

• Data can be entered easily using on-screen questionnaires;
• Questionnaires can be designed and formatted quickly and easily;
• Databases are built up automatically behind the scenes as data is input;
• Queries to search the database can be built up interactively and saved;
• A wide variety of graphs and charts can be generated quickly and easily.
• Data can be saved in a variety of file formats and used in other applications;

---

Access 2000 offers the following features:

• Common fields can be used to link data tables and set up relational databases;
• Data entry forms can be created and customised quickly and easily;
• Powerful queries to search the database can be generated using wizards and saved;
• Reports can be can be generated quickly using wizards and formatted very easily;
• Tasks can be automated using Macros;
• Access 2000 databases can be linked to Word 2000 to generate standard letters;
• Customised menu screens can be set up;

I think that Access 2000 will be the best package to use to set up the new system because it will allow me to create a relational database. PinPoint does not offer this feature. Setting up a relational database will avoid the unnecessary duplication of data and make it much easier to update information. The staff of MovieZone don’t have much experience with computers so the new system must be user friendly. Access 2000 has more powerful features than PinPoint for customising a system such as macros and menu screens, which can be used to make it more user friendly. Mr and Mrs Marshall don’t have Access 2000 on their computer but they have £1000 to spend on hardware and software so they can afford to buy it. Another important advantage is that I know how to use Access 2000 so it shouldn’t take me too long to set up the new system.

Resource Requirements

Access 2000 will be used to set-up and run the new system for MovieZone. To install and use this software a computer with the following minimum specification is needed:

• A Pentium 75 MHz processor or higher
• Windows 95 or later operating system
• A minimum of 24Mb RAM
• At least 161 Mb of free hard disk space
• A CD-ROM drive

Mr and Mrs Marshall have a computer following specification:

• A Pentium II 200 MHz processor
• Windows 95 operating system
• MS Office 97 Standard (this includes Word 97 and Excel 97 but not Access)
• 16 Mb of RAM
• A 2 Gb hard disk drive with 821 Mb of free space
• A CD-ROM drive
• A floppy disk drive

Mr and Mrs Marshall have told me that they can spend up to £1000 on new hardware and software. I am going to recommend that they buy the Office 2000 Professional package, which includes Access 2000. This will cost £450. To install and run this software they will need to upgrade the memory in their computer from 16Mb to 24Mb. This will cost £40. Mr and Mrs Marshall will also need to buy a printer so that they can print out things like lists, reports and letters to members with overdue videos. I think that a small laser printer will be the best choice because it will produce high quality printouts very quickly. This will cost approximately £350.

The computers at school and my PC at home all meet the minimum specification and already have Access 2000 installed. I will also need a printer. Every computer room at school has a laser printer so this isn’t a problem. I also have a small inkjet printer at home that I can use. This means that I have all the resources I need to set up and test the new system. 

System Flowchart

---

Table Design

Table: Members

The primary key for this table is MemberNo

Table: Videos 

The primary key for this table is VideoNo

---

Table: Loans

The primary key for this table is VideoNo

 

---

Entity Relationship Diagrams

The member table will be linked to the loan table using the member number field. This is a one-to-many relationship because each member can rent many videos. This is shown in the entity-relationship diagram below.

The video table will be linked to the loan table using the video number field. This is a one-to-one relationship because a video can only be rented to one member at a time. This is shown in the entity-relationship diagram below.

These relationships could be represented together in a single diagram as shown below

---

Menu Form Designs

---

---

Data Entry Form Designs

---

---

---

Query Designs

Query: Videos

This query will use the videos table to search for records matching the following conditions:

• Every video with a particular Certificate e.g. “18”
• Every video in a particular Category e.g. “Hor”
• Every video with a particular Certificate and Category e.g. “18” and “Hor”

These fields need to be displayed for the records that are found:

• VideoNo
• Title
• Category
• Certificate

These records need to be sorted into ascending order of VideoNo

Query: Overdue

This query will use the members, videos and loans tables to search for records matching the following condition:

• Every video on loan with a DateDue less than the current date.

These fields need to be displayed for the records that are found:

• MemberNo
• First Name
• Surname
• Address Line 1
• Address Line 2
• Town
• County
• Postcode
• VideoNo
• Title
• DateDue

These records need to be sorted into ascending order of MemberNo

---

Report Designs

---

Test Plan

---

Implementation Plan

To set up the MovieZone system I must complete the following tasks:

1. Create a new blank database;
2. Create a table to store information about members;
3. Create a table to store information about videos;
4. Create a table to store information about loans;
5. Set up relationships to link the members, videos and loans tables together;
6. Create a data entry form for the members table;
7. Create a data entry form for the videos table;
8. Create a data entry form for the loans table;
9. Create a query to search the videos table for all videos with a certain category;
10. Create a query to search the videos table for all videos with a certain certificate;
11. Create a query to search the videos table for all videos with a certain category and certificate;
12. Create a query to search the videos table for all the videos with a certain title;
13. Create a query to search the loans table for videos that are overdue;
14. Create a report to display information about overdue videos;
15. Create a menu form to display the main menu options;
16. Create a menu form to display the find video options;
17. Prepare a letter that can be used in a mail merge to generate reminders for members with overdue videos;
18. Create a macro to generate reminder letters for members with overdue videos;

Implementation commentary

---

Test Results

---

Modifications

---

Evaluation

---

User Guide