Database Design & Development Assignment -

Assignment title - Design and Implement a Relational Database for a local Print and Ink Refill Business.

Learning Outcome -

• Use an appropriate design tool to design a relational database system for a substantial problem.
• Develop a fully functional relational database system, based on an existing design.
• Test the system against user and system requirements.
• Produce technical and user documentation.

Purpose of this assignment - The work undertaken in this assignment should be used to design, implement, test and document a database solution to a given business problem.

Scenario -

A local printing and ink refill business requires a method of recording telephone orders from customers (business and individual) to create a delivery schedule. The system should be able to input the customer details and the required order, and from this generate an invoice and item billing. It would be useful if the system could identify frequent customers and provide them with discounts and bargains for their customer loyalty. The delivery route could also be generated for a satellite navigation system from the post codes provided so that a number of deliveries could be planned over an optimised route for the driver, as this would shorten the times for delivery and reduce fuel consumption of the vehicle.

The manager is considering how the order system could tie in with the stock control so that warnings could be generated by the system when items reach a minimum stock level, thereby providing a margin of time for him to re-order before running out completely.

It would also be useful for the business to keep a running total of takings for each day of business, and provide weekly and monthly earnings. This would help the manager identify trends in his business and indicate possible requirements to advertise and market special offers through brochures and leaflet delivery.

You are required to design, implement and test a relational database that will meet the above requirements. As the manager and staff are not technical people, they will need the use of an attractive and "easy to use" interface for the system. Having built the system, you will also need to provide a technical user guide on how to install, maintain and use the system.

Task 1 -

Develop design documentation that will enable you to develop a database that meets the above specification. This should include:

• An Entity Relationship Diagram.
• A Data Dictionary for each entity (with data normalised to Third Normal Form) showing data types selected, and the appropriate validations required.
• Details of how you will validate and verify data.
• User interface design and design of any reports that are needed for output to the user.
• A report assessing the effectiveness of the design in relation to user requirements.

You should include any additional design documentation that you feel will support the development of your database.

Task 2 -

Develop a fully functional database using Access based on the design you carried out in Task 1. This should include:

• A fully functional database in line with your design documentation. This database should incorporate all the queries required in the specification.
• Security and database maintenance procedures as part of your database build.
• A report assessing whether meaningful data was obtained from the queries you implemented in task 1.
• A report evaluating the effectiveness of your database solution.

You may be asked to demonstrate your database as part of the marking process.

Task 3 -

Develop a robust test plan. This should include:

• Testing documentation for your database that shows what is to be tested, the testing criteria and the expected outcome. Your documentation should indicate where the expected outcome was not met and the actions taken to resolve the problems.
• A report assessing the effectiveness of your testing including an explanation of your choice of test data.

Task 4 -

Develop technical/user documentation. This should include:

• A user guide that will allow the take away owner to install, maintain and use the system. Your user guide should start with an overview of the database an explanation of how the queries and data manipulation processes that you have implemented will produce information of benefit to the business.
• Diagrams showing the flow of data through the system and flowcharts describing how the system works.
• A report assessing any future improvements to the database which would ensure that your system remains robust and relevant.