· 10 years ago · Jun 04, 2016, 11:36 AM
1-- MySQL Script generated by MySQL Workbench
2-- 04/18/16 23:47:16
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='TRADITIONAL,ALLOW_INVALID_DATES';
9SET foreign_key_checks = 'ON';
10
11-- -----------------------------------------------------
12-- Schema kenkoF
13-- -----------------------------------------------------
14-- kekno test
15
16-- -----------------------------------------------------
17-- Schema kenkoF
18--
19-- kekno test
20-- -----------------------------------------------------
21CREATE SCHEMA IF NOT EXISTS `kenkoF` DEFAULT CHARACTER SET utf8 ;
22USE `kenkoF` ;
23
24
25
26-- -----------------------------------------------------
27-- Table `kenkoF`.`trainer`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS trainer (
30 `trainer_id` INT NOT NULL AUTO_INCREMENT,
31 PRIMARY KEY (`trainer_id`),
32 UNIQUE INDEX `trainer_id_UNIQUE` (`trainer_id` ASC),
33 `username` VARCHAR(16) NOT NULL,
34 `email` VARCHAR(255) NOT NULL,
35 `password` VARCHAR(32) NOT NULL,
36 `create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
37 `rating` INT NULL,
38 `images` LONGBLOB NULL,
39 `type` TINYINT(1) NULL)
40 ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `kenkoF`.`agent`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS agent (
47 `username` VARCHAR(16) NOT NULL,
48 `email` VARCHAR(255) NULL,
49 `password` VARCHAR(35) NOT NULL,
50 `create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
51 `agent_id` INT NOT NULL AUTO_INCREMENT,
52 PRIMARY KEY (`agent_id`),
53 UNIQUE INDEX `agent_id_UNIQUE` (`agent_id` ASC))
54 ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `kenkoF`.`program`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS program (
61 `program_id` INT NOT NULL AUTO_INCREMENT,
62 `trainer_id` INT NOT NULL,
63 PRIMARY KEY (`program_id`),
64 CONSTRAINT FOREIGN KEY (`trainer_id`) REFERENCES trainer(`trainer_id`)
65 ON DELETE NO ACTION
66 ON UPDATE NO ACTION)
67ENGINE = InnoDB;
68
69
70-- -----------------------------------------------------
71-- Table `kenkoF`.`customer`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS customer (
74 `username` VARCHAR(16) NOT NULL,
75 `email` VARCHAR(255) NOT NULL,
76 `password` VARCHAR(35) NOT NULL,
77 `create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
78 `weight` DECIMAL(5) NULL,
79 `customer_id` INT NOT NULL AUTO_INCREMENT,
80 `agent_id` INT NOT NULL,
81 `program_id` INT NOT NULL,
82 PRIMARY KEY (`customer_id`),
83 UNIQUE INDEX `customer_id_UNIQUE` (`customer_id` ASC),
84 CONSTRAINT `agent_id`
85 FOREIGN KEY (`agent_id`)
86 REFERENCES `kenkoF`.`agent` (`agent_id`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION,
89 CONSTRAINT `program_id`
90 FOREIGN KEY (`program_id`)
91 REFERENCES `kenkoF`.`program` (`program_id`)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION);
94
95
96-- -----------------------------------------------------
97-- Table `kenkoF`.`cron `
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS cron (
100 `cron_id` INT NOT NULL AUTO_INCREMENT,
101 `hour` INT NULL,
102 `minute` INT NULL,
103 `day` INT NULL,
104 `week` INT NULL,
105 `year` INT NULL,
106 `month` INT NULL,
107 `program_id` INT NOT NULL,
108 `item_desc` VARCHAR(255) NULL,
109 PRIMARY KEY (`cron_id`),
110 INDEX `cron_id_UNIQUE` (`cron_id` ASC),
111 CONSTRAINT `cron_id`
112 FOREIGN KEY (`program_id`)
113 REFERENCES `kenkoF`.`program` (`program_id`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION);
116
117
118SET SQL_MODE=@OLD_SQL_MODE;
119SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
120SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
121
122-- ---------------------------------------------------
123-- Test Data
124-- ---------------------------------------------------
125insert into agent(username, email, password) values ("mous", "mous@gmail.com", "1234");
126insert into trainer(username, email, password) values ("hossam", "hossam@gmail.com", "1234");
127insert into program(trainer_id) values (1);
128insert into customer(username, email, password, weight, program_id, agent_id) values ("hadi", "hadi@gmail.com", "1234", 80.0,1, 1);
129insert into cron(program_id, item_desc) values(1, "eat 1/2 pound of salmon with 2 spoons of white rice");