Reference no: EM132765757

CIS1000 Information Systems Concepts - University of Southern Queensland

Database Management System (DBMS) and Presentation

LO 1. Apply information systems concepts to identify and resolve organisational problems and to develop ethical considerations within a business context

LO 2. Apply information systems skills to develop practical solutions within a business context

LO 3. Communicate information systems concepts to both technical and non-technical audiences within a business context.

Task 1: Create and Import

Create a new blank Access Database (using any version from Access 2007 to current) and name it [lastname] [initial] _ [student number] _ [course code] _ assign1.accdb (eg. genrichr_0050051005_cis1000_assign1.accdb).

Import the four sets of data from your firm's Intranet (CIS1000 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 (any version from Access 2007 to current) using 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 File wizard popup, browse to find 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 field option on the first page of the wizard - then click Next.

4) Select the Tab option on the second page of the wizard and turn on the First Row Contains Field Names check 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

Data Type - giving each an appropriate data type (students will be marked on their logical choice of data types, based on the sample data provided).

When you are sure that each field has the appropriate data type, click Next.

6) Select No Primary Key option on the fourth 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.

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 Type from the following: Short Text, Number, Date/Time, Currency, Yes/No.

Add appropriate Field Descriptions to ALL data fields with a well-structured description, eg. for CustID an appropriate description would be:

Customer Identification | Data Type: Short Text: 3 Characters

Ensure that you also set properties for each field where appropriate. You MUST set the following: Field Size - set appropriate logical fields sizes for all Short 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).

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

Format - set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Text data fields that should be stored in lower-case (Email Address); and numbers that contain currency or percentages.

ME is the country code for Montenegro - should be formatted in Euros;

BZ is the country code for Belize - should be formatted in Dollars.

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

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

Required - set any data Fields that MUST contain data to Yes and leave any Fields where data is optional as No

Validation Rule and Validation Text - include appropriate Validation Rules in for each of the following data Fields: Title; State; and Item Type. This ensures that only limited value options can be entered for these data Fields.

Task 3: Set Primary Keys

Set the Primary Keys for the four (4) Tables as per the following table specifications in 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

Task 5: Edit Customers Table

Edit Customer ID 112 in the Customers Table as per the following instructions in Microsoft Access using Table Datasheet View:

1. Change the Title, Given Names and Family Name to your own details.

2. Change the Address, City, State and Postcode to your own details.

3. Using www.whereis.com.au look up the distance between your current address and Toowoomba QLD 4350 (to the nearest kilometre) - change the Freight Distance to this new value (if you live outside of Australia, please chose an Australian address).

4. Change the Mobile Number to your student ID eg. 0061 099999

Do not use the u1099999 version.

5. Change the Email Address to your own details.

6. Select that you would like to be on the Business's Mailing List.

Task 6: 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).

You must create the Queries using Access Design View, if you create the Queries using SQL this will result in ZERO marks for the Query.

Query names must conform to the Leszynski Naming Convention (LNC), as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials - including the Query Number and Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx.

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: Out of Stock Statues and Busts Orders:

Query 2: Eastern Australian Spock related Orders from a Supplier:

Query 3: Import Duty Belize (BZ) Cost Price Increase:

Query 4: Late Dispatched Orders Form Query:

Query 5: Invoice Query with Calculated Fields:

Task 7: Create Data Display Forms

Create the following two (2) data entry forms using the Form Wizard and Form Design.

Task 8: Create an Invoice Report

Dr Rod N Berry has provided you with the following sample Invoice. The client requires you to develop a similar invoice for their business.

Task 9: End of Project Presentation to the Client

Dr Sev N O'Nine has asked you to provide a professionally developed business presentation to the client, Dr Rod N Berry, outlining the process that was taken in the development of this Database Management System. Create a PowerPoint 2007 to current Presentation and name it '[lastname] [initial] _ [student number] _ [course code] _ [assignment number' (eg. genrichr_0050051005_cis1000_assign1.pptx).

Attachment:- Information Systems Concepts Assignment Files.rar