nevVTGTA

1DROP DATABASE IF EXISTS `agrohut` ;
2
3CREATE SCHEMA IF NOT EXISTS `agrohut` DEFAULT CHARACTER SET utf8 ;
4USE `agrohut` ;
5
6CREATE TABLE IF NOT EXISTS `agrohut`.`Users` (
7  `UserID` VARCHAR(8) NOT NULL,
8  `UserFName` VARCHAR(45) NOT NULL,
9  `UserLName` VARCHAR(45) NOT NULL,
10  `USER_TYPE` VARCHAR(1) NOT NULL,
11  `Password` VARCHAR(45) NOT NULL,
12  `DateCreated` DATE NOT NULL,
13  PRIMARY KEY (`UserID`))
14ENGINE = InnoDB;
15
16CREATE TABLE IF NOT EXISTS `agrohut`.`Product` (
17  `ProductID` VARCHAR(8) NOT NULL,
18  `DepartmentID` VARCHAR(8) NOT NULL,
19  `Category` VARCHAR(45) NOT NULL,
20  `IDSKU` VARCHAR(8) NOT NULL,
21  `ProductName` VARCHAR(45) NOT NULL,
22  `Quantity` INT NOT NULL,
23  `UnitPrice` DECIMAL NOT NULL,
24  `Ranking` INT NULL,
25  `ProductDesc` TEXT NULL,
26  `UnitsInStock` INT NULL,
27  `UnitsInOrder` INT NULL,
28  `Picture` BLOB NULL,
29  PRIMARY KEY (`ProductID`))
30ENGINE = InnoDB;
31
32CREATE TABLE IF NOT EXISTS `agrohut`.`Shopping Cart` (
33  `ShoppingCartID` VARCHAR(8) NOT NULL,
34  `ProductID` VARCHAR(8) NOT NULL,
35  `OrderStatus` VARCHAR(45) NULL,
36  PRIMARY KEY (`ShoppingCartID`))
37ENGINE = InnoDB;
38
39CREATE INDEX `ProductID_idx` ON `agrohut`.`Shopping Cart` (`ProductID` ASC);
40
41
42
43CREATE TABLE IF NOT EXISTS `agrohut`.`Wishlist` (
44  `WishlistID` VARCHAR(8) NOT NULL,
45  `ProductID` VARCHAR(8) NOT NULL,
46  PRIMARY KEY (`WishlistID`))
47ENGINE = InnoDB;
48
49CREATE INDEX `ProductID_idx` ON `agrohut`.`Wishlist` (`ProductID` ASC);
50
51
52CREATE TABLE IF NOT EXISTS `agrohut`.`Sellers` (
53  `SellerID` VARCHAR(8) NOT NULL,
54  `UserID` VARCHAR(8) NOT NULL,
55  `USER_TYPE` VARCHAR(1) NOT NULL,
56  `CompanyName` VARCHAR(45) NOT NULL,
57  `ContactFName` VARCHAR(45) NOT NULL,
58  `ContactLName` VARCHAR(45) NOT NULL,
59  `ContactPosition` VARCHAR(45) NOT NULL,
60  `Phone` VARCHAR(15) NOT NULL DEFAULT 'xxx-xxx-xxxx',
61  `Email` VARCHAR(45) NOT NULL,
62  `Logo` BLOB NULL,
63  PRIMARY KEY (`UserID`, `USER_TYPE`),
64   CHECK (USER_TYPE = 'B'))
65   ENGINE = InnoDB;
66
67CREATE INDEX `UserID_idx` ON `agrohut`.`Sellers` (`UserID` ASC);
68
69
70
71CREATE TABLE IF NOT EXISTS `agrohut`.`Shipper` (
72  `ShipperID` VARCHAR(8) NOT NULL,
73  `ShipperName` VARCHAR(45) NULL,
74  `ContactName` VARCHAR(45) NULL,
75  `Phone` VARCHAR(15) NULL,
76  PRIMARY KEY (`ShipperID`))
77ENGINE = InnoDB;
78
79CREATE UNIQUE INDEX `ShipperID_UNIQUE` ON `agrohut`.`Shipper` (`ShipperID` ASC);
80
81
82CREATE TABLE IF NOT EXISTS `agrohut`.`Orders` (
83  `OrderID` VARCHAR(8) NOT NULL, 
84  `UserID` VARCHAR(8) NOT NULL,
85  `ShipperID` VARCHAR(8) NOT NULL,
86  `OrderDate` DATE NOT NULL,
87  `RequiredDate` DATE NOT NULL,
88  `Freight` DECIMAL NOT NULL,
89  `SalesTax` DECIMAL NOT NULL,
90  `TimeStamp` TIMESTAMP NOT NULL,
91  `TransactStatus` VARCHAR(25) NOT NULL,
92  `InvoiceAmount` INT NOT NULL,
93  `PaymentDate` DATE NOT NULL,
94  `ItemQuantity` INT NULL,
95  PRIMARY KEY (`OrderID`))
96ENGINE = InnoDB;
97
98CREATE INDEX `UserID_idx` ON `agrohut`.`Orders` (`UserID` ASC);
99
100CREATE INDEX `ShipperID_idx` ON `agrohut`.`Orders` (`ShipperID` ASC);
101
102
103
104CREATE TABLE IF NOT EXISTS `agrohut`.`Payment` (
105  `PaymentID` VARCHAR(8) NOT NULL,
106  `OrderID` VARCHAR(8) NOT NULL,
107  `Payment_Type` VARCHAR(1) NOT NULL,
108  PRIMARY KEY (`PaymentID`))
109ENGINE = InnoDB;
110
111CREATE INDEX `OrderID_idx` ON `agrohut`.`Payment` (`OrderID` ASC);
112
113
114
115
116USE `agrohut` ;
117
118
119CREATE TABLE IF NOT EXISTS `agrohut`.`view1` (`id` INT);
120
121
122DROP TABLE IF EXISTS `agrohut`.`view1`;
123USE `agrohut`;
124
125