Want more features on Pastebin? Sign Up, it's FREE!
Guest

DB Creation

By: a guest on Jun 11th, 2013  |  syntax: MySQL  |  size: 3.08 KB  |  views: 36  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. DROP DATABASE IF EXISTS `Jeweller`;
  2. CREATE DATABASE `Jeweller`;
  3.  
  4. DROP TABLE IF EXISTS `Jeweller`.`Orders`;
  5. CREATE TABLE `Jeweller`.`Orders` (
  6.   `id` int(11) unsigned NOT NULL,
  7.   `date` date DEFAULT NULL,
  8.   PRIMARY KEY (`id`)
  9. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  10.  
  11. DROP TABLE IF EXISTS `Jeweller`.`Product_categories`;
  12. CREATE TABLE `Jeweller`.`Product_categories` (
  13.   `id` int(11) unsigned NOT NULL,
  14.   `name` varchar(100) NOT NULL,
  15.   PRIMARY KEY (`id`)
  16. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  17.  
  18. DROP TABLE IF EXISTS `Jeweller`.`Product_orders`;
  19. CREATE TABLE `Jeweller`.`Product_orders` (
  20.   `order_id` int(11) unsigned NOT NULL,
  21.   `product_id` int(11) unsigned NOT NULL,
  22.   `quantity` int(11),
  23.   `value` float,
  24.   FOREIGN KEY (`order_id`) REFERENCES `Jeweller`.`Orders`(`id`),
  25.   FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
  26.   CHECK (`quantity`>0),
  27.   CHECK (`value`>0)
  28. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  29.  
  30. DROP TABLE IF EXISTS `Jeweller`.`Product_returns`;
  31. CREATE TABLE `Jeweller`.`Product_returns` (
  32.   `sale_id` int(11) unsigned NOT NULL,
  33.   `product_id` int(11) NOT NULL,
  34.   `date` date DEFAULT NULL,
  35.   `quantity` int(11),
  36.   `value` float,
  37.   FOREIGN KEY (`sale_id`) REFERENCES `Jeweller`.`Sales`(`id`),
  38.   FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
  39.   CHECK (`quantity`>0),
  40.   CHECK (`value`>0)
  41. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  42.  
  43. DROP TABLE IF EXISTS `Jeweller`.`Product_sales`;
  44. CREATE TABLE `Jeweller`.`Product_sales` (
  45.   `sale_id` int(11) unsigned NOT NULL,
  46.   `product_id` int(11) NOT NULL,
  47.   `quantity` int(11),
  48.   `value` float,
  49.   FOREIGN KEY (`sale_id`) REFERENCES `Jeweller`.`Sales`(`id`),
  50.   FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
  51.   CHECK (`quantity`>0),
  52.   CHECK (`value`>0)
  53. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  54.  
  55. DROP TABLE IF EXISTS `Jeweller`.`Products`;
  56. CREATE TABLE `Jeweller`.`Products` (
  57.   `id` int(11) unsigned NOT NULL,
  58.   `product_category_id` int(11) NOT NULL,
  59.   `seller_id` int(11) NOT NULL,
  60.   `name` varchar(100) NOT NULL,
  61.   `description` text,
  62.   PRIMARY KEY (`id`),
  63.   FOREIGN KEY (`product_category_id`) REFERENCES `Jeweller`.`Product_categories`(`id`),
  64.   FOREIGN KEY (`seller_id`) REFERENCES `Jeweller`.`Sellers`(`id`)
  65. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  66.  
  67. DROP TABLE IF EXISTS `Jeweller`.`Sales`;
  68. CREATE TABLE `Jeweller`.`Sales` (
  69.   `id` int(11) unsigned NOT NULL,
  70.   `date` date DEFAULT NULL,
  71.   PRIMARY KEY (`id`)
  72. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  73.  
  74. DROP TABLE IF EXISTS `Jeweller`.`Sellers`;
  75. CREATE TABLE `Jeweller`.`Sellers` (
  76.   `id` int(11) unsigned NOT NULL,
  77.   `name` varchar(100) NOT NULL,
  78.   `address` varchar(100) DEFAULT NULL,
  79.   `phone` varchar(15),
  80.   `email` varchar(50) DEFAULT NULL,
  81.   PRIMARY KEY (`id`),
  82.   CHECK (LENGTH(`phone`)=10)
  83. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  84.  
  85. DROP TABLE IF EXISTS `Jeweller`.`Stock`;
  86. CREATE TABLE `Jeweller`.`Stock` (
  87.   `product_id` int(11) unsigned NOT NULL,
  88.   `quantity` int(11) DEFAULT NULL,
  89.   PRIMARY KEY (`product_id`),
  90.   FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`)
  91. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
clone this paste RAW Paste Data