ASSESSMENT BRIEF
Subject Code and Name	COMR2002 BUSINESS INFORMATION SYSTEMS
Assessment	Practical Skills – Database Application Assignment
Individual/Group	Individual
Learning Outcomes	Recognise the range of cultural, security, privacy and ethical issues confronting individuals and organisations as consequence of using Information Systems.   Apply practical skills in the major functions of spreadsheet and database tools.   Communicate with IT professionals, stakeholders and user groups of information systems.
Submission	By 11:55pm AEST/AEDT Sunday of Week 11
Weighting	40%
Total Marks	40 marks

Context:

The aim of this assessment is to assess the student’s ability to solve business problems using database design tools and software. It also aims to enable students to think about the impacts of using IT in Businesses and communicate key issues through a written report. Many businesses depend on the accurate recording, updating and tracking of their data on a minute- to-minute basis. Employees access this data using databases. An understanding of this technology allows business professionals to be able to perform their work effectively.

Submission Instructions

You need to design and implement the database using Microsoft Access.

• Provide a word document containing the following:
  • Introduction – a summary of the case study
  • Database Design: E-R Diagram
  • Impact of using technology (at least 500 Words)
    • Discuss how Business Information Systems are being used in the case study
    • Discuss the cultural, security, ethical and privacy issues in using IS and IT

• Provide the Microsoft Access files used in creating the tables, forms, queries and reports

Janie’s Pet Minding

Janie is a local supplier of pet walking and sitting services for business people. The plan is to eventually grow the business to also provide online accessories to pet owners. A few years after starting, the business has experienced growing demand for the services offered. Subsequently, Janie needs an accounting system to record transactions and manage the details of customers and their pets. Due to the growth of the business, Janie wants to hire a part-time worker to develop and manage a system for processing orders. Having successfully applied for the job, you begin researching with Janie about how the company works.

YOU:                Tell me about how you take orders.

JANIE:              When a potential customer requests a job, I first take down all of the customer information, such as the customer’s: first and last name, street address, city, state, and postcode. I also request their phone number (work and mobile) along with their email address in case I have urgent matters that need to be attended.

YOU:                Sorry to interrupt, but can some customers have the same name?

JANIE:              Yes. As I was saying, then I record all of the information about their pets, such as the pet’s name, date of birth, gender, type of animal, a photo, along with details of accessories used by the pet.

YOU:                What other information would you like to record in the database?

JANIE:              Well, I then show the customers a list of the services that I offer. Here, I detail the services including day stays, walking, overnight stays and pet taxi services. Each has a different cost, which needs to be recorded in the database. When the customer is ready to order a service, I complete a form with information about the customer, pet, pickup date, and payment method (Cash, credit card, cheque, account) recorded on the top of the form. However, I often make copying mistakes and would appreciate it if I did not have to enter repetitive information each time. I then put the date at the top and usually include an order number that I have created. After that heading, I list the service(s) the customer wants, including quantity.

YOU:                What other information would you like to get out of the database?

JANIE:              I would like to be able to:

• take orders quickly, because certain times are very busy for me.
• show how many services are due to be performed each week.
• change prices easily, because the cost of providing services is rising fast.

YOU:      I can create a form for you to quickly enter details about the services offered, and queries will help you with the other two tasks. I can also help you manage your business with specific queries that might help you improve the services offered to clients. Can you think of some analysis that would be helpful?

JANIE:   To improve my advertising campaigns and overall management of the business, I would love to see:

• my best-selling services
• who my best customers have been
• a report of what accounts remains unpaid as well as a break down of how customers have paid (for bank reconciliation and cash management purposes). Before running the report, I would also like an easy way to update the payment records in my database.

Required:

• Create an E-R Diagram for Janie’s business using a Diagram Editor or Microsoft Access’ relationship manager.

• Create tables using the following guidelines.

• Enter records for at least ten customers. Use a hypothetical name, address, phone number, and e-mail address details to create customer records. Assume that the customers are located in Australian cities and states.
• Enter at least four records for the services offered including: day stays, walking, overnight stays and pet taxi services.
• Each customer should have at least one order; a few customers should place two orders. Each order should contain at least one service and some must include multiple services.
• Appropriately limit the size of the text fields; for example, a telephone number does not need the default length of 255 characters.

• Create forms using the following guidelines

• Form 1: Create a form based on your Pets table. Save the form as “Form 1_Pet”. Your form should resemble that in Figure 1 and include a photo of the pet.

Form 2: Create a form and sub-form based on your Orders and Order Details tables. Save the form as “Form 1_Order”. Your form should resemble that in Figure 2.

Form 3: Create a form and save the form as “Form 3_Main Menu”. Your form should include links to each of the forms in your database by embedding a macro when a Shape is clicked as shown in Figure 3 (Note: pictures are not required).

• Form 4: Create a form and save the form as “Form 4_Main Menu”. Your form should include links to each of the forms in your database by embedding a macro when a Shape is clicked as shown in Figure 4 (Note: pictures are not required but be consistent in your presentation of forms).

• Create the following queries in your database

• Query 1: Create a select query called “Query 1_Services Due” that displays a listof all services that are due to be performed within a selected date range. Your output should resemble that shown in Figure 5, although your data will be different.

• Query 2: Create a parameter/update that prompts for the incremental amount of a price increase and then prompts for the service ID or service description. Save the query as “Query 2_Price Change”. Test the query by adding $0.25 to the cost of a service. View the changed price in your datasheet view of the updated table.
• Query 3: Create a query called “Query 3_Best Selling Services”. List the service descriptions and determine how many orders and the quantities that have been received for each service. Report the amounts in columns labelled “Hours Ordered” and “Number of Orders” (Note: these column labels will require changes from the default setting provided by the query generator). Sort the output. Your output should resemble the format shown in Figure 6, but the data will be different.

• Query 4: Create a query called “Query 4_Best Customers” that lists the customers’ names, phone numbers, hours ordered, number of orders and the total amounts of their orders (Note: these column labels will require changes from the default setting provided by the query generator). You will have to calculate the order amounts and sort the output. Your output should resemble the format shown in Figure 7, but the data will be different.

• Create the following reports in your database

• Report 1: Create a report named “Report 1_Customers by Location”. The data for the report will originate from a query, in which all customers who order services between a pre-defined date range are included in the report (use the parameters and a criteria filter such “>=[StartDate] And <[EndDate]+1)” to filter the query data. Then you calculate the Total sales (Price multiplied by the Quantity of services ordered in that date range). The date range for the query should be displayed at the top of the report. The report’s output should be grouped by Suburb and include headings for Street Address, First Name, Last Name, Customer Email, and Total. Include a Total for each group (suburb) displayed in a report. Make sure that all column headings and data are visible and that all money amounts are formatted properly into currency. Depending on your data, your output should resemble that shown in Figure 8.

Figure 8

Report 2: Create a report named “Report 2_Orders by Payment Method”. Bring the Favorite Colors query output into a report. Save the report as Favorite Colors. Make sure that all column headings and data are visible. Depending on your data, your report should resemble that in Figure 9.

Learning Rubric: Assessment 1

Assessment Criteria	Fail (Unacceptable) 0-49%	Pass (Functional) 50-64%	Credit (Proficient) 65-74%	Distinction (Advanced) 75 -84%	High Distinction (Exceptional) 85-100%
E-R Diagram	E-R Diagram is incorrect.   More than 50% of the entities, attributes and relationships identified are incorrect.	E-R diagram is complete but some attributes and relationships are incorrect.   Some entities identified should not be an entity.   Around 50% of attributes are missing (or incorrect) and incorrect relationships are identified.	E-R diagram is complete.   All entities identified are correct.   Around 25% of the attributes and relationships are missing or incorrect.	E-R diagram is complete.   Identified entities were correct and complete.   Attributes were correct and complete.   One or two relationships were missing (or incorrect)	E-R Diagram is complete.   All entities, attributes and relationships are identified correctly.
15%
Database Tables             20%	Database tables are incorrect and incomplete.   E-R Diagram is not correctly translated to database tables.   Majority of the tables, attributes are missing.	Database table is complete. All entities are translated into tables.   Some attributes of the ER Diagram are not in the table and types of attributes are incorrect.	Database table is complete.   All entities are translated into tables.   Attributes are all present and relationships among tables are established (primary keys and	Database table is complete.   All entities are correctly and completely translated into tables. Attributes and types of attributes were correctly identified. All data in the table were entered.	Database table is complete. All entities are correctly and completely translated into tables.   Attributes and types of attributes are correctly identified.

	Not all entities were translated into tables.   Tables are not normalised.	Relationships are not translated to tables (primary keys and foreign key are incorrect).   Tables are not normalised.   Some data in the table were entered.	foreign keys are mostly correct)   All data in the table were entered.   Most of the attributes have correct types.   Some tables are normalised.	Most table relationships are correct (primary keys and foreign keys and their relationship have been mostly correctly established).   All tables are normalised to third normal form.	All data in the table are entered.   All table relationships are correct (primary and foreign keys and their relationships are all correctly identified)   All tables are normalised to third normal form.
Queries       25%	SQL queries and generated are mostly incorrect.   Complex queries are not answered	More than 50% of the SQL queries are correct.   Complex queries are incorrect.	More than 75% of the SQL queries are correct.   Complex queries are mostly incorrect.	Almost all queries are correct (one or two incorrect queries).   Some complex queries were answered.	All SQL queries are correct.   Can generate complex queries.
Forms and Reports	Unsatisfactory forms. Forms does not enter data.   Reports are mostly incorrect. No report formatting used.	Satisfactory forms.   Some links to the tables are not functioning. Some data cannot be entered in the tables via forms.	Good forms. Most data can be entered in the tables via forms.   Some design were incorporated in the forms but not professional looking.	Very good forms. All elements of the forms are created. All data can be entered via forms.   Design were incorporated in the forms. Somewhat professional looking.	Excellent form. All elements of the forms are created.   All data can be entered via forms.   Professional looking forms.
15%		Simple design incorporated in the forms. Not professional looking.   More than 50% of the reports are correct. No report formatting used.	More than 75% of the reports are correct. Some report formatting used.	Almost all of the reports are correct (one or two incorrect). Report formatting used.	All of the reports are correct Report formatting used extensively.

Use of Business Information Systems in this study   10%	Unsatisfactory explanation provided is very general, lacks detail, does not answer the question.	Satisfactory explanation provided, several justifications discussed, lack detailed explanation.	Good explanation provided, several justifications discussed. Explanations are detailed but some are not convincing.	Very good explanation and justification. Explanations are convincing. Main problems are identified and discussed in details. Provided some examples.	Excellent explanation and justifications provided. Main problems are identified and prioritised. Each problem is discussed in detail. Examples were provided for each problem.
Cultural, security, ethical and privacy issues in using IS and IT	Makes assertions about the ethical, privacy and security issues that are not justified.	Some issues are justified by arguments and not merely assertions.	Issues identified are appropriate. Arguments are presented to justify the issues.	Issues identified are appropriate. Arguments are presented to justify the issues.	Issues identified are appropriate. Arguments are presented to justify the issues.
			Takes into account the complexities of the issues. Other viewpoints were acknowledged.	Accurately takes into account the complexities of the issues. Other viewpoints were acknowledged.	Accurately takes into account the complexities of the issues and provided examples. Other viewpoints were acknowledged.
15%			Justifies any conclusions reach with arguments and not merely assertions.	Justifies any conclusions reached with well-developed arguments and not merely assertions.	Justifies any conclusions reach with well-developed arguments and well articulated viewpoint on the issues and substantiated by research.