ACCT6001 Accounting Information Systems

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 

Answers:

Introduction 

Database management systems are essential to industries and societies because they provide a highly efficient system for usage several kinds of data. These methods are built to be really adaptable. It is used to help you establish your data rendering to a theme so that it is easy to track and validate your informations. This greatly moderates the workload needed to calculate numerical information 

Justification of the use of database 

A lot of databases are available in the real world. Few databases are open sources. For example, Open Source databases are MySQL, Oracle Student Edition, LibreOffice and etc. We analyzed all the Open Source databases. Other than LibreOffice are query based. These are needed any basic Query written knowledge. But LibreOffice is a GUI based database. End User can easily use and run the query, forms and report easily. So that I chosen LibreOffice DBMS  

Case Study of Our System 

It is a simple shopping database. Customer can order one or more product items. Each customer refers from any one of the Sales Rep.

This project is based on creating ERD, design the tables, queries, forms and report. All the queries are simple. It is used to learn the SQL query using Wizard by the student.  

ERD    

Functional Dependencies 

ProductNo -> ProductName, Category, OnHand, WareHouse, UnitPrice 

RepNo -> LastName, FirstName, Street, City, State, Zip, Commission, Rate 

CustomerNo -> CustomerName, Street, City, State, Zip, Balance, CreditLimit,

RepNo 

OrderNumber -> OrderDate, CustomerNo  

OrderNumber, ProductNo -> NumberOrdered, QuotedPrice 

Create Tables: 

CREATE TABLE Parts (ProductNo VARCHAR(5), ProductName VARCHAR(35) NOT NULL, Category VARCHAR(10) NOT NULL, OnHand INTEGER NOT NULL, WareHouse INTEGER NOT NULL, UnitPrice FLOAT NOT NULL, PRIMARY KEY (ProductNo)); 

CREATE TABLE SalesRep (RepNo VARCHAR(4), LastName VARCHAR(25) NOT NULL, FirstName VARCHAR(25) NOT NULL, Street VARCHAR(45) NOT NULL, City VARCHAR(30) NOT NULL, State VARCHAR(3) NOT NULL, Zip INTEGER NOT NULL, Commission FLOAT NOT NULL, Rate FLOAT NOT NULL, PRIMARY KEY (RepNo)); 

CREATE TABLE Customer (CustomerNo INTEGER, CustomerName VARCHAR(45) NOT NULL, Street VARCHAR(45) NOT NULL, City VARCHAR(45) NOT NULL, State VARCHAR(3) NOT NULL, Zip INTEGER NOT NULL, Balance FLOAT NOT NULL, CreditLimit FLOAT NOT NULL, RepNo VARCHAR(4) NOT NULL, PRIMARY KEY (CustomerNo), FOREIGN KEY (RepNo) REFERENCES SalesRep (RepNo)); 

CREATE TABLE Orders (OrderNumber INTEGER, OrderDate DATE NOT NULL, CustomerNo INTEGER NOT NULL, PRIMARY KEY (OrderNumber), FOREIGN KEY (CustomerNo) REFERENCES Customer (CustomerNo)); 

CREATE TABLE OrderItems (OrderNumber INTEGER, ProductNo VARCHAR(5), NumberOrdered INTEGER NOT NULL, QuotedPrice FLOAT NOT NULL, PRIMARY KEY (OrderNumber), FOREIGN KEY (OrderNumber) REFERENCES Orders (OrderNumber),  FOREIGN KEY (ProductNo) REFERENCES Parts(ProductNo));  

Forms 

Customer Information Form  

Sales Representative Form    

Product Information Form

Queries

List of all products they are selling (product id, product name, category)  

Query: 

SELECT ProductNo, ProductName, Category FROM Parts;  

List of all the customers (customer no. and customer name) who lives in the state of Victoria (VIC) 

Query: 

SELECT CustomerNo, CustomerName FROM Customer WHERE State = 'VIC'  

List of all sales representatives with commission rate greater than 5% 

Query: 

SELECT RepNo, LastName, FirstName, Street, City, State, Rate FROM SalesRep

WHERE Rate >= 0.06;   

List of all customers (customer no. and customer name) and their sales representatives name 

Query: 

SELECT c.CustomerNo, c.CustomerName, sr.LastName AS Rep Last Name, sr.FirstName AS Rep First Name FROM Customer c, SalesRep sr WHERE c.RepNo = sr.RepNo   

List of all sales representatives’ no., sales rep name, product no., product name ordered during the month of May 

Query: 

SELECT sr.RepNo, sr.LastName, sr.FirstName, p.ProductName FROM SalesRep AS sr, Customer AS c, Orders AS ord, OrderItems AS oi, Parts AS p WHERE sr.RepNo = c.RepNo AND c.CustomerNo = ord.CustomerNo AND ord.OrderNumber = oi.OrderNumber AND oi.ProductNo = p.ProductNo AND ord.OrderDate >= {d '2016-05-01' } AND ord.OrderDate <= {d '2016-05-31' }   

Report 

List of products (Product, id, product name, category, product price and quantity on hand) 

List of all customers (customer no., customer name, address) with balances greater than 5000 

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  

IT Controls 

A database is managed and controlled by a database management system (DBMS). This DBMS is a system software components and purchased and installed separately. In this scenario, we have used to design the database using LibreOffice Base 

Privacy and Security Issues 

All the databases are used in Application and Web based environment. Most of the databases are used in web related which is linked with the HTML form elements. So that it may affect with web threats like SQL Injection, Malware attacks, Weak authentications, unmanaged sensitive data, DoS attack and etc 

We should use advanced level of programming such as Parameterized Query. It may avoid SQL Injection and Unauthorized access.  If we use Firewall in web hosting, it is also used to prevent the DoS attack and unauthorized access the database 

References:

10 ethical issues confronting IT managers - TechRepublic. (n.d.). Retrieved from https://www.techrepublic.com/article/10-ethical-issues-confronting-it-managers/

IT Auditing and Controls - An Introduction. (n.d.). Retrieved from https://resources.infosecinstitute.com/it-audit-introduction/ Retrieved from https://www.linkedin.com/pulse/what-importance-database-management-system-scott-aveda