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); 

/********************************************************************************/