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.

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.