sql-schema-cd-database

-- CD Database Schema
-- Version 1.0
-- Copyright (c) 2014, MySQL AB
-- All rights reserved.
-- Bo mon HQT CSDL
-- Nhom 24 - Tao website ban dia CD ca nhac
-- Thanh Vien
-- 1. Dao Minh Hoa
-- 2. Hoang Thi Nhu
-- 3. @Nhung
-- 4. Pham Tat Dat
SET @OLD_UNIQUE_CHECKS =@@UNIQUE_CHECKS,
 UNIQUE_CHECKS = 0;


SET @OLD_FOREIGN_KEY_CHECKS =@@FOREIGN_KEY_CHECKS,
 FOREIGN_KEY_CHECKS = 0;


SET @OLD_SQL_MODE =@@SQL_MODE,
 SQL_MODE = 'TRADITIONAL';

DROP DATABASE
IF EXISTS cd;

CREATE DATABASE cd CHARACTER SET utf8 COLLATE utf8_general_ci;

USE cd;

--
-- Table structure for table `cdinfo`
--
DROP TABLE
IF EXISTS `cdInfo`;

CREATE TABLE `cdInfo` (
    `id_cd` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_kind` INT (11) UNSIGNED NOT NULL,
    `cd_code` VARCHAR (11) NOT NULL,
    `name_cd` VARCHAR (50) NOT NULL,
    `description` text,
    `singer` VARCHAR (30) NOT NULL,
    `thumbnail` text,
    `price` INT (20) NOT NULL,
    `quantityinstock` INT (11) NOT NULL,
    PRIMARY KEY (`id_cd`),
    KEY `id_kind` (`id_kind`),
    CONSTRAINT `fk_id_kind` FOREIGN KEY (`id_kind`) REFERENCES `cdKind` (`id_kind`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

--
-- Table structure for table `cdkind`
--
DROP TABLE
IF EXISTS `cdKind`;

CREATE TABLE `cdKind` (
    `id_kind` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name_kind` VARCHAR (50) NOT NULL,
    PRIMARY KEY (`id_kind`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

LOCK TABLES `cdkind` WRITE;
INSERT INTO `cdkind` (`id_kind`, `name_kind`) VALUES
(2, 'Nhạc Trẻ'),
(3, 'Nhạc cách mạng'),
(4, 'Nhạc tiền chiến'),
(5, 'Phim khoa học viễn tưởng'),
(6, 'Phim tà i liệu'),
(7, 'Phim hà nh động'),
(8, 'Phim hà i'),
(9, 'Nhạc Việt nam'),
(10, 'Nhạc quốc tế'),
(11, 'nhạc thiếu nhi'),
(12, 'Phim kinh dị');

UNLOCK TABLES;
--
-- Table structure for table ``order``
--
DROP TABLE
IF EXISTS `order`;

CREATE TABLE `order` (
    `id_order` INT (11) NOT NULL,
    `id_customer` INT (11) NOT NULL,
    `orderDate` DATE NOT NULL,
    `requiredDate` DATE NOT NULL,
    `shippedDate` DATE DEFAULT NULL,
    `STATUS` VARCHAR (15) NOT NULL,
    `comments` text,
    PRIMARY KEY (`id_order`),
    KEY `id_customer` (`id_customer`),
    CONSTRAINT `fk_order_1` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_id_customer` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

--
-- Table structure for table `orderdetail`
--
DROP TABLE
IF EXISTS `orderDetail`;

CREATE TABLE `orderDetail` (
    `id_order` INT (11) UNSIGNED NOT NULL,
    `cd_code` VARCHAR (11) NOT NULL,
    `quantityOrdered` INT (11) NOT NULL,
    `priceEach` DOUBLE NOT NULL,
    PRIMARY KEY (`id_order`),
    KEY `cd_code`(`cd_code`),
    CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id_order`) REFERENCES `orders` (`id_order`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

--
-- Table structure for table `customers`
--
DROP TABLE
IF EXISTS `customers`;

CREATE TABLE `customers` (
    `id_customer` INT (11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (50) NOT NULL,
    `password` VARCHAR (50) NOT NULL,
    `fullname` VARCHAR (50) NOT NULL,
    `address` VARCHAR (50) NOT NULL,
    `phone` VARCHAR (50) NOT NULL,
    `email` text NOT NULL,
    `status` INT (5) UNSIGNED NOT NULL DEFAULT '2',
    PRIMARY KEY (`id_customer`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

LOCK TABLES `customers` WRITE;

INSERT INTO `customers` (`id_customer`, `username`, `password`, `fullname`, `address`, `phone`, `email`, `status`) VALUES
(1, 'administrator', '6d626c9e354076a1ffe2b2eb46b10c76', 'Phạm Tất Đạt', '69A/38 Hoàng Văn Thái', '0963012472', 'tatdat171@gmail.com', 1),
(2, 'tatdat171', 'c0b38c91fc630782af1935721b3ce95c', 'Phạm Tất Đạt', '69A/38 Hoàng Văn Thái', '01692019206', 'kunny171@gmail.com', 1),
(3, 'admin23', 'd41d8cd98f00b204e9800998ecf8427e', 'Tạ Hùng Giang', 'khu sơn', '0979670703', 'he@yahoo.com', 1),
(4, 'bachnx', '827ccb0eea8a706c4c34a16891f84e7b', 'Nguyễn Thừa Ngôn', 'vĩnh phúc', '01245678', 'bach@gmail.com', 2),
(5, 'haanhdon', 'e10adc3949ba59abbe56e057f20f883e', 'Hà Hữu Đôn', 'hà nội', '0974136509', 'haanhdon@gmail.com', 2),
(6, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Nguyễn Xuân Bách', 'Kiều Mai - Hà Nội', '0976256106', 'nhatgai192@gmail.com', 2),
(7, 'ongbach12', 'd41d8cd98f00b204e9800998ecf8427e', 'Bùi Viết Nam', 'Thanh Xuân', '0913443506', 'salesoff@live.com', 2),
(8, 'admin123456', '123456', 'demo', 'demo', '0963012472', 'demo@demo.com', 2),
(9, 'demo1', 'e10adc3949ba59abbe56e057f20f883e', 'demo', 'demo', '0534523231', 'demo1@gmail.com', 2),
(10, 'demo234', '25f9e794323b453885f5181f1b624d0b', 'demo123', 'demo123456', '0987654321', 'demo2@gmail.com', 2),
(11, 'demo45', 'b6cf1f80542b430c758333ad76576f86', 'sen', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo45@live.com', 2),
(12, 'demo10', '43514a20c7801ebd4b1e6769939dd95f', 'demo10', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo10@gmail.com', 2),
(13, 'demo6', 'c0bb1c551d1313c106d778d016b6bbad', 'demo6', '69A/38 Hoà ng Văn Thái', '0987654321', 'demo6@gmail.com', 2),
(14, 'demo7', '71e781c93ace9742f8b4d179141eecd9', 'Demo', 'demo123456', '0987654321', 'demo7@live.com', 2),
(15, 'demo8', 'e807f1fcf82d132f9bb018ca6738a19f', 'Demo', 'demo123456', '0987654321', 'demo8@demo.com', 2);

UNLOCK TABLES;

--
-- Table structure for table `customersKind`
--
/*
CREATE TABLE `customersKind` (
    `idnumber` INT (15) NOT NULL AUTO_INCREMENT,
    `status` INT (5) UNSIGNED NOT NULL,
    `namekind` VARCHAR (20) NOT NULL,
    PRIMARY KEY (`idnumber`),
    KEY `status` (`status`),
    CONSTRAINT `fk_customerkind_1` FOREIGN KEY (`status`) REFERENCES `customers` (`status`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

LOCK TABLES `customersKind` WRITE;

INSERT INTO customersKind (status, namekind)
VALUES
    ('1', 'Administrator');

INSERT INTO customersKind (status, namekind)
VALUES
    ('2', 'User Normal');

INSERT INTO customersKind (status, namekind)
VALUES
    ('3', 'User VIP');

UNLOCK TABLES;
*/
--
-- Table structure for table `payments`
--
DROP TABLE
IF EXISTS `payment`;

CREATE TABLE `payments` (
    `id_customer` INT (11) NOT NULL,
    `checkNumber` VARCHAR(50) NOT NULL,
    `paymentDate` DATE NOT NULL,
    `amount` DOUBLE NOT NULL,
    PRIMARY KEY (`id_customer`, `checkNumber`),
    CONSTRAINT `fk_payments_1` FOREIGN KEY (`id_customer`) REFERENCES `customers` (`id_customer`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

LOCK TABLES `payments` WRITE;
INSERT INTO `payments` (`id_customer`, `checkNumber`, `paymentDate`, `amount`) VALUES
(1, 'HQ336336', '2014-04-27', 60000),
(1, 'JM555205', '2014-04-27', 145000),
(1, 'OM314933', '2014-04-26', 16700),
(1, 'BO864823', '2014-04-26', 1419000),
(1, 'HQ55022', '2014-04-27', 3264000),
(1, 'ND748579', '2014-04-25', 333000),
(4, 'GG31455', '2014-04-25', 45800),
(4, 'MA765515', '2014-04-25', 82000),
(4, 'NP603840', '2014-04-28', 75000),
(4, 'NR27552', '2014-04-25', 448900),
(4, 'DB933704', '2014-04-25', 195000),
(4, 'LN373447', '2014-04-25', 470000),
(4, 'NG94694', '2014-04-25', 400000),
(5, 'DB889831', '2014-04-26', 502000),
(5, 'FD317790', '2014-04-28', 140000),
(5, 'KI831359', '2014-04-27', 178000),
(5, 'MA302151', '2014-04-28', 346000),
(5, 'AE215433', '2014-04-27', 1012000),
(5, 'BG255406', '2014-04-28', 854000),
(5, 'CQ287967', '2014-04-25', 110000),
(6, 'ET64396', '2014-04-26', 83000),
(6, 'HI366474', '2014-04-27', 47000),
(6, 'HR86578', '2014-04-27', 556000),
(6, 'KI131716', '2014-04-25', 110000),
(6, 'LF217299', '2014-04-26', 433000),
(7, 'NT141748', '2014-04-25', 450000),
(7, 'DI925118', '2014-04-28', 105000),
(7, 'FA465482', '2014-04-26', 24000),
(7, 'FH668230', '2014-04-25', 33000),
(7, 'IP383901', '2014-04-26', 74000),
(7, 'DM826140', '2014-04-25', 262000),
(7, 'ID449593', '2014-04-25', 239000),
(7, 'PI42991', '2014-04-25', 165000),
(7, 'CL442705', '2014-04-25', 222000),
(7, 'MA724562', '2014-04-27', 500000),
(7, 'NB445135', '2014-04-25', 35000),
(8, 'AU364101', '2014-04-27', 36000),
(8, 'DB583216', '2014-04-27', 36000),
(8, 'DL460618', '2014-04-27', 468000),
(8, 'HJ32686', '2014-04-28', 598000),
(8, 'ID10962', '2014-04-28', 1100000),
(8, 'IN446258', '2014-04-25', 65000),
(8, 'JE105477', '2014-04-26', 120000),
(8, 'JN355280', '2014-04-26', 495000),
(8, 'JN722010', '2014-04-25', 402000),
(8, 'KT52578', '2014-04-25', 638000),
(8, 'MC46946', '2014-04-25', 354000),
(8, 'MF629602', '2014-04-26', 2000000),
(8, 'NU627706', '2014-04-26', 261000),
(8, 'IR846303', '2014-04-25', 36000),
(8, 'LA685678', '2014-04-25', 76000),
(8, 'CN328545', '2014-04-27', 471000),
(8, 'ED39322', '2014-04-26', 28000),
(8, 'HR182688', '2014-04-27', 20000),
(8, 'JJ246391', '2014-04-25', 50000),
(9, 'FP549817', '2014-04-26', 409000),
(9, 'FU793410', '2014-04-26', 4000),
(9, 'LJ160635', '2014-04-25', 39000),
(9, 'BI507030', '2014-04-25', 440000),
(9, 'DD635282', '2014-04-25', 260000),
(9, 'KM172879', '2014-04-26', 100000),
(9, 'ME497970', '2014-04-27', 35000),
(9, 'BF686658', '2014-04-25', 58000),
(9, 'GB852215', '2014-04-26', 20000),
(9, 'IP568906', '2014-04-26', 58000),
(9, 'KI884577', '2014-04-25', 399000),
(9, 'HI618861', '2014-04-27', 35000),
(9, 'NN711988', '2014-04-25', 633000),
(9, 'BR352384', '2014-04-25', 243000),
(9, 'BR478494', '2014-04-26', 507000),
(9, 'KG644125', '2014-04-27', 1260000),
(10, 'NI908214', '2014-04-27', 386000),
(10, 'BQ327613', '2014-04-26', 387000),
(10, 'DC979307', '2014-04-25', 441000),
(10, 'LA318629', '2014-04-28', 224000),
(10, 'ED743615', '2014-04-27', 125000),
(10, 'GN228846', '2014-04-27', 85000),
(10, 'GB878038', '2014-04-25', 180000),
(10, 'IL104425', '2014-04-25', 42000),
(10, 'AD832091', '2014-04-25', 190000),
(10, 'CE51751', '2014-04-27', 5120000),
(10, 'EH208589', '2014-04-25', 333000),
(11, 'GP545698', '2014-04-25', 1180000),
(11, 'IG462397', '2014-04-28', 2030000),
(11, 'CITI3434344', '2014-04-27', 2850000),
(11, 'IO448913', '2014-04-27', 2480000),
(11, 'PI15215', '2014-04-25', 420000),
(12, 'AU750837', '2014-04-26', 151000),
(12, 'CI381435', '2014-04-27', 47000),
(12, 'CM564612', '2014-04-25', 22000),
(12, 'GQ132144', '2014-04-28', 54000),
(12, 'OH367219', '2014-04-26', 4440000),
(13, 'AE192287', '2014-04-25', 2360000),
(13, 'AK412714', '2014-04-27', 376000),
(13, 'KA602407', '2014-04-25', 34000),
(13, 'AM968797', '2014-04-27', 528000),
(13, 'BQ39062', '2014-04-25', 471000),
(13, 'KL124726', '2014-04-27', 484000);
UNLOCK TABLES;
--
-- CREATE PROCEDURE check login and register
--
DROP PROCEDURE IF EXISTS `check_login`;
DELIMITER $$
CREATE PROCEDURE `check_login`(USER VARCHAR(50), pass VARCHAR(50))
READS SQL DATA
BEGIN SELECT `username`,`password` FROM customers WHERE USER=`username` AND pass=`password`;
END$$
DELIMITER;

DROP PROCEDURE IF EXISTS `check_reg`;
DELIMITER $$
CREATE PROCEDURE `check_reg`(USER VARCHAR(50), email_check text)
READS SQL DATA
BEGIN SELECT `username`,`email` FROM customers WHERE USER=`username` AND email_check=`email`;
END$$
DELIMITER ;

--
-- Create VIEW
--
DROP VIEW
IF EXISTS `cd_show`;
CREATE VIEW cd_show AS SELECT
    cdinfo.id_cd AS id,
    cdinfo.name_cd AS name,
    cdinfo.description,
    cdkind.name_kind AS category,
    cdinfo.singer,
    cdinfo.quantityinstock,
    cdinfo.price
FROM
    cdinfo
JOIN cdkind
WHERE
    cdinfo.id_kind = cdkind.id_kind
ORDER BY cdinfo.id_cd ASC;

--
-- View tong so user hien tai
--
DROP VIEW IF EXISTS `total_user`;
CREATE VIEW total_user AS SELECT COUNT(*) AS totaluser FROM customers;

--
-- View tong so san pham CD hien tai
--
DROP VIEW IF EXISTS `total_cd`;
CREATE VIEW total_cd AS SELECT COUNT(*) AS totalcd FROM cdinfo;

--
-- View tong so danh muc san pham CD hien tai
--
DROP VIEW IF EXISTS `total_cd_kind`;
CREATE VIEW total_cd_kind AS SELECT COUNT(*) AS totalcdkind FROM cdkind;

--
-- View tong so tien thu duoc trong ngay
--
DROP VIEW IF EXISTS `total_money_daily`;
CREATE VIEW total_moey_daily AS SELECT SUM(amount) AS total_money_daily FROM payments WHERE paymentDate = CURDATE();

--
-- View tong so tien thu duoc
--
DROP VIEW IF EXISTS `total_money`;
CREATE VIEW total_money AS SELECT SUM(amount) AS total_money FROM payments;

--
-- View tong so san pham da ban duoc
--
DROP VIEW IF EXISTS `total_cd_sale`;
CREATE VIEW `total_cd_sale` AS SELECT SUM(orderdetail.quantityOrdered) AS total_cd_sale FROM orderdetail JOIN `order` WHERE `order`.id_order = orderdetail.id_order AND `order`.`STATUS`='Shipped';

--
-- View tong so da dat mua trong ngay
--
DROP VIEW IF EXISTS `total_order_daily`;
CREATE VIEW `total_order_daily` AS SELECT COUNT(*) AS total_order_daily FROM `order` WHERE orderDate =CURDATE();

--
-- View 10 giap dich gan day
--
DROP VIEW IF EXISTS `payments_last_10`;
CREATE VIEW `payments_last_10` AS SELECT customers.username AS customer, payments.paymentDate AS TIME, payments.amount FROM payments JOIN customers WHERE payments.id_customer = customers.id_customer ORDER BY payments.paymentDate DESC LIMIT 10;