Business Intelligence and Data Visualization Project

Desklib provides solved assignments and past papers for students.

DATABASES AND BUSINESS INTELLIGENCE
Contents

ER DIAGRAM..............................................................................................................................3
CREATE DATABASE.....................................................................................................................4
CREATE TABLES.......................................................................................................................... 5
INSERT DATA INTO TABLES........................................................................................................ 9
RETRIEVE DATA FROM TABLE.................................................................................................. 15
MS ACCESS AS FRONTEND TO MYSQL DATABASE...................................................................19
BUSINESS INTELLIGENCE..........................................................................................................24
REFERENCES.............................................................................................................................30

CUSTOMER
COLUMN NAME DATA TYPE SIZE
CUSTOMER_ID (PK) INTEGER 10
FIRST_NAME VARCHAR 50
LAST_NAME VARCHAR 50
BIRTHDAY DATE
ADDRESS VARCHAR 50
STATE VARCHAR 50
PIN CODE INTEGER 10
ORDERS
COLUMN NAME DATA TYPE SIZE
ORDER_ID (PK) INTGER 10
CUSTOMER_ID (FK) INTGER 10
ORDER_STATUS VARCHAR 50
ORDER_AMOUNT DECIMAL 10,2
PRODUCTS
COLUMN NAME DATA TYPE SIZE
PRODUCT_ID (PK) INTGER 10
CATEGORY_ID (FK) VARCHAR 50
PRICE DECIMAL 10,2
MANAGEMENT
COLUMN NAME DATA TYPE SIZE
DEPARTMENT_ID (PK) INTGER 10
DEPARTMENT_NAME VARCHAR 50
CATEGORY
COLUMN NAME DATA TYPE SIZE
CATEGORY_ID (PK) INTGER 10
CATEGORY_NAME VARCHAR 50

ER DIAGRAM
(Bagui & Earp,2011)

CREATE DATABASE
(Kroenke & Auer,2012)
Create database sales;
Use sales database
Use sales;

CREATE TABLES
CUSTOMER
CREATE TABLE CUSTOMER
(CUSTOMER_ID INTEGER PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR (50) NOT NULL,
LAST_NAME VARCHAR (50) NOT NULL,
BIRTHDAY DATE NOT NULL,
ADDRESS VARCHAR (50) NOT NULL,
STATE VARCHAR (2) NOT NULL,
PIN_CODE INTEGER
);

ORDER
CREATE TABLE ORDERS
(ORDER_ID INTEGER PRIMARY KEY NOT NULL,
CUSTOMER_ID INTEGER NOT NULL,
ORDER_STATUS VARCHAR (50) NOT NULL,
ORDER_AMOUNT DECIMAL (10, 2),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID)
);
CATEGORY
CREATE TABLE CATEGORY
(CATEGORY_ID INTEGER PRIMARY KEY NOT NULL,
CATEGORY_NAME VARCHAR (50) NOT NULL
);

MANAGEMENT
CREATE TABLE MANAGEMENT (
DEPARTMENT_ID INTEGER PRIMARY KEY NOT NULL,
DEPARTMENT_NAME VARCHAR (50) NOT NULL);

PRODUCTS
CREATE TABLE PRODUCTS (
PRODUCT_ID INTEGER PRIMARY KEY NOT NULL,
CATEGORY_ID INTEGER NOT NULL,
PRICE DECIMAL NOT NULL,
FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (CATEGORY_ID)
);

INSERT DATA INTO TABLES
(Malecha et al., 2010)
CUSTOMER
INSERT INTO `sales`.`customer` (`CUSTOMER_ID`,`FIRST_NAME`,`LAST_NAME`,`BIRTHDAY`,
`ADDRESS`, `STATE`,`PIN_CODE`) VALUES (1,"Aditya","Raj","1994-04-25","97 Carr St West
Perth","WA",6005);
INSERT INTO `sales`.`customer` (`CUSTOMER_ID`,`FIRST_NAME`,`LAST_NAME`,`BIRTHDAY`,
`ADDRESS`, `STATE`,`PIN_CODE`) VALUES (2,"Ravi","Sharma","1995-10-30","1008 Wellington
St, West Perth","WA",6006);
INSERT INTO `sales`.`customer` (`CUSTOMER_ID`,`FIRST_NAME`,`LAST_NAME`,`BIRTHDAY`,
`ADDRESS`, `STATE`,`PIN_CODE`) VALUES (3,"Suraj","Verma","1993-06-05","42 King Edward St,
South Perth","WA",6151);
INSERT INTO `sales`.`customer` (`CUSTOMER_ID`,`FIRST_NAME`,`LAST_NAME`,`BIRTHDAY`,
`ADDRESS`, `STATE`,`PIN_CODE`) VALUES (4,"Sandeep","Agrawal","1992-10-04","20 Saunders
St, East Perth","WA",6004);
INSERT INTO `sales`.`customer` (`CUSTOMER_ID`,`FIRST_NAME`,`LAST_NAME`,`BIRTHDAY`,
`ADDRESS`, `STATE`,`PIN_CODE`) VALUES (5,"Deepika","Pandey","1991-05-12","58 Newcastle
St, Perth","WA",6000);

ORDER
INSERT INTO
`sales`.`orders`(`ORDER_ID`,`CUSTOMER_ID`,`ORDER_STATUS`,`ORDER_AMOUNT`)
VALUES(100,1,"DELIVERED",1000.00);
INSERT INTO `sales`.`orders`
(`ORDER_ID`,`CUSTOMER_ID`,`ORDER_STATUS`,`ORDER_AMOUNT`)
VALUES(101,2,"DELIVERED",1500.00);
INSERT INTO `sales`.`orders`
(`ORDER_ID`,`CUSTOMER_ID`,`ORDER_STATUS`,`ORDER_AMOUNT`)
VALUES(102,3,"DELIVERED",2500.00);
INSERT INTO `sales`.`orders`
(`ORDER_ID`,`CUSTOMER_ID`,`ORDER_STATUS`,`ORDER_AMOUNT`)
VALUES(103,1,"UNDELIVERED",1500.00);