Salford Business School  – Data Management

The Scenario
Wavery Foods are a small catering company based in Southampton that sell pre-prepared snacks and meals to local cruise tour companies and private yachts. The company has been operating since 2003 and has made a profit each year. Based in small premises near Southampton Docks, the company is run by its owner/managing director and employs 2 chefs and a delivery driver.
Although the company has a very small marketing budget, it has made many contacts through good word of mouth and has attracted many clients. The company’s most profitable clients are local businesses that run regular tours on the Solent and around the nearby coastline. In addition, many private yacht owners frequently order meals when entertaining or hosting conference events.
The company has a good range of snacks and meals on its menu, all of which are freshly prepared at the company’s premises.  It also owns and operates a small van that is used for deliveries.

The Project
As Wavery Foods becomes more popular, the managing director is finding it difficult to handle all the paper work. He currently produces menus and invoices on word processor software installed on the company’s laptop. However, all order and client information is currently stored on paper-based forms and preparation sheets for the two chefs and delivery forms for the driver are written by hand.
The company’s managing director now wants a database to store this information and to generate reports.  The database must be able to:
•    Record client orders (including details of the client, the menu items that they ordered, and payment and delivery details.)
•    Generate menus
•    Generate chef preparation sheets for orders
•    Generate delivery forms for orders
You have been contracted to design a database system that can satisfy the above needs and implement it in Microsoft Access.

The requirements of the assignment are as follows:

Section A

1.    Draw an entity relationship diagram (ERD) for your system design including entities, relationships, cardinality and optionality. You should also resolve any many to many relationships.

2.    Specify the attributes (table fields) for your system design and identify the primary and foreign keys.

3.    Implement your database design, using MS Access. You should include appropriate tables, fields and properly set up the table relationships. Extra credit will be given for the use of additional functions such as input validation, lookup wizards, user-friendly interface designs etc.

4.    Populate your database with appropriate test data (suggestion: minimum of 5 records per table). Ensure that the test data is sufficient to test fully each of the queries, forms and reports specified below.

Section B

Ensure that EVERY form and report includes the company name in the Header. You should also make sure that your data properly demonstrates the functionality of these queries, forms and reports.

Queries

1.    List all orders with a total value of over £100 and the clients who placed them.

2.    List the most expensive menu item(s) sold in the last month.

Input Forms

1.    Create an input form to be used for the input of orders

2.    Create an input form to be used for the input of menu items

Output Reports

1.    Create a report which can be used as an invoice for orders. This should show the order and client details along with the items ordered and the total cost of the order. As this will be printed and given to clients it should have a professional appearance

2.    Create a report which can be used as a menu. This will also be printed and given to clients and should have a professional appearance and an appropriate theme.

3.    Create a report which can be used as a preparation sheet for the chef. This should include the menu items included in an order and any special preparation or dietary notes.

5.    Create a report which can be used as a delivery form for the driver. This should include delivery details and allow a delivery to be signed for by clients.

Salford Business School  – Data Management

The Scenario
Wavery Foods are a small catering company based in Southampton that sell pre-prepared snacks and meals to local cruise tour companies and private yachts. The company has been operating since 2003 and has made a profit each year. Based in small premises near Southampton Docks, the company is run by its owner/managing director and employs 2 chefs and a delivery driver.
Although the company has a very small marketing budget, it has made many contacts through good word of mouth and has attracted many clients. The company’s most profitable clients are local businesses that run regular tours on the Solent and around the nearby coastline. In addition, many private yacht owners frequently order meals when entertaining or hosting conference events.
The company has a good range of snacks and meals on its menu, all of which are freshly prepared at the company’s premises.  It also owns and operates a small van that is used for deliveries.

The Project
As Wavery Foods becomes more popular, the managing director is finding it difficult to handle all the paper work. He currently produces menus and invoices on word processor software installed on the company’s laptop. However, all order and client information is currently stored on paper-based forms and preparation sheets for the two chefs and delivery forms for the driver are written by hand.
The company’s managing director now wants a database to store this information and to generate reports.  The database must be able to:
•    Record client orders (including details of the client, the menu items that they ordered, and payment and delivery details.)
•    Generate menus
•    Generate chef preparation sheets for orders
•    Generate delivery forms for orders
You have been contracted to design a database system that can satisfy the above needs and implement it in Microsoft Access.

The requirements of the assignment are as follows:

Section A

1.    Draw an entity relationship diagram (ERD) for your system design including entities, relationships, cardinality and optionality. You should also resolve any many to many relationships.

2.    Specify the attributes (table fields) for your system design and identify the primary and foreign keys.

3.    Implement your database design, using MS Access. You should include appropriate tables, fields and properly set up the table relationships. Extra credit will be given for the use of additional functions such as input validation, lookup wizards, user-friendly interface designs etc.

4.    Populate your database with appropriate test data (suggestion: minimum of 5 records per table). Ensure that the test data is sufficient to test fully each of the queries, forms and reports specified below.

Section B

Ensure that EVERY form and report includes the company name in the Header. You should also make sure that your data properly demonstrates the functionality of these queries, forms and reports.

Queries

1.    List all orders with a total value of over £100 and the clients who placed them.

2.    List the most expensive menu item(s) sold in the last month.

Input Forms

1.    Create an input form to be used for the input of orders

2.    Create an input form to be used for the input of menu items

Output Reports

1.    Create a report which can be used as an invoice for orders. This should show the order and client details along with the items ordered and the total cost of the order. As this will be printed and given to clients it should have a professional appearance

2.    Create a report which can be used as a menu. This will also be printed and given to clients and should have a professional appearance and an appropriate theme.

3.    Create a report which can be used as a preparation sheet for the chef. This should include the menu items included in an order and any special preparation or dietary notes.

5.    Create a report which can be used as a delivery form for the driver. This should include delivery details and allow a delivery to be signed for by clients.