Assignment Phase 2: Implementation

Scenario: A Newsagent has given you a contract to create a database application using the Access 2010 database management system. You have been supplied previously with some example data in the design phase of the assignment. The examples can be found on Blackboard in the Assignment 2011-12 Folder.

Phase 2: Implementation

An Access database is available in the Assignment Folder on Blackboard. This database file, assignment2011-12.accdb, contains 4 tables containing Newsagent data. You should not concern yourself with the design of the tables. (They are not perfect because there are issues that you were not presented with, for example the field sizes are overly generous to allow for imported data and I have used autonumber primary keys in some cases). Live data are rarely clear cut but we can aim high at least! You should implement and submit the following items as stated in sections A-D, in a single bound document:

DESIGN

• You should create an Orders table to hold the following information: OrderID, OrderDate, CustomerID, PublicationID in that order with appropriate datatypes and field properties. For OrderID, choose AutoNumber
• You should set OrderID as a primary key
• You should create the following relationships between the tables:
  • Customer one to many with Orders
  • Publications one to many with Orders
• You should import data (2527 records) from the text file, orders2011-12.txt, which can be found on Blackboard in the Assignment Folder. This file should be imported into the Orders table
• This is tab delimited text. Set the Text qualifier to {none}

You should submit:

• a statement of originality (see Student Handbook, also available via Computer Science web page http://www.aber.ac.uk/compsci/Dept/Teaching/resources.shtml)
• a printed screen dump of the Access Relationships window, showing clearly all tables, fields, and the relationships you have created between them
• one page of the datasheet of the Orders table after data import, so that the number of records is clearly visible
• a description of any problems incurred on data import and, if any occurred, the cause of the problem and the steps you took to overcome it in order to import successfully
• a Feedback sheet which can be found on Blackboard

FORM

Produce a form to display the details for a customer and their orders. The information should include the name and cost of each publication, as well as the delivery person’s name. This form should be suitable for adding new orders to the database. One of your design objectives should be to make at least one week’s information, including all the newspaper/magazine data, visible on one screen without scrolling or using ‘tabs’. However you should not use a font smaller than 8 point, nor should you compromise the objective of a visually attractive and appropriate layout to cram in extra events.

A bare pass mark for this part can be achieved by a wizard generated form that meets the above requirements and has not been substantially modified; however more marks will be given to forms showing design initiative and good layout

You should submit :

• a printed copy of an example form for the Customer Rev Allen Yardley containing the data for the period 4th – 14th November 2008
• a screen dump of the form design window

REPORT

You should produce a report that, for each customer, will print all orders. The report should include the cost of the customer’s bill for deliveries. Each customer should start on a new page. Example data is given in the design phase document is available on Blackboard in the Assignment 2011-12 Folder.

A pass mark for this part can be achieved by a wizard generated report that neatly reproduces the information asked for without the calculation, however more marks will be given to reports showing design initiative, and which include the customer’s bill and the Newsagent’s sales and profit calculations.

You should submit:

• one page of the report for a customer of your choice. This must be printed from Access. It must NOT be pasted into a word processor document, otherwise marks may be lost
• a printed screen dump(s) showing the complete design view for your report, so that the calculations are visible
• details of any calculations used and where they are used, if appropriate. These details should take not more than one A4 page

QUERIES

• Query 1 Design a query that will display, in the order given here: the Publication name, Cover Price, Publisher name, Publisher’s address (line 4), post code and contact number; for publications that cost less than £1.00 and are not published in Manchester. The details should be listed in alphabetic order of publisher, then alphabetically for each publication. The resulting datasheet should fit neatly onto one page of A4 whilst showing all data clearly
• Query 2 Design a query that shows a list of deliveries for a delivery person of the user’s choice for a date of the user’s choice. The query will show the deliverer’s full name, formatted within the query, the customer’s full name, again formatted within the query. It will also show the Order Date, the name of the Newspaper and enough of the customer’s address (could omit locality, town and postcode). The data should be in order of delivery ID, then by postcode, then by house name and finally by house number. Display the output of the query using Delivery ID 1 and OrderDate 09/11/2008. More marks are available for adding in a field containing a ‘Format’ to provide a day of the week value for the OrderDate. The resulting datasheet should fit neatly onto one page of A4, so that all data are visible and clear to the reader
• Query 3 Design a query that displays the Publication name, the Publisher name and the number of each publication that the Newsagent has delivered. The query should show the data for the week of 3rd – 9th November 2008. Display the result in a format that the user can understand clearly. The result for this should be neatly presented so that all data are shown on one page of A4. N.B. A query can also the output of another query to perform the necessary requirements

For each of the above three queries you should submit:

• a printed screen dump of any QBE grid(s) involved in each query.
• the datasheets of results from each query neatly presented. These must be printed from Access NOT pasted into a word processor document, otherwise marks may be lost.