this is review Please keep the answer short The SQL statements that answer these questions. please keep the answer simple will give thumbs up thank you :)! INSERT STATEMENT LINK below https://drive.google.com/file/d/1qHCkrods5K2V1syyS5IG6-Eb_3QQmud7/view?usp=sharing Table.SQL below -- MySQL Script generated by MySQL Workbench -- Sun Feb 21 00:00:31 2021 -- Model: New Model Version: 1.0 -- 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;
this is review
Please keep the answer short
The SQL statements that answer these questions. please keep the answer simple will give thumbs up thank you :)!
INSERT STATEMENT LINK below
https://drive.google.com/file/d/1qHCkrods5K2V1syyS5IG6-Eb_3QQmud7/view?usp=sharing
Table.SQL below
-- MySQL Script generated by MySQL Workbench
-- Sun Feb 21 00:00:31 2021
-- Model: New Model Version: 1.0
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table state
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS state (
state_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) NOT NULL,
abb VARCHAR(2) NOT NULL,
PRIMARY KEY (state_id),
UNIQUE INDEX abb_UNIQUE (abb ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table address
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS address (
address_id INT NOT NULL AUTO_INCREMENT,
street1 VARCHAR(45) NOT NULL,
street2 VARCHAR(45) NULL,
city VARCHAR(45) NOT NULL,
zip VARCHAR(5) NOT NULL,
state_id INT NOT NULL,
PRIMARY KEY (address_id),
INDEX FK_ADDRESS_STATE_IDX (state_id ASC),
CONSTRAINT FK_ADDRESS_STATE
FOREIGN KEY (state_id)
REFERENCES state (state_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table person
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS person (
person_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
sex VARCHAR(1) NOT NULL,
dob DATETIME,
address_id INT NULL,
PRIMARY KEY (person_id),
INDEX FK_PERSON_ADDRESS_IDX (address_id ASC),
CONSTRAINT FK_PERSON_ADDRESS
FOREIGN KEY (address_id)
REFERENCES address (address_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table branch
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS branch (
branch_no VARCHAR(5) NOT NULL,
address_id INT NOT NULL,
PRIMARY KEY (branch_no),
INDEX FK_BRANCH_ADDRESS_IDX (address_id ASC) COMMENT ' ',
UNIQUE INDEX ADDRESS_ID_UNIQUE (address_id ASC),
CONSTRAINT FK_BRANCH_ADDRESS
FOREIGN KEY (address_id)
REFERENCES address (address_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table staff
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS staff (
staff_no VARCHAR(5) NOT NULL,
person_id INT NOT NULL,
position VARCHAR(45) NULL,
salary INT NULL,
branch_no VARCHAR(5) NOT NULL,
PRIMARY KEY (staff_no),
INDEX FK_STAFF_PERSON_IDX (person_id ASC),
UNIQUE INDEX PERSON_ID_UNIQUE (person_id ASC),
INDEX fk_staff_branch1_idx (branch_no ASC),
CONSTRAINT FK_STAFF_PERSON
FOREIGN KEY (person_id)
REFERENCES person (person_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_staff_branch1
FOREIGN KEY (branch_no)
REFERENCES branch (branch_no)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
