Analysis of Johnson Music

My acknowledgement

Dear Mr Johnson,

Thank you for allowing me to research further into your shop. I will do my best to produce a system that will significantly improve stock control at your shop. I will write back to you soon to confirm a time for an interview with a salesperson at your shop.

Thanks,

Matthew Malloy.

---

Interviews

Contact

Dear Mr Johnson,

Thank you again for allowing me to conduct an interview with an employee at your shop. I am writing to propose that the interview will be held at your shop on Wednesday 20th June at 5.00pm. Please write back to confirm that this time is appropriate.

Thanks,

Matthew Malloy

Reply

Dear Matthew,

I am writing to you to confirm that Wednesday 20th June at 5.00pm will be suitable for you to conduct an interview with one of my employees. Thank you again for your interest in my shop.

George Johnson.

---

First interview script

Me: Do you think that there are problems with the way your sales are conducted?

Salesperson: Yes, I think there are definitely problems with them.

Me: Can you expand on this by saying what some of the problems are?

Salesperson: Well, firstly, whenever anything is bought, there is paperwork involved. This means searching through existing documents and filing new documents in order to sell just one product.

Me: Do you think it could be easily improved?

Salesperson: Yes it could definitely be improved because at the moment it is very time consuming. I have visited other shops very similar to ours and none of them seem to have problems like this.

Me: Do you think computerisation in your company would solve this problem?

Salesperson: I think computerisation would definitely help and speed up the process of sales significantly but I don’t think the problem could be completely solved.

Me: Do you think there is a way that the problem could be completely solved?

Salesperson: Well, obviously there would have to be some sort of control over the way stock is handled when conducting a sale, therefore there would always be some amount of work to do during a sale, whether it be on paper or on a computer. The quickest way of conducting sales would be to use some sort of barcode scanner that would automatically update the stock levels and say when more products need to be ordered in.

Me: Do you think that is a possibility for the shop in the future?

Salesperson: The automatic system would be very expensive to implement, and for a small shop like this one it would probably be too much. I think some sort of computerisation is in order for the company but it doesn’t need to be as drastic as what I described.

Me: Would you prefer to use a computerised system to update stock levels when doing sales or would you rather carry on how it currently runs?

Salesperson: I would definitely prefer a computerised system.

Me: Can you describe the process of conducting a sale the way your system currently runs?

Salesperson: Okay, for example, if someone wished to purchase a guitar. The customer would tell a member of staff that they wish to purchase the guitar. The member of staff would then check through paperwork to see how many of that model of guitar is left in stock. If there aren’t many left, then a note would be left to the stock control manager to say that more are needed. Otherwise, the member of staff would just update the paperwork to say that there is one less left of that guitar. This process would usually take around 5 – 10 minutes, and this is before the customer can leave the store with their purchased product.

Me: Thanks for your time; your answers have been useful.

Summary

The salesperson agrees with George Johnson and I that the way sales are currently handled definitely needs work. The current procedure during a sale is very long winded and takes much more time than needed. This has been other people’s experience with the shop as well as my own. The ideal system for the shop is not particularly feasible at this moment in time as the shop is very small and it isn’t really necessary. A good solution, though, would be a simpler computerised system. The salesperson agrees with me on this. In the next interview I will need to find out what information is kept on products, and I will research other problems that they have with the current system.

I will now ask for another interview with the same member of staff to get more in-depth information on what I have already found out.

---

Contact

Dear Mr Johnson,

I want to thank you and your employee for allowing me to conduct an interview. I am also writing to you to propose that you allow me to conduct another interview with the same member of staff to find out information in slightly more depth than what was discussed during the first interview. I would like for this interview to take place on Saturday 23rd June at 12.30pm. Please write back to tell me whether this is suitable. Thanks.

Matthew Malloy.

Accept interview

Dear Matthew,

I am writing to confirm that the time you proposed for your second interview is fine. I’ll see you on Saturday.

George Johnson.

---

Second interview script

Me: When updating paperwork during a sale, what exactly is the information that needs updating?

Salesperson: During a sale the information that needs updating mainly is the number of items left in stock for that particular product.

Me: What information do you hold on the products in the documents?

Salesperson: The name of the product, its unique identification code, its price, the number left in stock, and any additional information that needs to be held on the product.

Me: Does paperwork often get misplaced?

Salesperson: Paperwork does get lost sometimes but not very often, but when it does get lost then it is a great inconvenience. Due to all of the information that we have about the product being on the paperwork, unless someone can remember the price and additional information on the product for definite, then we have to contact Ibanez and get pricing information from them, and find out additional information on the product. The stock control manager then has to look through our stock room to count how many of that particular product is left in stock. New paperwork for that product is then produced which should hopefully be almost identical to the piece that was lost.

Me: What is the role of the stock control manager?

Salesperson: I’m not entirely sure about the whole role. I know that he keeps track of stock, counting the products left in stock so that they match up to the documents that are kept, and then when an item is going out of stock then it is his job to order more in.

Me: Do you think a computerised system for stock control would help him out?

Salesperson: I think it would definitely help him but you would have to ask him yourself to be sure.

Me: Thanks for your time and the additional information you have given me in this second interview.

Summary

In this interview I have discovered the different bits of information that are kept on the product for sale:

• The name of the product
• Unique identification
• Price
• Number left in stock
• Additional information

I have also found out that computerisation would not only help when conducting sales but it would probably help the stock control manager as well. I will need to conduct an interview with him to get his opinion on the matter, and find out exactly what his role is within the company.

Other problems I have discovered in this interview is that paperwork can get misplaced sometimes, and when this happens it causes a great inconvenience for the person conducting the sale and also the stock control manager.

I will now contact the owner of the shop to arrange an interview with the shop’s stock control manager.

---

Contact

Dear Mr Johnson,

Thank you to you and your member of staff for allowing me to conduct a further interview. I have found sufficient information from these interviews, and I would now like to conduct one last interview with your stock control manager on Tuesday 26th June at 5.00pm. Please write back to confirm that this is okay.

Thanks,

Matthew Malloy.

Accept interview

Dear Matthew,

I am writing to say that Tuesday 26th June at 5.00pm is a suitable time for your interview with our stock control manager. Thank you again for being interested in our stock control system.

Mr Johnson.

---

Third interview script

Me: What is your role in the running of Johnson Music?

SCM: My main job is to keep track of the number of items left in stock. I make counts of how many of each product is in the stock room and verify that it is the same as the documentation that is kept. This is to check whether any products are missing, and it helps keep our stock records accurate. I also order in new stock when currently available stock is low. This is so that we can have most of our products available to be sold on the spot most of the time.

Me: What is your opinion on the amount of paperwork that is involved with stock control?

SCM: It can get very confusing and tedious searching through paperwork all the time. Sometimes records go missing and I have to create a new record on that product which involves quite a lot of work. It consumes time that could be used conducting more useful work than if the paperwork goes missing. I am not a fan of the paperwork at all, but at the moment it is the only way of keeping track of stock.

Me: Would you be in favour of a computerised system being introduced to keep track of stock?

SCM: I would definitely be in favour of this. It would eliminate the problem of paperwork being lost and it would also significantly decrease the amount of time it takes to look up information about a product, such as the amount left in stock, due to not having to search through hundreds of pieces of paper.

Summary

In this interview I have discovered the full role of the stock control manager. His main jobs are:

• Keeping track of stock levels
• Verifying actual stock against documentation
• Ordering in new stock when current stock is low

The stock control manager agrees with the rest of us that computerisation would definitely help the company in speeding up sales and making it more efficient. A computerised system would make his job a lot easier and would make sales more convenient for both the salesperson and the customer.

E-mail to say thank you.

Dear Mr Johnson,

Thank you for allowing me to interview your stock control manager on Tuesday, it has been very helpful with the project. Thank you again for allowing me to interview one of your salespeople as well. I will write to you again soon to discuss further research into your shop.

Matthew Malloy.

I will now e-mail Mr Johnson to propose that his staff fill in the following questionnaire that I have put together:

Questionnaire

1. How would you rate the current sales system for guitars?

1. Very poor
2. Poor
3. Good
4. Very good

2. Would you prefer a computerised system or a paper-based system?

1. Computerised
2. Paper-based

3. In a day, how many customers do you serve?

1. 0 – 3
2. 3 – 6
3. 7 – 10
4. 10+

4. Of the amount of customers you serve, how many seem unsatisfied by the service (in terms of length of sales, organisation…)?

1. <25%
2. 25% - 50%
3. 50% - 75%
4. 75% +

5. Have you worked with a company with a better system than your current system before?

1. Yes
2. No

6. If yes, please give a brief description of it.

………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………

7. How would you rate the current stock control system?

1. Very poor
2. Poor
3. Good
4. Very good

---

Proposal for questionnaire for all staff

Dear Mr Johnson,

In order to find enough information to create a system for you that will be good enough to replace the way you currently handle your sales and stock control, I would find it useful if you would allow me to put together a questionnaire for you and your staff to fill in about various things. If you will allow this then I will post the questionnaires to you as soon as I receive your approval. Thank you,

Matthew Malloy.

Accept questionnaire

Dear Matthew,

I have consulted with my staff and they have all agreed to fill in your questionnaire. I will post them back to you as soon as they have all been filled in. Thanks for your interest.

George Johnson.

Thank you for questionnaire.

Dear Mr Johnson,

Thank you to both you and your staff for filling in the questionnaire. It has been very useful. I will write back to you about further research for my project.

Matthew Malloy.

On the following page are the results of the questionnaire. Actual samples can be found in the appendix at the end of the analysis.

---

Questionnaire results & analysis

1. How would you rate the current sales system for guitars?

1. Very poor        (8)
2. Poor                (2)
3. Good                (0)
4. Very good        (0)

The majority of staff that said that the current way sales are carried out is very poor. None of the staff considered it to be ‘good’ or ‘very good’. This means that all of the staff that filled in this questionnaire would agree that a better system is needed.

2. Would you prefer a computerised system or a paper-based system?

1. Computerised        (9)
2. Paper-based                (1)

Again, the majority of the staff that filled in the questionnaire would prefer to have a computerised system rather than one that is paper-based. The one person that answered ‘paper-based’ could have picked ‘paper-based’ for a number of reasons, none of which could be that they would prefer the current system judging from the results of question 1. This person could have less experience with computers than the rest of the staff, and would therefore prefer a better paper-based system than a computerised one.

3. In an average day, how many customers do you serve?

1. 0 – 3                (2)
2. 3 – 6                (4)
3. 7 – 10                (4)
4. 10+                (0)

Most staff members serve between 3 and 10 customers per day. This means that the shop isn’t particularly busy, and therefore staff wouldn’t usually be in much of a rush to serve the customers.

4. Of the amount of customers you serve, how many seem unsatisfied by the service (in terms of length of sales, organisation…)?

1. <25%                (1)
2. 25% - 50%        (2)
3. 50% - 75%        (6)
4. 75% +                (1)

There is some variation with answers on this question. However, this question was based purely on the member of staff’s judgement and is not completely reliable. One person thinks that less than 25% of their customers are unsatisfied with the speed of service. This could be due to this particular member of staff being quicker than the rest, or it could be down to this member of staff not picking up on the customer’s dissatisfaction. The majority of the staff, however, think that more than half of their customers are unsatisfied with the service they receive. This would mean that an improved system could improve satisfaction from both staff and customers.

5. Have you worked with a company with a better system than your current system before?

1. Yes                (4)
2. No                (6)

The answers were quite even on this question. The majority of staff hasn’t worked with a company with a better system than the one currently used at Johnson Music. This could be because that company has a worse system than Johnson Music, or it could also be that they haven’t worked with any other companies. Of the people that definitely have worked with another company previously, 4 people think that the system there was better than the one they currently use.

6. If yes, please give a brief description of it.

………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………

Every person that answered this question described some sort of computerised system. One of them was an automatic system, where stock was updated automatically when a barcode was scanned. The other three people described a manual system that involved a database stored on a computer system.

7. How would you rate the current stock control system?

1. Very poor        (4)
2. Poor                (6)
3. Good                (0)
4. Very good        (0)

None of the staff that filled in the questionnaire rated the current stock control system as ‘good’ or better. The majority thought that it was poor. This means that the majority of staff would probably welcome a new and improved stock control system.

I will now e-mail Mr Johnson to ask his permission to observe his shop for a day.

---

Observation proposal

Dear Mr Johnson,

Thank you for helping me research into your company so far by allowing me to conduct interviews with your staff and giving them questionnaires to fill in. I would like to make further research by conducting an observation one day in your shop to examine exactly how the sales process goes, and also to examine the stock control manager. I would like to conduct this observation on Friday 29th June, starting from when the shop opens and finishing when the last member of staff leaves the shop. I hope you will allow me to do this as it will be of great help. Thanks,

Matthew Malloy.

Accept observation

Dear Matthew,

Friday 29th June will be an acceptable time for you to conduct your observation on the shop. I will see you at 8.30 am on this day to discuss with you what you will be observing, and for you to do your observation. Thanks for your interest,

Mr Johnson.

---

Observation summary

On Friday 29th June I conducted an observation at Johnson Music to help with my research. I arrived at the shop at around 8.30am when the shop was opening. I met with Mr Johnson, and we discussed the areas of the shop that I would be observing throughout the day. One of the things I wanted to do in my observation was to watch the stock control manager conduct his job. I wanted to watch him order products in and to see him checking stock to see the procedure involved. Here is the schedule we decided on:

9.00am – 10.30am: Watching sales in the guitar department of the shop.

10.30am – 12.00pm: Watching sales in the bass guitar department of the shop.

12.00pm – 1.30pm: Watching sales in the amplifier department of the shop.

1.30pm – 3.00pm: Watching the stock control manager.

Here is a summary of what I observed during the day:

9.00am – 10.30am: Watching sales in the guitar department of the shop:

• There were two guitars sold during this time, and one effects pedal. The sale of the effects pedal was fairly quick, as there wasn’t as much paperwork to look through. This sale took around 3 minutes once the customer was at the counter.
• The first guitar sale took around 10 minutes. The customer informed a member of staff that they wished to purchase a guitar. The guitar was taken from the stock room. Then, the salesperson looked through paperwork to write that there was one less of this guitar left in stock. I noticed that the paperwork was very messy, as stock number on the paperwork was kept as a tally as it was always being updated. This could easily cause human error.
• The second guitar sale took around 10 minutes also, and followed a similar procedure to the sale of the first guitar.
• One thing I observed is that paperwork was all sorted into one filing cabinet. There was a drawer for guitars, a drawer for pedals and a drawer for amplifiers. Guitars/bass guitars were all stored in one drawer. This can make finding things very confusing. It would be much more organised if they were kept separately.

10.30am – 12.00pm: Watching sales in the bass guitar department of the shop:

• During my observation there was only one bass guitar sold. However, this sale took considerably longer than the sale of the guitar.
• The customer informed the member of staff that they wished to purchase the bass guitar. The member of staff then found the product for the customer.
• The member of staff then had to go upstairs to the guitar department where the paperwork was kept. The salesperson handled the paperwork upstairs, whilst the customer was left waiting downstairs. The salesperson then returned and allowed the customer to pay for their product and leave with their bass guitar. In total, this sale took around 15 minutes.

12.00pm – 1.30pm: Watching sales in the amplifier department of the shop:

• There were three amplifiers sold during my observation of the amplifier section of the shop. I chose to observe the sales of bass guitars. This is because the paperwork is all kept in a cabinet in the upstairs and all of the bass guitar amplifiers are downstairs. Therefore sales of bass amplifiers would be more problematic than regular amplifiers would be.
• My experience in watching the sales of the bass amplifiers was similar to that of the bass guitar sales. The customer would inform the member of staff that they wished to purchase the amplifier. The member of staff would then go upstairs leaving the customer downstairs, and the customer would then leave with their product. Each sale took around 10-15 minutes.

1.30pm – 3.00pm: Watching the stock control manager:

• When I started to observe the stock control manager he was counting stock of guitars. The first thing he did was get a batch of paperwork from the cabinet where it was all kept. He would then go through each document, checking the stock levels and then counting the actual stock in the stock room. This process, to me, seemed quite time consuming and didn’t serve much purpose other than making sure nothing had gone missing or checking whether any members of staff had forgotten to update paperwork when conducting a sale.
• It took the stock control manager around 1 hour to check the stock of all of the guitars in the shop.
• As he did this, he made a note of guitars that had 1 guitar or less left in stock. This was for his next job of the day, which was ordering in new stock.
• The stock control manager went through the list of products that needed ordering in, rang the manufacturer saying how many were needed.
• He then filed the paperwork back into the cabinet.
• During my observation some new stock arrived that had been ordered previously. The stock control manager checked the product name, and searched through documents to add to them that there was more of this product now in stock.

By 3.00pm I had observed most aspects of the shop, and my observation was complete.

I will now e-mail Mr Johnson to see if he will allow me to do an analysis of the current documentation used in the shop.

---

Documentation

Documentation proposal

Dear Mr Johnson,

Thank you for allowing me to observe your shop for a day. It has been very useful for me in my research. In order to complete my research into the way your shop is currently running, I would like to look at some of the documents you currently use to store information about your products. I would appreciate it if you would allow me to pick some up from your shop on Monday 2nd July at around 5.30pm. Please write back to tell me if this time is suitable for you or not.

Matthew Malloy

Accept documentation analysis

Dear Matthew,

I am writing to you to tell you that the time you proposed will be suitable for you to pick up the documentation that you wanted.

Mr Johnson

E-mail to say thanks for allowing me to conduct research

Dear Mr Johnson,

Thank you for allowing me to conduct research into your shop. It has been very useful to me with my project. I will write back to you soon to tell you of the progress that has been made. Thanks,

Matthew Malloy

---

Johnson Music – Stock Item Sheet

Name of product: _________________________

Product ID: ______________________________

Produce type: ____________________________

Price of product: £ ________________________

Additional information on product:

_____________________________________________________________________________________________________________________________________________________________________________________________________________

Number of product left in stock:

---

Johnson Music – Items out of stock

When an item goes out of stock, please add it to the list of items here so that it can be re-ordered in.

---

Documentation summary

The first document stores all the information about an item that the company have in stock. It is initially filled in by the stock control manager, but the document can be accessed by anyone who works in the shop because stock levels constantly need to be updated when an item is sold, or when more come into stock. If one of these documents are lost, a new one can be filled in by someone other than the stock control manager, however to check how many are left in stock, the stock control manager is needed. From what I gather, this system is not very organised. The main problem with what I can see on this document is how the number left in stock is stored. There is a big box. On one of these documents that is in use, this box is very messy due to old stock levels being scribbled out and new numbers being wrote in. This can get very confusing for whoever has to update it, and could result in more time being added on to the time of sales. If the stock levels were stored in a more organised way, then sales would potentially run a lot smoother.

The second document is also to be used by everyone who works in the shop, but the stock control manager gets the most important use out of it. When a member of staff is updating stock levels during a sale, if the number left in stock gets to zero, then the member of staff must write the product ID and the name of the product on this document. At the end of the day, the stock control manager uses this document to contact Ibanez to order more products in that have gone out of stock. A problem with this is that a member of staff may forget to write the name of a product down on the list, which would result in the product not being ordered in again until levels of stock are counted manually by the stock control manager.

---

Data flow diagrams

Before I can make any decisions on the solution to the current problem, I need to look at all the processes that are currently done, because the new system will be based around how the current system works. Using data flow diagrams I will need to describe how the current system works.

Level 0 DFD (context diagram)

---

Level 1 DFD

---

Level 2 DFDs

DFD 1 – Purchase guitar

DFD 2 – Purchase bass

---

DFD 3 – Purchase amplifier

DFD 4 – Purchase pedal

---

DFD 5 – Check stock levels

---

Statement of the problem

Through my observation, interviews, documentation and my questionnaire I have gathered enough information to find out what the main problems are with the current system. I have obtained the following information from these methods:

Very time consuming

Through every method of fact-finding I have gathered that the current system makes every aspect of the system very time consuming. It is time consuming to conduct a sale in the shop, it is time consuming to update stock levels in the shop and it is time consuming to count the levels of stock in the shop. There is far too much paperwork involved which is the main thing that slows processes down. The salesperson has to locate records of stock in order to conduct a sale. They then have to update the record. The stock control manager has to go through every single recorded, updating each item in stock as he goes along. The records aren’t very well organised which also adds to the time it takes to do each job.

Records are lost easily

Some information I found from interviews was that, due to the very manual way of keeping track of stock, records are easily misplaced and it is very time consuming to replace them. The stock control manager has to go through stock, counting the number of the items that are left in stock, and then has to fill in another stock sheet for the item that the previous one was misplaced for.

Insecure method of storing records

From my observation, I gathered that records are stored in filing cabinets that are kept behind the counter. This is very insecure, because if a member of staff wasn’t present at that time, anyone could go behind the counter and take documents, edit them or destroy them. A much more secure system would be on a computer that was password protected. This way, only authorised members of staff would be able to access records and edit them. Also, it would be much harder to lose records if they were stored on a computer system.

---

Possible solutions

From my visits to the music shop and through all my methods of fact finding, I have been able to come up with a list of three possible solutions to improving the system. Here are some descriptions of possible solutions and their advantages and disadvantages:

Improving the current system

This solution would involve keeping the current system, but improving on it to make it more efficient. It could involve having the employ new staff to keep track of all paperwork, and to make sure it is constantly in good order so that when current staff are trying to locate a specific file, they are able to do it quickly. It would also involve filing every different type of paperwork separately. For example, at the moment, all bass guitar and guitar paperwork are filed together. The logical solution to this would be to add a filing cabinet downstairs, and to file all guitar paperwork there (downstairs where guitars are sold) and to file all bass guitar paperwork upstairs (where bass guitars are sold). Other products such as amplifiers and pedals would be files separately to guitars. This may involve having to employ more than one person to keep track of filing.

Features

- All current features

- System currently more organised than present system

- Completely paper-based

Advantages

An advantage to improving the current system is that it would probably be the cheapest possible solution. It involves no new equipment being bought, and the only cost would be employing new staff. Another advantage would be that as it is the most similar to the current system, it wouldn’t take a lot of work to get used to the new system. It would be the easiest solution to implement and it would also be the quickest.

Disadvantages

Despite this being the quickest and easiest solution, it would not be the most efficient, as once the system is in place, the improvements made on the current system would not be as vast as they would be from other solutions. It would also involve employing more staff, which isn’t really necessary.

To use a computerised database

This solution would mean eliminating most paperwork, and adding all products to a computerised database on a piece of software such as Microsoft Access. The database would store the information that the store currently has on all products in records saved on to the computer. Information on each type of product would be stored in separate tables, and queries and reports would be used to find information about products when it is needed. Relationships would be used to link the tables together.

Features

- Forms for data viewing and entry

- Queries for filtering data

- Reports for printing and archiving data on paper

- Referential integrity

- Relationships

Advantages

An advantage to this is that it would probably be the most efficient solution to this. Once it has been put into place then it wouldn’t take much training for the staff to be able to use and the staff would be able to quickly locate records stored on products and customers. It would make life a lot easier for all of the staff members.

Disadvantages

A disadvantage of this is that it would cost more than the previous solution, and the fact that it would actually require some staff training for them to be able to use it. It is also a big change from the current system so it may take some getting used to for the members of staff that use it.

To use custom built software

This solution would involve paying a company to write a piece of software that will do specifically what the shop would need, or the shop employing as programmer to write the piece of software. It would do exactly what is needed from it and would really be the ideal solution once it has been put into place.

Features

- Does exactly what it is designed to do

- No excess features

Advantages

It would do exactly as needed, and wouldn’t have any useless features that the staff wouldn’t use. It would solve all problems with the current system.

Disadvantages

When it needs updating, it would require time and more money for the shop to employ a programmer to update it. It is also a very expensive solution and is not the most feasible. It would take a lot of time and money, and would require staff training. It is not really necessary for a shop such as Johnson Music.

---

Chosen solution

Looking at the advantages and disadvantages of each possible solution, it seems that the most feasible solution would be to create a computerised database to keep track of the shop’s stock. Improving the current system would not really improve the system enough to make significant difference to the time taken during a sale, and it wouldn’t make the stock control manager’s job much easier as it still involves a lot of paperwork. Creating a piece of custom built software wouldn’t be feasible for a shop like Johnson Music as it would cost a lot of money, take a lot of time, and a much easier solution could be found in creating a database. The piece of software that the database will be created on is Microsoft Access 2007. This would mean that it can link in with other Microsoft Office software such as Microsoft Word.

There are specific features that are in Microsoft Access that have influenced my decision to choose this as my solution to the problem. One of these features is the query feature. This will allow the user to find information almost instantly at the click of one button. The query feature filters the contents of a table to show only the relevant information. Queries are extremely customisable, and can even be used to archive data, therefore they are very useful.

Another feature that I will be using is the relationship feature. Key fields can be linked to other tables in order for them to share data. For example, the data entered into a table about sales will be linked to the data in the customers table and the products table. Another feature that is part of the relationship feature is called referential integrity. This means that data has to be present in the key field in order for it to be valid. In other words, an item that does not exist can’t be sold.

Reports are another feature that is included in Microsoft Access. Reports can display the results of a query in a printer-friendly form. This allows paper archives to be created, and also allows for data to be viewed without having to access the main system, as may be needed by the stock control manager when ordering new stock.

Microsoft Access allows for forms to be created. This means that data entry can be customised, and viewing data can be easier than viewing it in a table. The end-user will not be an experienced Microsoft Access user, and therefore they may find it much easier reading and entering data into forms rather than tables.

---

End-User Requirements

We now need to decide on the end-user requirements of the system now that the solution has been decided. To do this I need to know what features the user (the staff at Johnson Music) requires from the system and what hardware and software the system will need to meet the user’s requirements. To find out what the end-user expects from the system I will need to meet with them to discuss this, as I may not know everything that the user wants from the system.

I have e-mailed the owner of the shop and set up a meeting with a couple of members of staff to get ideas from them about their requirements from the system.

Summary of meeting with staff from Johnson Music:

Quantitative requirements

One point that was discussed in the meeting was the deadline that needs to be met for the system to be put into place. A deadline needs to be made because the shop may need to be closed to put the system in to place, and to train the members of staff to use the system. Once the database has been designed, computers need to be installed at the tills in the shop, one upstairs and one downstairs. The computers need to be connected to each other so that when the database is saved it is saved on both computers, so that both have the most up-to-date versions of the database so that no mistakes are made. The software needs to be installed on the computers and then records on all stock needs to be added into the database. Stock needs to be counted so that it can be saved on the database. Then staff need to be trained to use the database. This is a quantitative requirement.

The database needs to be able to be able to store enough records for all of the different items of stock, and it also needs to be able to store enough records for more items of stock to be added into the database. The minimum number of records the database needs to hold will be around 75. This is a quantitative requirement.

Qualitative requirements

Stock is one of the most important fields in the database because the database will be a stock control system. When stock gets low, the system needs to alert the user that more stock needs to be ordered in. This is a qualitative requirement.

Queries need to be created to find items that need to be ordered back into stock, and reports need to created to show the user which items there are, so that the user will be able to see which ones there are quickly and easily. This will make the stock control manager’s job a lot easier. This is a qualitative requirement.

Backing up the data would also be a good idea. The shop currently has a problem with stock sheets getting lost and stock constantly having to be recounted, so if the data was backed up, then any loss of data from the new system would mean the back-up can be restored and data will be regained again.

Similarly, data can also be archived so that when information on previous sales needs to be found then the user can search through an archive. This would be useful when creating statistics about sales, comparing the shop how it is doing at present with how it has been doing in the past. This is a qualitative requirement.

Validation checks are also important so that only correct information can be entered into the database. This will minimise human error when entering information into the database. This is a qualitative requirement.

---

Requirements specification

Before I design the database, I need to clearly list my aims for the new system. This is the requirements specification:

• To complete the new system by the deadline agreed
• The database will need to be able to store enough records
• The system will need to alert the user when stock is low or has run out
• Data must be backed up to minimise loss of data and to reduce the amount of work if data does get lost
• To find information about an item quickly
• Generate a unique ID for each customer entered into the database
• Generate a record for customer details entered into the database
• Produce an error if the customer already exists in the database
• Show the new customer table when a new customer has been added
• Show the new customer table when a customer has been deleted
• Generate a unique ID for each product added into the database
• Generate a record for product details entered into the database
• Show the new product table when a product has been added
• Show the new product table when a product has been deleted
• Generate a unique ID for each staff member added to the database
• Generate a record for each staff member added into the database
• Show the new staff table when a new staff member is added into the database
• Show the new staff table when a staff member is deleted from the database
• Generate a unique ID for each sale that takes place
• Generate a record for each sale that takes place
• Show the new sales table when a sale has been added into the database
• Update the stock levels for the item purchased after a sale
• Produce an error message if the customer doesn’t exist
• Produce an error message if the item isn’t in stock
• Correctly archive data from the sales table

---

Software and hardware requirements

There are particular software and hardware requirements that will have to be met in order for the solution to work properly.

Software requirements

The first software requirement is that it must have a common user interface, so that the user can navigate through the database quickly and easily. To improve ease as much as possible and to make the database look more professional, the interface must be consistent and clear throughout.

Also, the software must be easy to learn to use, and then be easy to use.

The software should also perform validation checks when data is entered in by the stock control manager or any other member of staff. This will minimise human error.

When queries are run, the output of the query should be shown clearly in a report.

Hardware requirements

The system will need to meet specific hardware requirements to run properly and with decent speed.

To run Microsoft Access 2007, the operating system would have to be Windows XP or above. In this case, Windows Vista is being used as it is the most up-to-date operating system. In order to run Microsoft Access 2007 on Windows Vista the system will need at least 512 MB of RAM to run Vista, and then a further 8 MB for Microsoft Access. This means that we will be looking at around 256 MB minimum RAM for the system.

It requires 170 MB of hard disk space, or a total of around 2 GB hard disk space for the Microsoft Office package.

The processor speed that is required is 500 MHz or more.

Peripherals such as a mouse, keyboard and printer will also be required.

The interface that will be used will come with Windows Vista.

The system will also require the necessary pieces of hardware in order to network two computers together.

The system is now ready to be designed.

---

Project feedback form

Student name: ________________________________

Coursework title: ______________________________

End user name: ________________________________

Stage: ________________________________________

Comments:

Signed: ______________________________                Date: _______________________

---

Appendix

This appendix contains the original letters sent to and from Mr Johnson and I. It also includes a sample of filled in questionnaires.