· 6 years ago · Mar 21, 2019, 02:16 AM
1-- MySQL Script generated by MySQL Workbench
2-- Mon Mar 11 21:01:04 2019
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema staffdb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `staffdb` ;
14
15-- -----------------------------------------------------
16-- Schema staffdb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `staffdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
19USE `staffdb` ;
20
21-- -----------------------------------------------------
22-- Table `staffdb`.`staff_role`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `staffdb`.`staff_role` ;
25
26CREATE TABLE IF NOT EXISTS `staffdb`.`staff_role` (
27 `s_role_ID` CHAR(30) NOT NULL,
28 `s_role_name` VARCHAR(45) NOT NULL,
29 `s_role_detail` VARCHAR(255) NOT NULL,
30 PRIMARY KEY (`s_role_ID`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `staffdb`.`staff`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `staffdb`.`staff` ;
38
39CREATE TABLE IF NOT EXISTS `staffdb`.`staff` (
40 `s_ID` INT NOT NULL AUTO_INCREMENT,
41 `s_u_ID` VARCHAR(256) NOT NULL,
42 `s_u_PW` VARCHAR(256) NOT NULL,
43 `s_role_ID` CHAR(30) NOT NULL,
44 `s_name` VARCHAR(45) NOT NULL,
45 `s_phone` CHAR(12) NOT NULL,
46 `s_address` VARCHAR(100) NOT NULL,
47 `s_email` VARCHAR(256) NULL,
48 `s_employed` TINYINT(1) NOT NULL,
49 PRIMARY KEY (`s_ID`),
50 INDEX `fk_s_cate_ID_idx` (`s_role_ID` ASC),
51 CONSTRAINT `fk_s_role_ID_staff`
52 FOREIGN KEY (`s_role_ID`)
53 REFERENCES `staffdb`.`staff_role` (`s_role_ID`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `staffdb`.`rights`
61-- -----------------------------------------------------
62DROP TABLE IF EXISTS `staffdb`.`rights` ;
63
64CREATE TABLE IF NOT EXISTS `staffdb`.`rights` (
65 `right_ID` CHAR(10) NOT NULL,
66 `right_name` VARCHAR(45) NOT NULL,
67 `right_detail` TEXT NULL,
68 PRIMARY KEY (`right_ID`))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `staffdb`.`right_detail`
74-- -----------------------------------------------------
75DROP TABLE IF EXISTS `staffdb`.`right_detail` ;
76
77CREATE TABLE IF NOT EXISTS `staffdb`.`right_detail` (
78 `r_d_ID` INT NOT NULL AUTO_INCREMENT,
79 `s_role_ID` CHAR(30) NOT NULL,
80 `right_ID` CHAR(10) NOT NULL,
81 `right_has` TINYINT(1) NOT NULL,
82 INDEX `fk_s_cate_ID_idx` (`s_role_ID` ASC),
83 INDEX `fk_right_ID_idx` (`right_ID` ASC),
84 PRIMARY KEY (`r_d_ID`),
85 CONSTRAINT `fk_r_s_role_ID`
86 FOREIGN KEY (`s_role_ID`)
87 REFERENCES `staffdb`.`staff_role` (`s_role_ID`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION,
90 CONSTRAINT `fk_r_right_ID`
91 FOREIGN KEY (`right_ID`)
92 REFERENCES `staffdb`.`rights` (`right_ID`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98SET SQL_MODE=@OLD_SQL_MODE;
99SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
100SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
101
102-- -----------------------------------------------------
103-- Data for table `staffdb`.`staff_role`
104-- -----------------------------------------------------
105START TRANSACTION;
106USE `staffdb`;
107INSERT INTO `staffdb`.`staff_role` (`s_role_ID`, `s_role_name`, `s_role_detail`) VALUES ('admin', 'Quản trị viên', 'Nắm quyá»n tất cả');
108INSERT INTO `staffdb`.`staff_role` (`s_role_ID`, `s_role_name`, `s_role_detail`) VALUES ('manager', 'Chủ shop', 'Nắm quyá»n tất cả chỉ dÆ°á»›i Quản trị viên');
109INSERT INTO `staffdb`.`staff_role` (`s_role_ID`, `s_role_name`, `s_role_detail`) VALUES ('fullstaff', 'Nhân viên chủ lá»±c', 'Quản lý tất cả ná»™i dung vá» hoa, hóa Ä‘Æ¡n, bình luáºn, đánh giá... Không quản lý nhân viên');
110INSERT INTO `staffdb`.`staff_role` (`s_role_ID`, `s_role_name`, `s_role_detail`) VALUES ('normalstaff', 'Nhân viên bình thÆ°á»ng', 'Quản lý ná»™i dung vá» hóa Ä‘Æ¡n, bình luáºn');
111INSERT INTO `staffdb`.`staff_role` (`s_role_ID`, `s_role_name`, `s_role_detail`) VALUES ('bot', 'Con bot vô năng', 'Test không chức năng');
112
113COMMIT;
114
115
116-- -----------------------------------------------------
117-- Data for table `staffdb`.`staff`
118-- -----------------------------------------------------
119START TRANSACTION;
120USE `staffdb`;
121INSERT INTO `staffdb`.`staff` (`s_ID`, `s_u_ID`, `s_u_PW`, `s_role_ID`, `s_name`, `s_phone`, `s_address`, `s_email`, `s_employed`) VALUES (0, 'admin', 'admin', 'admin', 'Quản trị viên', '0900090000', 'trên trá»i', 'admin@admin.com', 1);
122INSERT INTO `staffdb`.`staff` (`s_ID`, `s_u_ID`, `s_u_PW`, `s_role_ID`, `s_name`, `s_phone`, `s_address`, `s_email`, `s_employed`) VALUES (null, 'lam1990', 'lam1990', 'manager', 'Nguyễn Thế Lâm', '0905591115', '123 abc', 'lam1990@hdtv.com', 1);
123
124COMMIT;
125
126
127-- -----------------------------------------------------
128-- Data for table `staffdb`.`rights`
129-- -----------------------------------------------------
130START TRANSACTION;
131USE `staffdb`;
132INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q01', 'Quyá»n quản lý bó hoa', 'Quyá»n quản lý bó hoa');
133INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q02', 'Quyá»n quản lý hoa', 'Quyá»n quản lý hoa');
134INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q03', 'Quyá»n quản lý loại hoa', 'Quyá»n quản lý loại hoa');
135INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q04', 'Quyá»n quản lý dịp', 'Quyá»n quản lý dịp');
136INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q05', 'Quyá»n quản lý nhân viên', 'Quyá»n quản lý nhân viên');
137INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q06', 'Quyá»n quản lý Ä‘Æ¡n hà ng', 'Quyá»n quản lý Ä‘Æ¡n hà ng');
138INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q07', 'Quyá»n quản lý bình luáºn', 'Quyá»n quản lý bình luáºn');
139INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q08', 'Quyá»n quản lý đánh giá', 'Quyá»n quản lý đánh giá');
140INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q00', 'Quyá»n quản trị viên', 'Quyá»n tối cao');
141INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q09', 'Quyá»n quản lý quyá»n nhân viên', 'Quyá»n quản lý quyá»n nhân viên');
142INSERT INTO `staffdb`.`rights` (`right_ID`, `right_name`, `right_detail`) VALUES ('Q10', 'Quyá»n quản lý khách hà ng', 'Quyá»n quản lý khách hà ng');
143
144COMMIT;
145
146
147-- -----------------------------------------------------
148-- Data for table `staffdb`.`right_detail`
149-- -----------------------------------------------------
150START TRANSACTION;
151USE `staffdb`;
152INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (1, 'admin', 'Q00', 1);
153INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (2, 'manager', 'Q01', 1);
154INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (3, 'manager', 'Q02', 1);
155INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (4, 'manager', 'Q03', 1);
156INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (5, 'manager', 'Q04', 1);
157INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (6, 'manager', 'Q05', 1);
158INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (7, 'manager', 'Q06', 1);
159INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (8, 'manager', 'Q07', 1);
160INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (9, 'manager', 'Q08', 1);
161INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (10, 'manager', 'Q09', 1);
162INSERT INTO `staffdb`.`right_detail` (`r_d_ID`, `s_role_ID`, `right_ID`, `right_has`) VALUES (11, 'manager', 'Q10', 1);
163
164COMMIT;