ACCT6001 Assessment 4 – Database Application Page 1 of 11
| ASSESSMENT BRIEF | |
| Subject Code and Title | ACCT6001 Accounting Information Systems |
| Assessment | Assessment 4: Database Application |
| Individual/Group | Individual |
| Length | – |
| Learning Outcomes | 1. Explain the characteristics of relational databases and their role in creation and communication of business intelligence. 2. Identify and assess IT controls, auditing, ethical, privacy and security issues with respect to information. 3. Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases. 4. Communicate with IT professionals, stakeholders and user groups of information systems. |
| Submission | By 11:55pm AEST/AEDT Sunday End of Module 6.1 (Week 11) |
| 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.
ACCT6001 Assessment 4 – Database Application Page 2 of 11
Many companies 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.
CLIENT BRIEF
__________________________________________________________________________________
NIKO APPAREL MANUFACTURERS
DATABASE APPLICATION
NAM’s executive management based on the result of the cost-benefit analysis recommendation to
develop the inventory and sales database management system in house. Currently it has a sample
orders spreadsheet that looks like this:
| Customer No. | Customer Name | Order Number | Order Date | Product Number | Product description | Number ordered | Quoted Price | Warehouse | Representative No. |
| 282 | Rebels Sport shop | 21614 | 10/5/16 | T1345 | Women’s shirt | 11 | $21.85 | 1 | R123 |
| 356 | Amart | 21610 | 23/5/16 | P2345 | Men’s pants | 20 | $40.20 | 2 | R890 |
| 356 | Amart | 21610 | 23/5/16 | S0894 | Kid’s shorts | 24 | $20.50 | 1 | R890 |
| 608 | Sportspower | 21617 | 28/5/16 | S0894 | Kid’s shorts | 10 | $20.50 | 1 | R890 |
| 608 | Sportspower | 21617 | 28/5/16 | P2345 | Men’s pants | 15 | $40.20 | 2 | R890 |
There are other related spreadsheets being used. Some examples are customer information
spreadsheet, sale representative spreadsheet, product information spreadsheet. NAM had to create
several spreadsheets because of its inherent size limitations. Increase in their sales and growth
continues and they have to split its order data into multiple spreadsheets to fit in new information.
NAM’s employees have difficulty accessing related data. For example, if the sales representative wants
to see a customer’s address and the product’s standard price, the employee must open and search
other spreadsheets that contain this data.
DATA:
Having decided to replace its spreadsheet to a database, management has determined that Premiere
Products must maintain the following information about its sales reps, customers, and parts inventory:
ACCT6001 Assessment 4 – Database Application Page 3 of 11
• The sales rep number, last name, first name, address, total commission, and commission
rate for each sales rep.
• The customer number, name, address, current balance, and credit limit for each customer,
as well as the number of sales rep who represents the customer.
• The part number, description, number of units on hand, item class, number of warehouse
where the item is stored, and unit price for each part in inventory.
• NAM must also store information about its orders. The figure below shows a sample order:
The sample order has three components:
• Heading (top) of the order contains the order number and date; the customer’s number,
name, and address; and the sales rep’s number and name.
• The body of the order contains one or more order lines. Each order line contains a part
number, a part description, the number of units of the part ordered, and the quoted price
for the part. Each order line also contains a total (line total), which is a result of multiplying
the number ordered by the quoted price.
• The footing of the order contains the order total.
NAM must also store the following items for each customer’s order:
• For each order, the company must store the order number, the date the order was placed,
and the number of the customer that placed the order. The customer’s name and address
and the number of the sales rep who represented the customer are stored with the
customer information. The name of the sales repo is stored with the sales rep information.
• For each order line, the company must store the order number, the part number, the
number of units ordered, and the quoted price. Remember that the part description is
stored with the information about parts. The results of the multiplying the number of units
ordered by the quote price is not stored because the computer can calculate it when
necessary.
• The overall total is also not stored. Instead, the computer calculates the total whenever an
order is printed or displayed on the screen.
RELATIONSHIPS:
NAM is also interested in the relationships between the categories. For example, an employee wants
to be able to associate orders with the customers that ordered them, the sales repo who coordinated
the orders and the products that customer requested.
Here are the relationships they have identified so far:
• A sales rep can have many customers but a customer can only have one sales rep.
• A customer can have several orders.
• An order can have several products and a product can be in different orders.
ACCT6001 Assessment 4 – Database Application Page 4 of 11
Currently NAM has the following spreadsheets:
Order spreadsheet
| Customer No. | Customer Name | Order Number | Order Date | Product Number | Product description | Number ordered | Quoted Price | Warehouse | Representative No. |
| 282 | Rebels Sport shop | 21614 | 10/5/16 | T1345 | Women’s shirt | 11 | $21.85 | 1 | R123 |
| 356 | Amart | 21610 | 23/5/16 | P2345 | Men’s pants | 20 | $40.20 | 2 | R890 |
| 356 | Amart | 21610 | 23/5/16 | S0894 | Kid’s shorts | 24 | $20.50 | 1 | R890 |
| 608 | Sportspower | 21617 | 28/5/16 | S0894 | Kid’s shorts | 10 | $20.50 | 1 | R890 |
| 608 | Sportspower | 21617 | 28/5/16 | P2345 | Men’s pants | 15 | $40.20 | 2 | R890 |
Customer Information Spreadsheet
| Customer No. | Customer Name | Street | City | State | Zip | Balance | Credit Limit | Representative No. |
| 282 | Rebels Sport shop | Torrens | Mitcham | SA | 3336 | $6550.00 | $10000 | R123 |
| 356 | Amart | Hill | Springfield | VIC | 4000 | $2550.00 | $10000 | R890 |
| 608 | Sportspower | Delamere | Unley | NSW | 6000 | $8995.00 | $20000 | R890 |
Sales Rep Information Spreadsheet
| Rep No. | Last Name | First Name | Street | City | State | Zip | Commission | Rate |
| R123 | Miller | Valerie | 624 Rundle | Grove | WA | 5321 | $16,550.00 | 0.05 |
| R890 | David | Darren | 532 Jackson | Hyde Park | SA | 5062 | $24,345.00 | 0.07 |
| R458 | Denison | James | 1626 Torrens | Essendon | VIC | 3423 | $13,459.00 | 0.05 |
Parts Information Spreadsheet
| Product No. | Product Name | OnHand | Category | Warehouse | Unit Price |
| P2345 | Men’s Pants | 200 | Pants | 2 | $40.00 |
| S0894 | Kid’s shorts | 190 | Shorts | 1 | $20.00 |
| T1345 | Women’s T shirt | 80 | Shirt | 1 | $21.00 |
ACCT6001 Assessment 4 – Database Application Page 5 of 11
NAM’S DATABASE REQUIREMENTS:
1. NAM needs a new database designed and implemented given the information provided
above.
2. They also need the following forms created:
a. Customer Information Form
b. Sales Representative Form
c. Order Form
d. Product Information Form
3. The following are some of the queries that should be answered by the database:
a. List of all products they are selling (product id, product name, category)
b. List of all the customers (customer no. and customer name) who lives in the state of
Victoria (VIC).
c. List of all sales representatives with commission rate greater than 5%.
d. List of all customers (customer no. and customer name) and their sales
representatives name.
e. List of all sales representatives no., sales rep name, product no., product name
ordered during the month of May.
4. Priority reports that are needed are the following:
a. List of products (Product, id, product name, category, product price and quantity on
hand)
b. List of all customers (customer no., customer name, address) with balances greater
than 5000
c. List of sales representatives (sales rep no., sales rep name) and their commission.
Report should show in order (from highest to lowest) the sales rep commission.
Instructions:
Students are expected to develop a database given the same organisational brief used in
assessments 1-3. Students need to design and implement the database using LibreOffice
Base. Additional information is provided below and some tasks will be done during the week
you are expected to implement a practical activity.
The following are the list of requirements:
• Explain why is the organisation needs to use a database.
• Create an E-R Diagrams using Diagram Editor
• Convert the E-R diagrams to tables and convert to Third Normal Form
• Create database tables using LibreOffice Base
• Create queries and forms using LibreOffice Base and SQL
• Identify IT controls needed and ethical, privacy and security issues the organisation
might encounter when using databases.
ACCT6001 Assessment 4 – Database Application Page 6 of 11
Outcome to be submitted:
1) A word document containing:
a) Introduction – a summary of the case study
b) Justification of the use of database
c) Database Design: E-R Diagram
d) IT controls needed
e) Ethical, privacy and security issues
2) LibreOffice Base files used in creating the tables, forms, queries and reports
Criteria
• Need for the implementation of the database
• E-R Diagram
• Normalised Database Tables
• Queries
• Forms and Reports
• IT Controls, Ethical, Privacy and Security Issues
ACCT6001 Assessment 4 – Database ApplicationV3.docx Page 7 of 11
Learning Rubrics
| Assessment Attributes | Fail 0-49% | Pass 50- 64% | Credit 65-74% | Distinction 75-84% | High Distinction 85-100% |
| Need for the implementation of the database 15% | 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. |
| E-R Diagram 15% | 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. |
ACCT6001 Assessment 4 – Database ApplicationV3.docx Page 8 of 11
| Database Tables 20% | Database tables are incorrect and incomplete. E-R Diagram is not | Database table is complete. All entities are translated | Database table is complete. All entities are translated into tables. | Database table is complete. All entities are correctly and completely translated into tables. | Database table is complete. All entities are correctly and completely translated into tables. |
| correctly translated to database tables. Majority of the tables, attributes are missing. Not all entities were translated into tables. Tables are not normalised. | into tables. Some attributes of the ER Diagram are not in the table and types of attributes are incorrect. Relationships are not translated to tables (primary keys and foreign key are incorrect). Tables are not normalised. Some data in the table were entered. | Attributes are all present and relationships among tables are established (primary keys and foreign keys are mostly correct) All data in the table were entered. Most of the attributes have correct types. Some tables are normalised. | Attributes and types of attributes were correctly identified. 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). All tables are normalised to third normal form. | Attributes and types of attributes are correctly identified. 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. |
ACCT6001 Assessment 4 – Database ApplicationV3.docx Page 9 of 11
| Queries 20% | 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 15% | 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. Simple design incorporated in the forms. Not professional looking. More than 50% of the reports are correct. No report formatting used. | Good forms. Most data can be entered in the tables via forms. Some design were incorporated in the forms but not professional looking. More than 75% of the reports are correct. Some report formatting used. | 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. |
ACCT6001 Assessment 4 – Database ApplicationV3.docx Page 10
| IT Controls, Ethical, Privacy and Security Issues 15% | Makes assertions about the IT controls, 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. Takes into account the complexities of the issues. Other viewpoints were acknowledged. Justifies any conclusions reach with arguments and not merely assertions. | Issues identified are appropriate. Arguments are presented to justify the issues. Accurately takes into account the complexities of the issues. Other viewpoints were acknowledged. Justifies any conclusions reach with well-developed arguments and not merely assertions. | Issues identified are appropriate. Arguments are presented to justify the issues. Accurately takes into account the complexities of the issues and provided examples. Other viewpoints were acknowledged. Justifies any conclusions reach with well-developed arguments and wellarticulated viewpoint on the issues and substantiated by research. |
ACCT6001 Assessment 4 – Database ApplicationV3.docx Page 11
of 10