7cSXWPGx

1INSERT INTO Band VALUES(NULL,"Blank Banshee");
2SELECT * FROM Band;
3
4INSERT INTO Album VALUES(NULL,"Mezzanine",1998,2);
5SELECT * FROM Album;
6INSERT INTO Song VALUES(NULL, "Angel");
7SELECT * FROM Song;
8INSERT INTO Songlist VALUES(1,6);
9SELECT * FROM Songlist;
10
11#Person
12#Members
13
14-- MySQL Script generated by MySQL Workbench
15-- Mon Oct  8 16:56:19 2018
16-- Model: New Model    Version: 1.0
17-- MySQL Workbench Forward Engineering
18
19
20#SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=1;
21#SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
22SET @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';
23
24-- -----------------------------------------------------
25-- Schema mydb
26-- -----------------------------------------------------
27
28-- -----------------------------------------------------
29-- Schema mydb
30-- -----------------------------------------------------
31CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
32USE `mydb` ;
33
34-- -----------------------------------------------------
35-- Table `mydb`.`Band`
36-- -----------------------------------------------------
37DROP TABLE Band;
38CREATE TABLE IF NOT EXISTS `mydb`.`Band` (
39  `id` INT NOT NULL AUTO_INCREMENT,
40  `name` VARCHAR(45) NOT NULL UNIQUE,
41  PRIMARY KEY (`id`))
42ENGINE = InnoDB;
43
44-- -----------------------------------------------------
45-- Table `mydb`.`Album`
46-- -----------------------------------------------------
47DROP TABLE Album;
48CREATE TABLE IF NOT EXISTS `mydb`.`Album` (
49  `id` INT NOT NULL AUTO_INCREMENT,
50  `name` VARCHAR(45) NOT NULL,
51  `date` INT NOT NULL,
52  `Band_idBand` INT NOT NULL,
53  PRIMARY KEY (`id`),
54  INDEX `fk_Album_Band1_idx` (`Band_idBand` ASC) ,
55  CONSTRAINT `fk_Album_Band1`
56    FOREIGN KEY (`Band_idBand`)
57    REFERENCES `mydb`.`Band` (`id`)
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION)
60ENGINE = InnoDB;
61
62-- -----------------------------------------------------
63-- Table `mydb`.`Song`
64-- -----------------------------------------------------
65DROP TABLE Song;
66CREATE TABLE IF NOT EXISTS `mydb`.`Song` (
67  `id` INT NOT NULL AUTO_INCREMENT,
68  `name` VARCHAR(45) NULL,
69  PRIMARY KEY (`id`))
70ENGINE = InnoDB;
71
72-- -----------------------------------------------------
73-- Table `mydb`.`Songlist`
74-- -----------------------------------------------------
75DROP TABLE Songlist;
76CREATE TABLE IF NOT EXISTS `mydb`.`Songlist` (
77  `Song_idSong` INT NOT NULL,
78  `Album_idAlbum` INT NOT NULL,
79  INDEX `fk_Song_has_Album_Album1_idx` (`Album_idAlbum` ASC) ,
80  INDEX `fk_Song_has_Album_Song1_idx` (`Song_idSong` ASC) ,
81  PRIMARY KEY (`Song_idSong`, `Album_idAlbum`),
82  CONSTRAINT `fk_Song_has_Album_Song1`
83    FOREIGN KEY (`Song_idSong`)
84    REFERENCES `mydb`.`Song` (`id`)
85    ON DELETE NO ACTION
86    ON UPDATE NO ACTION,
87  CONSTRAINT `fk_Song_has_Album_Album1`
88    FOREIGN KEY (`Album_idAlbum`)
89    REFERENCES `mydb`.`Album` (`id`)
90    ON DELETE NO ACTION
91    ON UPDATE NO ACTION)
92ENGINE = InnoDB;
93
94
95-- -----------------------------------------------------
96-- Table `mydb`.`Person`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `mydb`.`Person` (
99  `id` INT NOT NULL AUTO_INCREMENT,
100  `name` VARCHAR(45) NULL,
101  `surname` VARCHAR(45) NULL,
102  PRIMARY KEY (`id`))
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`Members`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `mydb`.`Members` (
110  `id` INT NOT NULL,
111  `Members_idMembers` INT NOT NULL,
112  `Band_idBand` INT NOT NULL,
113  `joined` INT NOT NULL,
114  `retired` INT NULL,
115  INDEX `fk_Members_has_Band_Band1_idx` (`Band_idBand` ASC) ,
116  INDEX `fk_Members_has_Band_Members1_idx` (`Members_idMembers` ASC) ,
117  PRIMARY KEY (`id`),
118  CONSTRAINT `fk_Members_has_Band_Members1`
119    FOREIGN KEY (`Members_idMembers`)
120    REFERENCES `mydb`.`Person` (`id`)
121    ON DELETE NO ACTION
122    ON UPDATE NO ACTION,
123  CONSTRAINT `fk_Members_has_Band_Band1`
124    FOREIGN KEY (`Band_idBand`)
125    REFERENCES `mydb`.`Band` (`id`)
126    ON DELETE NO ACTION
127    ON UPDATE NO ACTION)
128ENGINE = InnoDB;
129
130
131SET SQL_MODE=@OLD_SQL_MODE;
132SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
133SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;