Oceanic Video Club is a private business, run by its owner Mr. Michael Coleman. Oceanic is a video club which is in the selling of films. The owner Mr. Michael has built up a large customer base and this is increasing year by year.

The business is going well as the amount of clients is increasing. As Mr. Michael hold a list of customers on a paper-based filling system, so Mr. Michael does not worry about the client as they are being informed automatically.

Consequently, he has decided to make an investment on a comprehensive customer information system so that he can hold details on his customers, who have been purchasing films in the last few years and now. Also to take their opinions about the video club so that he can offer his customers a better service..

An interview with Mr. Michael was arranged. These included:

> The objectives of the business.

> The precise objectives of the new system.

> The method currently used to record data.

> The information that needs to be kept on each customer.

> Any hardware or software constraints.

> The required output.

> The volume of data to be processed.

> The problems with the new and manual system.

1. How many employees do you have?

2. Do you sell on credit or cash?

3. Do you have any problems with debtors?

4. What are the expenses which you make each year?

5. What types of music do you sell?

6. Do you own or rent this place?

7. What problems do you get with the manual system?

8. How much money are you ready to invest in the computerization?

An interview with Mr. Michael was arranged and a lists of topics for discussion drawn up prior to the interview. These included:

Self: How many copies do you have in your stock?

Mr. Michael: I have about 1500 copies.

Self: Do you sell on credit or cash?

Mr. Michael: I work with both systems.

Self: Do you have any problems with debtors?

Mr. Michael: Yes as it is usually the case everywhere.

Self: What are the expenses which you make each year?

Mr. Michael: When films are damaged I incurred a loss.

Self: What types of films do you sells?

Mr. Michael: I sell all types of films.

Self: Do you own or rent this place?

Mr. I own this place.

Self: What problems do you get with the manual system?

Mr. Michael It is time consuming to fill records on paper and sometimes important documents are loss.

Self: How much are you ready to invest in the computerization?

Mr. Michael would invest about Rs30 000 in the new system.

Summary of the interview

A lots of problems with the new system has been revealed during the interviews, and the new objectives recommended was done but it was time consuming to take data from the old paper-based system and put it in the new system as their was more than 300 regular customer in the old system and with 50 new customers being added every year.

Problems to current system

1. Loss of records.

Solution A. Use of a cupboard.

Advantage

All the records will be arranged in the cupboard drawers.

Disadvantage

It takes a lot of place

Solution B. Used of a computerised database.

Advantage

Access to the information is rapid and there is less likelihood of the data becoming lost.

Disadvantage

Training is needed to use the system and this takes time and cost a lot of money.

2. Contacting customers about new items.

Solution A. By sending letter to customers.

The objectives may be stated in both quantitative and qualitative terms.

1. It should take less than 30 seconds to establish whether a customer is already on file and to fill in new films records.

2. It should be possible to go directly to the main menu screen to the entry of a transaction, without having to re-enter the customer’s name, etc…

3. Data entry should be as fast and easy as possible, particularly as there are several hundred existing customers and music to be entered when the system is first installed.

4. It should take less than one minute to trace music for a customer.

5. The new system is required to provide the following information;

a) A list of all available music.

b) A list of all customers.

6. The main menu should be displayed automatically when the database is loaded, and the whole system should be menu-driven.

The following is the data flow of the proposed system.

The system to be developed is a customer information system, and is intended to enhance rather than replace the current system of recording music and customers records. If this proves successful, it will be possible at a future date to replace the current methods of recording records so that the details are typed directly into the computer and the required copies printed out. This could then be extended to link into a computerized accounts system.

Software

The customer has requested that the system be developed using Ms Access, so useless a good reason transpired for using an alternative program or package Ms Access will be the first choice of software.

Hardware

In order to run Ms Access and be able to perform a mail merge using word, a PC 486 or Pentium with a minimum of 8 MB, and preferably 16 MB, will be required. A fast processor such as a P4 will be needed if the system is not going to appear slow in switching between screens.

Mr. Ah-Young is familiar with word and has a good keyboard skill, so should have no problem entering data and learning how to use the system. He would like to improve his knowledge of basic so that he can in the future perform new queries and reports as the need arises.

A database package will be ideal for implementing the system for Power Music Shop, and as Mr. Ah-Young has requested that it should be done in Ms Access. This is the package that must be used. It would probably be possible to implement the system using Ms Access or another programming language but it would take longer time and would involve the owner having to buy more software which is not really necessary, as Ms Access has all the capabilities required.

I already have some experience of Ms Access and it is available both at home and at college for development.

Using this package it will be possible to

* Set up the necessary tables and relationships.

* Produced customized input screens, using Ms Access to automate data entry wherever possible and to perform various validations.

* Use Ms Access modules to enable fast searches for a particular Customer and past stay.

* Design reports as needed.

The database contains two entities, which are CUSTOMER and MUSIC. Tables will be created for each of the entities.

Tables will be created for each of these entities.

The tables will contain the following data.

Attribute Name

Data type and length

Validation

Film code

Text (4)

Unique primary key

Title

Text (50)

Actor

Text (50)

Actress

Text (50)

Type

Text (20)

Number of copies

Numeric (2)

Duration

Text (6)

Attribute Name

Data type and length

Validation

Code

Text (4)

Unique primary key

Name

Text (20)

Other Names

Text (20)

Date Of Birth

Text (8)

Sex

Text (6)

Address

Text (50)

Tel number

Text (7)

ID Card Number

Text (16)

Two data entry form are needed.

This form will be used for several purposes so it needs facilities to

* Check to see whether a Customer is already on the database.

* Add a new Customer.

* Look though all the existing records for the current Customer and bring up more details if necessary.

The Customer details form will be as shown on the next page.

This form will be used for several purposes so it needs facilities to

* Check to see whether music is already on the database.

* Add a new music

* Look though all the existing records for the current music and bring up more details if necessary.

The music details form will be as shown on the next page.

Customer File

FILM File

The format of all the reports will be similar. The layout of the Customer report is shown below.

The menu structure is as follows:

A password will be attached to the database so that it is only accessible to someone who knows the password. Different access levels are not needed as Mr. Pascal is the only person who will be using the database.

9. Test Strategy

The test strategy will include five different types of testing as described below:

LOGICAL TESTING

This will be used to test every aspect of each form, report and query as soon as it is implemented, using valid, invalid and extreme data. Test data will be added to test each code module and results compared with expected results. Sufficient data will be added to ensure that there is at least one customer in each category. The test data that will be added initially is shown in Appendix. Subsequent test will often involve adding new data, which will then be deleted when the test works satisfactorily.

FUNCTIONAL TESTING

Each menu item will be tested in turn to ensure that no function has been missed out.

SYSTEM TESTING

When the system is complete, the whole range of tests will be carried out again to ensure that no errors have been introduced.

RECOVERY TESTING

The computer will be re-booted while the database is open to ensure that data is not lost or corrupted in the event of a power of a power failure.

ACCEPTANCE TESTING

The user will then be involved and asked to test all the capabilities of the program to ensure that all request functions are presented and working in the manner expected. This testing may result in further refinements.

SECTION 3

TESTING

TEST PLAN

Module: Password Menu

TEST NO.

TEST

EXPECTED RESULT

REMARKS

1

Insert correct password and click on ‘Login’

Main Menu should appear on the screen.

OK

2

Insert incorrect password and click on ‘Login’

A message saying wrong password should appear on the screen.

OK

Module: Main Menu

TEST NO.

TEST

EXPECTED RESULT

REMARKS

1

Click on ‘Customer File’ command button.

Item File must appear on the screen.

OK

2

Click on ‘Film File’ command button.

Customer File must appear on the screen.

OK

Module: Film File

TEST NO.

TEST

EXPECTED RESULT

REMARKS

1

Click on ‘Add’ command button.

Must be able to add a record.

OK

2

Click on ‘Save’ command button

Must be able to save a record.

OK

3

Click on ‘Delete’ command button.

Must be able to delete a record.

OK

4

Click on ‘Back to Main Menu’ command button.

Main Menu must appear on the screen.

OK

Module: Customer File

TEST NO.

TEST

EXPECTED RESULT

REMARKS

1

Click on ‘Add’ command button.

Must be able to add a record.

OK

2

Click on ‘Save’ command button

Must be able to save a record.

OK

3

Click on ‘Delete’ command button.

Must be able to delete a record.

OK

4

Click on ‘Back to Main Menu’ command button.

Main Menu must appear on the screen.

OK

SECTION 4

SYSTEM MAINTENANCE

1. SYSTEM OVERVIEW

This Customer information system is designed to keep records of Customer profiles and their past purchases. It is designed to run alongside the current manual system of recording purchases, rather than replacing it. The computer will be in the reception and the owner can use it to check whether a customer who comes in is already on the database, or to check on past purchases.

New data will be added to the database at a convenient time, possibly at the end of the week. The procedure that the user will fellow is described in the design section.

2. TABLES AND RELATIONSHIPS

Tables and relationships were set up as specified in the design section.

3. FORMS

The menu structure was set up as specified in the design section.

Forms were used as follows:

Main Menu

This is specified as the start-up form and loads automatically when database is opened.

All buttons either opening other form or reports, or quitting the database, were placed using wizards.

Report Menu

All buttons placed using wizards. Maximise macro runs on opening form.

Input Forms

The Customer details form has a combo box displaying Customer codes so that the user can look up the record for any Customer. The record source for this box is the database.

4. REPORTS

These are as described in the user manual. All reports were created using wizards and then tailored to produce a more appropriate layout.

SECTION 5

USER DOCUMENTATION

Initial set up

Password Menu

The Password menu is automatically started when the database is loaded. The password is case sensitive but it will only accept ‘OCEANIC’.

Main Menu

The Main Menu will automatically appear when you enter the password.

Film File

Add a new record

In order to add a new record, click on Add new and after adding all the information, click on ‘Save Record’.

Viewing a record

To view a record, find it using the Find record list box.

Deleting a record

To delete a record, first find it using the Find record list box, change the information and click on Delete record.

View a report

In order to view a report, click on ‘Film Report’.

Customer File

Add a new record

In order to add a new record, click on Add new and after adding all the information, click on ‘Save Record’.

Viewing a record

To view a record, find it using the Find record list box.

Deleting a record

To delete a record, first find it using the Find record list box, change the information and click on Delete record.

View a report

In order to view a report, click on ‘Customer Report’.

SECTION 6

APPRAISAL

The system has been completed and installed on the user’s PC. It was completed in the manner originally designed and agreed with the user, and is straightforward to use.

Referring to the original objectives listed in the Analysis section:

1. It should take less than 30 seconds to establish whether a film or a customer is already in the database.

2. It should be possible to go directly from the film file’s screen to the entry of a new film.

3. Data entry is as fast and easy as possible.

4. The new system provides the following information.

a) List of all films.

b) List of all customers.

5. The main menu should be displayed automatically when the program is loaded and the whole system should be menu driven

6. Mr. Pascal had some problems initially with data entry, confusing Items and customers, as this aspect works a little differently from the manual system. However he has now entered data for over 100 items and has no problems to report.

7. All reports are implemented as specified.

8. The menus work as planned.

SECTION 7

APPENDIX

Password Menu

Private Sub Login_Click()

On Error GoTo Err_Login_Click

Dim stDocName As String

Dim stLinkCriteria As String

Text1.SetFocus

If Text1.Text = “OCEANIC” Then

stDocName = “Mainmenu”

DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

MsgBox (“Wrong Password”)

End If

Exit_Login_Click:

Exit Sub

Err_Login_Click:

MsgBox Err.Description

Resume Exit_Login_Click

End Sub

Private Sub Exit_Click()

On Error GoTo Err_Exit_Click

DoCmd.Close

Exit_Exit_Click:

Exit Sub

Err_Exit_Click:

MsgBox Err.Description

Resume Exit_Exit_Click

End Sub

Main Menu

Private Sub Exit_Click()

On Error GoTo Err_Exit_Click

DoCmd.Close

Exit_Exit_Click:

Exit Sub

Err_Exit_Click:

MsgBox Err.Description

Resume Exit_Exit_Click

End Sub

FilmFile

Private Sub Add_Record_Click()

On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:

Exit Sub

Err_Add_Record_Click:

MsgBox Err.Description

Resume Exit_Add_Record_Click

End Sub

Private Sub Delete_Record_Click()

On Error GoTo Err_Delete_Record_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:

Exit Sub

Err_Delete_Record_Click:

MsgBox Err.Description

Resume Exit_Delete_Record_Click

End Sub

Private Sub Save_Record_Click()

On Error GoTo Err_Save_Record_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Record_Click:

Exit Sub

Err_Save_Record_Click:

MsgBox Err.Description

Resume Exit_Save_Record_Click

End Sub

Private Sub Film_Report_Click()

On Error GoTo Err_Film_Report_Click

Dim stDocName As String

stDocName = “Film Report”

DoCmd.OpenReport stDocName, acPreview

Exit_Film_Report_Click:

Exit Sub

Err_Film_Report_Click:

MsgBox Err.Description

Resume Exit_Film_Report_Click

End Sub

Private Sub Back_Click()

On Error GoTo Err_Back_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = “Main Menu”

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Back_Click:

Exit Sub

Err_Back_Click:

MsgBox Err.Description

Resume Exit_Back_Click

End Sub

Customer File

Private Sub Add_Record_Click()

On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:

Exit Sub

Err_Add_Record_Click:

MsgBox Err.Description

Resume Exit_Add_Record_Click

End Sub

Private Sub Delete_Record_Click()

On Error GoTo Err_Delete_Record_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:

Exit Sub

Err_Delete_Record_Click:

MsgBox Err.Description

Resume Exit_Delete_Record_Click

End Sub

Private Sub Save_Record_Click()

On Error GoTo Err_Save_Record_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Record_Click:

Exit Sub

Err_Save_Record_Click:

MsgBox Err.Description

Resume Exit_Save_Record_Click

End Sub

Private Sub Customer_Report_Click()

On Error GoTo Err_Customer_Report_Click

Dim stDocName As String

stDocName = “Customer Report”

DoCmd.OpenReport stDocName, acPreview

Exit_Customer_Report_Click:

Exit Sub

Err_Customer_Report_Click:

MsgBox Err.Description

Resume Exit_Customer_Report_Click

End Sub

Private Sub Back_Click()

On Error GoTo Err_Back_Click

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = “Main Menu”

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Back_Click:

Exit Sub

Err_Back_Click:

MsgBox Err.Description

Resume Exit_Back_Click

End Sub

Cite this page

Video Club Database Project. (2018, Nov 30). Retrieved from http://paperap.com/paper-on-video-club-database-project/

Let’s chat?  We're online 24/7