DB Report

Home >Information Systems homework help >DB Report

-- phpMyAdmin SQL Dump -- version 4.8.5 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Apr 27, 2019 at 09:48 PM -- Server version: 10.1.38-MariaDB -- PHP Version: 7.3.3 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `login` -- DELIMITER $$ -- -- Procedures -- CREATE DEFINER=`root`@`localhost` PROCEDURE `EmployeeSP_Delete` (INOUT `employee_id` INT) NO SQL DELETE from employees WHERE employees.employee_id=employee_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `EmployeeSP_Insert` (IN `employee_id` INT, IN `first_name` VARCHAR(60), IN `last_name` VARCHAR(60), IN `manager_id` INT) NO SQL INSERT into employees (employee_i,first_name, last_name,manager_id) VALUES (employee_i,first_name, last_name,manager_id)$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `EmployeeSP_Update` (INOUT `employee_id` INT, INOUT `last_name` VARCHAR(60)) NO SQL UPDATE employees SET last_name=last_name WHERE employees.employee_id=employee_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `ItemsSP_Delete` (INOUT `item_id` INT) NO SQL DELETE from items WHERE items.item_id=item_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `ItemsSP_Insert` (IN `item_id` INT, IN `title` VARCHAR(256), IN `unit_price` VARCHAR(60)) NO SQL INSERT into items (item_id,title,unit_price) VALUES (item_id,title,unit_price)$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `ItemsSP_Update` (INOUT `item_id` INT, INOUT `unit_price` VARCHAR(60)) NO SQL UPDATE items SET unit_price=unit_price WHERE items.item_id=item_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `OrdersSP_Delete` (INOUT `order_id` INT) NO SQL DELETE from orders WHERE orders.order_id=order_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `OrdersSP_Insert` (IN `order_id` INT, IN `user_id` INT, IN `order_date` DATE, IN `shipped_date` DATE, IN `employee_id` INT) NO SQL INSERT INTO orders (order_id,customer_id,order_date,shipped_date,employee_id) VALUES (order_id,customer_id,order_date,shipped_date,employee_id)$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `OrdersSP_Update` (INOUT `order_id` INT, INOUT `shipped_date` DATE) NO SQL UPDATE orders SET shipped_date=shipped_date WHERE orders.order_id=order_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Order_detailsSP_Delete` (INOUT `item_id` INT, INOUT `order_id` INT) NO SQL DELETE from order_details WHERE order_details.order_id=order_id AND order_details.item_id=item_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `order_detailsSP_Insert` (IN `order_id` INT, IN `item_id` INT, IN `order_qty` INT) NO SQL INSERT into order_details (order_id,item_id,order_qty) VALUES (order_id,item_id,order_qty)$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Order_detailsSP_Update` (INOUT `order_id` INT, INOUT `item_id` INT, INOUT `order_qty` INT) NO SQL UPDATE order_details SET order_qty=order_qty WHERE order_details.order_id=order_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `UsersSP_Delete` (INOUT `user_id` INT) NO SQL DELETE from users WHERE users.user_id=user_id$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `UsersSP_Insert` (IN `user_address` VARCHAR(60), IN `user_city` VARCHAR(20), IN `user_state` VARCHAR(2), IN `user_zip` VARCHAR(5), IN `user_phone` VARCHAR(10)) NO SQL INSERT into users (user_address,user_city,user_state,user_zip,user_phone) VALUES (user_address,user_city,user_state,user_zip,user_phone)$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `UsersSP_Update` (INOUT `user_id` INT, INOUT `user_phone` VARCHAR(60)) NO SQL UPDATE users SET user_phone=user_phone WHERE users.user_id=user_id$$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `employees` -- CREATE TABLE `employees` ( `employee_id` int(11) NOT NULL, `last_name` varchar(20) DEFAULT NULL, `first_name` varchar(20) DEFAULT NULL, `manager_id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `employees` -- INSERT INTO `employees` (`employee_id`, `last_name`, `first_name`, `manager_id`) VALUES (1, 'Smith', 'Cindy', NULL), (2, 'Jones', 'Elmer', 1), (3, 'Simonian', 'Ralph', 2), (4, 'Hernandez', 'Olivia', 9), (5, 'Aaronsen', 'Robert', 4), (6, 'Watson', 'Denise', 8), (7, 'Hardy', 'Thomas', 2), (8, 'Leary', 'Rhea', 9), (9, 'Locario', 'Paulo', 1); -- -------------------------------------------------------- -- -- Table structure for table `items` -- CREATE TABLE `items` ( `item_id` int(11) NOT NULL, `title` varchar(50) DEFAULT NULL, `unit_price` decimal(9,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `items` -- INSERT INTO `items` (`item_id`, `title`, `unit_price`) VALUES (1, 'Umami In Concert', '17.95'), (2, 'Race Car Sounds', '13.00'), (3, 'No Rest For The Weary', '16.95'), (4, 'More Songs About Structures and Comestibles', '17.95'), (5, 'On The Road With Burt Ruggles', '17.50'), (6, 'No Fixed Address', '16.95'), (7, 'Rude Noises', '13.00'), (8, 'Burt Ruggles: An Intimate Portrait', '17.95'), (9, 'Zone Out With Umami', '16.95'), (10, 'btcetera', '17.00'), (11, 'Amami In Concert', '17.95'), (12, 'Race Truck Sounds', '13.00'), (13, 'Always Rest For The Weary', '16.95'), (14, 'Songs About Structures', '17.95'), (15, 'Off Road With John Line', '17.50'), (16, 'No Fixed', '16.95'), (17, 'Noises', '13.00'), (18, 'Body Mind and Soul', '17.95'), (19, 'In The Zone', '16.95'), (20, 'Sweet Living', '17.00'); -- -------------------------------------------------------- -- -- Table structure for table `orders` -- CREATE TABLE `orders` ( `order_id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `order_date` date DEFAULT NULL, `shipped_date` date DEFAULT NULL, `employee_id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `orders` -- INSERT INTO `orders` (`order_id`, `user_id`, `order_date`, `shipped_date`, `employee_id`) VALUES (19, NULL, '2012-10-23', '2012-10-28', 6), (29, NULL, '2012-11-05', '2012-11-11', 6), (32, NULL, '2012-11-10', '2012-11-13', 2), (45, NULL, '2012-11-25', '2012-11-30', NULL), (70, NULL, '2012-12-28', '2013-01-07', 5), (89, NULL, '2013-01-20', '2013-01-22', 7), (97, NULL, '2013-01-29', '2013-02-02', 5), (118, NULL, '2013-02-24', '2013-02-28', 7), (144, NULL, '2013-03-21', '2013-03-29', 1), (158, NULL, '2013-04-04', '2013-04-20', 9), (165, NULL, '2013-04-11', '2013-04-13', 1), (180, NULL, '2013-04-25', '2013-05-30', NULL), (231, NULL, '2013-06-14', '2013-06-22', 9), (242, NULL, '2013-06-24', '2013-07-06', 3), (264, NULL, '2013-07-15', '2013-07-18', 6), (298, NULL, '2013-08-18', '2013-09-22', 3), (321, NULL, '2013-09-09', '2013-10-05', 6), (381, NULL, '2013-11-08', '2013-11-16', 7), (413, NULL, '2013-12-05', '2014-01-11', 7), (442, NULL, '2013-12-28', '2014-01-03', 5); -- -------------------------------------------------------- -- -- Table structure for table `order_details` -- CREATE TABLE `order_details` ( `order_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `order_qty` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `order_details` -- INSERT INTO `order_details` (`order_id`, `item_id`, `order_qty`) VALUES (19, 1, 1), (29, 9, 1), (32, 1, 1), (70, 9, 1), (89, 5, 1), (97, 1, 1), (118, 10, 1), (144, 3, 1), (158, 1, 1), (158, 3, 1), (180, 7, 1), (231, 10, 1), (242, 1, 1), (242, 7, 1), (264, 5, 1), (264, 10, 1), (298, 6, 1), (321, 6, 1), (381, 1, 2), (413, 6, 2); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `user_id` int(10) NOT NULL, `user_first` varchar(256) NOT NULL, `user_last` varchar(256) NOT NULL, `user_email` varchar(256) NOT NULL, `user_uid` varchar(256) NOT NULL, `user_pwd` varchar(256) NOT NULL, `user_address` varchar(265) NOT NULL, `user_city` varchar(265) NOT NULL, `user_state` varchar(265) NOT NULL, `user_zip` varchar(265) NOT NULL, `user_phone` varchar(265) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `users` -- INSERT INTO `users` (`user_id`, `user_first`, `user_last`, `user_email`, `user_uid`, `user_pwd`, `user_address`, `user_city`, `user_state`, `user_zip`, `user_phone`) VALUES (1, 'Agnes', 'Galimah', 'agnesgalimah@yahoo.com', 'Agalimah', '$2y$10$LPp4eCgZVm7gtI0WtlJnou20lhdKI/u/ugIomP.Qa2VpjQuynczmm', '60 Madison Ave', 'New York', 'NY', '10010', '2125554800'), (2, 'Vernesa', 'Scott', 'VerScott@gmail.com', 'VerGirl', '$2y$10$Xtwa9t.zAozoXYP/rZKyYuQjOnlXa7MZLk5C3xSRnPR/J8HOJSbFW', '11 E Rancho Madera Rd', 'Madison', 'WI', '53707', '2095551205'), (3, 'Akin', 'Ade', 'akinade@gmail.com', 'AkinAde1', '$2y$10$PYADFI88seqigcHNcG3TnOU5SYnw49FcE5yUeBQM109JlDW31kJCS', '1555 W Lane Ave', 'Columbus', 'OH', '43221', '6145554435'), (4, 'Mikayla', 'Davis', 'MikaylaDavis@yahoo.com', 'MDavis', '$2y$10$bM0Zb4ePI2Ar7AqLJM87YetMsRRn.WStoQsumveeW9z8/D51GoljG', '2021 K Street Nw', 'Washington', 'DC', '20006', '2025555561'), (5, 'Kendall', 'Mayte', 'KendallMayte@gmail.com', 'KMayte', '$2y$10$BlaVuMgpVerjKB0UXXsPxe0P7gf1Hzc3oKnpKZda76QARdppODyk6', '4775 E Miami River Rd', 'Cleves', 'OH', '45002', '5135553043'), (6, 'Kaitlin', 'Hostlery', 'KaitlinHostlery@yahoo.com', 'KHostlery', '$2y$10$dbGUhjQI7EHynkEPSpV5bOjyGGv6xVDzs7/OVrbpVp1hKrVVZA8dG', '3250 Spring Grove Ave', 'Cincinnati', 'OH', '45225', '8005551957'), (7, 'Derek', 'Chaddick', 'DerekChaddick@icloud.com', 'DChaddick', '$2y$10$1QHdTgdwCKXom7xeYJlvBufbjpDZmmyF0wHLi49iTf5DzTSdrmWsq', '9022 E Merchant Wy', 'Fairfield', 'IA', '52556', '5155556130'), (8, 'Deborah', 'Davis', 'DeborahDavis@gmail.com', 'DDavis', '$2y$10$Dv1MeN5EmqlIwM3bdJrJzOfTTGLsJs15yQR6JIsLszZnDyZSNRgi2', 'Davis\',\'415 E Olive Ave', 'Fresno', 'CA', '93728', '5595558060'), (9, 'Karina', 'Lacy', 'KarinaLacy@yahoo.com', 'KLacy', '$2y$10$aA1NZGzcyINQIhXFHTEVJu/K6jtmEjW5p0hZdaou2LvjfllLQBBrO', '882 W Easton Wy', 'Los Angeles', 'CA', '90084', '8005557000'), (10, 'Kurt', 'Nickalus', 'KurtNickalus@gmail.com', 'KNickalus', '$2y$10$KOn9PlRL2BW4yQ8Kyc9Weu/OeZZbRYEN6y5fW63vSlS3KBseTLj6O', '28210 N Avenue Stanford', 'Valencia', 'CA', '91355', '8055550584'), (11, 'Kelsey', 'Eulalia', 'KelseyEulalia@icloud.com', 'KEulalia', '$2y$10$48c8Kwvfjur537y4.q9ObeuH1OpKjNX4dhFgd8f7nqHfbxL13Zayq', '7833 N Ridge Rd', 'Sacramento', 'CA', '95887', '2095557500'), (12, 'Anders', 'Rohansen', 'AndersRohansen@icloud.com', 'ARohansen', '$2y$10$h6FwWfb4Bbl3YWBe0iJpMu3RE6JmcgVl3OssP8ujW/mhE9mM64RuS', '12345 E 67th Ave NW', 'Takoma Park', 'MD', '24512', '3385556772'), (13, 'Thalia', 'Neftaly', 'ThaliaNeftaly@yahoo.com', 'TNeftaly', '$2y$10$P8XvDvdzfFFNA7pIkz6bCOs0xd5AXvaLI4kHj4Ulsj2.BqKfx2YJ2', '2508 W Shaw Ave', 'Fresno', 'CA', '93711', '5595556245'), (14, 'Gonzalo', 'Keeton', 'GonzaloKeeton@gmail.com', 'GKeeton', '$2y$10$3rxP5CAejVGcEqbRNAGxl.HCXjyA0hHSCjfz/Dk7pt6Ddzt/rD69m', '12 Daniel Road', 'Fairfield', 'NJ', '07004', '2015559742'), (15, 'Ania', 'Irvin', 'AniaIrvin@yahoo.com', 'AIrvin', '$2y$10$9sc28u2VIdHhgu7T7KRzSO27PO5qXfOUUDBWS962KGPIeDm21GuCy', '1099 N Farcourt St', 'Orange', 'CA', '92807', '7145559000'), (16, 'Dakota', 'Baylee', 'DakotaBaylee@icloud.com', 'DBaylee', '$2y$10$HeCqr.y1HWQchTL1vrOU3eSitqmfW/iny8.mXU4oIqlTf4gF3Szz.', '1033 N Sycamore Ave.', 'Los Angeles', 'CA', '90038', '2135554322'), (17, 'Samuel', 'Jacobsen', 'SamuelJacobsen@gmail.com', 'SJacobsen', '$2y$10$1VIr.y/gH9TWPEaoV20uTed7OLKxkTir8hxuNiEXb6Quk3k.zgCwS', '3433 E Widget Ave', 'Palo Alto', 'CA', '92711', '4155553434'), (18, 'Justin', 'Javen', 'JustinJaven@gmail.com', 'JJaven', '$2y$10$VVdKYQt.aI8/rZdxPxFcr.UNh4fN8YvGDQUwpfuE7nVmZQauzdDTq', '828 S Broadway', 'Tarrytown', 'NY', '10591', '8005550037'), (19, 'Kyle', 'Marissa', 'KyleMarissa@yahoo.com', 'KMarissa', '$2y$10$ImYfYDs9kqmr.hSG4ud2vOy/6/dChYOWge/57piUVVVV3ulL8AQZS', '789 E Mercy Ave', 'Phoenix', 'AZ', '85038', '9475553900'), (20, 'Erick', 'Kaleigh', 'ErickKaleigh@yahoo.com', 'EKaleigh', '$2y$10$bYBStEgZspGL/zFJ0wmY/eWTgKzLhcbE1XEWcwi4guLlMgBXobnri', 'Five Lakepointe Plaza, Ste 500', 'Charlotte', 'NC', '28217', '7045553500'); -- -- Indexes for dumped tables -- -- -- Indexes for table `employees` -- ALTER TABLE `employees` ADD PRIMARY KEY (`employee_id`); -- -- Indexes for table `items` -- ALTER TABLE `items` ADD PRIMARY KEY (`item_id`); -- -- Indexes for table `orders` -- ALTER TABLE `orders` ADD PRIMARY KEY (`order_id`), ADD KEY `user_id` (`user_id`) USING BTREE, ADD KEY `employee_id` (`employee_id`) USING BTREE; -- -- Indexes for table `order_details` -- ALTER TABLE `order_details` ADD PRIMARY KEY (`order_id`,`item_id`), ADD KEY `item_id_idx` (`item_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `user_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44; -- -- Constraints for dumped tables -- -- -- Constraints for table `orders` -- ALTER TABLE `orders` ADD CONSTRAINT `customer_id` FOREIGN KEY (`user_id`) REFERENCES `customers` (`customer_id`), ADD CONSTRAINT `employee_id` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`); -- -- Constraints for table `order_details` -- ALTER TABLE `order_details` ADD CONSTRAINT `item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`), ADD CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;