Reference no: EM13825673

Applicable course objective:

• demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software (especially gaining proficiency in utilising databases, spreadsheets, and presentation applications).

• demonstrate written communication skills by understanding basic information communication and technology (ICT) terminology for effective communication and applying it within a business environment.

Applicable graduate qualities and skills gained from this assessment instrument:

• Problem Solving (Skill U2)

• Written & Oral Communication (Skill U4)

This assignment is quite complex, and exposes you to many different components in Microsoft Access2007/2010/2013, some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities and the course discussion forums, as well as exploring and experimenting on your own.

Dr Vivien "Viv" Aldiretired in 2009 after a long career in Human Resource Management.To keep busy and to fulfil a lifelong dream, Dr Aldistarted a unique musical instrument manufacture and mail order business called "Four Seasons Music" and began selling various types of imported musical instruments such as harps, bagpipes, banjos, xylophones, Hawaiian guitars, and mandolins to customers via mail order around Australia.These types of musical instruments have become very popular, especially since shows like Australia's Got Talent, The Voice and The X Factor have started;and Dr Aldi'sbusiness has experienced a rapid growth in their mail orders. When Dr Aldi first retired, she bought her own home computer to help manage the business's books and finances.

Four Seasons Music is located at Shop 1, 10Handel Boulevard, Toowoomba (about 100 km west of Brisbane).Dr Aldican be contacted via phone on (07) 4632 1156; fax on (07) 4632 9551; or email on enquiries@ 4seasons.com.au.

Viv Aldi is extremely impressed with our computing firm "Valkyries Technologies" and she has contracted our firm to assist her in setting up her various computer-based information systems. Please use our new business contact details as follows:52StraussStreet, Toowoomba; Phone (07) 4632 8512; fax on (07) 4632 2515; email [email protected].

The first computer-based information system that Dr Aldi is interested in is a Database Management System (DBMS) utilising Microsoft Access 2007/2010/2013. The DBMS will be used to store all of the business's customer details, information about the stock items that the business sells, the suppliers that the business uses and all postal order transactions for the business. It will allow Dr Aldi to run a number of queries on the data which she has specified below and she wishes to be able to print out an invoice for each customer at the end of each month.

Your firm's Systems Analyst has developed a database structure that has been determined to be suitable to create a database to store Dr Aldi's customer, items, supplier and postal orders data for the business, which has been provided as follows:

Table Specifications

Table:
tblCustomers
Fields: CustID(Primary Key), Title, FamilyName, GivenNames, Date of Birth, Address, City, State, Postcode, Freight Distance (Km), MobileNumber, eMailAddress, MailingList.

Table:
tblItems
Fields: ItemID(Primary Key), Classification, Instrument, FreightWeight (Kg), SupplierID, In Stock, OnOrder, CostPrice (AT) , CostPrice (LC) .

Table:
tblSuppliers
Fields: SupplierID(Primary Key), SupplierName, Address, City, State, Postcode, PhoneNumber, Recommended MarkUp

Table: tblOrders
Fields: CustID, ItemID, OrderDate(Primary Key), Dispatch Date, OrderQty
Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.
Dr VivAldi has provided to us a partial list of her customers, stock items, and suppliers and a partial list of hispostal orders for the second quarter 2015(April - June)to use as test data in four comma delimited text files which can be found on your firm's Intranet (CIS5100 Study Desk).

MrPeter Tchaikovsky, your manager, has set up the following tasks for you to complete for this phase of the project:

1. Create a Database and import the four (4) Text files into four (4) Tables

2. Modify Table Design

3. Set Primary Keys

4. Create Relationships between the Tables

5. Create Data and Update Queries

6. Create Data Display Forms

7. Create an Invoice Report

8. Create an End of Project Presentation to the Client

Task 1: Create and Import

Create a new blank Access 2007/2010/2013Database and name it[lastname] [initial] _ [student number] _ [course code] _ assign1.accdb(eg. genrichr_0050051005_cis5100_assign1.accdb).
Import the four sets of data from your firm's Intranet (CIS5100 Study Desk).

The easiest and quickest way to import data into Access is by using the Import Text Wizard. The following steps must be followed carefully to ensure the data is imported correctly:

Importing Tab Delimited Text files into Access 2007/2010/2013using the Wizard

1) On the External Data tab, in the Import & Link group, click the Text File data source icon.

2) In the Get External Data - Text Filewizard popup,browsetofind the ‘Assignment01 Text File Customers.txt'text file and select the Import the source data into a new table in the current database option - then click OK.

3) Select the Delimited - Characters such as commas or tab separate each fieldoption on the first page of the wizard - then click Next.

4) Select the Taboption on the second page of the wizard and turn on the First Row Contains Field Namescheck box - then click Next.

5) Set the following for each field on the third page of the wizard:

• Field Name- keep the field name as it appears when imported

• DataType-giving each an appropriate data type
Note: Numbers that will not be used for calculations should be set as Text. Numbers such as Freight Weight and Order Qty must be carefully and logically set to avoid loss of data (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).
See the Beskeen et al textbook Access 2010 Unit E for more details on setting appropriate Data Types
• When you are sure that each field has the appropriate data type, click Next.

6) Select No Primary Keyoption on the forth page of the wizard - these will be set in Task 2 - then click Next.

7) Name the table tblCustomers on the final page of the wizard and click Finish.
Repeat the above 7 steps for the rest of the text files naming each Database Table as follows:

Task 2: Modify Table Design

Modify the four imported tables' structures using the given Table Specifications,from the section above,in Microsoft Access using Table Design View:
• Use the above table specifications, checking that you have the correct table names and field names.
• Check that all fields have the appropriate Data Typefrom the following:

o Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
• Add an appropriate Field Descriptionsto describe the data field.
• Ensure that you also set properties for each field where appropriate. You MUST set the following:

o Field Size- set appropriate logical fields sizes for all Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double)

o Caption- set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)

o Format- set an appropriate format for all: Yes/No and Date data fields;Text data fields that should be stored in Upper-Case (State);and numbers that contain currency or percentages
Note:AT is the country code for Austria- should be formatted in Euros; LC is the country code for Saint Lucia - should be formatted in Dollars.

o Decimals- set an appropriate number of decimal places for any single or double numbers

o Default Value- set an appropriate default value for all Yes / No data fields

o Required- set any data fields that must contain data to Required and leave any fields where data is optional as unchecked.

o Validation Ruleand Validation Text- include appropriate Validation Rules for any data fields where there are a limited number of options (eg. Title and State)

Task 3: Set Primary Keys

Set the Primary Keysfor the four (4) Tables as per the following table specificationsin Microsoft Access using Table Design View:

Task 4: Create Relationships between the Tables

Create the following relationships between the four (4) tables:
• tblCustomers to tblOrders
• tblItems to tblOrders
• tblSuppliers to tblItems

You should check that the computer has identified correctly the 1 to ∞ relationships between the four (4) tables.

Task 5: Create Data and Update Queries

Create the following five (5) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).

Note: Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query's output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results.

• Query 1: UnfulfilledMother's DayBrass InstrumentOrders:

Dr Aldi would like to see how many Brassorders were ordered for Mother's Day but were unfulfilled due to the items being out of stock. In the past the majority of Mother's Day Brass Instrumentorders were placed in Apriland were for items greaterthan 2.5kilograms.
Create a Select Query that will display the full Customer'sName (including Title, Given Names and Family Name), Full Item Details (includingClassification, Instrument and Freight Weight), Order Date and Order Qty for all BrassInstrument orders,placed in the month of April (using the BETWEENcriteria tool), weighing more than to2.5 Kg, and that are currently on order but not in stock.

Display the results sorted by Order Date, check that have you included all necessary fields and ensure that you only display the required fields.

• Query 2: Southern Australian Percussion InstrumentOrders from a Supplier:

Dr Aldi would like to have a query that the businesscan reuse with a manually entered Supplier Name, to see how many customers living in Southern Australia (Tasmania, South Australia and Victoria) have purchased Percussion Instruments obtained from the Supplier as specified.

Create a Select Querythat will display the full Customer'sName (including Title, Given Names and Family Name), andFull Customer's Address for customers who live in Tasmania, South Australia and Victoria(using the IN criteria tools), and the ItemClassification, Instrument, Dispatch Date and OrderQty for all Customerswho have purchased PercussionInstruments obtained from a user specified Supplier.

Display the results sorted by Family Nameand State, check that have you included all necessary fields and ensure that you only display the required fields and test using Supplier'sName of"Ludwig van Beethoven Imports".

Query 3: Import Duty Saint Lucia (LC) Cost Price Increase:

A new Australian import duty of 8.125% has been added for all products being imported from Saint Luciaand Dr Aldi has asked you to develop a query that will update the Cost Price from Saint Luciaof all items that are currently on order. As it would be considered profiteering, it is important that the query does not update the Cost Price of items already in stock.
Create an Update Querythat will raise theSaint Lucia (LC) Cost Price for all items on orderthat are not in stockby 8.125%. It is always good practice to backup any data before running an update or a delete query. The following steps should be followed to achieve this Query:
• In the List of Tables, copy the table called tblItems and paste it with the new name tblBackupItems.

• Create an Update Query that will increase the Cost Price (LC) in tblItemsby 8.125% for all items currently not in stockbut on order.
• Include rounding to 2 decimal places with the update calculation by placing the following function around the update formula: ROUND(update_formula,2)

• Query 4: Late Dispatched Orders Form Query:

Dr Aldi would like to have a single query that the business can display within a form(Task 6: Form 1) to seethe customers who are:

i) Living in New South Wales, have had items dispatched late (between15thJune and today) and where the item was ordered before the 31stMay and the item is in stock

Or

ii) Living in Victoria, have had items dispatched late (between 12thJune and today) and where the item was ordered before the 31st May and the item is in stock

Create a Select Querythat will display the CustID, Full Customer's Name (including Title, Given Names, and Family Name), Full Customer's Address, Date of Birth, eMail Address, MailingList, ItemID, Full Item Details (includingClassification, Instrument, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers

i) living in New South Wales, who have placed Orders before the 31st May 2015and where these orders were onlydispatched between the 15thof June 2015and today (using the BETWEENcriteria tool and the DATE() function), and where the business currently has that item in stock

ii) OR living in Victoria (using the OR criteria row) , who have placed Orders before the 31st May 2015but where these orders were only dispatched between the 12th of June 2015and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
Display the results sorted by State and Order Date, check that have you included all necessary fields and ensure that you only display the required fields.

• Query 5: Invoice Query with Calculated Fields:

Dr Aldi would like to have a query that the business can use to produce customer invoices(Task 7) to see how many customers living in New South Walesordered a Horn related product in the months of April and May where the item was in stock.This query should also calculate the selling price (based on goods purchased from Austria) and order total, both of which will be needed on the customer invoices.

Create a Select Querythat will display the CustID, Full Customer'sName (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (includingClassification, Instrument, Freight Weight and Supplier Name), OrderDate, and OrderQty, including two calculated fields - Selling Price (AU) and Order Total (AU). Limit the query to only those customers living in New South Waleswho ordered aHornrelated product (using the LIKE criteria tool), who have placed orders in the months of April and May (using the BETWEEN criteria tool), and where the business currently has that item in stock.

Task 6: Create Data Display Forms

Create the following two (2) data entry forms using the Form Wizard and Form Design.
Note: Form names must conform with the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in Appendix 2 - including the Form Number and Two Meaningful Words to describe the query eg. frm1XxxxxxXxxxxx.

Form 1: Late Orders Form Display with Calculated Fields:

Using all fields from Task 5 Query 4, create a professional user friendly Display Form(using either the Columnar or JustifiedForm Wizard Templates) that will allow users to view order details stored in the Database Management System (DBMS).
Include two calculated Textbox fields that will calculate:
• Customer's Age in whole years (using the DATEDIFF function - see video Assignment 1 Extra Resources).
• Dispatch Delay in whole days (Dispatch Date - Order Date).

The form should demonstrate the following form controls:
• Image -an appropriate logo at the top of the form
• Label -an appropriate Form Title and appropriate Section Headings
• List Boxes or Combo Boxes - demonstrate for Title, State and ClassificationFields
• Check box - demonstrate for MailingList Field
• Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

• Form2: Supplier ItemsDisplay Form with Subform:

Create a professional user-friendly DisplayForm(using either the Columnar or JustifiedForm Wizard Templates) and a sub-formthat will allow users to vieweach supplier record's details stored in the Database Management System (DBMS).
The main form should contain AllSupplier details, the sub-form should contain a Datasheet view of the items sold by that particular Supplier(including at least Classification, Instrument, and Freight Weight) - name the subform: frmItemsSub.
The form should demonstrate the following form controls:

• Image -an appropriate logo at the top of the form

• Label -an appropriate Form Title and SubForm Heading

• List Box or Combo Box - demonstrate for State Fields

• Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

Task 7: Create an Invoice Report

Dr Aldi has provided you with thefollowing sample Invoice.The client requires you to develop a similar invoice for their business.

Using all fields from Task 5Query 5, create in the Report Wizard (using the BlockReport Wizard Template) a single Report called rptCustomerInvoice that produces a professional looking invoice for the business.

The invoice must have the following attributes:
• The report must contain at least all the elements displayed in the sample invoice (above).
• The report must be grouped by CustID.
• Each Customer's invoice must be on a separate page.

• Each Customer's invoice musthave the Customer's name merged into a single field including Title, Given Names and Family Nameby modifying one of the fields to include:
[Title] & " " & [GivenNames] & " " & [FamilyName]

• Each Customer's invoice must have part of the Customer's address merged into a single field including City, State and Postcode by modifying one of the fields to include:
[City] & " " & [State] & " " & [Postcode]
• Each Customer's invoice musthave Today's Date in the Page Header
• Each Customer's invoice must have an Invoice Total for each customer
• Each Customer's invoice mustnot have anyextraneous data including Grand Totals, Page Numbering, nor Summary Information.
• Each Customer's order must have the Order Date in the Details Section
• Each Image on the invoice must be inserted using the Image Control andmust have the following properties:

Task 8: End of Project Presentation to the Client

Mr Peter Tchaikovskyhas asked you to provide a professionally developed business presentation to the client, Dr VivAldi,outlining the process that was taken in the development of this Database Management System. Create a PowerPoint2007/2010/2013Presentation and name it ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number' (eg. genrichr_0050051005_cis5100_assign1.pptx).
Make sure that your professional developed business presentation is prepared so that it conforms to the course StudyDesk presentation resources. These resources include, but are not limited to appendix 1 and various videos.
The professional business presentations set of slides should be structured as follows:
• Title - outlining the full Business Name, Project Description, Student Name and Student ID.
• List of Topics
• Presentation Content - the following are the topics the presentation must answer.

Attachment:- prog.zip