a. Make the description of the problem b. Perform the ERD (Visual Paradigm type notation) c. Generate the DB d. Create an index for the journal entity that allows for better searches, give at least one example of Querys e. Create a user to access this DB and only perform selection operations

Note: Please check if my answers are correct of part b,c d and e. I am using mysql workbech but I want to make sure it works for vi in operative system linux

If you thing it will be better to change the code of the index (part d) for a better output view please do.

Note:I am missing part a I want to make sure got a good description clear please help.

b) ERD

c)

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';

CREATE SCHEMA IF NOT EXISTS `ex2` DEFAULT CHARACTER SET utf8 ; USE `ex2` ;

CREATE TABLE IF NOT EXISTS `ex2`.`magazine` ( `issn` INT(8) NOT NULL, `number` INT(4) NOT NULL, `title` VARCHAR(20) NOT NULL, `years` INT(10) NOT NULL, PRIMARY KEY (`issn`, `number`), UNIQUE INDEX `issn_UNIQUE` (`issn` ASC), UNIQUE INDEX `number_UNIQUE` (`number` ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ex2`.`article` ( `title` VARCHAR(20) NOT NULL, `start` VARCHAR(7) NOT NULL, `end` VARCHAR(7) NOT NULL, `issn` INT(8) NOT NULL, `number` INT(4) NOT NULL, PRIMARY KEY (`title`), UNIQUE INDEX `issn_UNIQUE` (`issn` ASC), UNIQUE INDEX `number_UNIQUE` (`number` ASC), UNIQUE INDEX `title_UNIQUE` (`title` ASC), INDEX `article_ibfk_1_idx` (`issn` ASC, `number` ASC), CONSTRAINT `article_ibfk_1` FOREIGN KEY (`issn` , `number`) REFERENCES `ex2`.`revista` (`issn` , `number`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ex2`.`author` ( `name` VARCHAR(20) NOT NULL, `email` VARCHAR(30) NOT NULL, `adscrip` INT(4) NOT NULL, PRIMARY KEY (`name`), UNIQUE INDEX `email_UNIQUE` (`email` ASC), UNIQUE INDEX `adscrip_UNIQUE` (`adscrip` ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `ex2`.`writing` ( `pos` INT(4) NOT NULL, `title` VARCHAR(20) NOT NULL, `name` VARCHAR(20) NOT NULL, UNIQUE INDEX `titulo_UNIQUE` (`title` ASC), INDEX `writing_ibfk_1_idx` (`name` ASC), CONSTRAINT `writing_ibfk_1` FOREIGN KEY (`name`) REFERENCES `ex2`.`author` (`name`) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT `writing_ibfk_2` FOREIGN KEY (`title`) REFERENCES `ex2`.`article` (`title`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

d)

create index idx_all on ejer2.magazine(issn,number,tittle,years)

e)

GRANT SELECT ON ex2.*TO'user';

titulo pos nombre inicio articulo escribe autor fin email adscrip publica revista ao ssn titulo numero