Supermarkets

1. Introduction

Supermarkets sell food and other household products to customers. Most supermarkets are very large stores which stock a wide range of products. Customers select the items that they want to buy from shelves as they go around a store. After a customer has selected the items he wants to purchase he takes them to a till where he must pay for them.

A supermarket needs an information system which can :

1.	Accurately calculate the cost of a customer's purchases.
2.	Try to ensure that the store does not run out of products that customers want to buy.
3.	Take the payment for goods from customers.

In most areas of the country customers can choose to shop at several different supermarkets. Therefore the supermarket owners must compete with each other to attract new customers and to keep their existing customers.

2. Why Computerise ?

A computerised information system can automate many activities that must be carried out in a supermarket.

Function	Why Computerise?
Stock Database	A computerised information system can store information about stock levels in a database. These can be automatically updated using transaction processing as items are sold. The database can automatically produce orders for items which will run out soon to ensure that they are reordered. These could be sent over the Internet or by FAX to the companies that supply the supermarket.
The Till	Each item has a barcode which is scanned at the till to identify it. The price of each item can then be looked up in the stock database which is connected to the till by a network. The till can automatically calculate the total price of a customer's shopping and print an itemised receipt.
Collecting Payment	The tills in the supermarket can be linked by a WAN to the computers that banks use to store customer account details. Using a debit card money can be transferred automatically from a customer's bank account to the supermarket's account to pay for goods.
Collecting Information About Customers	If a loyalty card system is set up then the supermarket can collect information about its customers as they shop. This can be used for marketing or sold to other companies.

3. Stock Database

A supermarket will store information about the products that are for sale as a file in a database. Other information such as supplier details can be stored in other files in the database if a relational database is used.

Structure of the Products File

Here is a small exert from the products file in a supermarket stock database :

Product Code	Item Name	Stock Level	Re-Order Level	Order Quantity	Price	Category	Supplier Code
100200019292	Spicy Pizza 12"	123	100	200	Ł2.49	FR	PC
100212422323	Canned Tomatoes 400g	742	500	1000	Ł0.24	TV	BW
102313538763	Frozen Peas 1lb	41	30	50	Ł0.89	FR	FV
403244976252	Freshly Squeezed Orange 1L	32	10	25	Ł2.59	JU	JL

The purpose of each field in the file is listed below :

Product Code : This is a unique code which identifies each product. Because each product has a different product code, it is used as the key field for this file.
Item Name : A brief description of each item is stored. This can be used to identify the item and can also be printed on till receipts.
Stock Level : The quanity of the product that is currently available at the store.
Re-Order Level : The minimum quanity of the product that should be available to ensure that the store does not run out of it. If the stock level falls below this figure then more on the product should be ordered from the appropriate supplier.
Order Quantity : The quantity of this product to order from the supplier when more is needed.
Price : The price that the customer will be charged for each unit of the product he purchases.
Category : A code identifying the type of product. e.g. FR = Frozen, TV = Tinned Vegetables
Supplier Code : A code identifying which company the product is supplied by so that the product can be reordered from the correct source.

Information in the category and supplier code fields is coded to speed up data input, reduce storage space and make reports more compact.

Updating the Products File

The products file can be updated for several different reasons. The most common ones are :

Reason	How Updated
Item Sold	Whenever an item is sold the "Stock Level" will be automatically reduced by a computerised till.
Delivery	When new items are delivered to the supermarket, the "Stock Level" field of each delivered item will be increased because the supermarket will now have more of the item. This change will be made manually by the person who accepts the delivery.
Change in Details	Sometimes an item's details will change. For example the price of the item may go up or the size of the can that the product is supplied in may change. These updates will be carried out manually by a computer operator.
Stock Check	Periodically a worker will count the quantity of an item that is actually available on the shelves in the supermarket. If this differs from the "Stock Level" listed on the computer then the database field must be updated to reflect the actual stock level.

Supermarkets need up-to-date information about the goods they have sold and the goods that they currently have in stock. Therefore transaction processing systems are used, with all updates to the stock database taking place very quickly.

Other Files

The products file will be the most important file in the stock database. Other files could be used to store information such as :

File

Purpose

Possible Structure

Links To Other Files

Suppliers File

Stores information about each supplier that the company purchases items from.

Field Name

Supplier Code

Supplier Name

Supplier Address

Supplier Phone No

Linked to the product file by the "Supplier Code" field.

Categories File

Stores information about the different categories of product that the supermarket sells.

Field Name

4. Computerised Till

After a customer has selected the items that he wants to purchase he takes them to a till to pay for them. Modern tills in supermarkets are actually small computers which are connected together on a local area network. They are known as Electronic Point of Sale (EPOS) terminals.

The network is used to connect the tills to the computer which stores the stock database.

The Payment Process

When an item is sold its product code is entered into the till (EPOS terminal). The product code is usually printed on the item as a number that a human can read and as a barcode which can be scanned using a barcode reader. The information included in the barcode for a product is country of origin, manufacturer and item code. Sometimes a barcode will not scan so the code must be typed on a small keyboard.

The entered product code is validated using a check digit that it contains to reduce the likelihood that the product code has been typed or scanned incorrectly.

The code is then looked up in the stock database on the store computer to find the product's price and name. If the product code has been successfully scanned and looked up then the till produces three outputs :

The name and price of the product are displayed on a small LCD screen.
The name and price of the product are printed on an itemised receipt for the customer.
An item accepted signal (usually a beep) is produced so that the till operator knows the sale was recorded.

The products file in the stock database is updated to show that the item has been sold. The stock level for the product will be reduced.

When all of the items that a customer has chosen have been recorded by the till, the total price of the purchases is calculated and printed on the receipt.

Items Sold By Weight

Some items, such as fresh fruit and vegetables are sold for a certain price per kilogram. These items do not have barcodes printed on them. To find the price per kilogram of an item that is sold by weight the checkout operator must either :

manually type a product code which he has remembered, or,
select the product from a menu on a touchscreen.

The price per kilo is then looked up in the stock database. The items are weighed on a pair of electronic scales and the computer system automatically calculates the correct price for the item and prints it on the receipt.

Methods Of Paying

A customer can usually pay for his purchases with cash, by cheque or using a debit or credit card. If the customer chooses to pay with a debit or credit card then the till will establish a connection with the computer at the customer’s bank to make an EFTPOS transaction.

System Flowchart

Most supermarkets use a transaction processing system to record sales. This means that the products file in the stock database is updated very shortly after each sale takes place. The operation of the sales process is shown in this system flowchart :

A backup copy of each sale will be stored in a transaction log file so that the data can be recovered if the products file is corrupted.

Advantages Of Using Computerised Tills

The use of computerised tills instead of manual tills brings benefits for customers and the owners of a supermarket. Some of these are :

For Customer

For Store

Receipts can include the names of the products that have been purchased as well as the prices.
The checkout operator can process sales more quickly as item prices are automatically looked up when a barcode is scanned. Therefore queing times are reduced.
The likelihood of an incorrect price being charged is reduced. This is because prices are looked up in a database instead of being read from price stickers on items and typed manually.

Processing customers' purchases at a till is speeded up. This means that each checkout operator can process more sales, reducing the cost of running the supermarket.
Because the tills are linked to a stock control system an accurate record of the stock levels can be kept. This will reduce the likelihood of the store selling out of an item and losing potential sales.
Each till can calculate how much money has been spent at it during the day. This can be used to check that no money is missing from the till at the end of each day.

5. Stock Reordering

When the stock level of an item becomes low, more of the item needs to be reordered by the supermarket. A supermarket will reorder more of an item before the item is completely sold out because ordered items may not be delivered to the supermarket until a few days after an order is placed.

This is a small exert from the products file in a supermarket stock database :

Product Code	Item Name	Stock Level	Re-Order Level	Order Quantity	Price	Category	Supplier Code
100200019292	Spicy Pizza 12"	123	100	200	Ł2.49	FR	PC
100212422323	Canned Tomatoes 400g	226	500	1000	Ł0.24	TV	BW
102313538763	Frozen Peas 1lb	12	30	50	Ł0.89	FR	FV
403244976252	Freshly Squeezed Orange 1L	32	10	25	Ł2.59	JU	JL

The Canned Tomatoes and Frozen Peas need to be reordered. This is because the stock level of these products has fallen below the re-order level at which an order for a new delivery should be placed.

Using Queries and Reports to Print Orders

A database query can be used to automatically select the products which need to be reordered. The criteria for the query will need to be :

Stock Level < Re-Order Level

The items found can be presented neatly as a report listing all of the items that need to be reordered. Here is an example reorder report :

The report only lists the products which need to be reordered. For each product the Product Code, name, quantity to order and supplier code is listed.

A More Sohisticated Printed Reordering System

If a relational database was used then the query could also look up the names and addresses of the suppliers in a supplier file and include these on the report.

A more sohisticated report layout could group together all of the products which need to be ordered from one supplier and print a page for each different supplier. Here is an example reorder report which has been produced to send to Frozen Vegetable Supplies Ltd :

The report could be posted to the supplier of sent by FAX.

Electronic Reordering

An alternative to sending printed orders to suppliers is to send orders electronically. This could be achieved using email or a custom written computer program. Orders could be transmitted from the supermarket to the supplier using a modem and telephone line. This would speed up the ordering process, meaning that goods could be delivered more quickly.

An electronic ordering system is particularly likely to be used if the supermarket is one of a group of supermarkets in a chain. In this case, most of the supermarket's products woud be ordered from a central warehouse which was owned by the same company as the supermarket.

Setting the Reorder Level

An automatic reordering system will only operate properly if the reorder levels are set appropriately to ensure that the supermarket is resupplied with items before it runs out of them. The appropriate reorder level for an item will depend upon :

How many units of the item are usually sold in a day.
How many days it will be between the supermarket placing an order and the new stock being delivered.

For example, suppose a supermarket sold on average 150 bottles of orange squash a day and it took three days for a new supply of orange to be delivered. Then to ensure that the supermarket did not run out of orange the reorder level would need to be at least :

3 * 150 = 450

In reality the reorder level would probably be higher than this in case the demand for orange was unusually high whilst the supermarket was waiting for a delivery.

The appropriate reorder level for each item could be set manually by the supermarket's manager, based on his experience. Alternatively it could be calculated by the computer, based on previous sales information and delivery times.

Setting the Order Quantity

The order quantity, i.e. the amount of the item that will be ordered, should be enough to keep the supermarket stocked for a number of days. However most supermarkets do not like to have too much of an item in stock as only limited storage space is available for goods.

6. Stock Check

The stock level field in the stock database stores a record of how many units of a particular item should be in stock at the current time. This level is adjusted automatically :

When items are sold the appropriate stock levels are reduced by the till.
When new items are delivered the appropriate stock levels are increased.

However stock can sometimes be added or removed from the supermarket without this being recorded on the database. For example :

Reason	Explanation
Theft	An item could be stolen from the store. This would be removed from the stock, but the database stock level would not be decreased.
Checkout Operator Error	When goods are sold they are removed from the stock database as their barcodes are scanned. If a customer purchased a large number of items then the checkout operator could easily forget to scan one of the items by mistake. The missed item would be removed from the stock, but the database stock level would not be decreased.
Delivery Error	The person who accepts the delivery of new items at a store could accidently record the number of items or type of items that have been delivered incorrectly.

The effect of these errors would be that the actual stock level is not the same as the stock level in the database. If the actual stock level was below the stock level in the database then the store could run out of an item before it was reordered.

To reduce the likelihood of this happening a stock check will be carried out periodically. A stock check means that the items that the supermarket sells are counted and the actual stock level of each item is compared to the stock level in the database. If the two do not match then the stock level in the database will be changed to the actual stock level.

It takes a lot of work by members of staff to carry out a complete stock check so this is not done very often, if ever. However stock checks on a small range of items within a store will be carried out regularly.

The Stock Check Device

Stock checks are carried out by a person using a small handheld computer with a built in barcode scanner, keyboard and Liquid Crystal Display (LCD). The computer contains some RAM which is used to store a copy of the information about some of the products, taken from the stock database. The stock check process works like this :

1.	Before the stock check takes place the information about the products to be checked is copied from the stock database into the RAM in the handheld computer.

2.	For each item that is to be checked : The member of staff scans the barcode on the item that he is counting. The computer displays the name of the item and how many units should be in stock, according to the stock database. The member of staff counts how many units of the item are actually on the shelf. The number of units that are actually on the shelf is typed on a keyboard and the stock level stored in the handheld computer is changed to the real stock level, if they differ.

3.	After all of the items have been checked, the handheld computer is connected to the computer storing the stock database and the stock levels on the database are updated.

Any significant differences between the expected and actual stock level will be noted by the computer so that the reasons for them can be investigated.

The handheld computer that is used to collect the information about stock levels is sometimes known as a data logger.

7. Loyalty Cards

In most areas of the country customers can choose to shop at several different supermarkets. Therefore the supermarkets must compete with each other to attract new customers and to keep their existing customers.

Many supermarkets use a loyalty card scheme to encourage their customers to always shop with them. Under a loyalty card scheme :

A customer enters his or her personal details onto a form and is issued with a loyalty card.
The card looks like a debit card and contains a unique customer identity number which is stored on a magnetic stripe.
Every time the customer shops at the supermarket he presents his card to the checkout operator who swipes it through the till.
For every pound the customer spends he or she earns a number of points.
The number of points a customer has is stored on a database. This is updated autmatically by the till every time the card is used.
The customer can exchange the points for a discount on future purchases or other items.

Using Loyalty Card Information For Advertising

Whenever a customer uses his loyalty card to shop the supermarket will collect information about how much he spends, where he shops and what products he buys. This information will be used to send targetted advertisements to customers. For example a customer who buys baby food regularly might be sent an advert for childrens clothes or toys.

Sending adverts and offers to only those people who are likely to be interested can significantly reduce the amount of money that a company has to spend on advertising its products. Therefore information about customers' shopping habits is very valuable.

The supermarket could send targetted advertisements out itself or sell information about its customers to other companies. The Data Protection Act controls what use companies can make of this information.

Advantages and Disadvantages of Loyalty Cards

	Advantages of Loyalty Cards	Disadvantages of Loyalty Cards
For Customers	Customers can earn a discount by using a loyalty card when shopping.	Some customers object to supermarkets collecting information about them and using or selling this. They believe it is an invasion of their privacy.
For Supermarkets	Supermarkets believe that customers with loyalty cards will shop at their stores more frequently and are less likely to try another supermarket.	A supermarket must set up new computer systems and employ staff to run its loyalty card scheme. The supermarket will have to pay for this.

8. The Future

Self-Scanning

During busy times queues build up at supermarket tills and customers may have to wait a long time to pay for the goods that they have purchased.

One method that can overcome the problem of long queues is self-scanning. In a supermarket which has a self-scanning system customers scan the barcodes of products as they put them into a trolley using a mini scanner on the trolley.

Ths scanner is connected to the supermarket stock database by a LAN using radio waves. As the products are scanned the total price of the shopping is calculated using the prices in the database. Once the customer has finished shopping the cost of the goods is transferred from his bank account to the supermarket's.

Supermarket employees make occasional checks on the contents of trolleys to ensure that customers are not tempted to put items into their trolleys without scanning them. Before customers are allowed to self-scan they must register with the supermarket so that they can be identified.

Internet Shopping

Some supermarkets now offer their customers the opportunity to shop from home using the Internet. A shopper can log on to a supermarket web site and select the products that he wants to purchase. The supermarket will then deliver these items to him at an agreed time. This is an example of electronic commerce (or e-commerce).

Advantages of Internet Shopping	Disadvantages of Internet Shopping
You can shop whenever you want to without having to go out. You do not have to queue up at a till to pay for the goods you buy. You can build a regular "shopping list" of goods which you want to buy every week and these will be delivered to you automatically.	Most supermarkets charge a delivery fee to bring the items to your home. You can not see the goods before you purchase them. This may be especially important with fresh produce such as vergetables and fruit. Some special offers which are available in store may not be available on the Internet. Some people are concerned about the security of making purchases via the Internet.

GCSE ICT Companion 04 - (C) P Meakin 2004