#
# these should already be set to the same types, # but just in case set them to correct type,attribute # /** You have a pmaTrigger essay quiz which is based on these 5 essay questions, and 10 more you will see in future scripts, you need to answer these questions in your notebook and compare your answers to http://cim.saddleback.edu/~premiere/essay5.html before attempting the pmaView quiz. Essay 1 Which database engine are we using and why? may want to revisit this after you watch the entire phpMyAdmin VG5 Series However, you can type the command show engines; in a sql window You should revisit this essay qeustion after we cover constraints/trigger Essay 2 What are the primary keys for each table, make sure you include complex Multi keys. Essay 3 When a row gets added to table: orders what is the default start and ship date, does this make sense? Essay 4 For all currency denomimations what is the default data type and why? Essay 5 When we exported the tables (fruitReset VG4 video) what were the starting default values of primary keys in tables customer, fruit and orders, do you notice anything specific, why do the tables orderLine and inventory do not have default values? Essay 6-9 are in fruitTrigger.sql.html */ /** You cannot run the file if any foreign key constraints exists, therefore I must first drop all foreign key contraints before altering tables, note a foreign key constraints stops you from deleting a table, therefore alter will give... #1025 - Error on rename of './fruit/#sql-f05_7838' to './fruit/fruit' (errno: 150) note if students follow instructions, these problems will not occur, but following instructions is a thing of the past. */ /* only these should exists alter table `inventory` drop foreign key `fruitID`; alter table `orderLine` drop foreign key `orderID`, drop foreign key `fruitID`; alter table `orders` drop foreign key `customerID`; */ #call fruitShared.dropAllForeignKeys( database() ); #call fruitShared.dropForeignKeys( database(), 'orderLine' ); #call fruitShared.dropForeignKeys( database(), 'orders' ); # drop procedure if exists #call fruitShared.dropAllViews( database() ); #drop view if exists `fruitView`; #drop view if exists `inventoryQuantity`; ALTER TABLE `customer` CHANGE `customerID` `customerID` MEDIUMINT( 11 ) NOT NULL AUTO_INCREMENT, DROP PRIMARY KEY, ADD PRIMARY KEY (`customerID`), engine=InnoDB; #will be discussing engines w constraints/triggers alter table `fruit` #make sure all columns same datatypes change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL AUTO_INCREMENT, CHANGE `price` `price` decimal( 5, 2 ) unsigned NOT NULL, CHANGE `name` `name` VARCHAR( 24 ) NOT NULL, #changing fruitID may require resetting primary key, only orders required this DROP PRIMARY KEY, ADD PRIMARY KEY (`fruitID`), #note drop primary key if exists does not exist engine=InnoDB; alter table `inventory` change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL, change `quantity` `quantity` mediumint( 11 ) unsigned NOT NULL, engine=InnoDB; alter table `orderLine` change `orderID` `orderID` MEDIUMINT( 11 ) NOT NULL, change `fruitID` `fruitID` smallint( 6 ) unsigned NOT NULL, change `quantity` `quantity` mediumint( 11 ) unsigned NOT NULL, engine=InnoDB; alter table `orders` change `orderID` `orderID` MEDIUMINT( 11 ) NOT NULL AUTO_INCREMENT, change `customerID` `customerID` MEDIUMINT( 11 ) NOT NULL, # note Timestamp supports defaults, DATETIME doesn't # note once you change date to startDate, you cannot do it 2nd time # change if exists `date` doesn not exist in mysql standard CHANGE `startDate` `startDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, # DROP IF EXISTS `shipDate` and check for date is not part of mysql standard DROP PRIMARY KEY, ADD PRIMARY KEY (`orderID`), engine=InnoDB; /** PTD Note this we cannot add shipDate if it already exists would be nice if sql had an if statement, but it doesn't if( (select count( * ) from `orders`) = 3 ) or a change if exists but instead I need to write an entire procedure to check for shipDate and then either modify or add it. problems is if a students runs this on an already modified it will fail so I needed to add the procedure below, note it is automatically called */ drop procedure if exists updateShipDate; delimiter $$ #unable to move this to fruitShared CREATE PROCEDURE updateShipDate( ) BEGIN -- add a column safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=database() AND COLUMN_NAME='shipDate' AND TABLE_NAME='orders') ) THEN alter table `orders` ADD `shipDate` TIMESTAMP NULL DEFAULT NULL; ELSE alter table `orders` CHANGE `shipDate` `shipDate` TIMESTAMP NULL DEFAULT NULL; END IF; END $$ delimiter ; call updateShipDate( ); check table customer, fruit, inventory, `orders`, `orderLine`; #