Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP DATABASE IF EXISTS `Jeweller`;
- CREATE DATABASE `Jeweller`;
- DROP TABLE IF EXISTS `Jeweller`.`Orders`;
- CREATE TABLE `Jeweller`.`Orders` (
- `id` int(11) unsigned NOT NULL,
- `date` date DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Product_categories`;
- CREATE TABLE `Jeweller`.`Product_categories` (
- `id` int(11) unsigned NOT NULL,
- `name` varchar(100) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Product_orders`;
- CREATE TABLE `Jeweller`.`Product_orders` (
- `order_id` int(11) unsigned NOT NULL,
- `product_id` int(11) unsigned NOT NULL,
- `quantity` int(11),
- `value` float,
- FOREIGN KEY (`order_id`) REFERENCES `Jeweller`.`Orders`(`id`),
- FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
- CHECK (`quantity`>0),
- CHECK (`value`>0)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Product_returns`;
- CREATE TABLE `Jeweller`.`Product_returns` (
- `sale_id` int(11) unsigned NOT NULL,
- `product_id` int(11) NOT NULL,
- `date` date DEFAULT NULL,
- `quantity` int(11),
- `value` float,
- FOREIGN KEY (`sale_id`) REFERENCES `Jeweller`.`Sales`(`id`),
- FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
- CHECK (`quantity`>0),
- CHECK (`value`>0)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Product_sales`;
- CREATE TABLE `Jeweller`.`Product_sales` (
- `sale_id` int(11) unsigned NOT NULL,
- `product_id` int(11) NOT NULL,
- `quantity` int(11),
- `value` float,
- FOREIGN KEY (`sale_id`) REFERENCES `Jeweller`.`Sales`(`id`),
- FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`),
- CHECK (`quantity`>0),
- CHECK (`value`>0)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Products`;
- CREATE TABLE `Jeweller`.`Products` (
- `id` int(11) unsigned NOT NULL,
- `product_category_id` int(11) NOT NULL,
- `seller_id` int(11) NOT NULL,
- `name` varchar(100) NOT NULL,
- `description` text,
- PRIMARY KEY (`id`),
- FOREIGN KEY (`product_category_id`) REFERENCES `Jeweller`.`Product_categories`(`id`),
- FOREIGN KEY (`seller_id`) REFERENCES `Jeweller`.`Sellers`(`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Sales`;
- CREATE TABLE `Jeweller`.`Sales` (
- `id` int(11) unsigned NOT NULL,
- `date` date DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Sellers`;
- CREATE TABLE `Jeweller`.`Sellers` (
- `id` int(11) unsigned NOT NULL,
- `name` varchar(100) NOT NULL,
- `address` varchar(100) DEFAULT NULL,
- `phone` varchar(15),
- `email` varchar(50) DEFAULT NULL,
- PRIMARY KEY (`id`),
- CHECK (LENGTH(`phone`)=10)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- DROP TABLE IF EXISTS `Jeweller`.`Stock`;
- CREATE TABLE `Jeweller`.`Stock` (
- `product_id` int(11) unsigned NOT NULL,
- `quantity` int(11) DEFAULT NULL,
- PRIMARY KEY (`product_id`),
- FOREIGN KEY (`product_id`) REFERENCES `Jeweller`.`Products`(`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement