As an intern for Furniture Row, you are asked to complete the final part of the

Question

As an intern for Furniture Row, you are asked to complete the final part of the database implementation for this firm. Using the SQL DDL statements from HW3 (at the bottom of this question), you are to insert data into the 17 tables (at least 20 rows per table) and create the following SQL DML queries.
Your assignments:

1. Populate the 17 tables with at least 20 rows per table. Note that rows in associative entity relations may not be 20 (could be more or fewer) since they are subject to the relationships between regular entity relations. (5 pts)
2. Do a Cartesian Product from the following tables (Product, Customer, Employee, Workcenter). The generated result may not make sense. (2 pts)
3. List customer information for customers that have placed orders for a particular product and from a particular city (You decide on the value of any product_desc from PRODUCT table and city value from CUSTOMER table). Use Equi-join. (2 pts)
4. List employee information for those employees whose first name starts with ‘J’ and worked in a certain workcenter location (You decide the value of location from the WORKCENTER table and first name from EMPLOYEE). Use Inner Join. (2 pts)
5. List names of salespeople who belong to a certain territory description (You decide on the value of territory_desc from TERRITORY table). Use Natural Join. (2 pts)
6. Do a LEFT JOIN on PRODUCT_LINE and PRODUCT tables. (2 pts)
7. List material_id from MATERIAL and SUPPLIES tables using UNION. (2 pts)
8. List customer_id from ORDER and CUSTOMER tables using UNION ALL. (2 pts)
9. List employee_id from EMPLOYEE and SKILL tables using INTERSECT (EXISTS). (2 pts)
10. Create a dynamic VIEW for customer information who ordered products made in certain location (You decide on the value of that location from WORKCENTER table). (2 pts)
11. Create a Stored Procedure for obtaining employee information for most popular product (The product that has the most count in the ORDER table). This requires IN parameter for the count. (2 pts)

Export your furniture-row .sql file from PHPMyAdmin and submit both the .sql file and your text file containing the working SQL DML queries to canvas. Do note down any assumptions that you make if any such information is missing in the homework instructions.

SQL DDL statements from HW3

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

Explanation / Answer

1. Assuming data is already populated in the all 17 tables

2. SELECT * FROM Product
CROSS JOIN Customer
CROSS JOIN Employee
CROSS JOIN Workcenter

3. SELECT cust.*
FORM customer cust ,product prod
WHERE lower(cust.city) = "abc" /*actually in create statement there is no such column
called city so i am assume that this column shoild be there*/
AND lower(prod.poduct_desc) = "xyz"

4. SELECT emp.*
FROM employee emp,
work_center work
WHERE emp.emp_name LIKE 'J%'
AND lower(work.location) = "xyz"

5. Not a valid question as there is no such column called "territory_desc" in "territory" table

6. SELECT *
FROM product
LEFT JOIN product_line
ON (product_line_id)

7. SELECT material_id
FROM material
UNION
select material_id
FROM supplies

8. SELECT cutomer_id
FROM order
UNION ALL
SELECT customer_id
FROM customer

9. SELECT employee_id
FROM employee
INTERSECT
SELECT employee_id
FROM skill

10. create or replace view emp_view
SELECT cust.*
FROM customer cust,
order_product ordprod,
work_center work,
work_center_product workprod
WHERE work.work_center_id = workprod.work_center_id
AND work.product_id = ordprod.product_id
AND work.location = "xyz"

11. More information needed as there is a IN parameter of count(but count of what column?)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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