Question

Use MySQL Workbench to create a simple ER model and use the Forward Engineering functionality of Workbench to create a simple DB (specs follow). This focuses on how MySQL Workbench is used to create a model and then a DB from it, rather than on ER modeling.

The sample model you are to create is on books, authors, and publishers - a model that could be part of a larger ER model for a library. A book has an ISBN number and can have one or more authors, while an author can have written or co-authored more than one book. You need to make additional assumptions when creating entities and relationships - they must be reasonable and clearly stated. Entities representing books, authors, and publishers, respectively, should each have at least 4 attributes each, but no more than seven. Furthermore, assign at least 3 different names/values for each attribute. Identifiers in your model must be such that names (identifiers chosen) for primary keys must end with an underscore followed by the digits '444'.

Once your model is complete and ready, use MySQL Workbench to create a DB using Forward Engineer functionality. Once the DB is created, enter data into tables while ensuring that there are at least two books that have multiple authors; one that has just one author; and at least one author who co-authored at least two books. Furthermore, for each of the entities representing one of book, author, or publisher, the primary key value must end with an underscore followed by the digits '444'. That is, when you are inserting tuples into a table/relation representing one of book, publisher, or author tables, the primary key value must end with an underscore followed by the digits '444'.

Answer the following questions and submit the following as well:
1. Assumptions that you made when creating your entities and relationships (your assumptions must not contradict
the specs above (i.e., that a book has an ISBN number and can have one or more authors, while an author can have
written or co-authored more than one book)).
2. Screenshot of your final model that shows the whole Workbench window.
3. Screenshot of just your model diagram (as opposed to the whole Workbench Window).
4. Screen shots showing content of your tables.
5. A sequence of screenshots such that each one shows a model after you added one relation. Thus, if there are two relations, there should be two screenshots.
6. The auto-generated script from using the Forward Engineering function

Answer 1

EXPECTED ANSWER

All the given conditions have been considered while developing the entities and populating tables.

1. There are 4 entities

a) BOOK

b) AUTHOR

c) BOOK_AUTHOR (To support many-to-many relationship between Books and Authors)

d) PUBLISHER

All the details of the design are provided below as screenshots and text.

The auto generated script during Forward Engineer -ing is given below:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema library
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema library
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `library` DEFAULT CHARACTER SET utf8 ;
USE `library` ;

-- -----------------------------------------------------
-- Table `library`.`PUBLISHER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`PUBLISHER` (
`PUBLISHER_ID` VARCHAR(20) NOT NULL,
`PUBLISHER_NAME` VARCHAR(45) NULL,
`PUBLISHER_ADDRESS` VARCHAR(45) NULL,
PRIMARY KEY (`PUBLISHER_ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `library`.`BOOK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`BOOK` (
`BOOK_ID` VARCHAR(20) NOT NULL,
`ISBN` VARCHAR(45) NULL,
`BOOK_NAME` VARCHAR(45) NULL,
`PRICE` INT NULL,
`PUBLISHER_PUBLISHER_ID` VARCHAR(20) NOT NULL,
PRIMARY KEY (`BOOK_ID`, `PUBLISHER_PUBLISHER_ID`),
INDEX `fk_BOOK_PUBLISHER1_idx` (`PUBLISHER_PUBLISHER_ID` ASC),
CONSTRAINT `fk_BOOK_PUBLISHER1`
FOREIGN KEY (`PUBLISHER_PUBLISHER_ID`)
REFERENCES `library`.`PUBLISHER` (`PUBLISHER_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `library`.`AUTHOR`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`AUTHOR` (
`AUTHOR_ID` VARCHAR(20) NOT NULL,
`AUTHOR_NAME` VARCHAR(60) NULL,
PRIMARY KEY (`AUTHOR_ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `library`.`BOOK_AUTHOR`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `library`.`BOOK_AUTHOR` (
`BOOK_BOOK_ID` VARCHAR(20) NOT NULL,
`AUTHOR_AUTHOR_ID` VARCHAR(20) NOT NULL,
PRIMARY KEY (`BOOK_BOOK_ID`, `AUTHOR_AUTHOR_ID`),
INDEX `fk_BOOK_has_AUTHOR_AUTHOR1_idx` (`AUTHOR_AUTHOR_ID` ASC),
INDEX `fk_BOOK_has_AUTHOR_BOOK1_idx` (`BOOK_BOOK_ID` ASC),
CONSTRAINT `fk_BOOK_has_AUTHOR_BOOK1`
FOREIGN KEY (`BOOK_BOOK_ID`)
REFERENCES `library`.`BOOK` (`BOOK_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_BOOK_has_AUTHOR_AUTHOR1`
FOREIGN KEY (`AUTHOR_AUTHOR_ID`)
REFERENCES `library`.`AUTHOR` (`AUTHOR_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `library`;

DELIMITER $$
USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`PUBLISHER_BEFORE_INSERT` BEFORE INSERT ON `PUBLISHER` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.PUBLISHER_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because PUBLISHER_ID ',NEW.PUBLISHER_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`PUBLISHER_BEFORE_UPDATE` BEFORE UPDATE ON `PUBLISHER` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.PUBLISHER_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because PUBLISHER_ID ',NEW.PUBLISHER_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`BOOK_BEFORE_INSERT` BEFORE INSERT ON `BOOK` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.BOOK_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because BOOK_ID ',NEW.BOOK_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`BOOK_BEFORE_UPDATE` BEFORE UPDATE ON `BOOK` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.BOOK_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because BOOK_ID ',NEW.BOOK_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`AUTHOR_BEFORE_INSERT` BEFORE INSERT ON `AUTHOR` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.AUTHOR_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because AUTHOR_ID ',NEW.AUTHOR_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

USE `library`$$
CREATE DEFINER = CURRENT_USER TRIGGER `library`.`AUTHOR_BEFORE_UPDATE` BEFORE UPDATE ON `AUTHOR` FOR EACH ROW
BEGIN
DECLARE dummy,baddata INT;
SET baddata = 0;
IF NEW.AUTHOR_ID NOT LIKE '%_444' THEN
SET baddata = 1;
END IF;

IF baddata = 1 THEN
SELECT CONCAT('Cannot Insert This Because AUTHOR_ID ',NEW.AUTHOR_ID,' is Invalid')
INTO dummy FROM information_schema.tables;
END IF;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
THANK YOU.....!