Terry's Turkeys

Chris Davies Computing Coursework

Introduction

I have been asked to design/ create an application, either programmed or using a relational database or using a combination of both, to replace the current manual record-keeping system for a company called Terry’s Turkeys; a small farm which supplies fresh, home-grown turkeys at Christmas. Initially, at least, there will be only one standalone workstation with a printer attached. This is to be kept locked in the farm office.

There are a number of methods of developing the new system.

Manual system. Improve the system that Terry uses at present; however this would be very tedious as invoices have to be word processed, printed and then kept in a folder for future reference.
Spreadsheet system. This would allow Terry to keep details of customers, turkeys and orders on separate sheets. The calculation of invoices would be made easier to some degree. However, it would take a long time to enter data and is less user friendly. Also the format of reports is much harder in Excel.
A purposely designed system. Written using a ‘high’ language such as Pascal. This would obviously have a lot of advantages as the system would cater for every aspect of Terry’s activities; because it’s been specifically designed for the task. However the cost of this kind of package is very high and Terry would spend a lot, if not all of the farms profits. Also it takes a long time to code some areas whereas in Access they are easily done in tables.
Access database. The new system can be developed using Access, making a relational database with different tables storing the customer, turkey and order details.

For this project a relational database (Access) will be used. This package is suitable as it has many features which can be used in developing the system, such as:

The ability to link tables via a primary key and foreign key;
The ability to set a password to prevent unauthorised access and to protect customer details.
Facilities which allow the creation of reports which can be formatted as required (labels), and previewed before printing.
The ability to create a menu by using the “switchboard” function.
Access can perform advanced queries.
Access can make forms to insert data.
Access is very user friendly.
It has a query language, which means it can perform advanced searches.
You can hide certain forms out of view.
Access can have relational tables, which are linked together. This avoids repetition in entering in data as more than one department of the system can share one bit of data.

The database could eventually be extended into an office network which will require a network interface card in the original computer. A hub could be used to link several computers together or it could perhaps have a wireless network meaning no problem with wires entering or leaving the office.

DATA REQUIREMENTS

There are many data requirements for the new database system which include the following:

Data on the turkeys, this will include unique turkey Number and the turkey’s weight.
Data on the customer, this will include customer ID, customer’s surname, customer’s forename, middle initial and telephone number.
Data of the order; which will include, order number, date of the order and requested turkey weight(s).
Data on the price per kilogram of a turkey, and data on the preparation cost.
To complete an order the requested turkey weight(s) data has to be matched to an appropriate turkey weight.

Entity-Relationship diagrams

Entity-relationship diagrams are diagrams which show the relationship between entities in a database. In order to be able to draw an entity-relationship diagram you need to know the degree and the name of the relationship. The degree of the relationship will come under one of three categories:

One-to-one, e.g. relationship between customer and order number which is unique to every person.
One –to-many, e.g. if a customer orders more than 1 turkey the relationship between order number and requested turkey weights
Many-to-many, e.g. if a customer orders more than 1 turkey the relationship between requested turkey weights and turkey weights given.

Below are examples of data entities, which are relevant to the system.

USER INTERFACE

When thinking about the user interface you should think about who the system is for, what tasks the system is performing, what environment the system will be in and whether the technology is feasible.

There will be one menu that will appear on start up giving the user (Terry) the option to add new customers and orders or view existing data by opening the various forms and reports using simple buttons. They will be arranged in a logical order of what will be used most and the order they will be used in. It will have an image, as without one the menu will look very plain and boring having only a title and buttons. The menu will have a grey and light blue background so as to be easy to look at.

Outputs

Reports

The following reports need to be included in the system: -

Order print:

When a customer places their order an order form is printed. This includes their requested weights, customer ID, forename, surname and most importantly their order number, making it quicker and easier when they come to collect their turkey(s). These details will be added into a report which will have a very simple, plain layout with the order number at the bottom in larger bold, red font, as this is the most important information on the report.

Initial Turkey List labels

Turkey labels containing just the turkey number and turkey weight will need to be printed after Terry has weighed the turkeys and entered their weights into the turkey list. They will be made using the label wizard to make a report and be printed on specific labelled paper. The turkey number will be in red coloured font as this is the primary key. The labels will be sorted in weight order making it easier and faster for Terry to stick the labels on the correct weight turkey. The turkey weight will be at the top of the label with the turkey number underneath.

Customer receipt:

The receipt will be given to the customer when they collect their turkey and will need to include the customer details: Customer ID, forename and surname so that they can be sure they have received the correct order. Also the order details: Turkey weights requested and turkey weights given so that the customer can see they have got the correct turkeys.

The main purpose of the receipt is to reassure the customer that they are receiving everything they are paying for and can see where their money has gone. Therefore the price details must ...

This is a preview of the whole essay

Customer receipt:

The main purpose of the receipt is to reassure the customer that they are receiving everything they are paying for and can see where their money has gone. Therefore the price details must be included, showing the cost of each turkey and any discount if it has been given. Lastly the total price the customer needs to pay Terry.

The receipt needs to look good as it is the main representation of Terry’s Turkeys. Therefore it is necessary to include colour and images. The font will be ‘Times New Roman’ as this looks good and is easy to read. The customer details information will be in larger font and bold so that it will stand out so the customer will notice straight away if they have the wrong receipt. The total price will be in larger font, bold and in red as this is the most important information on the receipt.

Turkey labels with details

Turkey labels containing the Turkey number, Turkey weight, customer’s names and order number will be made using the label wizard to make a report and be printed on specific labelled paper. The labels will be sorted in weight order making it easier and faster for Terry to stick the labels on the correct weight turkey, over the initial label containing the same turkey number. The turkey number will be in red coloured font as this is the primary key.

Forms

The data in the system will be entered and displayed through forms in Access rather than straight into the table where it is stored. This is because of many reasons such as:

It is easier to read from forms than tables.
Forms can display one record at a time, rather than all of them at once, as on a list, which make it harder to isolate one particular record.
Forms are better for presentation as they can have images on them and you can change the font and size.
With tables you cannot have buttons, whereas you can with forms.
Sub forms can be entered into forms.

Overall forms are better for this type of system as they are more user friendly; they look less intimidating than tables and require less work on behalf of the user. They can have buttons on them to take the user to and from different forms.

Each form will have a title to identify it to the user (Terry). The forms will not be cluttered and if they incorporate images they will have a maximum of two. This will insure that the results are easily readable and not interfered with by unnecessary clutter. The field headings will be written in a clear easily read font like ‘Tahoma’ and in size 9, making the information clear and easy to read.

There are three main forms, which are used for entering data about the customers, turkeys and the orders. Command buttons will be added to open other form, making it easier and quicker to view/enter the desired information. Each of these forms follow the same structure to make the forms consistent in presentation and familiar to the user (Terry). All the forms will have a professional sumi painting style background. This is because it is not too bright and glaring but just allows a pleasant hint of colour, allowing the writing to be easily visible.

‘Control tip text’ will be used in the forms to give extra information or instructions to the user (Terry).

Query outputs:

The results of the queries could either be displayed in a table or in a form created by a wizard. The presentation of the results would be better in a form for a number of reasons. (Explained above under the heading ‘forms’)

METHODS OF DATA ENTRY

The user (Terry) only needs to enter data into the database about the turkeys, customer and order.

Data is entered through forms in Access rather than straight into the table where it is stored. This is because the forms are a lot more user friendly and can have added bonuses such as command buttons to open other forms and match specific data entries and sub forms etc. (as explained above under the heading ‘forms’)

Also in forms you can use what is called ‘control tip text’ to make data entry easier. It provides instructions or extra information for the user (Terry) to know what needs to be entered or what the button does. On the whole, however, it is self explanatory and very easy to get used to.

The user (Terry) will have very easy access to the data entry forms. He will simply click on the appropriate button in the menu using a computer mouse, which will take him to the required form. This will not require the user (Terry) to have knowledge about opening and closing forms in a raw database. Then, once in the appropriate form, the user (Terry) will be able to use a keyboard and mouse to type in the appropriate information.

In general the user (Terry) will use the keyboard for data entry and will use the mouse for the operation of the system (e.g. buttons).

RECORD STRUCTURE

Turkey Records

The 1st table will be the turkey details.

Turkey details (Turkey number, weight of turkey, available?)

Turkey Number the Individual turkey ID, no duplicates as the same turkey cannot be sold twice.

Weight of Turkey is recorded so that Terry can see which turkey to give to the customers by matching the closest turkey weight to the weight of the turkey requested. The turkey number given is then recorded in the order form. A query could be used to search for the nearest turkey weight to the one requested by the customer so that Terry doesn’t have to spend time looking through the weights, however Terry prefers to do this himself.

Available? When a turkey is sold Terry would tick the check box to show that the turkey has already been sold. When the nearest turkey weight to the one requested by the customer is queried for it could also only search through the ones that have not been ticked. This would stop errors occurring such as selling the same turkey twice by mistake.

Customer Records

The first table will be the customer details; it needs to be separate from the other details so it can be kept safe and not accessed by anyone, due to customer confidentiality. This could be achieved by having it password protected.

Customer details (Customer ID, Order Number, Forename, Surname, Middle initial, Telephone no.)

The customer ID is the primary key as it allows easy identification of the customer to their details. The order number is the secondary key as it is also unique and can be used to search for an order. It is also the foreign key as it will be used to link the customer to their order.

The customer ID For each new customer added they will receive a unique identification to their details; it will therefore be the primary key so there will be no duplicates.

Order Number is the secondary key as it is also unique and can be used to search for an order. It is also the foreign key, as it will be used to link the customer to their order.

Forename will be added to ensure that the customer receives the correct order by giving their forename. It will have a field size of 15 as it is very rare for a person to use/have a forename longer than 15 characters.

Surname is recorded so the customer can check they have the correct order. Duplicates will be allowed as some people have the same surname. It is limited to a field size of 15.

Middle initial is noted so that if there is more than 1 customer with the same forename and surname the middle initial will most likely be different, to make sure it is the correct customer. The field size is 1 as only one letter is needed. Duplicates allowed.

Telephone number the customer’s telephone number is recorded so that they can be contacted if there are any problems, but mainly so they can be contacted to tell them to come to pick up their turkey(s).

Order Records

The third table will be the order details.

Order details (Order Number, Date of order, Requested weight, Turkey number 1, Requested weight 2, Turkey number 2, Requested weight 3, Turkey number 3,

Requested weight 4, Turkey number 4, Requested weight 5, Turkey number 5)

Order Number is the primary key as it is unique and can be used to search for an order. It is also the foreign key, as it will be used to link to the customer details.

Date of order is noted as priority is given to those who ordered their turkey(s) first.

The validation rule on the date of the order means that orders can only be placed between 1st of November and the 24th of December.

Requested weight(s) that the customer wants are recorded and must be between 5kg and 10kg, noted to the nearest half kilogram. It is a real number.

Turkey weight(s) given

Unique Turkey number(s) The number of the turkey with the most appropriate weight is given to the customer; there cannot be any duplicates as the same turkey cannot be sold twice.

Cancelled orders cannot be deleted, so if a customer wishes to cancel their order the ‘Cancelled’ tick box should be ticked.

FILE ORGANISATION

A relational database will be created where tables work collectively rather than individually. For this to happen there is a need for links between the tables. The links need to be between one key field in a table and a foreign key in another. The foreign key and primary key must ideally both be called the same thing, spelt exactly the same and essentially be of the same field type so that there is not a syntax error. Below is a list of the links used between different tables: -

Order number in the Order Table and order number in the Customer Table so that the order details and customer details can be kept separate, as the customer details are confidential.
Turkey number in the turkey table and turkey number in the order table, so that the given turkey weight can be looked up and entered from the turkey table.

Turkey list form construction

A form will be made including every field from the turkey list table,

(Turkey number, Weight of turkey, Been Ordered),

to enter the turkey details into the database.

It will be displayed in a list, so the data can be all entered/ viewed quickly.

Customer Details form construction

The customer details form will be made using every field from the customer details table:

(Customer ID, Order Number, Forename, Surname, Middle initial, Telephone no.)

The customer details form will be arranged as simply as possible to allow data to be added quickly and easily.

The customer ID will be at the top of the form as it is the most important field, being the primary key.

The order number will be in bold type, to make it stand out, as this is the foreign key.

Plan:

The picture will make the form more user friendly.

Order form construction

An order form will be made to enter the initial order details.

It will use certain fields from the order table:

(Order Number, Date of Order, Requested weight 1, Requested weight 2, Requested weight 3, Requested weight 4, Requested weight 5, Cancelled)

The order number is the primary key, so is placed at the top of the form, with the date underneath.

Plan:

Turkey Query Construction

A query will be made to search through the turkey list to see which turkeys are still available.

It will be created using the wizard and selecting the turkey list table. It will show all the fields of the turkey list table. (Turkey number, Weight of turkey, Been Ordered)

The criteria of the been ordered field will be

This means that if a turkey has been ticked as ‘been ordered’ it will not be shown.

The weight of the turkey will be sorted in ascending order.

The user (Terry) will use this to see the available turkeys. It will remove the ones that have already been ordered so that the same turkey does not get accidentally sold twice.

Turkey Query form construction

The turkey’s available query will be designed in the same format as the turkey list form.

The user (Terry) can use this form to search for the requested weight against the actual turkey weights to find the nearest appropriate turkey weight available. The weight of the turkey is sorted in ascending order in a list which makes it quick and easy for Terry to find the correct weight. When Terry finds a turkey with the appropriate turkey weight and enters its turkey number into the order form he must tick the ‘been ordered’ box, so it will not be shown when the order form is next loaded.

I will place this form as a sub form in the order query form, to keep everything neat and compact, with related headings the same colour.

Order query

An order query will be made using every field of the order table:

(Order Number, Date of order, Turkey number 1, Requested weight 1, Turkey weight given 1, Turkey number 2, Requested weight 2, Turkey weight given 2, Turkey number 3, Requested weight 3, Turkey weight given 3, Turkey number 4, Requested weight 4, Turkey weight given 4, Turkey number 5, Requested weight 5, Turkey weight given 5, Cancelled.)

The date of order will be sorted in ascending order.

The criteria of the cancelled field will be

This means that if an order has been ticked as cancelled it will not be shown.

Order query form construction

This form will be made from the order query.

The order number is the primary key, so I have placed this at the top of the form. I have placed the date of the order next in the order form, for easy reference, even though orders will be sorted in ascending order. Included next is the turkey order details from the customer and lastly the turkey query sub-form, for easy access for Terry to find the relevant turkey for sale.

The cancellation field will not be shown in the form so an order can only be cancelled from the initial order form.

Order print query

A query will be made to display the correct information to be printed to hand to the customer when their order is made.

(Customer ID, Order Number, Forename, Surname, Requested weight 1, Requested weight 2, Requested weight 3, Requested weight 4, Requested weight 5.)

The order number will have the criteria

[enter order no]

The customers unique order number will need to be entered to display the correct information.

A report will be made from this query, so the information can be printed from the command button on the order form.

Price and discount query

A query will be made to work out the price the customer has to pay. To do this we need to workout if they should receive a discount, as a 5% discount is given if there is a 1kg or greater difference between the turkey weight and the required weight. If the difference is smaller than 1Kg then no discount is given.

The formula to see if there should be a discount is:

Diff =Abs (turkey weight given – requested weight)

Abs means that the absolute will be taken so there will not be any negatives but the positive equivalent value. This helps to make the next formulae correct.

The turkey weight given and the weight requested are taken from the order and weights table. See previous standard notation.

We can then make a formula to work out the price to pay after discount:

If difference>1 THEN (turkey weight*4.75*0.95)

If difference>1 means that if the difference if greater than 1 then do this…

The part of the formulae: (turkey weight*4.75) is working out the normal turkey price without any discount

The part of the formulae: *0.95 is working out the percentage to keep of the proper turkey price

The final formula is:

If difference>1THEN (turkey weight4.750.95)+2.50 ELSE (turkey weight*4.75)+2.50

The ELSE (turkey weight*4.75)+2.50 means that if the difference is not greater than 1 then do this instead. It will work out the normal price without any discount

The plus 2.50 is the preparation price which is added to the turkey cost.

The format will be in currency so the price is displayed in pound sterling.

These formulae will be used in several queries for each different turkey data in the order form (i.e turkey weight 1 – requested weight 1 then query for turkey weight 2 – requested weight 2 etc.). Several queries will be used as this way it is easier to debug if anything should go wrong.

A form will then be constructed to display the final price of each order and the final price of each turkey ordered. The final price will be at the top in larger font as this is the most important piece of information.

Final Turkey label query

A query will be made to display the correct information to be printed on the turkey labels. The query contains fields from:

The order table:

(Order Number, Turkey number 1, Turkey Weight given 1, Turkey number 2, Turkey Weight given 2, Turkey number 3, Turkey Weight given 3, Turkey number 4, Turkey Weight given 4, Turkey number 5, and Turkey Weight given 5.)

Also fields from the customer details table:

(Forename, Surname.)

Reports can then be made to print the labels of all turkey number 1’s in the orders. Another report to print labels for all turkey number 2’s in the orders. Etc.

Receipt query

A query will be made to display the correct information for the customers receipt. The query contains fields from:

The customer details table:

(Customer ID, Forename, Surname, telephone no)

The order table:

(Order Number, Requested weight 1, Turkey Weight given 1, Requested weight 2, Turkey Weight given 2, Requested weight 3, Turkey Weight given 3, Requested weight 4, Turkey Weight given 4, Requested weight 5, Turkey Weight given 5, Turkey number 1, Turkey number 2, Turkey number 3, Turkey number 4, Turkey number 5.)

And also from the price queries:

price plus prep, price plus prep 2, price plus prep 3, price plus prep 4, price plus prep 5, Total Price.)

The order number will have the criteria

[enter order number]

The customers unique order number will need to be entered to display the correct information.

A report will be made from this query, so the receipt information can be viewed and printed in an appropriate style on headed paper including the farm details.

Customer Details Query

A query with all the fields from the customer details table will be made to search for a customer details using their surname. (Customer ID, Order Number, Forename, Surname, Middle initial, and telephone no.)

The criteria of the surname field will be:

[Enter Surname]

This query will only be necessary if the customer forgets their order number in which case it will be used to find their details. As their surname may not be unique there could be multiple results, therefore the customer will have to check the details to ensure they are correct.

A form will be created to display the details in a more user friendly way; however the layout will simply just have the fields straight underneath each other.

SECURITY AND INTEGRITY OF DATA

Integrity

Input Masks

Input masks can be used in tables to format data and have some sort of control over what values can be entered into the field. Input masks consist of literal characters along with other special characters, which determine the kind of value that can be entered. Input masks are mainly used in text and date/time fields, but can be used in number or currency fields. Input masks will be used to make sure the user (Terry) doesn’t make obvious errors when inputting data. Obvious meaning, for example, not putting capitals at the beginning of a name or putting too many digits in a phone number.

Validation Rules

A validation rule specifies a test which has to be performed on any data entered into the field. If that data doesn’t pass the test a message comes up on the screen, which shows the text specified in the validation. Validation rules will be used to make sure that information is not entered wrongly:

maximum turkey weight of 10 Kg
minimum turkey weight of 5 Kg
requested turkey weights to the nearest half kilogram
turkey weights to the nearest 5 grams
orders only after 1/11/04

Safety

Back up recommendations

To ensure the safety of the database daily back ups should be made so that if the system for some reason loses its integrity, Terry will have a recent back up to fall back on. This type of a daily back up is called a differential backup. This will ensure that the majority of customer’s orders will not be affected and therefore Terry will not lose all of his customers. The regular backup should be made to a removable storage device such as a floppy disk. There should be a floppy disk for daily back ups done on Saturdays, Tuesday s and Thursdays. Another floppy disk should be used for data back ups on Mondays, Wednesdays and Fridays. This means that if for some reason the system goes down and the daily back up that day had gone wrong, there is always the other floppy disk, which even though is dated may still be of some use.

Security

Security is provided by making sure there are safeguards to ensure that data is not accidentally or maliciously corrupted.

Passwords

The system will have a password, which prevents the user from changing the design or appearance of forms. All the user will be able to do is enter the appropriate information in the correct sections. The only person who will have total access to all areas, both the users and behind the scenes of the database, will be the administrator (Terry).

Encryption

Data could be encrypted meaning data appears garbled unless you know how to decrypt the data. This would add more security.

Other Security Recommendations

I would suggest the user has various security measures for the system.

There should be a password to open the application and keyboards and monitors should be locked, via a password, when they are not being used or are left unattended which only the user (Terry) will know. This will keep the database secure from people who are not officially allowed access to the database. By putting passwords on the system it will also comply with the Data Protection Act. There will be several rules for passwords; these include:

Passwords should be at least 6 characters
Passwords must not be displayed on the screen but must be masked by asterisks.
Files containing passwords must be encrypted
Passwords must be kept confidential
Passwords must be changed regularly

Keeping the room containing the computer system securely locked and alarmed when they are not being used.

Installing a virus checker.

Testing strategy

Everything that has an input on the system will be tested. This will include command buttons, queries, validation rules and input masks. This will test the functionality of the system. It will be done by testing slightly incorrect values and extreme values. The system will also be checked against the brief to check it meets the end-user’s requirements.

Terry's Turkeys