Introduction
Metropolitan
Office (Pvt) Ltd is Metropolitan’s specialist imaging and printing company with
a portfolio that includes copiers, printers, multifunction units and production
graphic arts machines under the Canon and Oce brands of products. The company
also provides a range of service products such as out sourced printing services
and customized layout and designing services at its Print Hub center.
Metropolitan has been
providing equipment for the imaging and printing sector since its beginnings in
1958. Being the sole authorized distributor for the Canon range of imaging and
printing products for the last 3 decades, Metropolitan supports the increase in
productivity and efficiency through these products and services that are cost
effective, efficient and needless to mention user friendly.
Being the market
leader in this sector, the company also offers value added services such as
scanning, designing and printing, and compiling large volume reports and
manuscripts. The service includes ‘exclusive color' printing at cost effective
terms offered to customers who seek instant and short-run print jobs.
The Company is a
leading workplace and engineering solutions provider for the public, private,
NGO and SME sectors. The company also offers a range of cash management
solutions, cheque scanning and network solutions from leading global brands
such as Casio, Double A and Canon.
We understand that
service support for any equipment purchased is a key to creating delighted
customers and to this we are committed to providing customers unparalleled
service support. But the sales or technical division may keep the track of
records in manually and rest of things will prepare with help of the office
application software. The managerial persons so will be analyzing the turnover
and gross profit in branches. We have the system to develop the organizing of
data in a storage area and get data back for analyzing when required.
Only important role
is to enter all the selling and technical related data input to the data store.
The system will be executed to manipulate the data processing section to
generate the management information as per the requirement of managerial staff.
The whole thing of back end part is presented here for implementing above
tasks.
Goal of the project
The company
basically two type of customers such as government and co-operate and the
product range divided into few categories as mentioned below.
·
Air Conditioner
·
Business Multi-Function Devices
·
Calculator
·
Cameras
·
Cash Management Solutions
·
Communication Solution
·
Consumables
·
Desktop & Laptops
·
Fax Multifunction
·
Generators
·
Inkjets Printers
·
Large Format Printing
·
Laser Printer
·
Phones & Tabs
·
Photocopy Machine
·
Projectors
·
Production & Graphic Art
·
Scanners
Various
product categories have the different models and its specification and unit
prices and the data may include the item name, model, capacity, special
features, unit price, other assecories, delivery schedule. The sales persons
may sell the product to customers and should achieve the target according to
company procedure. They can visit the customer’s location and identify the
requirements and make the quotation or proposal available for the customers by
selecting the products and their models.
When
order from the customer received, it should record in order file and sales
persons will consider the order as winning bid. Order details may include the
quotation no, date of receive, item name, quantity, unit price, remark.
Sales
persons should prepare the daily sales reports regularly when visited the
customers and collect their requirements. If the sales person sent them the
quotation, it also should record with details of quotation no/tender no, item
name, unit price, term, delivery date, validity of bid, customer name. The
government organization will invite to propose their quotation by calling
tender procedure.
If
it is a new customer, the sales person should collect the customer details such
as name, address, contact, fax, status, Contact person, special contact no,
designation. In sales division, different level of sales persons are available
for the customers to contact and their rank (Senior Sales EXE, Junior Sales
Exe, Junior Sales Rep, Senior Sales Rep), epf no, name, address may record in
personal file.
We
have to design a database to collect these large volumes of data which is
happening in manual system for better decisions and analyze in future to be
achieved the goal.
- · To stock in the fast moving equipments
- · To quote the best price by compare previous bid
- · To understand latest strength of equipments
- · To understand customer desired level on equipments
- · To understand the competition
- · To be solved the weakness of process
Implementation
Create Tables
CREATE TABLE 'child'
(
'name' varchar(80)
NOT NULL,
'epfno' varchar(10)
NOT NULL,
'relationship'
varchar(170) NOT NULL,
PRIMARY KEY ('name')
)
CREATE TABLE 'customer'
(
'custid' varchar(10)
NOT NULL,
'name' varchar(50)
NOT NULL,
'address'
varchar(150) NOT NULL,
'contact'
varchar(10) NOT NULL,
'designation'
varchar(65) NOT NULL,
PRIMARY KEY
('custid')
)
CREATE TABLE 'employee'
(
'name' varchar(80) NOT NULL,
'epfno' varchar(10) NOT NULL,
'address' varchar(170) NOT NULL,
'rank' varchar(50) NOT NULL,
PRIMARY KEY ('epfno')
)
CREATE TABLE 'orderdetails'
(
'orderno' varchar(10) NOT NULL,
'quotationno' varchar(10) NOT NULL,
'dateofreceive' datetime NOT NULL,
'itemno' varchar(10) NOT NULL,
'quantity' int(11) NOT NULL,
'price' int(11) NOT NULL,
'remark' varchar(200) NOT NULL,
PRIMARY KEY ('orderno')
)
ALTER TABLE 'quotation'
ADD CONSTRAINT ' orderdetails _ibfk_1' FOREIGN KEY (' quotationno ')
REFERENCES 'quotation' (' quotationno ');
ALTER TABLE 'quotation'
ADD CONSTRAINT ' orderdetails _ibfk_2' FOREIGN KEY (' itemno ')
REFERENCES 'products' (' itemno ');
CREATE TABLE 'products' (
'itemname' varchar(50) NOT NULL,
'model' varchar(50) NOT NULL,
'capacity' varchar(170) NOT NULL,
'brand' varchar(30) NOT NULL,
'features' varchar(170) NOT NULL,
'price' int(11) NOT NULL,
'accessories' varchar(2000) NOT NULL,
'deliveryschesule' varchar(70) NOT NULL,
PRIMARY KEY ('model')
)
CREATE TABLE 'quotation'
(
'quotationno' varchar(10) NOT NULL,
'model' varchar(50) NOT NULL,
'customerid' varchar(10) NOT NULL,
'epfno' varchar(10) NOT NULL,
'quantity' int(10) NOT NULL,
PRIMARY KEY ('quotationno')
)
ALTER TABLE 'quotation'
ADD CONSTRAINT 'quotation_ibfk_1' FOREIGN KEY ('customerid') REFERENCES
'customer' ('custid');
ALTER TABLE 'quotation'
ADD CONSTRAINT 'quotation_ibfk_2' FOREIGN KEY ('epfno') REFERENCES 'employee'
('epfno');
f
INSERT
INTO 'child' ('name', 'epfno', 'relationship') VALUES
('aseni',
'5042', 'daughter');
INSERT
INTO 'child' ('name', 'epfno', 'relationship') VALUES
('iduwara', '5066', 'son');
INSERT
INTO 'child' ('name', 'epfno', 'relationship') VALUES
('iduwari', '5066', 'daughter');
INSERT
INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES
('pdhs1',
'provincial director of health service', 'apura', '025-223381', 'supply
officer'),
INSERT
INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES
('ped1', 'provincial education department',
'apura', '025-226678', 'supply clerk');
INSERT
INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES
('rdhs1', 'regional director of health
service', 'apura', '025-222333', 'accountant');
INSERT
INTO 'customer' ('custid', 'name', 'address', 'contact', 'designation') VALUES
('uc1', 'urban council', 'apura',
'025-222234', 'accountant');
INSERT
INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES
('sumith',
'5042', 'medawachchiya', 'executive');
INSERT
INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES
('indika', '5066', 'medawachchiya',
'technical');
INSERT
INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES
('samarasinghe', '5097', 'talawa', 'junior
executive');
INSERT
INTO 'employee' ('name', 'epfno', 'address', 'rank') VALUES
('nimesh', '6066', 'srawastipura',
'technical');
INSERT
INTO 'orderdetails' ('orderno', 'quotationno', 'dateofreceive', 'itemno',
'quantity', 'price', 'remark') VALUES ('o1', 'q1', '2016-11-03 12:01:40',
'f788gs', 10, 2100, '');
INSERT
INTO 'products' ('itemname', 'model', 'capacity', 'brand', 'features', 'price',
'accessories', 'deliveryschesule') VALUES
('calculator',
'f788gs', '16 x 2 line lcd 497 function', 'canon', 'solar recharge', 2100,
'safety case', '1 week');
INSERT
INTO 'products' ('itemname', 'model', 'capacity', 'brand', 'features', 'price',
'accessories', 'deliveryschesule') VALUES
('generator', 'gsa1200', '1.2kw', 'pramac',
'recoil', 55000, 'lift handle', '1 weeks');
INSERT
INTO 'products' ('itemname', 'model', 'capacity', 'brand', 'features', 'price',
'accessories', 'deliveryschesule') VALUES
('copier', 'ir2520', '20 cpm', 'canon',
'duplex', 155000, 'full drum/toner kit', '3 weeks');
INSERT
INTO 'products' ('itemname', 'model', 'capacity', 'brand', 'features', 'price',
'accessories', 'deliveryschesule') VALUES
('air conditioner', 'ys9fz09', '9000 btu',
'york', 'auto shut when gas low', 51000, 'remote', '2 weeks');
INSERT
INTO 'products' ('itemname', 'model', 'capacity', 'brand', 'features', 'price',
'accessories', 'deliveryschesule') VALUES
('air conditioner', 'ys9fz12', '12000 btu r22
gas', 'york', 'auto shut if gas low', 65000, 'wireless lcd remote', '2 weeks');
INSERT
INTO 'quotation' ('quotationno', 'model', 'customerid', 'epfno', 'quantity')
VALUES
('qp1',
'f788gs', 'pdhs1', '5842', 20);
INSERT
INTO 'quotation' ('quotationno', 'model', 'customerid', 'epfno', 'quantity')
VALUES
('qp2', 'ys9fz12', 'rdhs1', '5842', 2);
INSERT
INTO 'quotation' ('quotationno', 'model', 'customerid', 'epfno', 'quantity')
VALUES
('qp3', 'ys9fz09', 'ped1', '5097', 1);
INSERT
INTO 'quotation' ('quotationno', 'model', 'customerid', 'epfno', 'quantity')
VALUES
('qp4', 'ir2520', 'uc1', '5842', 1);
Tools and methodologies used
Here I used data manipulation
language (DML) and DML modifies the database instance by inserting, updating
and deleting its data. DML is responsible for all forms data modification in a
database. MYSQL is equipped with data manipulation language (DML).
The tools of the SQL Transaction
language is used here for building the database structure and can connect to
external program using ADO.net Adapter of OLEDB Data Base system. As The SQL editors, we used MySQL editor and
various database Software such as Microsoft Access 2010, Microsoft SQL Express
2008. But we have more flexibility to handle the web based and portable android application with help of
MySql.
The relational database management system
is heavily used in this project and with proper realtions between entities is
normalized to extract correct data flows by external applications. To maintain
the correct data in entities or tables, we used constraints and data type
properties in attributes which was described the entity for accuracy of data.
Conclusion
The
fast moving marketing company in Sri Lanka had a huge volume of data to store
and need the management information accordingly with short time for better
analyzing of future sales or to forecast the turnover with heavy range of
products. Therefore the database system is an essential requirement in such
situation. So the design of database system is helpful to any front end
software to communicate with the end user for the any purpose of data analyzing
part.
