Designing a Database to Manage Stock in a Clothes Shop

1.1 The problem and how I discovered it

I discovered the problem when I saw that the files of customer orders were unorganized and customer were angry for late stock arrivals. I also noticed that some of the buyers or consumers were taking this product although it was outdated.

1.2 The Business

This business is of a big company which is located in Jandia in Fuerteventura in the south of the Island. I was doing a survey on the shops as a consumer when I discovered that some of the products were from previous seasons and old. I told the manager about the problem they asked me for help.

1.3 The real User

The manager is the in charge of dealing with these types of problems. Although in this case Ms.Sara, the manager is not able to find the solution to this problem. In this case I have decided to help her and to provide a better service to the customer.

1.4 The current problem and inefficient

The current main problem is that the manager or the employees aren’t getting to know when the clothes are outdated or is from old seasons. I’m being asked to create or to find some kind of solution to solve this major problem.

There are many problems with the method that the employees are using now which is to go around and looking at other people’s clothes and see the fashion. They have been facing many problems with their method. For example:

*There aren’t as many employees as needed for this method.

*It might result a bit boring and tiring

*Can produce headaches and blurry eyesight.

*Orders may be lost.

1.5 Different ways this problem can be solved

Manual way-Write down all the customer’s orders and file them together. Also I would like to include that it would also make the in-charge’s life easier if the products were placed in a more visible and easier accessible place so that none of the products are missed out.

PC way- This method would be a lot easier because it wouldn’t include as many people as the manual way. This method consists of creating a database using Microsoft Access. While learning I discovered an all new way of using access.The employee has to introduce the date on which the customer issues the order.

1.6 Objectives

*Create main menu for easy navigation through the queries

*Create a Report to show orders taken.

*Create a Database with queries to manage orders more quickly.

* Add buttons and make sure they work so that we can see the forms easily and briefly.

* Create a new table for every order and name it after the customer.

* Include dates sold and bought

* Also include an image of the item

*Add supplier name, address and ID

1.7 Best solution

The best solution would be the PC one. It could be to create an Access database in which I would include all of the products, the suppliers, the date of manufacture and the type of product it was. This would help the owner of the business to be more organized and can provide a better service to the customers by telling them what season the clothes, in my case were manufactured in. This would attract more customers as the owner could provide important additional information like the supplier name and the product information for example the number of articles sold all over the world.

1.8 Advantages and disadvantages of PC way

Advantages

*More organization

*Better product information

*Quick research

*Easier to change, add or delete products

Disadvantages

*Difficult to create such database

*Problematic for people who don’t know how to use access

*Any miss-type may lead to serious problems

1.9 Advantages and disadvantages of manual way

Advantages

* Things are easier to find and quicker

* No worries about viruses or hackers

Disadvantages

* Can be lost or damaged easily

* Can be difficult to understand somebody else’s writing

ANALYSE

2.1 Hardware

In my solution I will need very little hardware, the basics. I will need a computer with normal capabilities, for example two gigabytes of ram, a keyboard, a mouse, a screen to display the output and a 5.1 megapixel camera to take out photos of my products.

2.2 Software

To accomplish my solution, besides hardware will also need some basic software. I will need an access, either of Microsoft office or Open Office but I prefer Open Office incase low costs are required as It is free. I will naturally also need an operating system either Windows vista or Windows XP,I prefer one of these instead of Linux or Mac OS because they are more common, easier to use and also most of the work doesn’t need any training.

2.3 Data input

Data

Type

Collection

Input

Validation

Photographs

image

Memory card or camera roll

USB

Google images

Bar-code

Information

Product

Bar-code reader

8 digits

Supplier address

Text

Phone directory

Keyboard

Phone or address directory

Product info.

Text

Supplier

Keyboard

Supplier

2.4 Data processing

fffggg

2.5 Back up and security

As the Document we are doing is very important we must always remember to save it, this could save us a lot of typing because if there is a power cut documents won’t get erased. We should remember to save our document every 5-10 minutes, also we need to save it when we change or insert something manually.

You should save it on your PC’s desktop or in ‘My Documents’ also it would be intelligent to save documents on two other or external hard disks or send it to your own e-mail.

The size of the file depends on the type, for example if it is done in access,word or PowerPoint, it would also effect the size of the document if there is fancy colored writing, outstanding writing or lots of pictures. Normally a word document of 10 -12 pages with three to four colored pictures would weigh 294 KB (302.026 bytes), this means you would be able to save three thousand more files like this on a one gigabyte pen drive.

Yes, I will set up passwords to my document incase a hacker or spammer brakes in and steals my data, this way I will be sure to have a unique solution to the problem. I can set passwords to documents by going on Tools>Options>Security and set up the password.

Design

3.4 Subtasks

1. Create Table

1.1- Open Database.

1.2- Click ‘Create table in design view’.

1.3- Insert name of columns in ‘ID’.

1.4- Select data type.

1.5- Add any descriptions needed.

1.6- Then click the icon on the top left to see it in ‘datasheet view’.

1.7- Then insert the name of the table.

1.8- Write in the data.

2. Create Form

2.1- Open Database.

2.2- Go to ‘Forms’ tab.

2.3- Click on ‘Form Wizard’.

2.4- Select tables and fields required. Click ‘next’

2.5- Select layout. Click ‘next’

2.6- select style. Click ‘next’

2.7- Name it and click finish.

3. Create Queries

3.1- Go to Queries tab.

3.2- Click ‘Query wizard’.

3.3- Select table and fields. Click ‘next’.

3.4- Select detailed or summarized query. Click ‘next’.

3.5- Select title. Click ‘Finish’.

3.6- Add any buttons. And save.

4. Create reports-

4.1- Go to Reports tab.

4.2- Click ‘Report wizard’.

4.3- Select tables and fields to include. Click ‘next’.

4.4- Select grouping. Click ‘next’.

4.5- Sort order. Click ‘next’.

4.6- Select style. Click ‘next’.

4.7- Select title and click ‘finish’.

5. Relationships-

5.1- Click the relationships button on the toolbar.

5.2- Select the tables needed.

5.3- Drag the field of one table to the field of another table to create a relationship.

5.4- Then select the type of relationship. E.g. One-to-many, Many-to-many or One-to-one.

6. Graphs-

6.1- Open form in design view.

6.2- Then click the graph icon on the toolbar.

6.3- Then draw a box for the graph. Click ‘Next’

6.4- Select the queries or tables to include in the graph. Click ‘Next’

6.5- Then select chart type. Click ‘Next’

6.6- Reorganize axis. Double click on next and then hit finish.

3.5 Test plan

Test number

Part of the system to be tested

Test data

Actual result

1

Add new record(New shoe)

Cost= -36

Not valid. Negative cost

2

Update record (t-shirt)

Name: Diesel jeans size 40 blue and black with yellow dots and a silver chain

Not valid. Field limit exceeded.

3

Add new record (New jeans)

Selling price=29.90 $

Not valid. Wrong currency.

4

Update record (Shirt)

Date bought= 29/1/2009

Valid test

5

Add new item

Name: Diesel jeans

Bar-code=00-00-00-1

Date bought:1/1/09

Date sold= 2/2/09

Cost=12 �

Selling=26

Supplier= 0-0-0-3

Valid test

6

Update item

Cost=24.90

Valid test

7

Add supplier

Name=Samsung12345678910

Not valid. Character limit exceeded

8

Update address of supplier

Address=15468793-2

Not valid= Wrong format

9

Add Supplier ID

ID= abcdefghij

Not valid. Incorrect ID. Should be in numbers for my database.

10

Add supplier

Name= Sony

Valid test

11

Add phone number

Phone number=928753357

Valid test

12

Update name

Previous name= Diesel

Current name= Diesel GC

Valid Test

13

Ticket number

Ticket number=hi

Not valid. It should be in numbers

14

Change total

Total=49.90$

Not valid. Wrong currency.

15

Update employee name

Name=Joseph Joe Thomas Smith White Kadinopolous Mavin

Not valid. Field length exceeded

16

Update ticket number

Ticket number=49

Valid test

17

Update change

Change=4.90

Valid test

IMPLEMENTATION

Test plan

Screenshot n

Test number 1-Add new record(New shoe)- This test is invalid because the cost price of the shoe is in minus this is why it is invalid.

Test number 2- Update record (Jeans)- Invalid because the name of the jeans is too long and that’s why the message dialogue appears saying that its too long. This would also be an invalid test.

Screenshot n 2

Test number 3- This test result is invalid because the wrong currency was inserted and therefore that dialogue box appeared.

Screenshot n 3

Test Number 5- This test has been accomplished because it has a valid result.

Screenshot n 4

Test number 7- Update supplier name, Valid test as the result is positive.

Screenshot n 5

Test number 13- Update employee name- Invalid result as a dialogue box appears saying that the test is too long.

Screenshot n 6

Test number 17- Add new ticket- Valid test as every field is created properly.

Test number 12- Update change field- Valid test result as all the requirements are accomplished without any problems.

Screenshot n 7

Test number 9- Update supplier name- Invalid result as no more characters can be introduced

Screenshot n 8

Test number 19- Update supplier ID- Invalid test result as a dialogue appears asking to enter a new value.

Objectives

Completed

*Create main menu for easy navigation through the queries

*Create a Report to show products bought taken.

*Create a Database with queries to manage orders more quickly.

* Add buttons and make sure they work so that we can see the forms easily and briefly.

* Include dates sold and bought

*Add supplier name, address and ID

Not completed

* Also include an image of the item

* Create a new table for every order and name it after the customer.