Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE IF NOT EXISTS `cvs` /*!40100 DEFAULT CHARACTER SET utf8 */;
- USE `cvs`;
- -- MySQL dump 10.13 Distrib 5.6.13, for Win32 (x86)
- --
- -- Host: localhost Database: cvs
- -- ------------------------------------------------------
- -- Server version 5.6.17
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
- /*!40101 SET NAMES utf8 */;
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
- /*!40103 SET TIME_ZONE='+00:00' */;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
- --
- -- Table structure for table `account`
- --
- DROP TABLE IF EXISTS `account`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `account` (
- `AccountID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Email` VARCHAR(128) NOT NULL,
- `FirstName` VARCHAR(50) NOT NULL,
- `LastName` VARCHAR(50) NOT NULL,
- `PhotoPath` VARCHAR(256) DEFAULT NULL,
- `isActive` tinyint(4) NOT NULL DEFAULT '0',
- `Password` VARCHAR(64) NOT NULL,
- `Employer` INT(10) UNSIGNED DEFAULT NULL,
- PRIMARY KEY (`AccountID`),
- UNIQUE KEY `UN_Email` (`Email`),
- KEY `FK_Account_Company` (`Employer`),
- CONSTRAINT `FK_Account_Company` FOREIGN KEY (`Employer`) REFERENCES `company` (`CompanyID`) ON DELETE SET NULL ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `accountrole`
- --
- DROP TABLE IF EXISTS `accountrole`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `accountrole` (
- `AccountRoleID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `RoleID` INT(10) UNSIGNED NOT NULL,
- `AccountID` INT(10) UNSIGNED NOT NULL,
- PRIMARY KEY (`AccountRoleID`),
- KEY `FK_AccountRole_Account` (`AccountID`),
- KEY `FK_AccountRole_Role` (`RoleID`),
- CONSTRAINT `FK_AccountRole_Account` FOREIGN KEY (`AccountID`) REFERENCES `account` (`AccountID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_AccountRole_Role` FOREIGN KEY (`RoleID`) REFERENCES `role` (`RoleID`) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `company`
- --
- DROP TABLE IF EXISTS `company`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `company` (
- `CompanyID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `City` VARCHAR(64) NOT NULL,
- `Name` VARCHAR(128) NOT NULL,
- `PhoneNumber` VARCHAR(32) NOT NULL,
- `Postcode` VARCHAR(16) NOT NULL,
- `PrimaryAddress` VARCHAR(128) NOT NULL,
- `SecondaryAddress` VARCHAR(128) DEFAULT NULL,
- `RegistrationNumber` VARCHAR(64) NOT NULL,
- `ManagingAccount` INT(10) UNSIGNED DEFAULT NULL,
- `InvoiceLimit` FLOAT(15,3) DEFAULT NULL,
- `ParentCompany` INT(10) UNSIGNED DEFAULT NULL,
- `Country` INT(10) UNSIGNED DEFAULT NULL,
- PRIMARY KEY (`CompanyID`),
- KEY `FK_Company_Account` (`ManagingAccount`),
- KEY `FK_Company_Company` (`ParentCompany`),
- KEY `FK_Company_Country` (`Country`),
- CONSTRAINT `FK_Company_Account` FOREIGN KEY (`ManagingAccount`) REFERENCES `account` (`AccountID`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `FK_Company_Company` FOREIGN KEY (`ParentCompany`) REFERENCES `company` (`CompanyID`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `FK_Company_Country` FOREIGN KEY (`Country`) REFERENCES `country` (`CountryID`) ON DELETE SET NULL ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `companyproduct`
- --
- DROP TABLE IF EXISTS `companyproduct`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `companyproduct` (
- `CompanyProductID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Quantity` INT(10) UNSIGNED NOT NULL,
- `OwnedBy` INT(10) UNSIGNED DEFAULT NULL,
- `ProductID` INT(10) UNSIGNED DEFAULT NULL,
- PRIMARY KEY (`CompanyProductID`),
- KEY `FK_CompanyProduct_Company` (`OwnedBy`),
- KEY `FK_CompanyProduct_Product` (`ProductID`),
- CONSTRAINT `FK_CompanyProduct_Company` FOREIGN KEY (`OwnedBy`) REFERENCES `company` (`CompanyID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_CompanyProduct_Product` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE NO ACTION ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `country`
- --
- DROP TABLE IF EXISTS `country`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `country` (
- `CountryID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `ISO3166-1a2` VARCHAR(2) NOT NULL,
- `ISO3166-1a3` VARCHAR(3) NOT NULL,
- `Name` VARCHAR(64) NOT NULL,
- `NumericCode` VARCHAR(3) NOT NULL,
- PRIMARY KEY (`CountryID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `currency`
- --
- DROP TABLE IF EXISTS `currency`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `currency` (
- `CurrencyID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `ISO4217` VARCHAR(3) NOT NULL,
- `NumericCode` VARCHAR(3) NOT NULL,
- `Name` VARCHAR(64) DEFAULT NULL,
- PRIMARY KEY (`CurrencyID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=172 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `invoice`
- --
- DROP TABLE IF EXISTS `invoice`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `invoice` (
- `InvoiceID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Status` VARCHAR(32) DEFAULT NULL,
- `InvoiceNumber` VARCHAR(64) NOT NULL,
- PRIMARY KEY (`InvoiceID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `order`
- --
- DROP TABLE IF EXISTS `order`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `order` (
- `OrderID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `PurchasedOn` datetime NOT NULL,
- `PurchasedBy` INT(10) UNSIGNED NOT NULL,
- `PurchasedFor` INT(10) UNSIGNED NOT NULL,
- `InvoiceID` INT(10) UNSIGNED NOT NULL,
- PRIMARY KEY (`OrderID`),
- KEY `FK_Order_Account` (`PurchasedBy`),
- KEY `FK_Order_Company` (`PurchasedFor`),
- KEY `FK_Order_Invoice` (`InvoiceID`),
- CONSTRAINT `FK_Order_Account` FOREIGN KEY (`PurchasedBy`) REFERENCES `account` (`AccountID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_Order_Company` FOREIGN KEY (`PurchasedFor`) REFERENCES `company` (`CompanyID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_Order_Invoice` FOREIGN KEY (`InvoiceID`) REFERENCES `invoice` (`InvoiceID`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `orderitem`
- --
- DROP TABLE IF EXISTS `orderitem`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `orderitem` (
- `OrderItemID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Name` VARCHAR(64) NOT NULL,
- `Quantity` INT(11) NOT NULL,
- `UnitPrice` FLOAT(15,0) NOT NULL,
- `Decription` VARCHAR(64) DEFAULT NULL,
- `OrderID` INT(10) UNSIGNED NOT NULL,
- PRIMARY KEY (`OrderItemID`),
- KEY `FK_OrderItem_Order` (`OrderID`),
- CONSTRAINT `FK_OrderItem_Order` FOREIGN KEY (`OrderID`) REFERENCES `order` (`OrderID`) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `product`
- --
- DROP TABLE IF EXISTS `product`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `product` (
- `ProductID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Info` VARCHAR(256) NOT NULL,
- `isPopular` tinyint(4) NOT NULL DEFAULT '0',
- `Name` VARCHAR(64) NOT NULL,
- `UnitPrice` FLOAT(15,3) NOT NULL,
- PRIMARY KEY (`ProductID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `report`
- --
- DROP TABLE IF EXISTS `report`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `report` (
- `ReportID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `FilePath` VARCHAR(256) NOT NULL,
- `GeneratedOn` datetime DEFAULT NULL,
- `Status` VARCHAR(32) DEFAULT NULL,
- `GeneratedBy` INT(10) UNSIGNED DEFAULT NULL,
- `GeneratedFor` INT(10) UNSIGNED DEFAULT NULL,
- `ProductID` INT(10) UNSIGNED NOT NULL,
- `SalesPeriod` INT(10) UNSIGNED NOT NULL,
- PRIMARY KEY (`ReportID`),
- KEY `FK_Report_Account` (`GeneratedBy`),
- KEY `FK_Report_Company` (`GeneratedFor`),
- KEY `FK_Report_Product` (`ProductID`),
- KEY `FK_Report_SalesData` (`SalesPeriod`),
- CONSTRAINT `FK_Report_Account` FOREIGN KEY (`GeneratedBy`) REFERENCES `account` (`AccountID`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `FK_Report_Company` FOREIGN KEY (`GeneratedFor`) REFERENCES `company` (`CompanyID`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `FK_Report_Product` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
- CONSTRAINT `FK_Report_SalesData` FOREIGN KEY (`SalesPeriod`) REFERENCES `salesdata` (`SalesDataID`) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `role`
- --
- DROP TABLE IF EXISTS `role`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `role` (
- `RoleID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Label` VARCHAR(128) NOT NULL,
- PRIMARY KEY (`RoleID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `salesdata`
- --
- DROP TABLE IF EXISTS `salesdata`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `salesdata` (
- `SalesDataID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `FilePath` VARCHAR(256) NOT NULL,
- `SalesPeriod` VARCHAR(64) NOT NULL,
- `Status` VARCHAR(32) NOT NULL,
- `UploadedOn` datetime NOT NULL,
- `UploadedBy` INT(10) UNSIGNED DEFAULT NULL,
- `UploadedFor` INT(10) UNSIGNED DEFAULT NULL,
- PRIMARY KEY (`SalesDataID`),
- KEY `FK_SalesData_Account` (`UploadedBy`),
- KEY `FK_SalesData_Company` (`UploadedFor`),
- CONSTRAINT `FK_SalesData_Account` FOREIGN KEY (`UploadedBy`) REFERENCES `account` (`AccountID`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `FK_SalesData_Company` FOREIGN KEY (`UploadedFor`) REFERENCES `company` (`CompanyID`) ON DELETE SET NULL ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `systemlogentry`
- --
- DROP TABLE IF EXISTS `systemlogentry`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `systemlogentry` (
- `LogEntryID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Text` VARCHAR(256) DEFAULT NULL,
- `Timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `Title` VARCHAR(128) NOT NULL,
- `Type` VARCHAR(32) NOT NULL,
- PRIMARY KEY (`LogEntryID`)
- ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Table structure for table `transaction`
- --
- DROP TABLE IF EXISTS `transaction`;
- /*!40101 SET @saved_cs_client = @@character_set_client */;
- /*!40101 SET character_set_client = utf8 */;
- CREATE TABLE `transaction` (
- `TransactionID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `Amount` FLOAT(15,3) NOT NULL,
- `ConsumerID` VARCHAR(128) NOT NULL,
- `Date` datetime NOT NULL,
- `SalesDataID` INT(10) UNSIGNED DEFAULT NULL,
- `Country` INT(10) UNSIGNED DEFAULT NULL,
- `Currency` INT(10) UNSIGNED DEFAULT NULL,
- PRIMARY KEY (`TransactionID`),
- KEY `FK_Transaction_Country` (`Country`),
- KEY `FK_Transaction_Currency` (`Currency`),
- KEY `FK_Transaction_SalesData` (`SalesDataID`),
- CONSTRAINT `FK_Transaction_Country` FOREIGN KEY (`Country`) REFERENCES `country` (`CountryID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_Transaction_Currency` FOREIGN KEY (`Currency`) REFERENCES `currency` (`CurrencyID`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `FK_Transaction_SalesData` FOREIGN KEY (`SalesDataID`) REFERENCES `salesdata` (`SalesDataID`) ON DELETE CASCADE ON UPDATE CASCADE
- ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
- /*!40101 SET character_set_client = @saved_cs_client */;
- --
- -- Dumping routines for database 'cvs'
- --
- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
- /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
- /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
- -- Dump completed on 2015-05-08 17:49:58
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement