NCuLvFTB

1-- MySQL Workbench Synchronization
2-- Generated: 2019-10-02 13:45
3-- Model: New Model
4-- Version: 1.0
5-- Project: Name of the project
6-- Author: hasho
7
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @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';
11
12CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
13
14CREATE TABLE IF NOT EXISTS `mydb`.`Client` (
15  `Email` VARCHAR(320) NOT NULL,
16  `FirstName` VARCHAR(20) NOT NULL,
17  `LastName` VARCHAR(20) NOT NULL,
18  `Phone` INT(10) UNSIGNED NULL DEFAULT NULL,
19  `Address` VARCHAR(45) NOT NULL,
20  `Sex` VARCHAR(1) NULL DEFAULT 'N',
21  `Birthdate` DATE NULL DEFAULT NULL,
22  PRIMARY KEY (`Email`),
23  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC) VISIBLE)
24ENGINE = InnoDB
25DEFAULT CHARACTER SET = utf8;
26
27CREATE TABLE IF NOT EXISTS `mydb`.`PalsPay Account` (
28  `username` VARCHAR(32) NOT NULL,
29  `hashedPassword` VARCHAR(16) NOT NULL DEFAULT 'ENCRYPTED',
30  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
31  `clientEmail` VARCHAR(320) NOT NULL,
32  `Balance` DOUBLE NULL DEFAULT NULL,
33  PRIMARY KEY (`username`),
34  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
35  UNIQUE INDEX `clientEmail_UNIQUE` (`clientEmail` ASC) VISIBLE,
36  CONSTRAINT `fk_clientEmail`
37    FOREIGN KEY (`clientEmail`)
38    REFERENCES `mydb`.`Client` (`Email`)
39    ON DELETE NO ACTION
40    ON UPDATE NO ACTION)
41ENGINE = InnoDB
42DEFAULT CHARACTER SET = utf8;
43
44CREATE TABLE IF NOT EXISTS `mydb`.`Transaction` (
45  `TransactionNumber` INT(11) NOT NULL,
46  `buyHistory` TIMESTAMP NOT NULL,
47  `buyAmount` DOUBLE NOT NULL,
48  `seller` VARCHAR(30) NOT NULL,
49  `buyer` VARCHAR(16) NOT NULL,
50  PRIMARY KEY (`TransactionNumber`, `seller`, `buyer`),
51  UNIQUE INDEX `seller_UNIQUE` (`seller` ASC) VISIBLE,
52  INDEX `fk_Transaction_PalsPay Account1_idx` (`buyer` ASC) VISIBLE,
53  UNIQUE INDEX `buyer_UNIQUE` (`buyer` ASC) VISIBLE,
54  UNIQUE INDEX `TransactionNumber_UNIQUE` (`TransactionNumber` ASC) VISIBLE,
55  CONSTRAINT `fk_BuyerUsername`
56    FOREIGN KEY (`buyer`)
57    REFERENCES `mydb`.`PalsPay Account` (`username`)
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION,
60  CONSTRAINT `fk_SellerBenefiaciary`
61    FOREIGN KEY (`seller`)
62    REFERENCES `mydb`.`Benefiaciary` (`websiteBenefiaciary`)
63    ON DELETE NO ACTION
64    ON UPDATE NO ACTION)
65ENGINE = InnoDB
66DEFAULT CHARACTER SET = utf8;
67
68CREATE TABLE IF NOT EXISTS `mydb`.`Benefiaciary` (
69  `websiteBenefiaciary` VARCHAR(30) NOT NULL,
70  `nameBenefiaciary` VARCHAR(20) NOT NULL,
71  `BankAccountNo` INT(10) UNSIGNED NOT NULL,
72  `BankAccountRoutingNo` INT(10) UNSIGNED NOT NULL,
73  PRIMARY KEY (`websiteBenefiaciary`),
74  UNIQUE INDEX `websiteBenefiaciary_UNIQUE` (`websiteBenefiaciary` ASC) VISIBLE,
75  UNIQUE INDEX `idBankAccount_UNIQUE` (`BankAccountNo` ASC) VISIBLE,
76  UNIQUE INDEX `BankAccountNo_UNIQUE` (`BankAccountRoutingNo` ASC) VISIBLE)
77ENGINE = InnoDB
78DEFAULT CHARACTER SET = utf8;
79
80CREATE TABLE IF NOT EXISTS `mydb`.`Credit Card` (
81  `CreditCardNumber` INT(10) UNSIGNED NOT NULL,
82  `ExpDate` DATE NOT NULL,
83  `cardHolderEmail` VARCHAR(45) NOT NULL,
84  PRIMARY KEY (`CreditCardNumber`),
85  UNIQUE INDEX `CreditCardNumber_UNIQUE` (`CreditCardNumber` ASC) VISIBLE,
86  UNIQUE INDEX `cardHolder_UNIQUE` (`cardHolderEmail` ASC) VISIBLE,
87  CONSTRAINT `fk_cardHolder`
88    FOREIGN KEY (`cardHolderEmail`)
89    REFERENCES `mydb`.`Client` (`Email`)
90    ON DELETE NO ACTION
91    ON UPDATE NO ACTION)
92ENGINE = InnoDB
93DEFAULT CHARACTER SET = utf8;
94
95
96SET SQL_MODE=@OLD_SQL_MODE;
97SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
98SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;