MS SQL Schema
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (13th Edition) Chapter 02 */
/* */
/* Morgan Importing [MI-CH02] Project Create Tables */
/* */
/* Microsoft SQL Server 2008 R2 / 2012 code solutions */
/* */
/********************************************************************************/
CREATE TABLE ITEM (
ItemID Int NOT NULL IDENTITY (1,1),
[Description] VarChar(255) NOT NULL,
PurchaseDate Date NOT NULL,
Store Char(50) NOT NULL,
City Char(35) NOT NULL,
Quantity Int NOT NULL,
LocalCurrencyAmount Numeric(18,2) NOT NULL,
ExchangeRate Numeric(12,6) NOT NULL,
CONSTRAINT Purchase_Item_PK PRIMARY KEY (ItemID),
);
CREATE TABLE SHIPMENT (
ShipmentID Int NOT NULL IDENTITY (1,1),
ShipperName Char(35) NOT NULL,
ShipperInvoiceNumber Int NOT NULL,
DepartureDate Date NULL,
ArrivalDate Date NULL,
InsuredValue Numeric(12,2) NOT NULL,
CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID)
);
CREATE TABLE SHIPMENT_ITEM (
ShipmentID Int NOT NULL,
ShipmentItemID Int NOT NULL,
ItemID Int NOT NULL,
Value Numeric(12,2) NOT NULL,
CONSTRAINT ShipmentItem_PK PRIMARY KEY(ShipmentID, ShipmentItemID),
CONSTRAINT Ship_Item_Ship_FK FOREIGN KEY(ShipmentID)
REFERENCES SHIPMENT(ShipmentID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT Ship_Item_Item_FK FOREIGN KEY(ItemID)
REFERENCES ITEM(ItemID)
ON UPDATE NO ACTION
ON DELETE CASCADE
);
/********************************************************************************/
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (12th Edition) Chapter 07 */
/* */
/* Morgan Importing [MI-CH07] Project Insert Data */
/* */
/* Microsoft SQL Server 2008 / 2008 R2 code solutions */
/* */
/********************************************************************************/
/***** ITEM Data ****************************************************************/
INSERT INTO ITEM
VALUES(
'QE Dining Set', '07-Apr-13', 'Eastern Treasures', 'Manila', 2, 403405, 0.01774);
INSERT INTO ITEM
VALUES(
'Willow Serving Dishes', '15-Jul-13', 'Jade Antiques', 'Singapore', 75, 102, 0.5903);
INSERT INTO ITEM
VALUES(
'Large Bureau', '17-Jul-13', 'Eastern Sales', 'Singapore', 8, 2000, 0.5903);
INSERT INTO ITEM
VALUES(
'Brass Lamps', '20-Jul-13', 'Jade Antiques', 'Singapore', 40, 50, 0.5903);
/***** SHIPMENT Data ************************************************************/
INSERT INTO SHIPMENT VALUES
('ABC Trans-Oceanic', 2008651, '10-Dec-12', '15-Mar-13', 15000.00);
INSERT INTO SHIPMENT VALUES
('ABC Trans-Oceanic', 2009012, '10-Jan-13', '20-Mar-13', 12000.00);
INSERT INTO SHIPMENT VALUES
('Worldwide', 49100300, '05-May-13', '17-Jun-13', 20000.00);
INSERT INTO SHIPMENT VALUES
('International', 399400, '02-Jun-13', '17-Jul-13', 17500.00 );
INSERT INTO SHIPMENT VALUES
('Worldwide', 84899440, '10-Jul-13', '28-Jul-13', 25000.00);
INSERT INTO SHIPMENT VALUES
('International', 488955, '05-Aug-13', '11-Sep-13', 18000.00);
/***** SHIPMENT Item ************************************************************/
INSERT INTO SHIPMENT_ITEM VALUES(3, 1, 1, 15000);
INSERT INTO SHIPMENT_ITEM VALUES(4, 1, 4, 1200);
INSERT INTO SHIPMENT_ITEM VALUES(4, 2, 3, 9500);
INSERT INTO SHIPMENT_ITEM VALUES(4, 3, 2, 4500);
/********************************************************************************/
MySQL Schema
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (13th Edition) Chapter 02 */
/* */
/* Morgan Importing [MI-CH02] Database Create Tables */
/* */
/* These are the MySQL 5.6 SQL code solutions */
/* */ /********************************************************************************/
CREATE TABLE ITEM (
ItemID Int NOT NULL AUTO_INCREMENT,
Description VarChar(25) NOT NULL,
PurchaseDate Date NOT NULL,
Store Char(50) NOT NULL,
City Char(35) NOT NULL,
Quantity Int NOT NULL,
LocalCurrencyAmount Numeric(18,2) NOT NULL,
ExchangeRate Numeric(12,6) NOT NULL,
CONSTRAINT Purchase_PK PRIMARY KEY (ItemID)
);
CREATE TABLE SHIPMENT (
ShipmentID Int NOT NULL AUTO_INCREMENT,
ShipperName Char(35) NOT NULL, ShipperInvoiceNumber Int NOT NULL,
DepartureDate Date NULL,
ArrivalDate Date NULL,
InsuredValue Numeric(12,2) NOT NULL,
CONSTRAINT Shipment_PK PRIMARY KEY (ShipmentID)
);
CREATE TABLE SHIPMENT_ITEM (
ShipmentID Int NOT NULL,
ShipmentItemID Int NOT NULL,
ItemID Int NOT NULL,
Value Numeric(12,2) NOT NULL,
CONSTRAINT ShipmentItem_PK PRIMARY KEY(ShipmentID, ShipmentItemID),
CONSTRAINT Ship_Item_Ship_FK FOREIGN KEY(ShipmentID)
REFERENCES SHIPMENT(ShipmentID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT Ship_Item_Item_FK FOREIGN KEY(ItemID)
REFERENCES ITEM(ItemID)
ON UPDATE NO ACTION
ON DELETE CASCADE
);
/********************************************************************************/
/********************************************************************************/
/* */
/* Kroenke and Auer - Database Processing (13th Edition) Chapter 02 */
/* */
/* Morgan Importing [MI-CH02] Database Insert Data */
/* */
/* These are the MYSQL 5.6 SQL code solutions */
/* */ /********************************************************************************/
/***** ITEM Data ************************************************************/
INSERT INTO ITEM
(Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
VALUES(
'QE dining set','2013-04-07', 'Eastern Treasures', 'Manila', 2, 403405, 0.01774);
INSERT INTO ITEM
(Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
VALUES(
'Willow serving dishes', '2013-07-15', 'Jade Antiques', 'Singapore', 75, 102, 0.5903);
INSERT INTO ITEM
(Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
VALUES(
'Large Bureau', '2013-07-17', 'Eastern Sales', 'Singapore', 8, 2000, 0.5903);
INSERT INTO ITEM
(Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount, ExchangeRate)
VALUES(
'Brass lamps', '2013-07-20', 'Jade Antiques', 'Singapore', 40, 50, 0.5903);
/***** SHIPMENT Data ********************************************************/
INSERT INTO SHIPMENT
(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES(
'ABC Trans-Oceanic', 2008651, '2012-12-10', '2013-03-15', 15000.00);
INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES(
'ABC Trans-Oceanic', 2009012, '2013-01-10', '2013-03-20', 12000.00);
INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES('Worldwide', 49100300, '2013-05-05', '2013-06-17', 20000.00);
INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES(
'International', 399400, '2013-06-02', '2013-07-17', 17500.00);
INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES(
'Worldwide', 84899440, '2013-07-10', '2013-07-28', 25000.00);
INSERT INTO SHIPMENT(ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)
VALUES(
'International', 488955, '2013-08-05', '2013-10-11', 18000.00);
/***** SHIPMENT Item ********************************************************/
INSERT INTO SHIPMENT_ITEM VALUES(3, 1, 1, 15000);
INSERT INTO SHIPMENT_ITEM VALUES(4, 1, 4, 1200);
INSERT INTO SHIPMENT_ITEM VALUES(4, 2, 3, 9500);
INSERT INTO SHIPMENT_ITEM VALUES(4, 3, 2, 4500);
/********************************************************************************/