MAN6905 Database And Business Intelligence

This assignment is related to the stock system and some datasets for analysis. In the part 1 I implement the database in mysql workbench to create the table, insert the data into tables and perform some queries to check the dataflow is working well or not. Then I connect the mysql database with the access database and create purchase form for customer and sale form for supplier. A user not knows how to insert data into the database by using insert statements. So that by using these form user easily enter the data into the database. In part 2 I pick the dataset from given link and analysis the data by using pivot tables and pivot charts.

Part-1

First normal form (1NF): -

• Inventory_Sales_Purchase_Order: -

Customer_Purchase_Order

Second Normal Form (2NF): -

• Inventory: -

Inventory _Purchase_Order: -

Inventory_Sales_Order: -

Customer_Purchase_Order

Third Normal Form: -

• Inventory: -

Customer

Purchase_Order: -

Sales_Order: -

SQL: - Create tables: -

/*------------------1. inventory tables--------------------------*/

create table Inventory

Inventory_ID varchar(6) Primary key,

Item_Name varchar(50) not null,

Quantity int not null,

Per_UnitCost Decimal(10,2) not null

/*------------------2. Customer tables--------------------------*/

create table Customer

Customer_ID int auto_increment Primary key,

Name varchar(50) not null,

Address varchar(200) not null,

Phone varchar(12) not null

/*------------------3. Supplier tables--------------------------*/

create table Supplier

Supplier_ID varchar(6) primary key,

Name varchar(50) not null,

Address varchar(200) not null,

Phone Varchar(12) not null

/*------------------4. Purchase_Order tables--------------------------*/

create table Purchase_Order(

Purchase_No varchar(6) primary key,

Inventory_ID varchar(6) not null,

Customer_ID int not null,

Quantity int not null,

Per_UnitCost Decimal(10,2) not null,

Order_Date date not null,

Foreign key (Inventory_ID) references Inventory(Inventory_ID),

Foreign key (Customer_ID) references Customer(Customer_ID)

/*------------------5. Sales_Order tables--------------------------*/

create table Sales_Order

Sale_No varchar(10) primary key,

Inventory_ID varchar(10) not null,

Supplier_ID varchar(10) not null,

Quantity int not null,

Per_UnitCost Decimal(6,2) not null,

Order_Date date not null,

foreign key (Inventory_ID) references Inventory(Inventory_ID),

foreign key (Supplier_ID) references Supplier(Supplier_ID)

  Insert data into tables: -

/*--------------------1. inventory tables--------------------------*/

insert into Inventory values

('IN101','Chair',90,600),

('IN102','Table',77,700),

('IN103','Stand',20,650),

('IN104','Men Pent',15,700),

('IN105','Bed Sheet',90,400);

/*--------------------2. Customer Table-----------------------*/

insert into Customer(Name, Address, Phone) values

('Reena','US','0987654321'),

('Meena','US','1234567890'),

('Rekha','US','5676567657'),

('mikka','US','0989098789');

/*------------------3. Supplier Table -------------------*/

insert into Supplier values

('S001','Jack','US','77865875678'),

('S002','Bina','US','6789098098'),

('S003','caaniya','US','5678767876'),

('S004','miya','US','0989098778'),

('S005','Robin','US','0989080980');

/*-------------------4. Purchase_Order tables--------------------------*/

insert into Purchase_Order values

('P001','IN101',1,6,789,'2017-09-01'),

('P002','IN102',2,6,1000,'2017-09-02'),

('P003','IN103',3,3,999,'2017-09-03'),

('P004','IN104',4,2,1200,'2017-09-04');

/*------------------5. Sales_Order tables--------------------------*/

insert into Sales_Order values

('SL101','IN101','S001',67,500,'2016-02-01'),

('SL102','IN102','S002',70,600,'2016-02-01'),

('SL103','IN103','S003',15,500,'2016-02-01'),

('SL104','IN104','S004',10,600,'2016-02-01'),

('SL105','IN105','S005',50,300,'2016-02-01');

     Queries: -

1. display all tables,: -

Identify stock levels for items

select Item_Name, Quantity as 'Quantity',

concat('$',Per_UnitCost) as 'Per unit cost',

Concat('$',Quantity*Per_UnitCost) as 'Total_Cost'

from Inventory;

• Identify customer total expenditure

Select Customer.Name as 'Customer Name', Customer.Address as 'Address',

 Customer.Phone as 'Phone',  Inventory.Item_Name,

Purchase_Order.Quantity as 'Total Unit',

Concat('$',Purchase_Order.Per_UnitCost) as 'unit Cost',

Concat('$',Purchase_Order.Quantity* Purchase_Order.Per_UnitCost) as 'Total Cost'

from Customer, Inventory, Purchase_Order

where Customer.Customer_ID =Purchase_Order.Customer_ID

and Inventory.Inventory_ID=Purchase_Order.Inventory_ID;

b.     A description of the dataset: -

This dataset is related to Taxation statistics 2014–15: Number of companies and total income, by industry code2. The information of this dataset is related to industry code, business description, number of companies, total income number, total income etc. this is large data set and select some dataset.

c. A screen captures showing the first page of the Excel spreadsheet containing the dataset.

2. Screen captures of ONE pivot table analysis on the dataset utilized together with ONE chart output that you have created.

A clear written analysis of the pivot table and chart: -

In pivot table contains the industry code and total income in each industry. Here in pivot chart we see that the total income in each industry and each industry represent by industry code. In this chart we analysis that industry code 01310 have maximum income and industry code 01150 have minimum income in 2014-15.

2. Dataset 2 detail: -

a. The URL of the dataset.

b.     A description of the dataset: -

This dataset is related to the Taxation statistics 2011–12 Individual tax: Selected items, by age, residency status, gender and tax assessment ranges, and 2011–12 income years. This dataset contains information related to the age rang, gender, tax assessment rang, gross tax complementary tax etc.

c. A screen captures showing the first page of the Excel spreadsheet containing the dataset.

2. Screen captures of ONE pivot table analysis on the dataset utilized together with ONE chart output that you have created.

3. A clear written analysis of the pivot table and chart.

The Pivot table store information related to the Age range and Medicare levy. So that in pivot chart we represent the total Medicare levy in each age group. In this chart we analysis that age group 40-44 have maximum Medicare levy and age group 70-74 have minimum Medicare levy.

References

Carlberg, C. (2011). Statistical analysis. Indianapolis, IN: Que.

Cronan, J. (2010). Microsoft Office Access 2010. New York: McGraw-Hill.

DuBois, P. (2013). MySQL. Upper Saddle River, NJ: Addison-Wesley.

DuBois, P., & Go?mez Pastor, J. (2005). MySQL. Madrid: Anaya Multimedia.

Friedrichsen, L. (2011). Microsoft Access 2010 illustrated. Australia: Course Technology/Cengage Learning.

Frye, C. (2010). Microsoft Access 2010 plain & simple. Sebastopol, Calif.: O'Reilly Media.

Kofler, M., & Kramer, D. (2001). MySQL. Berkeley: Apress.

O'Leary, T., & O'Leary, L. (2011). Microsoft Access 2010. New York, NY: McGraw-Hill.

Pachev, S. (2012). MySQL. Helion.

Parsons, J. (2011). New perspectives on Microsoft Excel 2010. Boston, MA: Course Technology.

Schneller, D. (2010). MySQL Admin Cookbook. Birmingham: Packt Pub.

Schwartz, B., Zaitsev, P., Tkachenko, V., & Zawodny, J. (2012). High performance MySQL. Sebastopol, CA: O'Reilly Media.

Syrstad, T., & Jelen, B. (2011). Using Microsoft Excel 2010. Indianapolis, Ind.: Que.

Tahaghoghi, S., & Williams, H. (2007). Learning MySQL. Sebastopol, Calif.: O'Reilly.

Ullman, L. (2006). MySQL. Berkeley, CA: Peachpit.