Database design for a games shop.

• My first suggestion is that MR. Walcott could use a laptop with Microsoft spreadsheet on it. This will be much faster and more organized than the current system. He could have all the information under one group, all his customers’ details and which game they loaned under another. There will be no chance of losing the data as it will be in the same place and will be easy to access. Also MR. Walcott loves his technology; he would not get tired of using it or misinterpreting data. This is an option I will seriously consider.
• My second suggestion is to use a filing cabinet with a folder for each customer, this way he could find each customers details and which game they rented, for how long and has it been returned all under one folder but the downside is that he will have to buy many cabinets and at least 10,000 folders. This will cost a lot of money, but MR. Walcott has already got a laptop so that will be no extra cost. Also this is a very tiring and complicated way for MR. Walcott as he is very lazy. This will also take at least the space of one small room which the company has not got.
• My third suggestion is a notebook, this is portable and easy to access, blockgamester will not lose any loose paper. But if you lose this book you lose your business so that is too much responsibility for a notebook as there is no back up.
• My final solution is Microsoft database, I have done research and all good rental companies use this software. This is the ideal solution for MR. Walcott’s problem. Database is the fastest and most efficient option available to me. He can easily search his system by simply entering the name of the customer or the name of the game and see how long it’s been loaned or who has got it. He can print it out the report easily. After considering everything, I have chosen Microsoft database as a solution, simply because it is the most reliable and best option available to me. This will be my system.

         

User requirements:

After having a meeting with MR. Walcott and Ben they have set me the following user requirements:

• The system should have a menu option.
• The menu should have a rental section.
• The system should have information about his games.
• The system should have information about customers.
• The system should have information about transactions.
• The system should be able to recover information by entering
• Customer’s name.
• The system should be able to show overdue games.
• The system should have a printable list of people who have an overdue game.
• The system should be easy to use.
• The system should be secure.
• The system should not mix up any data.
• We spoke over the phone about minor requirements which you will see later in the database.

Analyse

Software

 For me there is only one software that is available for me to produce for MR. Walcott and Ben, which is Microsoft access. The reason I am choosing this software is because after doing some research it is the best software available to me for blockgamester’s problem. Furthermore MR. Walcott has already got a laptop with Microsoft access installed in it so he does not have to spend any money on buying a software. Also they are advanced in ICT so using and maintaining this software should not be a problem.

Microsoft is also needed to write and print letters.

Hardware

The hardware that I require for this is a 3.06 processor because a lower processor is slower. A 4GB memory ram to enhance the computer’s memory, a storage hard-drive with 2 GB capacity for transfer of files.

A multi function printer, which includes fax, copier and scanner this, will come in handy when I am sending letters.

Data collection/input

 

I will need details about the games, customers and loans. Blockgamester will send me all the information I require very soon. Also at the moment there is no system in place for a new customer but I will change that, I intend to make a customer form, the customer will have to fill the form with all of his details including personal details. After this blockgamester will easily be able to update their software with this information, they will also be able to input new games into the database.  

Now when a customer wants to rent a game blockgamester can easily input information, if it is a new customer, they will need to fill in the customer form, or in case of a returning customer they will just have to write the name of the game that was rented.

Common input data will be automatically validated by the databases ‘autonumber’. Autonumber is a unique primary key which is used for certain information such as ‘items’ or ‘people’. It cuts out the mistakes which will aid blockgamester if they are in a rush or forget to enter something.

Data validation

Data will be validated before it can be stored in the laptop. Common mistakes like a telephone number which have 11 digits, if by mistake there are not 11 digits an error message will appear.

Data flow diagram

        Receive error report

        No/overdue

Data manipulation

 In the system I will require the following tables and they will be structured as shown:

• Members: (client number, title, first name, surname, address phone number and email address)
• Games: (game ID(primary key), name of game, system, genre, rental price per day)
• Rentals:  (client number (foreign key), game ID(FK), days kept, days returned.

Mr. Walcott will also be two reports which will need to be printed out:

• Overdue games:  it will be sourced from the query, Mr. Walcott will print out the report and pass it to Ben, who will then chase the customer up.
• Popular game:  this report will contain, the popular games and genre, once Mr. Walcott has concluded which games will increase the effectiveness of his business, Ben will purchase these games.

There will be an advanced query used to search the database:

• Overdue games: this search will be done to see which members returned their game late and who has still not returned it. From the results a mail merged letter will be send and a report will be done.

Output

To view the data Mr. Walcott will need a monitor and a printer. He could buy a projector and a all in one printer, however this is too expensive and there is no need to use advance technology when there is a simple monitor and printer available. Mr. Walcott will need a monitor and a printer to view the reports, he can view them on the screen or print the out.

Backup/ security methods

The backup method that I have advised MR. Walcott to use is a USB, every time he updates the system he will have to update it on the USB, the USB’s will all be held in different locations in case of a human or natural error such as a fire or tsunami. Moreover the database will be secure because there will be a password on it. The password must have 6 letter and 2 numbers. You should have a password on the database just to make sure nobody can hijack it and get private and confidential client information.                            

        

Design

On the following pages are my initial designs which will be viewed by Mr. Walcott and then improved following his feedback.

Here is some information on the initial designs which I will create and they solve the problem of the user:

• Games and members details form: the games details and members details forms was one of the main problems Mr. Walcott had. My solution will allow MR. Walcott to just type in the data into the relevant field hence the database will update automatically. There will be a primary key, this will help incase there is a confusion.

• Games by genre report:  this report helps MR. Walcott because he wanted to find out which games were more popular and this multi file report does just that, it contains information about how many people rented out a certain game of a certain genre. After concluding MR. Walcott can use it to help his business.

• Rentals form: this form contains a foreign key from the members and games table. Mr. Walcott will be able to input data of all the transactions. However he will not be able to input transaction details of a member that does not exist. This is very useful for members and Mr. Walcott.

• Mail-merged letter: Mr. Walcott required sending all of his members who are overdue a letter and this fulfills his wishes. It will get the names of the customers that are overdue from overdue rentals search query.

• Overdue members contact sheet: this is a easy access sheet which will be printable, it has all the information about the clients who are overdue.

• Overdue rentals-query based report: in this report it has information about the overdue customers who have not responded to emails. This report can be printed out if necessary.

• Customers detail search: in this report it contains information about each specific member which can be easily accessed by Mr. Walcott

        

        

User feedback: comments on initial design

Dear Haseeb,

Firstly I would like to thank you for your wonderful piece of work and all the hard work you put in to the initial designs. I believe they meet the requirements which I set you. I am very happy with it and I can see it would make my life a whole lot easier.  Everything I ordered you to do you have done and with top marks.

Secondly I believe some improvements can be made, I believe I will be wasting my time inputting so much information such as the title of a customer, it would be very easy if I had just three choices to make which are Mr., Mrs. And Miss. Also field lengths are too long, you can see where I am going so I would appreciate if you could make appropriate measures.

Lastly I think having foreign keys is not enough in the rentals form. It would be more helpful if you could insert the name of the customer from the members table into the rentals, this will help me verify customers information. Also a bit of color to the database would be appreciated, they look to plain at the moment, and so could you make them look brighter. In regards to the game rented information, I would like you to add days hired and required return date to the letter, all in all, everything else seems perfect.

Thank you for your help and look forward to hearing from you soon.

You’re sincerely

Mr. Walcott

Final designs

On the following pages are my final designs after taking into account Mr. Walcott’s comments I have made the required changes.

Final design

I have to redesign the forms, I have made some adjustments after the comments of the user. Here are the changes from the initial design:

Complete following Subtasks

• menu system
• multiple table sourced report games by genre
• input forms for each of the tables
• relationships between table
• rentals, members and games table includes adding information
• overdue rentals query
• password for security reasons
• query sourced overdue rentals contact sheet report
• customer search wildcard parameter query
• mail merged letter sourced from the queries field name
• test the system for and errors, hence correct them.

Test plan

        

Implementation

        

GAMES, MEMBERS AND RENTALS TABLE

        

Test plan

Evidence of error correct after correcting all errors

I had some minor issues with the security whilst I was running the press button checks. I had a few problems with some of them and I got the same response from all of them here is the message my database gave me whilst performing certain actions but overall I realized it was just a little glitch. Also I have more information about the problems I had and how I solved them.

        

Evaluate

User feedback

Dear Haseeb

I would like to thank you for a brilliant piece of work which you have produced, the final database system is absolutely outstanding. The database has met all the requirements I set you.

Firstly I would like to tell you what I like about the database:

On the forms, the buttons make my life very easy, I can easily input data and browse through with the click of a button. The forms are well designed and are appropriate for me.

One of the things which I like most about the database is the menu, the way you set it up is very easy and effective. Also the data is stored in tables this will ensure I make few mistake as possible. I can easily access sub menus and input data so you have achieved this task with full marks.

The main problem with my company was the overdue rentals, I could not keep up, however you have solved this problem. Now I can see who is overdue, I can see their details and also the mail merged letter is there to make life easier. The query is handful because it gives me information about the game rented date, returned date and how long it was rented for.

In the database I like the way I can search through the customers. I can find out the status of the game rented out. The way I can view this I brilliant. Also the genre report is very helpful it clearly lets me know which game is more popular and what genre people like the most hence I can buy that sort of game and rent it out to increase sales.

Secondly I would like to tell you the things which I don’t like too much:

There are a lot of minor problems which take time and we all know time is money so wasting time is not good for my business.                                          The database system should open up automatically to the menu system but it does not, hence have to manually do it.

It is good that I can enter data through the menu system but it is not good that once I have entered it I can’t delete it quickly and without wasting time, further to the problem if I have to delete a record I have to do it manually.

This is a key problem with the database, when a customer calls me to find out about a game if it is in stock, and I check the database, the database will tell me that one copy is available however  from my own knowledge I know that I have just rented it out hence it is not in stock, the games in stock should update automatically once a game has been loaned out but it does not.

Overall there are more positives than negatives so thank you very much my good friend

You’re sincerely

Mr. Walcott

PS:  Ben is very happy and impressed with the database, he sends his regards.

Self assessment and further improvements

In this section I will be assessing myself after receiving Mr. Walcott’s letter.

Blockgamester main problem was losing data about the game due to the fact he wrote on paper, now Mr. Walcott can easily input data in to the system in a more organized way.  Furthermore he can now reply to customers quickly with the customer detail search, with regards to their game overdue. I believe all in all, all of the user requirements were achieve with success.

Unfortunately I believe after reading his letter, the database was not created to my highest standard. I should have used common sense in some of the situations such as:

Mr. Walcott wasted time in regards to deleting a record, if a customer cancelled something I believe I should of designed a delete query button, after reading his letter I believe this would benefit him.

I believe I should have had time on the main menu form and this would help Mr. Walcott opened the database whenever he intended to.

Also I should have carried out an investigation into why it would not automatically update and should have solved this. Overall I believe there were a few niggles with the database but I tried to solve the problem and Mr. Walcott’s problem, I believed I passed the test.

My user requirements and how I achieved them:

After having a meeting with MR. Walcott and Ben they have set me the following user requirements, I achieved them:

• The system should have a menu option.
• The menu should have a rental section.
• The system should have information about his games.
• The system should have information about customers.
• The system should have information about transactions.
• The system should be able to recover information by entering the customer’s name.
• The system should be able to show overdue games.
• The system should have a printable list of people who have an overdue game.
• The system should be easy to use.
• The system should be secure.
• The system should not mix up any data.
• We spoke over the phone about minor requirements which you will see later in the database

Evaluation of above user requirements

• The database has a menu system.
• In the database on the menu system there is a rental section.
• The database has information about the game, there is a games table.
• The database has information about the customers, there is a customer table.
• The database has information about the transactions, there a rentals table.
• The database can search, overdue search. When query run brings up information.
• The system provides you with information, regarding the customers which are overdue with a game.
• There is a print button on a report
• The system is clear, easy to use, helpful and does not give any problems.