Microsoft Word - ACCT6001_Assessment 4 Brief.docx
ACCT6001_Assessment Brief 4_Case Study - Database Page 1 of 11
ASSESSMENT BRIEF
Subject Code and Name ACCT6001 Accounting Information Systems
Assessment Assessment 4 – Database Application - Case Study
Individual/Group Individual
Length XXXXXXXXXXWords +/- 10%)
Learning Outcomes
(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
elational databases.
(e) Communicate with IT professionals, stakeholders and
user groups of information systems.
Submission
12 Weeks Delivery Mode:
Week 11/ Module 6.1 Sunday 11:55pm AEST/AEDT
6 Weeks Delivery Mode:
Week 6/ Module 6.2 Sunday 11:55pm AEST/AEDT
Weighting
30%
Total Marks 100 Marks
Context:
The aim of this assessment is to assess the student’s ability to solve business problems using
database design tool 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
usinesses 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 2 of 11
Submission Instructions
You need to design and implement the database using Li
eOffice Base or Microsoft Access.
(1) Provide a word document containing the following:
a. Introduction - a summary of the case study
. Justification of the use of database
c. Database Design: E-R Diagram
d. Discussion on the decision usefulness of two additional queries that you propose
e. Ethical, privacy and security issues
(2) Provide the Li
eOffice Base or Microsoft Access files used in creating the
tables, forms, queries and reports
ACCT6001_Assessment Brief 4_Case Study - Database Page 3 of 11
Robin’s Apparel Business
Robin is a local artist who has years of experience in the textile industry. Her children attend
the local university, and they sometimes ask her to design and print custom t-shirts for their
university organizations, such as clubs or sports teams. Friends of Robin’s children have
always complimented them on the custom shirts, and have requested some of their own.
Robin has decided to go into business printing custom apparel and selling it to organizations
at the local university. Her forecasts indicate that her first-year sales will be quite high, so she
will be very busy generating all the products for the orders. As a consequence, she wants to
hire a part-time worker to handle the orders that come into the shop. One of Robin’s children
is a friend of yours, and knows you are cu
ently studying accounting information systems.
Your friend asks you to apply for the job. Before you begin, you interview Robin about how
the company works.
YOU: Tell me about how you take orders.
ROBIN: When a potential customer requests a custom job, I first take down all the
customer information, such as the person’s name, street address, city, state,
and postcode. I also request their phone number in case I have questions about
the order.
YOU: So
y to inte
upt, but can some customers have the same name?
ROBIN: Yes. As I was saying, then I show them the list of items I have for customization
in our showroom, which is my dining room. There I show them t-shirts, both
long and short sleeved; fleece jackets, either pullover or zipped; hoodies, caps,
and fleece hats. Each has a different price, which is marked on the item, and
each can be ordered in a variety of colors and sizes, except headwear. When
the customer is ready to order, I fill out a form with the customer information
epeated on the top of the order. I would like not to have to rewrite all this
information because I sometimes make copying mistakes. Then I put the date
at the top and usually include an order number that I have created. After that
heading, I list the item the customer wants, including size, color, and quantity.
YOU: What other information would you like to get out of the database?
ROBIN: I would like to be able to take orders quickly, because certain times are very
usy for me. In addition, my market is college students, who are often on a
limited budget. I would like to be able to show them which items are cheaper
than $20. I would also like a way to change prices easily, because the cost of
textiles is rising fast.
YOU: I can create a form for you to enter the orders quickly, and queries will help
you with the other two tasks. I can also help you analyze your sales with
specific queries that might help you sell more in the future. Can you think of
some marketing analysis that would be helpful?
ACCT6001_Assessment Brief 4_Case Study - Database Page 4 of 11
ROBIN: I would love to see a listing of the best-selling products, the best-selling colors,
and a list of who my best customers have been. That way, I can send them
coupons for future work and hope to increase sales.
YOU: How about creating the bills to send out to customers?
ROBIN: I would like to have a list of all my customers with their orders individually
noted, and a summary of what they owe me. I could pass the list on to my
collection agency, which handles all my unpaid bills. In addition, I often give a
20% discount to customers in the next city, Glendale, to create more repeat
usiness.
Required:
(1) Why does Robin needs a database for her business? Provide justification for your
answer.
(2) Create an E-R Diagram using Diagram Editor for Robin’s business.
(3) Create tables in a database using the following guidelines. Use Li
eOffice Base or
Microsoft Access for creating this database.
(a) Enter at least seven records for the products: two types of t-shirts (short and long
sleeved), two types of fleece jackets (pullover and zipped), hoodies, baseball caps,
and fleece hats. Assume that all apparel except headwear comes in small, medium,
and large sizes.
(b) Enter records for at least fifteen customers. Use your own name, address,
telephone number, and e-mail address to create an additional customer record.
Assume that the business comes from your university town and one other town
nea
y.
(c) Each customer should have at least one order; a few customers should place two
orders. Each order should contain multiple items.
(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 a form and subform based on your Orders table and Order Line Item table. Save
the form as Orders. Your form should resemble that in Figure 1.
ACCT6001_Assessment Brief 4_Case Study - Database Page 5 of 11
Figure 1
(5) Create the following queries in your database
(a) Query 1: Create a select query called Products Less Than $15 that displays a list of
all products that cost less than $15. Your output should resemble that shown in
Figure 2, although your data will be different.
Figure 2
(b) Query 2: Create a query called Favorite Colors. List the colors of the products
ordered and determine how many have been ordered of each; report the amounts
in a column labelled Number Ordered. Sort the query output. Note the column
heading change from the default setting provided by the query generator. Your
output should resemble the format shown in Figure 3, but the data will be
different.
ACCT6001_Assessment Brief 4_Case Study - Database Page 6 of 11
Figure 3
(c) Query 3: Create a query called Best Selling Product. List the product descriptions
and determine how many orders have been received for each product. Report the
amounts in a column labelled Number Ordered. Sort the output. Note the column
heading change from the default setting provided by the query generator. Your
output should resemble the format shown in Figure 4, but the data will be
different.
Figure 4
(d) Query 4: Create a query called Best Customers that lists the customers’ names,
phone numbers, and the total amounts of their orders. You will have to calculate
the order amounts and sort the output. Note the column heading change from the
default setting provided by the query generator. Your output should resemble the
format shown in Figure 5, but the data will be different.
ACCT6001_Assessment Brief 4_Case Study - Database Page 7 of 11
Figure 5
(6) Create the following reports in your database
(a) Report 1: Create a report named Customers’ Orders. The date range for the report
should be displayed at the top. The report’s output should show headings for
Customer Name, Product Description, Quantity, New Price, and Total. All of this
data originates in a query, in which all customers who live in a specified city receive
a 20% discount. All other customers pay the full price. The discounted price or full
price is noted in the New Price column. Then you calculate the amount of money
owed for each product, which is the New Price multiplied by the Quantity. Save
the query as For Report,
ing the query data into a report, and group the report
on Customer Name. Make sure that all column headings and data are visible and
that all money amounts are formatted properly into cu
ency. Depending on your
data, your output should resemble that shown in Figure 6.
Figure 6