HOMEWORK 3 IS 4420 As an DB intern for Furniture Row, (HW3), you are asked to 1)

Question

HOMEWORK 3

IS 4420

As an DB intern for Furniture Row, (HW3), you are asked to 1) transform the relational model (attached) to metadata, 2) then to SQL DDL, and 3) to be implemented in their new MySQL database. Export the .sql file for the ‘furniture row’ database and submit to HW 3 on canvas.

Do note down any assumptions that you make if any information is missing in the homework

WORKCENTER WORKCENTER PRODUCT Product ID WorkCenter ID Workcenter ID Location PRODUCT LINE WORKCENTER EMPLOYEE Product line ID Product line name Workcenter ID Employee ID PRODUCT Product D Product desc Product finish Uni Product line ID EMPLOYEE price Employee ID Emp Name Address Supervisor ID PRODUCT-MATERIAL Product ID Material ID SKILL Employee ID Skill MATERIAL Material ID Unit of measure CUSTOMER-TERRITORY ORDERTABLE SUPPLIES Order D Order Date customer Customer D Territory ID Materia ID vendor ID Unit price CUSTOMER TERRITORY VENDOR Customer ID Cust name Address Territory ID Territory desc Vendor ID vendor Name Vendor address SALESPERSON ORDER PRODUCT SALESPERSON ID sName Phone Ierritory ID Product D Order ID Quantity

Explanation / Answer

CREATE DATABASE `furniture_row`;

CREATE TABLE `furniture_row`.`product_line` (
`product_line_id` INT NOT NULL,
`product_line_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`product_line_id`),
UNIQUE INDEX `product_line_name_UNIQUE` (`product_line_name` ASC));

CREATE TABLE `furniture_row`.`product` (
`product_id` INT NOT NULL,
`product_desc` VARCHAR(255) NULL DEFAULT NULL,
`product_finish` DATE NOT NULL,
`unit_price` DOUBLE NOT NULL,
`product_line_id` INT NOT NULL,
PRIMARY KEY (`product_id`),
INDEX `product_line_id_idx` (`product_line_id` ASC),
CONSTRAINT `product_line_id_FK`
    FOREIGN KEY (`product_line_id`)
    REFERENCES `furniture_row`.`product_line` (`product_line_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`material` (
`material_id` INT NOT NULL,
`unit_of_measure` VARCHAR(45) NOT NULL,
PRIMARY KEY (`material_id`));

CREATE TABLE `furniture_row`.`product_material` (
`product_id` INT NOT NULL,
`material_id` INT NOT NULL,
PRIMARY KEY (`product_id`, `material_id`),
CONSTRAINT `material_id_FK1`
    FOREIGN KEY (`material_id`)
    REFERENCES `furniture_row`.`material` (`material_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `product_id_FK1`
    FOREIGN KEY (`product_id`)
    REFERENCES `furniture_row`.`product` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`vendor` (
`vendor_id` INT NOT NULL,
`vendor_name` VARCHAR(255) NOT NULL,
`vendor_address` VARCHAR(255) NOT NULL,
PRIMARY KEY (`vendor_id`));

CREATE TABLE `furniture_row`.`supplies` (
`material_id` INT NOT NULL,
`vendor_id` INT NOT NULL,
`unit_price` DOUBLE NULL DEFAULT 0,
PRIMARY KEY (`material_id`, `vendor_id`),
INDEX `vendor_id_idx` (`vendor_id` ASC),
CONSTRAINT `material_id_FK2`
    FOREIGN KEY (`material_id`)
    REFERENCES `furniture_row`.`material` (`material_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `vendor_id`
    FOREIGN KEY (`vendor_id`)
    REFERENCES `furniture_row`.`vendor` (`vendor_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`work_center` (
`work_center_id` INT NOT NULL,
`location` VARCHAR(255) NOT NULL,
PRIMARY KEY (`work_center_id`));

CREATE TABLE `furniture_row`.`work_center_product` (
`product_id` INT NOT NULL,
`work_center_id` INT NOT NULL,
PRIMARY KEY (`product_id`, `work_center_id`),
INDEX `work_center_id_FK1_idx` (`work_center_id` ASC),
CONSTRAINT `proudct_id_FK3`
    FOREIGN KEY (`product_id`)
    REFERENCES `furniture_row`.`product` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `work_center_id_FK1`
    FOREIGN KEY (`work_center_id`)
    REFERENCES `furniture_row`.`work_center` (`work_center_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`employee` (
`employee_id` INT NOT NULL,
`emp_name` VARCHAR(45) NOT NULL,
`address` VARCHAR(255) NULL,
`supervisor_id` INT NULL,
PRIMARY KEY (`employee_id`));
ALTER TABLE `furniture_row`.`employee`
ADD INDEX `employee_id_FK1_idx` (`supervisor_id` ASC);
ALTER TABLE `furniture_row`.`employee`
ADD CONSTRAINT `employee_id_FK1`
FOREIGN KEY (`supervisor_id`)
REFERENCES `furniture_row`.`employee` (`employee_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

CREATE TABLE `furniture_row`.`work_center_employee` (
`work_center_id` INT NOT NULL,
`employee_id` INT NOT NULL,
PRIMARY KEY (`work_center_id`, `employee_id`),
INDEX `employee_id_FK2_idx` (`employee_id` ASC),
CONSTRAINT `work_center_id_FK2`
    FOREIGN KEY (`work_center_id`)
    REFERENCES `furniture_row`.`work_center` (`work_center_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `employee_id_FK2`
    FOREIGN KEY (`employee_id`)
    REFERENCES `furniture_row`.`employee` (`employee_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`skill` (
`employee_id` INT NOT NULL,
`skill` VARCHAR(45) NOT NULL,
PRIMARY KEY (`employee_id`, `skill`),
CONSTRAINT `employee_id`
    FOREIGN KEY (`employee_id`)
    REFERENCES `furniture_row`.`employee` (`employee_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`customer` (
`customer_id` INT NOT NULL,
`customer_name` VARCHAR(45) NOT NULL,
`address` VARCHAR(255) NULL,
PRIMARY KEY (`customer_id`));

CREATE TABLE `furniture_row`.`order` (
`order_id` INT NOT NULL,
`order_date` DATE NOT NULL,
`customer_id` INT NOT NULL,
PRIMARY KEY (`order_id`),
INDEX `customer_id_idx` (`customer_id` ASC),
CONSTRAINT `customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `furniture_row`.`customer` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`order_product` (
`product_id` INT NOT NULL,
`order_id` INT NOT NULL,
`quantity` DOUBLE NULL DEFAULT 0,
PRIMARY KEY (`product_id`, `order_id`),
INDEX `order_id_FK_idx` (`order_id` ASC),
CONSTRAINT `product_id_FK3`
    FOREIGN KEY (`product_id`)
    REFERENCES `furniture_row`.`product` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `order_id_FK`
    FOREIGN KEY (`order_id`)
    REFERENCES `furniture_row`.`order` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`territory` (
`territory_id` INT NOT NULL,
PRIMARY KEY (`territory_id`));

CREATE TABLE `furniture_row`.`customer_territory` (
`customer_id` INT NOT NULL,
`territory_id` INT NOT NULL,
PRIMARY KEY (`customer_id`),
INDEX `territory_id_FK1_idx` (`territory_id` ASC),
CONSTRAINT `customer_id_FK2`
    FOREIGN KEY (`customer_id`)
    REFERENCES `furniture_row`.`customer` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `territory_id_FK1`
    FOREIGN KEY (`territory_id`)
    REFERENCES `furniture_row`.`territory` (`territory_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `furniture_row`.`sales_person` (
`sales_person_id` INT NOT NULL,
`sname` VARCHAR(45) NULL,
`phone` VARCHAR(20) NULL,
`territory_id` INT NOT NULL,
PRIMARY KEY (`sales_person_id`),
INDEX `territory_id_FK2_idx` (`territory_id` ASC),
CONSTRAINT `territory_id_FK2`
    FOREIGN KEY (`territory_id`)
    REFERENCES `furniture_row`.`territory` (`territory_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.