ASSESSMENT BRIEF
Subject Code and Name

Assessment
Individual/Group

Learning Outcomes

ACCT6001 Accounting Information Systems

Assessment 4 – Database Application - Case Study
Individual

(b) Explain the characteristics of relational databases and
their role in creation and communication of business
intelligence.
(c) Identify and assess IT controls, auditing, ethical,
privacy and security issues with respect to information.
(d) Apply technical knowledge and skills in creating
information for the workplace using spreadsheets and
relational databases.
(e) Communicate with IT professionals, stakeholders and
user groups of information systems.

Submission

By 11:55pm AEST/AEDT Sunday end of Week 11/Module
6.1
For Intensive class: By 11:55pm AEST/AEDT Sunday end of
Week 6/Module 6.2

Weighting
Total Marks

30%
100 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.
ACCT6001_Assessment Brief 4_Case Study - Database

Page 1 of 11

Submission Instructions
You need to design and implement the database using Microsoft Access.
(1) Provide a word document containing the following:
a) Introduction - a summary of the case study
b) Compare different relational databases (e.g. Microsoft Access and SQL Server)
c) Database Design: E-R Diagram
d) Outline how distributed database technology (e.g. Blockchain) differs from
traditional databases like Microsoft Access.
(2) Provide the Microsoft Access files used in creating the tables, forms, queries
and reports

ACCT6001_Assessment Brief 4_Case Study - Database

Page 2 of 11

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?

ACCT6001_Assessment Brief 4_Case Study - Database

Page 3 of 11

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:
(1) Why should Janie implement a Microsoft Access database for her business as
opposed to another database solution such as Microsoft SQL Server? Provide
justification for your answer by comparing different types of databases.
(2) Create an E-R Diagram for Janie’s business using a Diagram Editor or Microsoft Access’
relationship manager.
(3) Create tables using the following guidelines.
(a) 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.
(b) Enter at least four records for the services offered including: day stays, walking,
overnight stays and pet taxi services. Assume that all apparel except headwear comes
in small, medium, and large sizes.
(c) 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.
(d) Appropriately limit the size of the text fields; for example, a telephone number
does not need the default length of 255 characters.
(4) Create forms using the following guidelines
(a) 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.

ACCT6001_Assessment Brief 4_Case Study - Database

Page 4 of 11

Figure 1
(b) 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.

Order Details go here

Figure 2

ACCT6001_Assessment Brief 4_Case Study - Database

Page 5 of 11

(c) 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).

Figure 3
(d) 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).

ACCT6001_Assessment Brief 4_Case Study - Database

Page 6 of 11

Figure 4
(5) Create the following queries in your database
(a) Query 1: Create a select query called “Query 1_Services Due” that displays a list of
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.

Figure 5

(b) 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.
(c) 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
ACCT6001_Assessment Brief 4_Case Study - Database

Page 7 of 11

should resemble the format shown in Figure 6, but the data will be different.

Figure 6
(d) 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.

Figure 7
(e) Query 5: Create an action/update query that change the “Paid” field of orders with
a “Cash” payment method by prompting the user to enter a “from date” and “to
date” range. Save the query as “Query 5_Paid Change”. Test the query by running
the query for the first week of cash payments stored in the order table. View the
changed values in your datasheet view of the updated table
(6) Create the following reports in your database
(a) 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.

ACCT6001_Assessment Brief 4_Case Study - Database

Page 8 of 11

Figure 8
(b)

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.

Figure 9
(7) Explain to Janie how distributed database technologies such as Blockchain
differ from traditional databases like Microsoft Access?

ACCT6001_Assessment Brief 4_Case Study - Database

Page 9 of 11

Learning Rubric: Assessment 1
Assessment Criteria
Implementation and
comparison of
databases

Fail
(Unacceptable)
0-49%
Unsatisfactory
explanation provided is
very general, lacks detail,
does not answer the
question.

Pass
(Functional)
50-64%
Satisfactory explanation
provided, several
justifications discussed, lack
detailed explanation.

Credit
(Proficient)
65-74%
Good explanation
provided, several
justifications discussed.
Explanations are detailed
but some are not
convincing.

Distinction
(Advanced)
75 -84%
Very good explanation and
justification. Explanations are
convincing. Main problems
are identified and discussed in
details. Provided some
examples.

High Distinction
(Exceptional)
85-100%
Excellent explanation and
justifications provided. Main
problems are identified and
prioritised. Each problem is
discussed in detail. Examples
were provided for each
problem.

E-R Diagram is incorrect.

E-R diagram is complete but
some attributes and
relationships are incorrect.

E-R diagram is complete.

E-R diagram is complete.

E-R Diagram is complete.

All entities identified are
correct.

Identified entities were
correct and complete.

All entities, attributes and
relationships are identified
correctly.

Around 25% of the
attributes and
relationships are missing
or incorrect.

Attributes were correct and
complete.

Database table is
complete.

Database table is complete.

15%
E-R Diagram

More than 50% of the
entities, attributes and
relationships identified
are incorrect.

Some entities identified
should not be an entity.
Around 50% of attributes
are missing (or incorrect)
and incorrect relationships
are identified.

15%
Database Tables

Database tables are
incorrect and incomplete.
E-R Diagram is not
correctly translated to
database tables.

20%

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.

ACCT6001_Assessment Brief 4_Case Study - Database

All entities are translated
into tables.
Attributes are all present
and relationships among
tables are established
(primary keys and

Page 9 of 11

One or two relationships were
missing (or incorrect)

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).

foreign keys are mostly
correct)
All data in the table were
entered.

Most table relationships are
correct (primary keys and
foreign keys and their
relationship have been mostly
correctly established).

Most of the attributes
have correct types.

All tables are normalised to
third normal form.

Tables are not normalised.
Some data in the table were
entered.

25%
Forms and Reports

SQL queries and
generated are mostly
incorrect.
Complex queries are not
answered
Unsatisfactory forms.
Forms does not enter
data.
Reports are mostly
incorrect. No report
formatting used.

More than 50% of the SQL
queries are correct.

More than 75% of the
SQL queries are correct.

Complex queries are
incorrect.

Complex queries are
mostly incorrect.

Satisfactory forms.

Good forms. Most data
can be entered in the
tables via forms.

Some links to the tables are
not functioning.
Some data cannot be
entered in the tables via
forms.
Simple design incorporated
in the forms. Not
professional looking.

15%

More than 50% of the
reports are correct. No
report formatting used.

ACCT6001_Assessment Brief 4_Case Study - Database

Some design were
incorporated in the
forms but not
professional looking.
More than 75% of the
reports are correct.
Some report formatting
used.

Page 10 of 11

All table relationships are
correct (primary and foreign
keys and their relationships
are all correctly identified)
All tables are normalised to
third normal form.

Some tables are
normalised.
Queries

All data in the table are
entered.

Almost all queries are correct
(one or two incorrect
queries).

All SQL queries are correct.
Can generate complex
queries.

Some complex queries were
answered.
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.
Almost all of the reports are
correct (one or two incorrect).
Report formatting used.

Excellent form. All elements
of the forms are created.
All data can be entered via
forms.
Professional looking forms.
All of the reports are correct
Report formatting used
extensively.

Blockchain
(distributed)
databases versus
traditional
(relational)
databases

Makes assertions about
the ethical, privacy and
security issues that are
not justified.

Some issues are justified by
arguments and not merely
assertions.

10%

ACCT6001_Assessment Brief 4_Case Study - Database

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.

Justifies any conclusions
reach with arguments
and not merely
assertions.

Justifies any conclusions
reached with well-developed
arguments and not merely
assertions.

Page 11 of 11

Justifies any conclusions reach
with well-developed
arguments and well
articulated viewpoint on the
issues and substantiated by
research.