-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.45-community-nt
--
-- Create schema hanbaidb
--

CREATE DATABASE IF NOT EXISTS hanbaidb;
USE hanbaidb;

DROP TABLE IF EXISTS `dt_nhmeisai`;
DROP TABLE IF EXISTS `dt_nouhin`;

DROP TABLE IF EXISTS `mt_syohin`;
DROP TABLE IF EXISTS `mt_tokui`;
DROP TABLE IF EXISTS `mt_tantou`;

DROP TABLE IF EXISTS `mt_syozoku`;
DROP TABLE IF EXISTS `mt_rank`;
DROP TABLE IF EXISTS `mt_sbunrui`;
DROP TABLE IF EXISTS `mt_maker`;

--
-- Definition of table `mt_syozoku`
--

CREATE TABLE `mt_syozoku` (
  `SZCD` smallint(5) unsigned NOT NULL,
  `SZNAME` varchar(40) NOT NULL,
  PRIMARY KEY  (`SZCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_syozoku`
--

INSERT INTO `mt_syozoku` (`SZCD`, `SZNAME`) VALUES 
(101, '本社営業部営業一課'),
(102, '本社営業部営業二課'),
(103, '本社営業部営業三課'),
(201, '関西営業部営業一課'),
(202, '関西営業部営業二課'),
(203, '関西営業部営業三課'),
(801, '市販ソフトウェア営業チーム');

--
-- Definition of table `mt_tantou`
--
CREATE TABLE `mt_tantou` (
  `TNCD` int(10) unsigned NOT NULL,
  `TNNAME` varchar(20) NOT NULL,
  `FURI` varchar(30) default NULL,
  `TEL` char(13) default NULL,
  `KTEL` char(13) default NULL,
  `EMAIL` varchar(100) default NULL,
  `SZCD` smallint(5) unsigned NOT NULL,
  `NDATE` date default NULL,
  `BDATE` date default NULL,
  `SDIST` tinyint(3) unsigned default 0,
  PRIMARY KEY  (`TNCD`),
  KEY `FK_mt_tantou_1` (`SZCD`),
  CONSTRAINT `FK_mt_tantou_1` FOREIGN KEY (`SZCD`) REFERENCES `mt_syozoku` (`SZCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_tantou`
--
INSERT INTO `mt_tantou` (`TNCD`,`TNNAME`,`FURI`,`TEL`,`KTEL`,`EMAIL`,`SZCD`,`NDATE`,`BDATE`,`SDIST`) VALUES 
(10021, '阿倍礼一','アベ　レイイチ','','','abe@hoge.hoge',101,'1995-04-01','1973-10-10',0),
(10022, '倉橋　恵','クラハシ　メグミ','','','kurahashi@hoge.hoge',101,'1998-04-01','1975-06-16',1),
(10101, '狩場　勇','カリバ　イサム','','','kariba@hoge.hoge',201,'2002-10-01','1973-05-12',0);

--
-- Definition of table `mt_rank`
--

CREATE TABLE `mt_rank` (
  `RANK` char(1) NOT NULL,
  `HRITU` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`RANK`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_rank`
--

INSERT INTO `mt_rank` (`RANK`, `HRITU`) VALUES 
('A', 60),
('B', 65),
('C', 70),
('D', 75),
('E', 80);

--
-- Definition of table `mt_tokui`
--
CREATE TABLE `mt_tokui` (
  `TKCD` int(10) unsigned NOT NULL,
  `TKNAME` varchar(30) NOT NULL,
  `ZIP` char(8) default NULL,
  `ADDR1` varchar(50) NOT NULL,
  `ADDR2` varchar(50) default NULL,
  `TEL` char(13) default NULL,
  `FAX` char(13) default NULL,
  `TNCD` int(10) unsigned NOT NULL,
  `RANK` char(1) default 'D',
  `YLIMT` decimal(10) default 1000000,
  PRIMARY KEY  (`TKCD`),
  KEY `FK_mt_tokui_1` (`TNCD`),
  CONSTRAINT `FK_mt_tokui_1` FOREIGN KEY (`TNCD`) REFERENCES `mt_tantou` (`TNCD`),
  KEY `FK_mt_tokui_2` (`RANK`),
  CONSTRAINT `FK_mt_tokui_2` FOREIGN KEY (`RANK`) REFERENCES `mt_rank` (`RANK`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_tokui`
--
INSERT INTO `mt_tokui` (`TKCD`,`TKNAME`,`ZIP`,`ADDR1`,`ADDR2`,`TEL`,`FAX`,`TNCD`,`RANK`,`YLIMT`) VALUES 
(111111, '新東京事務機','160-0006','東京都　新宿区','舟町','','',10021,'A','10000000'),
(111112, 'オオイ事務機','160-0006','東京都 品川区 ','西大井','','',10021,'C','60000000'),
(111110, '天王寺文具','543-0023','大阪府大阪市天王寺区','味原町','','',10101,'B','3000000'),
(301310, 'キュービック','157-0074','東京都世田谷区','大蔵','','',10022,'D','1000000'),
(301320, 'ルービック','157-0022','東京都世田谷区','梅丘','','',10022,'E','1000000');

--
-- Definition of table `mt_sbunrui`
--

CREATE TABLE `mt_sbunrui` (
  `BNCD` smallint(5) unsigned NOT NULL,
  `BNNAME` varchar(30) NOT NULL,
   PRIMARY KEY  (`BNCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_sbunrui`
--

INSERT INTO `mt_sbunrui` (`BNCD`, `BNNAME`) VALUES 
(10, '文房具'),
(20, '用紙類'),
(30, '学用品'),
(40, '光学機器'),
(50, '複写機'),
(60, 'ラベル印刷機'),
(70, '印刷機'),
(100, '複合機'),
(110, 'パソコン'),
(910, 'メモリ'),
(920, 'インクカートリッジ');

--
-- Definition of table `mt_maker`
--

CREATE TABLE `mt_maker` (
  `MKCD` char(2) NOT NULL,
  `MKNAME` varchar(30) NOT NULL,
   PRIMARY KEY  (`MKCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_maker`
--

INSERT INTO `mt_maker` (`MKCD`, `MKNAME`) VALUES 
('21', 'オクヨ'),
('22', 'マイナス'),
('30', 'ウチダトーコー'),
('31', 'キアノン'),
('32', 'Ｄプロ'),
('FJ', '富士道'),
('IB', 'ビックブルー'),
('ID', 'I-Oデタナ');

--
-- Definition of table `mt_syohin`
--
CREATE TABLE `mt_syohin` (
  `SHCD` char(6) NOT NULL,
  `SHNAME` varchar(50) NOT NULL,
  `UNAME` char(4) default NULL,
  `UPRICE` decimal(9,2) default 0,
  `BNCD` smallint(5) unsigned NOT NULL,
  `MKCD` char(2) NOT NULL,
  `PDNO` varchar(12) NOT NULL,
  PRIMARY KEY  (`SHCD`),
  KEY `FK_mt_syohin_1` (`BNCD`),
  CONSTRAINT `FK_mt_syohin_1` FOREIGN KEY (`BNCD`) REFERENCES `mt_sbunrui` (`BNCD`),
  KEY `FK_mt_syohin_2` (`MKCD`),
  CONSTRAINT `FK_mt_syohin_2` FOREIGN KEY (`MKCD`) REFERENCES `mt_maker` (`MKCD`),
  KEY `Index_4` (`PDNO`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `mt_syohin`
--

INSERT INTO `mt_syohin` (`SHCD`,`SHNAME`,`UNAME`,`UPRICE`,`BNCD`,`MKCD`,`PDNO`) VALUES 
('210012', 'タイトルブレナイ','台',20000,60,'21','ABDCEFG12345'),
('210016', 'タイトルブレナ２','台',22000,60,'21','ABDCEFG45678'),
('220010', 'きれいな光沢紙100枚','袋',800,20,'22','IT-123AGC'),
('220011', 'あざやか光沢紙100枚','袋',900,20,'22','IT-224AGC'),
('220012', 'しっとり光沢紙100枚','袋',1000,20,'22','IT-345AGF'),
('220015', '写真用印画紙','袋',1500,20,'22','IT-897AGX'),
('320620', 'デプリンタＤＰ‐６２０','台',380000,70,'32','DP-620SKFD'),
('320650', 'デプリンタＤＰ‐６５０','台',430000,70,'32','DP-650SKFG'),
('320750', 'デプリンタＤＰ‐７５０','台',460000,70,'32','DP-750SGGY'),
('320850', 'デプリンタＤＰ‐８５０','台',670000,70,'32','DP-850SGFX'),
('ID0001', 'トートバッグ512MB','個',2000,910,'ID','TB-BH512BB'),
('ID0002', 'トートバッグ1GB','個',3000,910,'ID','TB-BH1GB'),
('ID0011', 'イージーディスク512MB','個',2500,910,'ID','EZ-BH512BB'),
('ID0012', 'イージーディスク1GB','個',3500,910,'ID','EZ-BH1GBB');

--
-- Definition of table `dt_nouhin`
--

CREATE TABLE `dt_nouhin` (
  `NHNO` int(10) unsigned NOT NULL auto_increment,
  `NHDATE` date NOT NULL,
  `TKCD` int(10) unsigned NOT NULL,
  `REMARK` varchar(100) default NULL,
   PRIMARY KEY  (`NHNO`),
   KEY `FK_dt_nouhin_1` (`TKCD`),
  CONSTRAINT `FK_dt_nouhin_1` FOREIGN KEY (`TKCD`) REFERENCES `mt_tokui` (`TKCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `dt_nouhin`
--

INSERT INTO `dt_nouhin` (`NHNO`,`NHDATE`,`TKCD`,`REMARK`) VALUES 
(1,'2007-07-25',111111, ''),
(2,'2007-07-25',111110, ''),
(3,'2007-07-26',301310, '');

--
-- Definition of table `dt_nhmeisai`
--
CREATE TABLE `dt_nhmeisai` (
  `NHNO` int(10) unsigned NOT NULL,
  `NHMENO` smallint(5) unsigned NOT NULL,
  `SHCD` char(6) NOT NULL,
  `SURYO` smallint(5) NOT NULL,
  `UPRICE` decimal(9,2) default 0,
  PRIMARY KEY  (`NHNO`,`NHMENO`),
  KEY `FK_dt_nhmeisai_1` (`NHNO`),
  CONSTRAINT `FK_dt_nhmeisai_1` FOREIGN KEY (`NHNO`) REFERENCES `dt_nouhin` (`NHNO`), 
  KEY `FK_dt_nhmeisai_2` (`SHCD`),
  CONSTRAINT `FK_dt_nhmeisai_2` FOREIGN KEY (`SHCD`) REFERENCES `mt_syohin` (`SHCD`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

--
-- Sample Data `dt_nhmeisai`
--

INSERT INTO `dt_nhmeisai` (`NHNO`,`NHMENO`,`SHCD`,`SURYO`,`UPRICE`) VALUES 
(1,1,'210012',1,1200),
(1,2,'220011',10,540),
(2,1,'320620',1,247000),
(3,1,'ID0001',6,1500),
(3,2,'ID0011',6,1875),
(3,3,'220010',5,600);