To investigate and try solving the information handling problems of MovieZone a small video rental shop in Stoke-on-Trent.
Sample Project
MOVIEZONE VIDEO RENTAL SYSTEM
Contents
Analysis
Project proposal
Consulting the users of the current system
Describing the current system
Data flow diagrams
Possible solutions
Chosen solution
System objectives
Performance criteria
Current resources
Design
Chosen software
Resource requirements
System flowchart
Table design
Entity relationship diagrams
Data entry form designs
Menu form designs
Query designs
Report designs
Test plan
Implementation
Implementation plan
Implementation commentary
Testing
User guide
Evaluation
Project Proposal
Project title
MovieZone Video Rental System
Aims of project
To investigate and try solving the information handling problems of MovieZone a small video rental shop in Stoke-on-Trent. The owners of the shop want me to design and set up a more efficient way of storing and handling information about videos, members and loans.
Input
Processing
Output
Member details (member number, name, address, date of birth).
Video details (video number, title, category, certificate).
Loan details (video number, member number, length of loan, date due back).
Look up member details.
Look up video details.
Look up loan details.
Search for videos matching member's requirements.
Search for overdue videos.
Member details.
Video details.
Loan details.
Reports listing videos matching member's requirements.
List of overdue videos.
Letters to members who have overdue videos.
MovieZone Customer Questionnaire
This questionnaire will help us find out what you think about the service currently offered by MovieZone and try to improve it. Your opinions are very important to us. As a thank you for completing this form we will give you one night's free rental of any new release.
. What is your favourite type of movie?
? Action
? Comedy
? Horror
? Science fiction
? Thriller
? Romance
? Other -- please state _____________________________
2. How many times a week do you rent a movie?
? Once
? Twice
? Three times
? More -- please state _____________________________
3. How easy is it to find out if we have a particular movie available for rental?
? Generally quite easy
? Generally quite difficult
? Don't know
4. Approximately how many minutes does it usually take you from choosing a movie to leaving the shop with it?
________ minutes
5. How can we improve our service?
______________________________________________________________
______________________________________________________________
______________________________________________________________
______________________________________________________________
______________________________________________________________
______________________________________________________________
6. Please write your name and membership number in the spaces provided below.
First name ( ( ( ( ( ( ( ( ( ( ( ( ( (
Surname ( ( ( ( ( ( ( ( ( ( ( ( ( (
Membership number ( ( ( (
That's the end of the questionnaire! Thank you for taking the time to complete it. Please hand it in to receive your free rental voucher (one per customer only).
Interview questions
Sample documents
Problem Description
This project will aim to solve the information handling problems of MovieZone Video a video rental service based in Kidsgrove, Stoke-on-Trent.
MovieZone rents videos out to customers who have completed an application form and become members. MovieZone currently has 90 members and a collection of 200 videos.
To do this work and provide these services information is collected and stored about videos and members. Information about each video is written down on a card that is kept inside its case. Information about each member is written down on a card. These cards are stored in a card index box in ascending order of member number. Information about videos on loan is stored in another card index box.
When a customer wants to rent a video they take it from the shelf and hand it to the shop assistant who takes the video's details card from inside its case and asks the customer for their membership number. The assistant finds the card with the member's details on it in the members box. The member is asked to confirm their name and address as a security check. If the details that the member gives match the ones on the card the assistant asks how many days they want to rent the video for. The assistant works out the date that the video will due back and writes it on the video details card, which is then attached to the member details card with a paper clip and put into loans box. When a video is returned the assistant searches through the loans box to find the video and member details cards. The member details card is put back in the members box. The last due back date on the video details card is crossed out and put back in the video case, which is placed back on the shelf. At the end of every day the loans box is searched to see any videos that were due back haven't been returned. The names of members with overdue videos are written down on a piece of paper and reminder cards are filled in and posted to them.
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 ...
This is a preview of the whole essay
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
Field Name
Data Type
Validation Rule
Description
Typical Data
MemberNo
Number
Must be between 1 and 5000
Number used to identify a member
019
FirstName
Text
None - any value possible
First name of member
Mark
Surname
Text
None - any value possible
Surname of member
Porter
AddressLine1
Text
None - any value possible
First line of member's address
21 Green Lane
AddressLine2
Text
None - any value possible
Second line of member's address
Talke
Town
Text
None - any value possible
Town that the member lives in
Stoke-on-Trent
County
Text
None - any value possible
County that the member lives in
Staffordshire
Postcode
Text
None - any value possible
Postcode of member's address
ST8 9LP
DateOfBirth
Date
Must be a valid date
Date of birth of member
22/11/81
The primary key for this table is MemberNo
Table: Videos
Field Name
Data Type
Validation Rule
Description
Typical Data
VideoNo
Number
Must be between 1 and 10000
Number used to identify a video
092
Title
Text
None - any value possible
The title of the video
Star Wars
Category
Text
Must be one of Act, Com, Hor, Scf, Thr
The category of the video
SCF
Certificate
Text
Must be one of U, 12, PG, 15, 18
The certificate of the video
U
The primary key for this table is VideoNo
Table: Loans
Field Name
Data Type
Validation Rule
Description
Typical Data
MemberNo
Number
Must be between 1 and 5000
Number used to identify a member
019
VideoNo
Number
Must be between 1 and 10000
Number used to identify a video
092
LengthofLoan
Text
Can only be 1, 2 or 3
How many days the video has been loaned out for
2
DateDue
Date
Must be a valid date
The date the video is due to be returned
21/06/01
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
Menu form design for: frmStartMenu
This form will be used to
Menu form design for: frmMainMenu
This form will be used to
Data Entry Form Designs
Data entry form design for: frmMembers
This form will be used to find, add, edit and delete member details.
This form will use fields from the following tables/queries:
Table/query name
Field(s)
tblMembers
MemberNo, FirstName, Surname, AddressLine1, AddressLine2, Town, County, Postcode, DateOfBirth
Data entry form design for: frmVideos
This form will be used to find, add, edit and delete video details.
This form will use fields from the following tables/queries:
Table/query name
Field(s)
tblVideos
VideoNo, Title, Category, Certificate
Data entry form design for: frmLoans
This form will be used to find, add, edit and delete loan details.
This form will use fields from the following tables/queries:
Table/query name
Field(s)
tblMembers
MemberNo, FirstName, Surname, DateOfBirth
tblVideos
VideoNo, Title, Certificate
tblLoans
LengthofLoan, DateDue
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:
. 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;
0. Create a query to search the videos table for all videos with a certain certificate;
1. Create a query to search the videos table for all videos with a certain category and certificate;
2. Create a query to search the videos table for all the videos with a certain title;
3. Create a query to search the loans table for videos that are overdue;
4. Create a report to display information about overdue videos;
5. Create a menu form to display the main menu options;
6. Create a menu form to display the find video options;
7. Prepare a letter that can be used in a mail merge to generate reminders for members with overdue videos;
8. Create a macro to generate reminder letters for members with overdue videos;
Implementation commentary
Test Results
Modifications
Evaluation
User Guide
Sample Project
Appendix A
Sample project - 1
