oregonstate phpMyAdmin SQL Dump

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: classmysql.engr.oregonstate.edu:3306
-- Generation Time: Dec 07, 2017 at 10:16 PM
-- Server version: 10.1.22-MariaDB
-- PHP Version: 7.0.23

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: `cs340_tusingz`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`cs340_tusingz`@`%` PROCEDURE `updateAllCollegeStats` ()  NO SQL
BEGIN

DECLARE appC INT;
DECLARE done int default 0;
DECLARE current_cName varchar(20);
DECLARE cNamecur cursor for select cName from CollegeStats;
DECLARE CONTINUE handler for not found set done = 1;

OPEN cNamecur;

REPEAT

    FETCH cNamecur into current_cName;

    UPDATE CollegeStats SET appCount = (SELECT COUNT(*) from Apply 			WHERE Apply.cName = current_cName)

    where cName = current_cName;


UNTIL done
end repeat;
close cNamecur;

END$$

CREATE DEFINER=`cs340_tusingz`@`%` PROCEDURE `updateCollegeStats` (IN `Name` VARCHAR(20))  NO SQL
BEGIN
DECLARE appC INT;

SELECT COUNT(*) INTO appC FROM Apply WHERE cName = Name;

UPDATE CollegeStats SET appCount = appC WHERE cName = Name;

END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `Grocery_item`
--

CREATE TABLE `Grocery_item` (
  `ItemID` int(11) NOT NULL,
  `Info` text NOT NULL,
  `Name` varchar(40) NOT NULL,
  `Calories` int(11) NOT NULL,
  `Price` decimal(4,2) NOT NULL,
  `Image` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Grocery_item`
--

INSERT INTO `Grocery_item` (`ItemID`, `Info`, `Name`, `Calories`, `Price`, `Image`) VALUES
(1, 'Apples are the #1 fruit in America', 'Apple', 5, '0.99', 'https://www.organicfacts.net/wp-content/uploads/2013/05/Apple4.jpg'),
(2, 'Banananananas', 'Bananas', 10, '0.89', 'https://img.purch.com/h/1000/aHR0cDovL3d3dy5saXZlc2NpZW5jZS5jb20vaW1hZ2VzL2kvMDAwLzA2NS8xNDkvb3JpZ2luYWwvYmFuYW5hcy5qcGc='),
(3, 'Cantaloupe of the gods', 'Cantaloupe', 30, '3.99', 'http://www.specialtyproduce.com/sppics/788.png'),
(4, 'Really good for your rabbits', 'Carrot', 2000, '7.00', 'http://media.mercola.com/assets/images/foodfacts/carrot-nutrition-facts.jpg'),
(6, 'Mmmmm finger lickin\' good', 'Chicken Wings', 400, '6.99', 'https://st.depositphotos.com/1010305/1851/i/950/depositphotos_18513939-stock-photo-raw-chicken-legs-on-a.jpg'),
(7, 'The coolest of Cool Aids', 'Cool Aid', 9001, '0.33', 'https://ih0.redbubble.net/image.426698105.1945/flat,800x800,075,f.u1.jpg'),
(8, 'From the best pigs around!', 'Bacon', 600, '2.99', 'http://www.seriouseats.com/images/2016/10/20161018-best-way-to-bake-bacon-18.jpg'),
(9, 'Best for Thanksgiving dinners!', 'Turkey', 7000, '14.99', 'https://scoobysfarm.com/wp-content/uploads/2016/08/whole-chicken.jpg');

-- --------------------------------------------------------

--
-- Table structure for table `Orders`
--

CREATE TABLE `Orders` (
  `OrderID` int(11) NOT NULL,
  `EmployeeID` int(11) DEFAULT NULL,
  `ShopperID` int(11) NOT NULL,
  `ShoppingID` int(11) NOT NULL,
  `Shopper_signature` int(11) NOT NULL DEFAULT '0',
  `Picker_signature` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Orders`
--

INSERT INTO `Orders` (`OrderID`, `EmployeeID`, `ShopperID`, `ShoppingID`, `Shopper_signature`, `Picker_signature`) VALUES
(1, 2, 2, 27, 0, 1),
(2, NULL, 2, 28, 0, 0);

--
-- Triggers `Orders`
--
DELIMITER $$
CREATE TRIGGER `AddNewCart2` AFTER INSERT ON `Orders` FOR EACH ROW BEGIN

DECLARE storeInt INTEGER;

SET storeInt = (SELECT MAX(ShoppingID) FROM Shopping_cart) + 1;

INSERT INTO Shopping_cart (ShoppingID, Total, ShopperID) VALUES (storeInt, 0, new.ShopperID);

END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `ifExists` BEFORE INSERT ON `Orders` FOR EACH ROW BEGIN

IF NOT EXISTS(select ShoppingID from Purchased_item where ShoppingID = new.ShoppingID) THEN

	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'There are no items in your shopping cart';

END IF;

END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `Picker_upper`
--

CREATE TABLE `Picker_upper` (
  `EmployeeID` int(11) NOT NULL,
  `First_name` varchar(40) NOT NULL,
  `Last_name` varchar(40) NOT NULL,
  `Username` varchar(20) DEFAULT NULL,
  `Password` varchar(20) DEFAULT NULL,
  `Credit_card` int(11) NOT NULL,
  `Phone_number` int(11) NOT NULL,
  `Address` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Picker_upper`
--

INSERT INTO `Picker_upper` (`EmployeeID`, `First_name`, `Last_name`, `Username`, `Password`, `Credit_card`, `Phone_number`, `Address`) VALUES
(1, 'new', 'new', 'newuser', 'n', 1, 1, '1'),
(2, 'Zach', 'Tusing', 'Hello', 'h', 403, 503, '234');

-- --------------------------------------------------------

--
-- Table structure for table `Purchased_item`
--

CREATE TABLE `Purchased_item` (
  `ItemID` int(11) NOT NULL,
  `ShoppingID` int(11) NOT NULL,
  `Quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Purchased_item`
--

INSERT INTO `Purchased_item` (`ItemID`, `ShoppingID`, `Quantity`) VALUES
(1, 1, 2),
(3, 4, 2),
(2, 5, 3),
(3, 6, 2),
(1, 6, 5),
(2, 9, 4),
(1, 9, 3),
(1, 13, 1),
(2, 14, 1),
(1, 15, 1),
(1, 16, 1),
(2, 17, 1),
(1, 17, 1),
(3, 18, 1),
(2, 19, 1),
(1, 20, 2),
(2, 21, 1),
(1, 22, 1),
(1, 23, 1),
(1, 24, 1),
(2, 25, 1),
(2, 26, 6),
(3, 26, 4),
(2, 27, 1),
(1, 27, 1),
(3, 27, 1),
(7, 27, 10),
(1, 28, 5),
(3, 28, 4),
(9, 28, 4);

--
-- Triggers `Purchased_item`
--
DELIMITER $$
CREATE TRIGGER `removeTotal` AFTER DELETE ON `Purchased_item` FOR EACH ROW BEGIN

	declare storeInt decimal(13,2);

	SELECT Price into storeInt FROM Grocery_item WHERE ItemID = old.ItemID;

    UPDATE Shopping_cart SET total = total - (storeInt * old.Quantity) WHERE ShoppingID = old.ShoppingID;

END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `updateTot` AFTER UPDATE ON `Purchased_item` FOR EACH ROW BEGIN
	declare storeInt decimal(13,2);

	SELECT Price into storeInt FROM Grocery_item WHERE ItemID = new.ItemID;

    UPDATE Shopping_cart SET total = total + storeInt WHERE ShoppingID = new.ShoppingID;

END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `updateTotal` AFTER INSERT ON `Purchased_item` FOR EACH ROW BEGIN

	declare storeInt decimal(13,2);

	SELECT Price into storeInt FROM Grocery_item WHERE ItemID = new.ItemID;

    UPDATE Shopping_cart SET total = total + storeInt WHERE ShoppingID = new.ShoppingID;

END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `Shopper`
--

CREATE TABLE `Shopper` (
  `ShopperID` int(11) NOT NULL,
  `First_name` varchar(40) NOT NULL,
  `Last_name` varchar(40) NOT NULL,
  `UserName` varchar(20) NOT NULL,
  `Password` varchar(20) NOT NULL,
  `Credit_card` int(11) NOT NULL,
  `Phone_number` int(11) NOT NULL,
  `Address` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Shopper`
--

INSERT INTO `Shopper` (`ShopperID`, `First_name`, `Last_name`, `UserName`, `Password`, `Credit_card`, `Phone_number`, `Address`) VALUES
(1, 'Zach', 'Tusing', 'Zach', 'z', 666000, 503, '6625'),
(2, 'Cameron', 'Friel', 'Cam', 'c', 666999, 503, '902'),
(3, 'test', 'test', 'new', 'test', 0, 0, 'test');

--
-- Triggers `Shopper`
--
DELIMITER $$
CREATE TRIGGER `addNewCart` AFTER INSERT ON `Shopper` FOR EACH ROW BEGIN

DECLARE storeInt INTEGER;

SET storeInt = (SELECT MAX(ShoppingID) FROM Shopping_cart) + 1;

INSERT INTO Shopping_cart (ShoppingID, Total, ShopperID) VALUES (storeInt, 0, new.ShopperID);

END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `Shopping_cart`
--

CREATE TABLE `Shopping_cart` (
  `ShoppingID` int(11) NOT NULL,
  `Total` decimal(6,2) NOT NULL,
  `ShopperID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Shopping_cart`
--

INSERT INTO `Shopping_cart` (`ShoppingID`, `Total`, `ShopperID`) VALUES
(0, '0.00', 1),
(1, '9.00', 2),
(2, '-1.65', 3),
(3, '0.00', 2),
(4, '7.98', 2),
(5, '2.67', 2),
(6, '12.93', 2),
(7, '0.00', 2),
(8, '0.00', 2),
(9, '6.53', 2),
(10, '0.00', 2),
(11, '0.00', 2),
(12, '0.00', 2),
(13, '0.99', 2),
(14, '0.89', 2),
(15, '0.99', 2),
(16, '0.99', 2),
(17, '1.88', 2),
(18, '3.99', 2),
(19, '0.89', 2),
(20, '1.98', 2),
(21, '0.89', 2),
(22, '0.99', 2),
(23, '0.99', 2),
(24, '0.99', 2),
(25, '0.89', 2),
(26, '21.30', 2),
(27, '9.17', 2),
(28, '80.87', 2),
(29, '0.00', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Grocery_item`
--
ALTER TABLE `Grocery_item`
  ADD PRIMARY KEY (`ItemID`);

--
-- Indexes for table `Orders`
--
ALTER TABLE `Orders`
  ADD PRIMARY KEY (`OrderID`),
  ADD KEY `EmployeeID` (`EmployeeID`),
  ADD KEY `ShopperID` (`ShopperID`),
  ADD KEY `ShoppingID` (`ShoppingID`);

--
-- Indexes for table `Picker_upper`
--
ALTER TABLE `Picker_upper`
  ADD PRIMARY KEY (`EmployeeID`);

--
-- Indexes for table `Purchased_item`
--
ALTER TABLE `Purchased_item`
  ADD KEY `ItemID` (`ItemID`),
  ADD KEY `ShoppingID` (`ShoppingID`);

--
-- Indexes for table `Shopper`
--
ALTER TABLE `Shopper`
  ADD PRIMARY KEY (`ShopperID`);

--
-- Indexes for table `Shopping_cart`
--
ALTER TABLE `Shopping_cart`
  ADD PRIMARY KEY (`ShoppingID`),
  ADD KEY `ShopperID` (`ShopperID`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Orders`
--
ALTER TABLE `Orders`
  ADD CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `Picker_upper` (`EmployeeID`),
  ADD CONSTRAINT `Orders_ibfk_2` FOREIGN KEY (`ShopperID`) REFERENCES `Shopper` (`ShopperID`),
  ADD CONSTRAINT `Orders_ibfk_3` FOREIGN KEY (`ShoppingID`) REFERENCES `Shopping_cart` (`ShoppingID`) ON UPDATE CASCADE;

--
-- Constraints for table `Purchased_item`
--
ALTER TABLE `Purchased_item`
  ADD CONSTRAINT `Purchased_item_ibfk_1` FOREIGN KEY (`ItemID`) REFERENCES `Grocery_item` (`ItemID`),
  ADD CONSTRAINT `Purchased_item_ibfk_2` FOREIGN KEY (`ShoppingID`) REFERENCES `Shopping_cart` (`ShoppingID`) ON UPDATE CASCADE;

--
-- Constraints for table `Shopping_cart`
--
ALTER TABLE `Shopping_cart`
  ADD CONSTRAINT `Shopping_cart_ibfk_1` FOREIGN KEY (`ShopperID`) REFERENCES `Shopper` (`ShopperID`) ON UPDATE CASCADE;
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 */;