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

1. IT Controls, Ethical, Privacy and Security Issues