bVH73RjU

1DROP SCHEMA IF EXISTS FOG;
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7CREATE SCHEMA IF NOT EXISTS `FOG` DEFAULT CHARACTER SET utf8 ;
8USE `FOG` ;
9
10
11CREATE TABLE IF NOT EXISTS `FOG`.`Category` (
12    `category_name` VARCHAR(45) NOT NULL,
13    PRIMARY KEY (`category_name`)
14)  ENGINE=INNODB 
15DEFAULT CHARACTER SET=UTF8;
16
17DROP TABLE IF EXISTS `Items`;
18CREATE TABLE `Items` (
19    `id` INT(11) NOT NULL AUTO_INCREMENT,
20    `category` VARCHAR(45) NOT NULL,
21    `length` INT(11) DEFAULT NULL,
22    `unit` VARCHAR(25) NOT NULL,
23    `description` VARCHAR(100) NOT NULL,
24    `stockquantity` INT(11) NOT NULL,
25    PRIMARY KEY (`id`),
26    FOREIGN KEY (`category`)
27        REFERENCES `FOG`.`Category` (`category_name`)
28)  ENGINE=INNODB;
29  
30CREATE TABLE IF NOT EXISTS `FOG`.`Customer_Description` (
31    `id_items` INT(11) NOT NULL AUTO_INCREMENT,
32    `description` VARCHAR(45) NOT NULL,
33    PRIMARY KEY (`description`),
34    FOREIGN KEY (`id_Items`)
35        REFERENCES `FOG`.`Items` (`id`)
36)  ENGINE=INNODB
37 DEFAULT CHARACTER SET=UTF8;
38    
39  
40CREATE TABLE IF NOT EXISTS `FOG`.`User_Login` (
41    `User_Id` INT NOT NULL AUTO_INCREMENT,
42    `Email` VARCHAR(45) NOT NULL,
43    `Password` VARCHAR(45) NOT NULL,
44    `Role` VARCHAR(45) NOT NULL,
45    PRIMARY KEY (`User_Id`)
46)  ENGINE=INNODB 
47 DEFAULT CHARACTER SET=UTF8;
48    
49CREATE TABLE IF NOT EXISTS `FOG`.`Order` (
50    `Id_Order` INT NOT NULL AUTO_INCREMENT,
51    `fk_User_Id` INT(11) NULL,
52    `Length` INT(11) NOT NULL,
53    `Width` INT(11) NOT NULL,
54    `Flat_Roof` TINYINT(1) NULL DEFAULT NULL,
55    `Shed` TINYINT(1) NULL DEFAULT NULL,
56    `ShedLength` INT NULL DEFAULT NULL,
57    `Evt` VARCHAR(500) NULL DEFAULT NULL,
58    `Date` DATETIME NULL,
59    `State` VARCHAR(20) NOT NULL DEFAULT 'Forespørgsel',
60    PRIMARY KEY (`Id_Order`)
61)  ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARACTER SET=UTF8;
62
63CREATE TABLE IF NOT EXISTS `FOG`.`User_Info` (
64    `fk_Order_Id` INT(11) NOT NULL,
65    `Name` VARCHAR(45) NOT NULL,
66    `Email` VARCHAR(45) NOT NULL,
67    `Phone` INT(9) NOT NULL,
68    `Zip` INT(4) NOT NULL,
69    PRIMARY KEY (`fk_Order_Id`),
70    CONSTRAINT `fk_Order_Id` FOREIGN KEY (`fk_Order_Id`)
71        REFERENCES `FOG`.`Order` (`Id_Order`)
72)  ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARACTER SET=UTF8;
73
74
75INSERT INTO `FOG`.`Category` VALUES('Skruer');
76INSERT INTO `FOG`.`Category` VALUES('Brædder');
77INSERT INTO `FOG`.`Category` VALUES('Beslag');
78INSERT INTO `FOG`.`Category` VALUES('Hængsler');
79INSERT INTO `FOG`.`Category` VALUES('Tagbeklædning');
80
81
82INSERT INTO `Items` (`category`,`description`,`unit`,`stockquantity`) VALUES('Skruer','plastmo bundskruer 200 stk.', 'Pakke',1000);
83
84INSERT INTO `Customer_Description` VALUES (1,'Skruer til tagplader');
85
86SET SQL_MODE=@OLD_SQL_MODE;
87SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
88SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;