fQycKY51

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `mydb` ;
7
8-- -----------------------------------------------------
9-- Table `mydb`.`User`
10-- -----------------------------------------------------
11CREATE  TABLE IF NOT EXISTS `mydb`.`User` (
12  `idUser` INT NOT NULL AUTO_INCREMENT ,
13  `username` VARCHAR(15) NOT NULL ,
14  `password` VARCHAR(20) NOT NULL ,
15  `salt` VARCHAR(20) NOT NULL ,
16  PRIMARY KEY (`idUser`) ,
17  UNIQUE INDEX `username_idx` (`username` ASC) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `mydb`.`Periodical`
23-- -----------------------------------------------------
24CREATE  TABLE IF NOT EXISTS `mydb`.`Periodical` (
25  `idPeriodical` INT NOT NULL ,
26  `name` VARCHAR(45) NOT NULL ,
27  `publisher` VARCHAR(45) NOT NULL ,
28  `frequency` TINYINT NULL ,
29  `unitprice` INT NULL ,
30  PRIMARY KEY (`idPeriodical`) )
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`Subscription`
36-- -----------------------------------------------------
37CREATE  TABLE IF NOT EXISTS `mydb`.`Subscription` (
38  `idSubscription` INT NOT NULL ,
39  `User_idUser` INT NOT NULL ,
40  `Periodical_idPeriodical` INT NOT NULL ,
41  `begin` DATE NOT NULL ,
42  `expires` DATE NOT NULL ,
43  `status` TINYINT NOT NULL ,
44  PRIMARY KEY (`idSubscription`) ,
45  INDEX `fk_Subscription_User` (`User_idUser` ASC) ,
46  INDEX `fk_Subscription_Periodical1` (`Periodical_idPeriodical` ASC) ,
47  CONSTRAINT `fk_Subscription_User`
48    FOREIGN KEY (`User_idUser` )
49    REFERENCES `mydb`.`User` (`idUser` )
50    ON DELETE NO ACTION
51    ON UPDATE NO ACTION,
52  CONSTRAINT `fk_Subscription_Periodical1`
53    FOREIGN KEY (`Periodical_idPeriodical` )
54    REFERENCES `mydb`.`Periodical` (`idPeriodical` )
55    ON DELETE NO ACTION
56    ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58
59
60
61SET SQL_MODE=@OLD_SQL_MODE;
62SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
63SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;