#
/** * setups up all 5 fruit Tables, with alters script, primary keys are set, you just need to run the two views and then you are ready for the pmaTrigger.sql series */ -- phpMyAdmin SQL Dump -- version 4.4.10 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jul 05, 2015 at 01:09 PM -- Server version: 5.5.33-MariaDB -- PHP Version: 5.3.17 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 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: `DB2jstudent0` -- -- -------------------------------------------------------- -- -- Table structure for table `customer` -- drop table if exists `customer`; CREATE TABLE IF NOT EXISTS `customer` ( `customerID` mediumint(11) NOT NULL, `name` varchar(32) NOT NULL COMMENT 'customer name', `password` char(40) NOT NULL COMMENT 'Password for customer', `email` varchar(64) NOT NULL COMMENT 'customer email' ) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=latin1; -- -- Dumping data for table `customer` -- INSERT INTO `customer` (`customerID`, `name`, `password`, `email`) VALUES (100, 'Minnie Mouse', '51b27e8e57be94200897a71ba136099542d925bc', 'mmouse0@cim.saddleback.edu'), (120, 'Joe Student', '3a5e2f520b038d308f4486613a6a6bbd3ab3c860', 'jstudent0@cim.saddleback.edu'), (130, 'premiere', '3a5e2f520b038d308f4486613a6a6bbd3ab3c860', 'premiere@cim.saddleback.edu'); -- -------------------------------------------------------- -- -- Table structure for table `fruit` -- --drop table if exists `fruit`; CREATE TABLE IF NOT EXISTS `fruit` ( `fruitID` smallint(6) unsigned NOT NULL, `name` varchar(24) NOT NULL, `price` decimal(5,2) unsigned NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `fruit` -- INSERT INTO `fruit` (`fruitID`, `name`, `price`) VALUES (1, 'apple', '2.50'), (2, 'pear', '3.00'), (3, 'banana', '1.00'), (4, 'grape', '0.75'), (5, 'peach', '1.25'), (6, 'plum', '1.25'), (8, 'pluot', '4.50'); -- -------------------------------------------------------- -- -- Table structure for table `inventory` -- --drop table if exists `inventory`; CREATE TABLE IF NOT EXISTS `inventory` ( `fruitID` smallint(6) unsigned NOT NULL, `quantity` mediumint(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory` -- INSERT INTO `inventory` (`fruitID`, `quantity`) VALUES (1, 100), (3, 500), (2, 100), (5, 10), (1, 250), (2, 200); -- -------------------------------------------------------- -- -- Table structure for table `orderLine` -- --drop table if exists `orderLine`; CREATE TABLE IF NOT EXISTS `orderLine` ( `orderID` mediumint(11) NOT NULL, `fruitID` smallint(6) unsigned NOT NULL, `quantity` mediumint(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `orderLine` -- INSERT INTO `orderLine` (`orderID`, `fruitID`, `quantity`) VALUES (23, 1, 100), (23, 2, 50), (24, 3, 100), (24, 5, 20), (24, 2, 50); -- -------------------------------------------------------- -- -- Table structure for table `orders` -- --drop table if exists `orders`; CREATE TABLE IF NOT EXISTS `orders` ( `orderID` mediumint(11) NOT NULL, `customerID` mediumint(11) NOT NULL, `startDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `shipDate` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1; -- -- Dumping data for table `orders` -- INSERT INTO `orders` (`orderID`, `customerID`, `startDate`, `shipDate`) VALUES (23, 120, '2012-08-30 07:00:00', NULL), (24, 130, '2012-09-01 07:00:00', NULL); -- -- Indexes for dumped tables -- -- -- Indexes for table `customer` -- ALTER TABLE `customer` ADD PRIMARY KEY (`customerID`); -- -- Indexes for table `fruit` -- ALTER TABLE `fruit` ADD PRIMARY KEY (`fruitID`); -- -- Indexes for table `orders` -- ALTER TABLE `orders` ADD PRIMARY KEY (`orderID`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `customer` -- ALTER TABLE `customer` MODIFY `customerID` mediumint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=131; -- -- AUTO_INCREMENT for table `fruit` -- ALTER TABLE `fruit` MODIFY `fruitID` smallint(6) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table `orders` -- ALTER TABLE `orders` MODIFY `orderID` mediumint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=25; #