Video Club Database Project Essay Example

Video Club Database Project Essay Example Video Club Database Project Essay Video Club Database Project Essay 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 customers 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 users 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 files 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