Figure 13 Customer entity relationshipsBusiness Systems Analysis & Design – Bilston Glen Distillery

As part of this module, we were given a scenario of a Scottish distillery to carry out a hard systems analysis to improve the business. Bilston Glen Distillery follows in the tradition of Highland distilling in that it uses pot stills and matures its whisky in old sherry casks. In order to meet new food handling requirements, a permanent record of use of each cask is to be maintained.

Using hard systems methodology, this report will analyse the business processes carried out by Bilston Glen Distillery, particularly in relation to the lifecycle of the casks and propose an initial design of an information system to support the business needs.

This will be carried out by looking at the following methods:

  • Data Flow Diagrams
  • Entity Life Histories
  • Logical Data Structure
  • Data Dictionary

Data Flow Diagrams

Level 1 DFD

Level 0 Data Flow Diagram


We can first look at the overall system using a Level 0 data flow diagram, this shows the data system as a whole and it emphasizes the way it interacts with external entities.


When looking at Bilson Glen we can see that the external entities are the customers purchasing the end products of the whisky. The other external entity is the suppliers, firstly the suppliers of the casks and the suppliers of the other ingredients of the whisky production process as shown in figure 2.


The customer must be able to view details of the products available and place an order, they will receive an invoice for the order along with the order and they will make a payment to the distillery. From the supplier Bilston Glen will place an order for supplies and make a payment to the supplier. They will receive an invoice for the supplies and the delivery of the supplies.

Level 1 DFD

Level 1 Data Flow Diagram Bilston Glen Distillery


The next stage is to create the Level 1 Data Flow Diagram as shown in figure 3. This highlights the main functions which are to be carried out by the system. The diagram shows the processes starting with the customer. The level 0 diagram is expanded to show the further main business processes of the production of the whisky and the monitoring of the casks, as it is required that a record of the testing is kept to meet food handling regulations this is a core process within the business. The tests to be logged are the initial logging of the casks, daily test, monthly test, annual test, final test, the cask is then inspected before being reused, repaired or disposed of.

The other main processes are the handling of the customer orders, ordering of supplies, managing of stock (both the stock to be sold to the customer and stock of supplies for production) and the keeping of accounts.

The main business processes are:

  • Handle customer orders
  • Stock control (supplies and products in stock)
  • Order supplies
  • Cask Management
  • Filling
  • Distilling
  • Quality Control
  • Emptying
  • Keep accounts

The main data stores required for the businesses processes are:


  • Customer details
  • Customer orders
  • Product information
  • Production supplies
  • Supplier Orders
  • Suppliers
  • Cask information
  • Filling information
  • Batch Information
  • Daily Checks
  • Monthly Checks
  • Annual Checks
  • Post Production Checks



Level 2 DFD  – Handle Customer Orders

Level 2 DFD Handing of Customer Orders


One of the core processes of the business is the handling of customer orders as illustrated in figure 4. When looking at the process of the ordering in two main functions as shown below in figure 5.


Function 1 – Process Order Receive order

Issue invoice and goods

Update stock

Function 2 – Process Payments 1. Receive payment
2. Update accounts

Figure 5 main functions of order handing


When placing the order the customer must be able to access the available products from the stock in the warehouse / stock room. Once the order has been received and approved the invoice is used and the goods are dispatched, the stock must then be updated. The other side of the process is the handling of customer payments, once the payment is received the accounts need to be updated to reflect the payment.


Level 2 DFD – Cask Management

Level 2 DFD showing cask management process

The process of cask management is integral to the business. As can be seen in the data flow diagram in figure 6 the cask information is logged into a data store when the cask is received from the supplier. The cask is then filled; it would be useful to the business process to keep a log of which casks have been filled and which have not so this could be logged into a data store. Once the cask has been filled it is stored in a batch and the batch information must be recorded into a data store. The details of the tests must be recorded, a data store of each test type of test is the best way for this information to be logged. The cask is again tested after the whisky has been send for blending so the result and outcome of this must also be logged. A resus decision or repaired cask can do back to the start of the cask management process.


Entity Life History

Entity life history of order process

Entity Life History Order process

When looking at the order process it can be illustrated using an entity life history as in figure 7. When the customer places the order, it then begins the main body of the life history. The order can be in 2 states after being placed, valid or invalid. The order can then be amended if needed (there is no limit to the number of changes that could be made) from there the end of the order status is the order delivered or cancelled.



Entity life history cask

Entity life history cask

­­The main states in the life history of the cask can be defined as purchase, inspection, quality control, and emptying. From the cask inspection, the cask details are logged, they are filled and put into batches. The quality control consists of the daily, monthly and annual checks. When the casks are emptied the post production test is carried out and the casks can be reused, repaired or disposed of. If the casks are to be reused they are then returned to the cask inspection stage of the life history.

Logical Data Structure

Looking at the key entities a logical data structure by looking firstly at the attributes that would be required by each entity. The customer, supplier and cask entities are examined in more detail in mind map form the figures 9, 10 and 11 below.

customer mind map


supplier mind map


cask mind map


When looking at the mind map the key attributes can begin to be defined. When looking at the customer and supplier order entities it must be considered that more than one of each product could be contained within the order so an additional entity for the order line must be created and from there the total for the order can be calculated from the total of each line.

For the supplier entity this is shown in the tables stockOrderLine and pricedStockOrderLine as illustrated figure 12 below.  When looking at the customer entity this is shown as orderLine and pricedOrderLine as shown in figure 13.

Supplier entity relationships

Figure 13 Customer entity relationships



Figure 14 cask management entity relationships

The process of cask management the entity of the cask is central in the entity relationship diagram. The cask can be in one batch but the batch can have many casks. One cask can have many daily, monthly and annual checks but there can only be one final check and post production test.


Data Dictionary

A data dictionary is sued to record data about the system. The data taken as illustrated in the level 1 and 2 diagrams and entity relationship diagrams defines the system as required for the distillery. The data dictionary used to describe the cask can be shown below with the full data dictionary in appendix B.

caskInformation = *data store* {caskInformation}

caskInformation = caskSerialNumber  + capacity + dateOfPurchase + supplierName + sourceOfOrigin
wiskeyBatch = *data store* {whiskyBatch}

whiskyBatch=batchNumber + caskSerialNumber + capacity + dateOfFilling +  lengthOfMaturing

dailyCheck = *data store * {dailyCheck} daily cask details are recorded

dailyCheck= checkID + batchNumber + caskSerialNumber + dateOfCheck


monthlyCheck = *data store * {monthlyCheck} monthly cask details are recorded

monthlyCheck = monthlycheckID + batchNumber + caskSerialNumber + caskWeight  + conditionNote
anualCheck = *data store * {anualCheck} daily cask details are recorded
anualCheck = anualcheckID + batchNumber + caskSerialNumber + caskWeight + caskContent + caskCondition + sampleResults + changeNote


finalCheck = *data store * {finalCheck} daily cask details are recorded

finalCheck = finalcheckID + batchNumber + caskSerialNumber + caskWeight + caskContent + caskCondition + sampleResults + checkNote

postProductionInspaction *data store * {postProductionInspection}
postProductionInpsection = caskSerialNumber  + inspectionResult + dateOfInspection




The analysis carried out shows how Bilston Glen Distilery can take the information of their core business processes and begin to model it into a relational database system.

The implementation of the system would allow the distillery to comply with food handing regulations and maintain its clients and profitability.