Stock System And Data Analysis Assignment

Part 1

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.

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Part-1

Customer_Name

Address

Phone

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Item

Pur_Quanity

Purchase_Unit_Cost

Pur_Order_Date

Supplier_Name

Address

Phone

Sales_Qunatity

Sales_unit_Cost

Sale_Order_Date

Quantity

Inventory_Cost

Reena

US

0987654321

Chair

6

789

01-sep-2017

jack

US

77865875678

67

500

01-feb-2016

90

600

Meena

US

1234567890

Table

6

1000

02-sep-2017

bina

US

6789098098

70

600

01-feb-2016

77

700

Rekha

US

5676567657

stand

3

999

03-sep-2018

caaniya

US

5678767876

15

500

02-fan-2016

20

650

mikka

US

0989098789

Men pent

2

1200

04-sep-2018

miya

US

0989098778

10

600

03-feb-2016

15

700

Bed sheet

robin

US

0989080980

50

300

04-feb-2016

90

400

First normal form (1NF): –

  • Inventory_Sales_Purchase_Order: –

Inventory_ID(PK)

Item_Name

Quantity

UnitCost

Purchase_No

Pur_Qunity

Pur_UnitCost

Pur_Order_Date

Sale_No

Sale_Qunity

Sale_UnitCost

Sale_Order_Date

IN101

Chair

90

600

P001

6

789

01-SEP-2017

S001

67

500

01-feb-2016

IN102

Table

77

700

P002

6

1000

02-SEP-2017

S002

70

600

01-feb-2016

IN103

Stand

20

650

P003

3

999

03-SEP-2017

S003

15

500

02-feb-2016

IN104

Men Pent

15

700

P004

2

1200

04-SEP-2017

S004

10

600

03-feb-2016

IN105

Bed Sheet

90

400

S005

50

300

04-feb-2016

Customer_Purchase_Order

Purchase_No(PK)

Customer_ID

Name

Address

Phone

P001

1

Reena

US

0987654321

P002

2

Meena

US

1234567890

P003

3

Rekha

US

5676567657

P004

4

mikka

US

0989098789

Supplier_Sales_Order

Sales_No(PK)

Supplier_ID

Name

Address

Phone

S101

S001

Jack

US

77865875678

S102

S002

Bina

US

6789098098

S103

S003

caaniya

US

5678767876

S104

S004

miya

US

0989098778

S105

S005

Robin

US

0989080980

Second Normal Form (2NF): –

  • Inventory: –

Inventory_ID(PK)

Item_Name

Quantity

UnitCost

IN101

Chair

90

600

IN102

Table

77

700

IN103

Stand

20

650

IN104

Men Pent

15

700

IN105

Bed Sheet

90

400

Inventory _Purchase_Order: –

Inventory_ID(FK)

Purchase_No

Quantity

Per_UnitCost

Order_Date

IN101

P001

6

789

01-SEP-2017

IN102

P002

6

1000

02-SEP-2017

IN103

P003

3

999

03-SEP-2017

IN104

P004

2

1200

04-SEP-2017

Inventory_Sales_Order: –

Inventory_ID(FK)

Sale_No

Quantity

Per_UnitCost

Order_Date

IN101

SL001

67

500

01-feb-2016

IN102

SL002

70

600

01-feb-2016

IN103

SL003

15

500

02-feb-2016

IN104

SL004

10

600

03-feb-2016

IN105

SL005

50

300

04-feb-2016

Customer_Purchase_Order

Purchase_No(PK)

Customer_ID

Name

Address

Phone

P001

1

Reena

US

0987654321

P002

2

Meena

US

1234567890

P003

3

Rekha

US

5676567657

P004

4

mikka

US

0989098789

Supplier_Sales_Order

Sales_No(PK)

Supplier_ID

Name

Address

Phone

SL101

S001

Jack

US

77865875678

SL102

S002

Bina

US

6789098098

SL103

S003

caaniya

US

5678767876

SL104

S004

miya

US

0989098778

SL105

S005

Robin

US

0989080980

Third Normal Form: –

  • Inventory: –

Inventory_ID(PK)

Item_Name

Quantity

Per_UnitCost

IN101

Chair

90

600

IN102

Table

77

700

IN103

Stand

20

650

IN104

Men Pent

15

700

IN105

Bed Sheet

90

400

Customer

Customer_ID (PK)

Name

Address

Phone

1

Reena

US

0987654321

2

Meena

US

1234567890

3

Rekha

US

5676567657

4

mikka

US

0989098789

Supplier: –

Supplier_ID (PK)

Name

Address

Phone

S001

Jack

US

77865875678

S002

Bina

US

6789098098

S003

caaniya

US

5678767876

S004

miya

US

0989098778

S005

Robin

US

0989080980

Purchase_Order: –

Purchase_No(FK)

Inventory_ID(FK)

Customer_ID (FK)

Quantity

Per_UnitCost

Order_Date

P001

IN101

1

6

789

01-SEP-2017

P002

IN102

2

6

1000

02-SEP-2017

P003

IN103

3

3

999

03-SEP-2017

P004

IN104

4

2

1200

04-SEP-2017

Sales_Order: –

Sales_No(PK)

Inventory_ID(FK)

Supplier_ID(FK)

Quantity

Per_UnitCost

Order_Date

SL101

IN101

S001

67

500

01-feb-2016

SL102

IN102

S002

70

600

01-feb-2016

SL103

IN103

S003

15

500

02-feb-2016

SL104

IN104

S004

10

600

03-feb-2016

SL105

IN105

S005

50

300

04-feb-2016

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,: –

Table name

Screenshot

Inventory

Select *from Inventory;

Customer

select *from Customer;

Supplier

Select *from Supplier;

Purchase

Select *from Purchase_Order;

Sales

Select *from Sales_Order;

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.

Walkenbach, J. (2010). Excel 2010 power programming with VBA. Hoboken, N.J.: Wiley Pub.