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
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);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started