Entity-Relationship Model With Normalized Tables And MySQL Queries

2.      Normalization: –

Customer Table

Customer Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
Customer_ID	Title	Name Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Phone	Email	Address	Username	Password
1	Mr	Abhi	0987654312	[email protected]	US	Abhi123	[email protected]()
2	Mrs	Aditi	9878987889	[email protected]	US	Aditi123	Mj98*&^
3	Mrs	Diksha	4567656712	[email protected]	US	Diksha123	Nh78%&
4	Mr	Ankit	0987896667	[email protected]	US	Ankit123	%^&YTGH

Supplier
Supplier_ID	Title	Name	Phone	Email	Address	Username	Password
1	Mr	Rajeev	0987654311	[email protected]	US	rajeev123	09iok*7
2	Mrs	Rekha	9878987882	[email protected]	US	rekha123	Mnj09#$
3	Mrs	Meena	4567656713	[email protected]	US	meena123	Mng987RT
4	Mr	Aman	0987896664	[email protected]	US	aman123	POK4554

Inventory
Inventory_No	Product_Code	Category	Name	Description	Price	Quantity
1	098789	Chair	CC	Round chair	1000	100
2	890987	Table	TT	Round Table	1000.09	15
3	567878	Pen	PP	blue	1000.89	20
4	456765	Copy	CP	123 pages	60	100
5	678767	Book	BB	C++	1200.00	80

Purchase_Order
Purchase_Order_No	Customer_ID	Inventory_No	Purchase_Date	Deliver_Date	Quantity	Price	Discount
1	1	2	2017-01-01	2017-01-02	3	1300	20
2	2	2	2017-01-02	2017-01-03	5	1400	100
3	2	4	2017-01-03	2017-01-04	6	100	10
4	3	5	2017-01-04	2017-01-05	2	1500	90
5	4	1	2017-01-05	2017-01-06	2	1500	200

Sales_Order
Sales_Order_No	Supplier_ID	Inventory_No	Sales_Order_Date	Deliver_Date	Quantity	Price	Discount
1	1	2	2016-01-01	2016-01-02	10	900	30
2	2	2	2016-01-02	2016-01-03	12	900	200
3	2	4	2016-01-03	2016-01-04	10	70	60
4	3	5	2016-01-04	2016-01-05	10	1000	80
5	4	1	2016-01-05	2016-01-06	10	999	100

Create Table Statements: –

#Customer table

create Table Customer

(

Customer_Id int auto_increment primary key,

Title varchar(5) not null,

Name varchar(50) not null,

Phone varchar(12) not null,

Email varchar(50),

Address varchar(200) not null,

Username varchar(50) not null,

Password varchar(10) not null

#Supplier table

 create table Supplier(

 Supplier_ID int auto_increment Primary key,

 Title varchar(5) not null,

 Name varchar(50) not null,

 Phone varchar(12) not null,

 Email varchar(50),

 Address varchar(200) not null,

 Username varchar(50) not null,

 Password varchar(10) not null

nventory Table

create table Inventory

Inventory_No int auto_increment primary key,

Product_Code varchar(100) not null,

Category Enum(‘Chair’,’Table’,’Book’,’Pen’,’Copy’,’Stand’),

Name varchar(10) not null,

Description long,

Price decimal(10,2) not null,

Quantity int not null

#Purchase_Order table

 create table Purchase_Order (

  Purchase_Order_No int Auto_Increment primary key,

  Customer_ID int not null,

  Inventory_No int not null,

  Purchase_Date date not null,

  Deliver_Date date not null,

  Quantity int not null,

  Price decimal(10,2) not null,

  Discount Decimal(10,2) not null,

  foreign key (Customer_ID) references Customer(Customer_ID),

  Foreign key (Inventory_No) References Inventory(Inventory_No)

#Sales_Order Table

   create table Sales_Order (

Sale_No int Auto_Increment primary key,

Supplier_ID int not null,

Inventory_No int not null,

Sale_Order_Date date not null,

Deliver_Date date not null,

Quantity int not null,

Price decimal(10,2) not null,

Discount Decimal(10,2) not null,

foreign key (Supplier_ID) references Supplier(Supplier_ID),

Foreign key (Inventory_No) References Inventory(Inventory_No)

.     Insert data statements: –

#Customer table

insert into Customer(Title, Name, Phone, Email, Address, Username, Password) Values

(‘Mr’,’Abhi’,’0987654312′,’[email protected]‘,’US’,’Abhi123′,’[email protected]()’),

(‘Mrs’,’Aditi’,’9878987889′,’[email protected]‘,’US’,’Aditi123′,’Mj98*&^’),

(‘Mrs’,’Diksha’,’4567656712′,’[email protected]‘,’US’,’Diksha123′,’Nh78%&’),

(‘Mr’,’Ankit’,’0987896667′,’[email protected]‘,’US’,’Ankit123′,’%^&YTGH’);

#Supplier table

insert into Supplier(Title, Name, Phone, Email, Address, Username, Password) Values

(‘Mr’,’Rajeev’,    ‘0987654311’,’[email protected]‘,’US’,’rajeev123′,’09iok*7′),

(‘Mrs’,’Rekha’,’9878987882′,’[email protected]‘,’US’,’rekha123′,’Mnj09#$’),

(‘Mrs’,’Meena’, ‘4567656713’,’[email protected]‘,’US’,’meena123′,’Mng987RT’),

(‘Mr’,’Aman’,’0987896664′,’[email protected]‘,’US’,’aman123′,’POK4554′);

#Inventory table

insert into Inventory (Product_Code, Category, Name, Description, Price, Quantity) Values

(‘098789′,’Chair’,’CC’,’Round chair’,1000,100),

(‘890987′,’Table’,’TT’,’Round Table’,1000.09,15),

(‘567878′,’Pen’,’PP’,’blue’,1000.89,20),

(‘456765′,’Copy’,’CP’,’123 pages’,60,100),

(‘678767′,’Book’,’BB’,’C++’,1200.00,80);

#Purchase_Order table

insert into Purchase_Order (Customer_ID, Inventory_No, Purchase_Date, Deliver_Date, Quantity, Price, Discount) values

(1,2,’2017-01-01′,’2017-01-02′,3,1300,20),

(2,2,’2017-01-02′,’2017-01-03′,5,1400,100),

(2,4,’2017-01-03′,’2017-01-04′,6,100,10),

(3,5,’2017-01-04′,’2017-01-05′,2,1500,90),

(4,1,’2017-01-05′,’2017-01-06′,2,1500,200);

#Sales_Order table

insert into Sales_Order(Supplier_ID, Inventory_No,Sale_Order_Date, Deliver_Date, Quantity, Price, Discount) values

(1,2,’2016-01-01′,’2016-01-02′,10,900,30),

(2,2,’2016-01-02′,’2016-01-03′,12,900,200),

(2,4,’2016-01-03′,’2016-01-04′,10,70,60),

(3,5,’2016-01-04′,’2016-01-05′,10,1000,80),

(4,1,’2016-01-05′,’2016-01-06′,10,999,100);

4.     Queries: –

display all tables

select *from Customer;

Select *From Supplier;

Select *From Inventory;

select *from purchase_Order;

select *from Sales_Order;

Identify stock levels for items

select Product_Code,Category,Name, Quantity as ‘Quantity’,

concat(‘$’,Price),Concat(‘$’,Quantity*Price) as ‘Total_Cost’

from Inventory;

• Identify customer total expenditure

Select Customer.C_Name as ‘Customer Name’, Customer.Address,

Customer.Phone_Number, Concat(Inventory.Item_Type,’ ‘, Inventory.Item_Name) as ‘Item’,

Purchase.Quantity as ‘Total Unit’,

Concat(‘$’,Purchase.Price) as ‘unit Cost’,

Concat(‘$’,(Purchase.Quantity* Purchase.Price)-Purchase.Discount) as ‘Total Cost’

from Customer, Inventory, Purchase

where Customer.Customer_ID =Purchase.Customer_ID

and Inventory.Inventory_No=Purchase.Inventory_No; 

Part 

1. A description of the dataset: –

I select Taxation statistics 2011–12 Individual tax: Selected items, by age, residency status, gender and tax assessment ranges, 2011–12 income year dataset. This dataset contains Age rang, Gender, tax, expenses, Gross Profit, Business Expenses, Business Income etc. in this dataset I select Gender, Age range, Business Expenses and Business Income.

1. 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: –

Supplier Table

To create Pivot table I select Gender, Age Range, Business Expenses and Business Income. I apply sum operation on Business Expenses. So that in Pivot chart I find that total business expenses and income in each age rang gender. By using the pivot chart we analysis that Female age rang40-44 has maximum Business income and expenses and Male age range 40-44 has maximum business income and expenses. But female, as compare to male, has less business income and expenses.

This dataset is related to the Proportion of ICT spend by cost element. This dataset has information related to the Year, internal person, services outsourced to the external provider, software, hardware, carriage and other. I select the year, software, hardware and carriage cost from this dataset

 A screen captures showing the first page of the Excel spreadsheet containing the dataset: –

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

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

I select Year, software cost; hardware cost and carriage cost for the pivot table and also apply the sum operation on the software cost, hardware cost and carriage. So that I pivot chart we find the cost of software, hardware and carriage in each year.in the chart, we represent software cist by the blue bar, hardware cost by the red bar and carriage cost by the green bar. In the pivot chart, we analysis that year 2009-10 and 2014-15 has maximum software cost that is 17%. Year2009-10 has maximum hardware cost that is 14%. In the year 2008-09 and 2009-10 have maximum carriage cost that is 8%. But we can also analysis that software has maximum cost as compare to hardware and carriage in each year.

References

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

Coronel, C., & Morris, S. (2018). Database Systems. Mason, OH: Cengage Learning US.

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

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

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.

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

O’Leary, T., & O’Leary, L. (2011). Microsoft Excel 2010. New York: McGraw-Hill.

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

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

PATHAK, N. (2011). DATABASE MANAGEMENT SYSTEM. [S.l.]: HIMALAYA PUBLISHING HOUSE.

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 P