MS SQL Schema

/**********************************************************************************/
/*                                                                                */
/* Kroenke and Auer - Database Processing (13th Edition) Chapters 07/10A          */
/*                                                                                */
/* The View Ridge Gallery (VRG) - Create Tables                                   */
/*                                                                                */
/* These are the Microsoft SQL Server 2008R2/2012 SQL code solutions              */
/*                                                                                */
/**********************************************************************************/
 
USE master
IF NOT EXISTS(SELECT * FROM sys.databases WHERE name='ViewRidge')
CREATE DATABASE ViewRidge
 
IF OBJECT_ID('dbo.ARTIST') IS NOT NULL DROP TABLE dbo.ARTIST
IF OBJECT_ID('dbo.CUSTOMER') IS NOT NULL DROP TABLE dbo.CUSTOMER
IF OBJECT_ID('dbo.CUSTOMER_ARTIST_INT') IS NOT NULL DROP TABLE dbo.CUSTOMER_ARTIST_INT
IF OBJECT_ID('dbo.TRANS') IS NOT NULL DROP TABLE dbo.TRANS
IF OBJECT_ID('dbo.WORK') IS NOT NULL DROP TABLE dbo.WORK
 
GO

USE ViewRidge
 
CREATE TABLE ARTIST (
   ArtistID            Int                 NOT NULL IDENTITY(1,1),
   LastName            Char(25)            NOT NULL,
   FirstName           Char(25)            NOT NULL,
   Nationality         Char(30)            NULL,
   DateOfBirth         Numeric(4)          NULL, 
   DateDeceased        Numeric(4)          NULL,

   CONSTRAINT  ArtistPK            PRIMARY KEY(ArtistID),
   CONSTRAINT  ArtistAK1           UNIQUE(LastName, FirstName),
   CONSTRAINT  NationalityValues   CHECK (Nationality IN ('Canadian', 'English', 'French', 'German', 'Mexican', 'Russian', 'Spanish', 'United States')),
   CONSTRAINT  BirthValuesCheck    CHECK (DateOfBirth < DateDeceased),
   CONSTRAINT  ValidBirthYear      CHECK (DateOfBirth LIKE '[1-2][0-9][0-9][0-9]'),
   CONSTRAINT  ValidDeathYear      CHECK (DateDeceased LIKE '[1-2][0-9][0-9][0-9]')
   );
 
CREATE TABLE WORK (
   WorkID              Int                 NOT NULL IDENTITY(500,1),
   Title               Char(35)            NOT NULL,
   Copy                Char(12)            NOT NULL,
   Medium              Char(35)            NULL,
   [Description]       Varchar(1000)       NULL DEFAULT 'Unknown provenance', 
   ArtistID            Int                 NOT NULL,

   CONSTRAINT  WorkPK              PRIMARY KEY(WorkID), 
   CONSTRAINT  WorkAK1             UNIQUE(Title, Copy), 
   CONSTRAINT  ArtistFK            FOREIGN KEY(ArtistID)
               REFERENCES ARTIST(ArtistID) 
                   ON UPDATE NO ACTION 
                   ON DELETE NO ACTION,
   );

CREATE TABLE CUSTOMER (
   CustomerID          Int                 NOT NULL IDENTITY(1000,1),
   LastName            Char(25)            NOT NULL,
   FirstName           Char(25)            NOT NULL,
   Street              Char(30)            NULL,
   City                Char(35)            NULL,
   [State]             Char(2)             NULL,
   ZipPostalCode       Char(9)             NULL,
   Country             Char(50)            NULL,
   AreaCode            Char(3)             NULL,
   PhoneNumber         Char(8)             NULL,
   Email               Varchar(100)        NULL,

   CONSTRAINT  CustomerPK          PRIMARY KEY(CustomerID), 
   CONSTRAINT  EmailAK1            UNIQUE(Email)
   );
 
CREATE TABLE TRANS (
   TransactionID       Int                 NOT NULL IDENTITY(100,1),
   DateAcquired        Date                NOT NULL, 
   AcquisitionPrice    Numeric(8,2)        NOT NULL, 
   AskingPrice         Numeric(8,2)        NULL,
   DateSold            Date                NULL,
   SalesPrice          Numeric(8,2)        NULL,
   CustomerID          Int                 NULL,
   WorkID              Int                 NOT NULL,

   CONSTRAINT  TransPK             PRIMARY KEY(TransactionID), 
   CONSTRAINT  TransWorkFK         FOREIGN KEY(WorkID)
               REFERENCES WORK(WorkID)
                   ON UPDATE NO ACTION 
                   ON DELETE NO ACTION,
   CONSTRAINT  TransCustomerFK     FOREIGN KEY(CustomerID)
               REFERENCES CUSTOMER(CustomerID) 
                   ON UPDATE NO ACTION
                   ON DELETE NO ACTION,
   CONSTRAINT SalesPriceRange     CHECK ((SalesPrice > 0) AND (SalesPrice <=500000)), 
   CONSTRAINT ValidTransDate      CHECK (DateAcquired <= DateSold)
   );
 
CREATE TABLE CUSTOMER_ARTIST_INT(
   ArtistID            Int                 NOT NULL,
   CustomerID          Int                 NOT NULL,

   CONSTRAINT  CAIntPK             PRIMARY KEY(ArtistID, CustomerID), 
   CONSTRAINT  CAInt_ArtistFK      FOREIGN KEY(ArtistID)
               REFERENCES ARTIST(ArtistID) 
                   ON UPDATE NO ACTION 
                   ON DELETE CASCADE,
   CONSTRAINT  CAInt_CustomerFK FOREIGN KEY(CustomerID) 
               REFERENCES CUSTOMER(CustomerID)
                   ON UPDATE NO ACTION 
                   ON DELETE CASCADE
   );

/********************************************************************************/
/*                                                                              */
/*    Kroenke and Auer - Database Processing (13th Edition) Chapter 07/10A      */
/*                                                                              */
/*    The View Ridge Gallery (VRG) Database - Insert Data                       */
/*                                                                              */
/*    These are the Microsoft SQL Server 2008R2/2012 SQL code solutions         */
/*                                                                              */
/********************************************************************************/
/*                                                                              */
/*    This file contains the initial data for each table.                       */
/*                                                                              */
/*    NOTE:  We will have a problem entering the surrogate key values shown in  */
/*    the text.                                                                 */
/*                                                                              */
/*    The database table structure was set up using the SQL Server T-SQL        */
/*    keyword IDENTITY to create surrogate keys. This means that be default     */
/*    we cannot enter values into any  column defined with INDENTITY.           */
/*                                                                              */
/*    But that means we cannot enter the primary key values as shown.           */
/*    To work around this, we will use the T-SQL command IDENTITY INSERT.       */
/*                                                                              */
/*    When IDENTITY INSERT is set to OFF (the default), only the DBMS           */
/*    can enter data into the controled column in the table.                    */
/*                                                                              */
/*    When IDENTITY INSERT is set to ON, values can be input into the           */
/*    controled column in the table. However, IDENTITY INSERT can only be set   */
/*    to ON for only one table at a time. Futher, IDENTITY INSERT requires      */
/*    the use of a column list containing the name of the surrogate key in each */
/*    INSERT command.                                                           */
/*                                                                              */
/********************************************************************************/
/*    Be sure IDENTITY INSERT is OFF for all tables.                            */

USE ViewRidge

/********************************************************************************/
/*    INSERT data for CUSTOMER                                                  */
/*    Set IDENTITY INSERT to ON for CUSTOMER;                                   */
/*    reset it to OFF after CUSTOMER data is inserted.                          */

SET IDENTITY_INSERT dbo.CUSTOMER ON
 
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1000, 'Janes', 'Jeffrey', '123 W. Elm St', 'Renton', 'WA', '98055', 
'USA', '425', '543-2345', 'Jeffrey.Janes@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1001, 'Smith', 'David', '813 Tumbleweed Lane', 'Loveland', 'CO', '81201', 
'USA', '970', '654-9876', 'David.Smith@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1015, 'Twilight', 'Tiffany', '88 1st Avenue', 'Langley', 'WA', '98260', 
'USA', '360', '765-5566', 'Tiffany.Twilight@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1033, 'Smathers', 'Fred', '10899 88th Ave', 'Bainbridge Island', 'WA', '98110', 
'USA', '206', '876-9911', 'Fred.Smathers@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1034, 'Frederickson', 'Mary Beth', '25 South Lafayette', 'Denver', 'CO', '80201',
'USA', '303', '513-8822', 'MaryBeth.Frederickson@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1036, 'Warning', 'Selma', '205 Burnaby', 'Vancouver', 'BC', 'V6Z 1W2', 
'Canada', '604', '988-0512', 'Selma.Warning@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1037, 'Wu', 'Susan', '105 Locust Ave', 'Atlanta', 'GA', '30322', 
'USA', '404', '653-3465', 'Susan.Wu@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1040, 'Gray', 'Donald','55 Bodega Ave', 'Bodega Bay', 'CA', '94923', 
'USA', '707', '568-4839', 'Donald.Gray@somewhere.com');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber)
        VALUES (1041, 'Johnson', 'Lynda', '117 C Street', 'Washington', 'DC', '20003', 
'USA', '202', '438-5498');
INSERT INTO CUSTOMER
        (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, 
Country, AreaCode, PhoneNumber, Email)
        VALUES (1051, 'Wilkens', 'Chris', '87 Highland Drive', 'Olympia', 'WA', '98508', 
'USA', '360', '765-7766', 'Chris.Wilkens@somewhere.com');
 
 SET IDENTITY_INSERT dbo.CUSTOMER OFF 

/********************************************************************************/
/*    INSERT data for ARTIST                                                    */
/*    Set IDENTITY INSERT to ON for ARTIST;                                     */
/*    reset it to OFF after ARTIST data is inserted.                            */
 
SET IDENTITY_INSERT dbo.ARTIST ON
 
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (1, 'Miro', 'Joan', 'Spanish', 1893, 1983);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (2, 'Kandinsky', 'Wassily', 'Russian', 1866, 1944);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (3, 'Klee', 'Paul', 'German', 1879, 1940);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (4, 'Matisse', 'Henri', 'French', 1869, 1954);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (5, 'Chagall', 'Marc', 'French', 1887, 1985);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (11, 'Sargent', 'John Singer', 'United States', 1856, 1925);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (17, 'Tobey', 'Mark', 'United States', 1890, 1976);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (18, 'Horiuchi', 'Paul', 'United States', 1906, 1999);
INSERT INTO ARTIST
        (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
        VALUES (19, 'Graves', 'Morris', 'United States', 1920, 2001);
 
SET IDENTITY_INSERT dbo.ARTIST OFF

/********************************************************************************/
/*        INSERT data for CUSTOMER_ARTIST_INT                                   */
/*        AUTO_INCREMENT modificaitons are NOT needed for CUSTOMER_ARTIST_INT   */
/*        There are NO surrogate keys in CUSTOMER_ARTIST_INT.                   */
 
INSERT INTO CUSTOMER_ARTIST_INT VALUES (1, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (1, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (2, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (2, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (4, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (4, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1051);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1051);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1051);

/********************************************************************************/
/*        INSERT data for WORK                                                  */
/*        SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }   */
/*        SET IDENTITY_INSERT dbo.WORK OFF after WORK data is inserted.         */
 
SET IDENTITY_INSERT dbo.WORK ON
 
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (500, 'Memories IV', 'Unique', 'Casein rice paper collage', 
'31 x 24.8 in.', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (511, 'Surf and Bird', '142/500', 'High Quality Limited Print', 
'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (521, 'The Tilled Field', '788/1000', 'High Quality Limited Print', 
'Early Surrealist style', 1);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (522, 'La Lecon de Ski', '353/500', 'High Quality Limited Print', 
'Surrealist style', 1);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (523, 'On White II', '435/500', 'High Quality Limited Print', 
'Bauhaus style of Kandinsky', 2);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (524, 'Woman with a Hat', '596/750', 'High Quality Limited Print', 
'A very colorful Impressionist piece', 4);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (537, 'The Woven World', '17/750', 'Color lithograph', 
'Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (548, 'Night Bird', 'Unique', 'Watercolor on Paper',  
'50 x 72.5 cm. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (551, 'Der Blaue Reiter', '236/1000', 'High Quality Limited Print', 
'The Blue Rider-Early Pointilism influence', 2);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (552, 'Angelus Novus', '659/750', 'High Quality Limited Print', 
'Bauhaus style of Klee', 3);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (553, 'The Dance', '734/1000', 'High Quality Limited Print', 
'An Impressionist masterpiece', 4);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (554, 'I and the Village', '834/1000', 'High Quality Limited Print', 
'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (555, 'Claude Monet Painting', '684/1000', 'High Quality Limited Print', 
'Shows French Impressionist influence of Monet', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (561, 'Sunflower', 'Unique', 'Watercolor and ink', 
'33.3 x 16.1 cm. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (562, 'The Fiddler', '251/1000', 'High Quality Limited Print', 
'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (563, 'Spanish Dancer', '583/750', 'High Quality Limited Print', 
'American realist style - From work in Spain', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (564, 'Farmer''s Market #2', '267/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (565, 'Farmer''s Market #2', '268/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (566, 'Into Time', '323/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (570, 'Untitled Number 1', 'Unique', 'Monotype with tempera', 
'4.3 x 6.1 in. Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (571, 'Yellow Covers Blue', 'Unique', 'Oil and collage', 
'71 x 78 in. - Signed', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (578, 'Mid-Century Hibernation', '362/500', 'High Quality Limited Print', 
'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (580, 'Forms in Progress I', 'Unique', 'Color aquatint', 
'19.3 x 24.4 in. - Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (581, 'Forms in Progress II', 'Unique', 'Color aquatint', 
'19.3 x 24.4 in. - Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (585, 'The Fiddler', '252/1000', 'High Quality Limited Print', 
'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (586, 'Spanish Dancer', '588/750', 'High Quality Limited Print', 
'American Realist style - From work in Spain', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (587, 'Broadway Boggie', '433/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (588, 'Universal Field', '114/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (589, 'Color Floating in Time', '487/500', 'High Quality Limited Print', 
'Northwest School Abstract Expressionist style', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (590, 'Blue Interior', 'Unique', 'Tempera on card', 
'43.9 x 28 in.', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (593, 'Surf and Bird', 'Unique', 'Gouache', 
'26.5 x 29.75 in. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (594, 'Surf and Bird', '362/500', 'High Quality Limited Print', 
'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (595, 'Surf and Bird', '365/500', 'High Quality Limited Print', 
'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
        VALUES (596, 'Surf and Bird', '366/500', 'High Quality Limited Print', 
'Northwest School Expressionist style', 19);
 
SET IDENTITY_INSERT dbo.WORK OFF

/********************************************************************************/
/*        INSERT data for TRANS                                                 */
/*        SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }   */
/*        SET IDENTITY_INSERT dbo.WORK OFF after WORK data is inserted.         */
 
SET IDENTITY_INSERT dbo.TRANS ON
 
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (100, '2005-11-04', 30000.00, 45000.00, '2005-12-14', 42500.00, 1000, 500);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (101, '2005-11-07', 250.00, 500.00, '2005-12-19', 500.00, 1015, 511);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice,
        AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (102, '2005-11-17', 125.00, 250.00, '2006-01-18', 200.00, 1001, 521);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (103, '2005-11-17', 250.00, 500.00, '2006-12-12', 400.00, 1034, 522);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (104, '2005-11-17', 250.00, 250.00, '2006-01-18', 200.00, 1001, 523);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (105, '2005-11-17', 200.00, 500.00, '2006-12-12', 400.00, 1034, 524);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (115, '2006-03-03', 1500.00, 3000.00, '2006-06-07', 2750.00, 1033, 537);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (121, '2006-09-21', 15000.00, 30000.00, '2006-11-28', 27500.00, 1015, 548);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (125, '2006-11-21', 125.00, 250.00, '2006-12-18', 200.00, 1001, 551);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (126, '2006-11-21', 200.00, 400.00, 552);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (127, '2006-11-21', 125.00, 500.00, '2006-12-22', 400.00, 1034, 553);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (128, '2006-11-21', 125.00, 250.00, '2007-03-16', 225.00, 1036, 554);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (129, '2006-11-21', 125.00, 250.00, '2007-03-16', 225.00, 1036, 555);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (151, '2007-05-07', 10000.00, 20000.00, '2007-06-28', 17500.00, 1036, 561);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (152, '2007-05-18', 125.00, 250.00, '2007-08-15', 225.00, 1001, 562);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (153, '2007-05-18', 200.00, 400.00, '2007-08-15', 350.00, 1001, 563);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (154, '2007-05-18', 250.00, 500.00, '2007-09-28', 400.00, 1040, 564);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (155, '2007-05-18', 250.00, 500.00, 565);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (156, '2007-05-18', 250.00, 500.00, '2007-09-27', 400.00, 1040, 566);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (161, '2007-06-28', 7500.00, 15000.00, '2007-09-29', 13750.00, 1033, 570);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (171, '2007-08-23', 35000.00, 60000.00, '2007-09-29', 55000.00, 1000, 571);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (175, '2007-08-23', 40000.00, 75000.00, '2007-12-18', 72500.00, 1036, 500);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (181, '2007-10-11', 250.00, 500.00, 578);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (201, '2008-02-28', 2000.00, 3500.00, '2008-04-26', 3250.00,        1040, 580);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (202, '2008-02-28', 2000.00, 3500.00, '2008-04-26', 3250.00, 1040, 581);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (225, '2008-06-08', 125.00, 250.00, '2008-09-27', 225.00, 1051, 585);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (226, '2008-06-08', 200.00, 400.00, 586);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (227, '2008-06-08', 250.00, 500.00, '2008-09-27', 475.00, 1051, 587);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (228, '2008-06-08', 250.00, 500.00, 588);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (229, '2008-06-08', 250.00, 500.00, 589);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
        VALUES (241, '2008-08-29', 2500.00, 5000.00, '2008-09-27', 4750.00, 1015, 590);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (251, '2008-10-25', 25000.00, 50000.00, 593);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (252, '2008-10-27', 250.00, 500.00, 594);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (253, '2008-10-27', 250.00, 500.00, 595);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
        VALUES (254, '2008-10-27', 250.00, 500.00, 596);
        
SET IDENTITY_INSERT dbo.TRANS OFF

/****************************************************************************/  
/* CREATE PROCEDURE InsertCustomerAndInterests                              */  

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

CREATE PROCEDURE InsertCustomerAndInterests
        @NewLastName        Char(25),
        @NewFirstName       Char(25),
        @NewAreaCode        Char(3),
        @NewPhoneNumber     CHar(8),
        @NewEmail           VarChar(100),
        @Nationality        Char(30)
AS
    DECLARE     @RowCount   AS Int,
                @ArtistID   AS Int,
                @CustomerID AS Int

-- Check to see if Customer already exist in database
 
    SELECT  @RowCount = COUNT(*)
    FROM    dbo.CUSTOMER
    WHERE   LastName = @NewLastName
        AND FirstName = @NewFirstName
        AND AreaCode = @NewAreaCode
        AND PhoneNumber = @NewPhoneNumber
        AND Email = @NewEmail
 
-- IF @Rowcount > 0 THEN Customer already exists.
    IF (@RowCount > 0)
        BEGIN
        PRINT '******************************************************'
        PRINT ''
        PRINT '   The Customer is already in the database.  '
        PRINT ''
        PRINT '   Customer Last Name         =  '+@NewLastName
        PRINT '   Customer First Name        =  '+@NewFirstName
        PRINT ''
        PRINT '******************************************************'
        RETURN
     END

-- IF @RowCount = 0 THEN Customer does not exist in database. 
ELSE
    BEGIN
    -- Insert new Customer data. 
        INSERT INTO dbo.CUSTOMER
            (LastName, FirstName, AreaCode, PhoneNumber, Email)
            VALUES(@NewLastName, @NewFirstName, @NewAreaCode, @NewPhoneNumber, @NewEmail)
        -- Get new CuatomerlD aurrogate key value.
        SELECT @CustomerID = CustomerID
        FROM dbo.CUSTOMER
        WHERE LastName = @NewLastName
            AND FirstName = @NewFirstName
            AND AreaCode = @NewAreaCode
            AND PhoneNumber = @NewPhoneNumber
            AND Email = @NewEmail
        PRINT '****************************************************'
        PRINT ''
        PRINT '   The new Customer is now in the database. '
        PRINT ''
        PRINT '   Customer Last Name         =  '+@NewLastName
        PRINT '   Customer First Name        =  '+@NewFirstName
        PRINT ''
        PRINT '****************************************************'
 
-- Create intersection record for each appropriate Artist.
DECLARE ArtistGursor CURSOR FOR
        SELECT ArtistID
        FROM dbo.ARTIST
        WHERE Nationality = @Nationality
-- Process each appropriate Artist 
OPEN    ArtistCursor
    FETCH NEXT FROM ArtistCursor INTO @ArtistID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO dbo.CUSTOMER_ARTIST_INT
                (ArtistID, CustomerID)
                VALUES (@ArtistID, @CustomerID)
                PRINT '*************************************************'
                PRINT ''
                PRINT '   New CUSTOMER_ARTIST_INT row added. '
                PRINT ''
                PRINT '   ArtistID    =  '+CONVERT(Char(6), @ArtistID)
                PRINT '   CustomerlD  =  '+CONVERT(Char(6), @CustomerID)
                PRINT ''
                PRINT '*************************************************'
            FETCH NEXT FROM ArtistCursor INTO @ArtistID
        END
    CLOSE ArtistCursor
    DEALLOCATE ArtistCursor
END

/****************************************************************************/ 
/* CREATE PROCEDURE InsertCustomerWithTransaction                           */ 
/****************************************************************************/

CREATE PROCEDURE InsertCustomerWithTransaction
             @NewCustomerLastName       Char(25),
             @NewCustomerFirstName      Char(25),
             @NewCustomerAreaCode       Char(3),
             @NewCustomerPhoneNumber    Char(8),
             @NewCustomerEmail          Varchar(100),
             @ArtistLastName            Char(25),
             @WorkTitle                 Char(35),
             @WorkCopy                  Char(12),
             @TransSalesPrice           Numeric(8,2)
AS
   DECLARE   @RowCount        AS Int,
             @ArtistID        AS Int,
             @CustomerID      AS Int,
             @WorkID          AS Int,
             @TransactionID   AS Int

-- Check to see if Customer already exists in database

   SELECT  @RowCount = COUNT(*)
   FROM    dbo.CUSTOMER
   WHERE   LastName = @newCustomerLastName
       AND FirstName = @newCustomerFirstName
       AND AreaCode = @newCustomerAreaCode
       AND PhoneNumber = @newCustomerPhoneNumber
       AND Email = @newCustomerEmail

   -- IF @RowCount > 0 THEN Customer already exists.
   IF (@RowCount > 0)
      BEGIN
          PRINT '******************************************************'
          PRINT ''
          PRINT '   The Customer is already in the database. '
          PRINT ''
          PRINT '   Customer Last Name   =  '+@NewCustomerLastName
          PRINT '   Customer First Name  =  '+@NewCustomerFirstName
          PRINT '******************************************************'
          RETURN
      END
   -- IF @RowCount = 0 THEN Customer does not exist in database.
   ELSE
      -- Start transaction - Rollback everything if unable to complete it.
      BEGIN TRANSACTION
         -- Insert new Customer data.
         INSERT INTO dbo.CUSTOMER
            (LastName , FirstName, AreaCode, PhoneNumber, Email)
            VALUES (@NewCustomerLastName, @NewCustomerFirstName, @NewCustomerAreaCode, @NewCustomerPhoneNumber, @NewCustomerEmail)
         -- Get new CustomerID surrogate key value.
         SELECT    @CustomerID = CustomerID
         FROM      dbo.CUSTOMER
         WHERE     LastName = @NewCustomerLastName
            AND    FirstName = @NewCustomerFirstName
            AND    AreaCode = @NewCustomerAreaCode
            AND    PhoneNumber = @NewCustomerPhoneNumber
            AND    Email = @NewCustomerEmail

   -- Get ArtistID surrogate key value, check for validity.
   SELECT    @ArtistID = ArtistID
   FROM      dbo.ARTIST
   WHERE     LastName = @ArtistLastName

   IF @ArtistID IS NULL
      BEGIN
         PRINT '******************************************************'
         PRINT ''
         PRINT '   Invalid ArtistID '
         PRINT ''
         PRINT '******************************************************'
         ROLLBACK TRANSACTION
         RETURN
      END

   -- Get WorkID surrogate key value, check for validity.
   SELECT     @WorkID = WorkID
   FROM      dbo.WORK
   WHERE     ArtistID = @ArtistID
      AND     Title = @WorkTitle
      AND     Copy = @WorkCopy

   IF @WorkID IS NULL
      BEGIN
         PRINT '******************************************************'
         PRINT ''
         PRINT '   Invalid WorkID '
         PRINT ''
         PRINT '******************************************************'
         ROLLBACK TRANSACTION
         RETURN
      END

   -- Get TransID surrogate key value, check for validity .
   SELECT     @TransactionID = TransactionID
   FROM      dbo.TRANS
   WHERE     WorkID = @WorkID
      AND     SalesPrice = NULL
 
   IF @TransactionID IS NULL
      BEGIN
         PRINT '******************************************************'
         PRINT ''
         PRINT '   Invalid TransactionID '
         PRINT ''
         PRINT '******************************************************'
         ROLLBACK TRANSACTION
         RETURN
      END
 
      -- All surrogate key values of OK, complete the transaction
      BEGIN
         -- Update TRANS row
         UPDATE dbo.TRANS
         SET    DateSold = GETDATE(),
                SalesPrice = @TransSalesPrice,
                CustomerID = @CustomerID
         WHERE TransactionID = @TransactionID
         -- Create CUSTOMER ARTIST INT row
         INSERT INTO dbo.CUSTOMER_ARTIST_INT (CustomerID,ArtistID)
                VALUES (@CustomerID , @ArtistID)
      END
   -- Commit the Transaction
   COMMIT TRANSACTION
 
      -- The transaction is completed. Print output
      BEGIN
         -- Print Customer results.
         PRINT '******************************************************'
         PRINT ''
         PRINT '   The new Customer is now in the database. '
         PRINT ''
         PRINT '   Customer Last Name  =  '+@NewCustomerLastName
         PRINT '   Customer First Name =  '+@NewCustomerFirstName
         PRINT '******************************************************'
         -- Print Transaction result
         PRINT ''
         PRINT '   Transaction complete . '
         PRINT ''
         PRINT '   TransactionID       =  ' +CONVERT (Char (6), @TransactionID)
         PRINT '   ArtistID            =  ' +CONVERT (Char (6), @ArtistID)
         PRINT '   WorkID              =  ' +CONVERT (Char (6), @WorkID)
         PRINT '   Sales Price         =  ' +CONVERT (Char (12), @TransSalesPrice)
         PRINT ''
         PRINT '******************************************************'
         -- Print CUSTOMER_ARTIST_INT update
         PRINT ''
         PRINT '   New CUSTOMER ARTIST INT row added. '
         PRINT ''
         PRINT '   ArtistID            =  ' +CONVERT (Char (6), @ArtistID)
         PRINT '   CustomerID          =  ' +CONVERT (Char (6), @CustomerID)
         PRINT ''
      END
--  ***** End of stored procedure InsertCustomerWithTransaction *****

/****************************************************************************/ 
/* CREATE TRIGGER TRANS_AfterInsertSetAskingPrice                           */ 
/****************************************************************************/

CREATE TRIGGER TRANS_AfterInsertSetAskingPrice
    ON TRANS
    AFTER INSERT
AS
BEGIN
 
    DECLARE    @PriorRowCount       AS Int,
               @WorkID              AS Int,
               @TransactionID       AS Int,
               @AcquisitionPrice    AS Numeric(8,2),
               @NewAskingPrice      AS Numeric(8,2),
               @SumNetProfit        AS Numeric(8,2),
               @AvgNetProfit        AS Numeric(8,2)
 
    SELECT  @TransactionID = TransactionID,
            @AcquisitionPrice = AcquisitionPrice,
            @WorkID = WorkID
    FROM    inserted
 
-- First find if work has been here before.
 
    SELECT     *
    FROM       dbo.TRANS AS T
    WHERE       T.WorkID = @WorkID
 
-- Since this is an AFTER trigger, @@Rowcount includes the new row.

SET @PriorRowCount = (@@ROWCOUNT -1)
 
IF (@PriorRowCount = 0)
    -- This is first time work has been in the gallery.
    -- Set @NewAskingPrice to twice the acquitsition cost.
    SET @NewAskingPrice = (2 * @AcquisitionPrice)
 
ELSE
    -- The work has been here before
    -- We have to determine the value of @NewAskingPrice
    BEGIN
        SELECT      @SumNetProfit = SUM(NetProfit)
        FROM        dbo.ArtistWorkNetView AWNV
        WHERE       AWNV.WorkID = @WorkID
        GROUP BY    AWNV.WorkID;
 
        SET @AvgNetProfit = (@SumNetProfit / @PriorRowCount);
 
            -- Now choose larger value for the new AskingPRice
 
        IF ((@AcquisitionPrice + @AvgNetProfit)
                > (2 * @AcquisitionPrice))
            SET @NewAskingPrice = (@AcquisitionPrice + @AvgNetProfit)
        ELSE
        SET @NewAskingPrice = (2 * @AcquisitionPrice)
    END
--  Update TRANS with the value of AskingPrice
 
UPDATE  dbo.TRANS
SET     AskingPrice = @NewAskingPrice
WHERE   TransactionID = @TransactionID
 
-- The INSERT is completed. Print output
BEGIN
-- The INSERT is completed. Print output BEGIN
        PRINT '*****************************************************'
        PRINT ''
        PRINT '   INSERT complete. '
        PRINT ''
        PRINT '   TransactionlD     =   '+CONVERT(Char(6), @TransactionID)
        PRINT '   WorkID            =   '+CONVERT(Char(6), @WorkID)
        PRINT '   Acquisition Price =   '+CONVERT(Char(12), @AcquisitionPrice)
        PRINT '   Asking Price =    =   '+CONVERT(Char(12), @NewAskingPrice)
        PRINT ''
        PRINT '******************************************************'
    END
END

/****************************************************************************/ 
/* CREATE VIEW ArtistWorkNetView                                            */ 
/****************************************************************************/

/* *** SQL-CREATE-VIEW-CH07-06 *** */ 
CREATE VIEW ArtistWorkNetView AS
    SELECT  LastName AS ArtistLastName, FirstName AS ArtistFirstName, 
            W.WorkID, Title, Copy, DateSold, AcquisitionPrice, SalesPrice,
            (SalesPrice - AcquisitionPrice) AS NetProfit 
    FROM    TRANS AS T JOIN 
            WORK AS W ON  T.WorkID = W.WorkID JOIN 
            ARTIST AS A ON W.ArtistID = A.ArtistID;

/****************************************************************************/ 
/* CREATE VIEW ArtistTotalNetView                                           */ 
/****************************************************************************/

/* *** SQL-CREATE-VIEW-CH07-07 *** */
CREATE VIEW ArtistWorkTotalNetView AS
    SELECT      ArtistLastName, ArtistFirstName, WorkID, Title, Copy, SUM(NetProfit) AS TotalNetProfit
    FROM        ArtistWorkNetView
    GROUP BY    ArtistLastName, ArtistFirstName, WorkID, Title, Copy;

/****************************************************************************/ 
/* CREATE VIEW CustomerPhoneView                                            */ 
/****************************************************************************/

/* *** SQL-CREATE-VIEW-CH07-04 *** */
CREATE VIEW CustomerPhoneView AS
    SELECT      LastName AS CustomerLastName, FirstName AS CustomerFirstName,
                ('(' + AreaCode + ')' + PhoneNumber) AS CustomerPhone
    FROM CUSTOMER;

MySQL Schema

/********************************************************************************/
/*                                                                              */
/*     Kroenke and Auer - Database Processing (13th Edition) Chapter 10C        */
/*                                                                              */
/*     The View Ridge Gallery (VRG) - Create Tables                             */
/*                                                                              */
/*     These are the MySQL 5.6 SQL code solutions                               */
/*                                                                              */
/********************************************************************************/


CREATE TABLE ARTIST (
    ArtistID            Int                 NOT NULL AUTO_INCREMENT,
    LastName            Char(25)            NOT NULL,
    FirstName           Char(25)            NOT NULL,
    Nationality         Char(30)            NULL,
    DateOfBirth         Numeric(4)          NULL,
    DateDeceased        Numeric(4)          NULL,

    CONSTRAINT  ArtistPK            PRIMARY KEY(ArtistID),
    CONSTRAINT  ArtistAK1           UNIQUE(LastName, FirstName),
    CONSTRAINT  NationalityValues   CHECK (Nationality IN ('Canadian', 'English', 'French', 'German', 'Mexican', 'Russian', 'Spanish', 'United States')),
    CONSTRAINT  BirthValuesCheck    CHECK (DateOfBirth < DateDeceased),
    CONSTRAINT  ValidBirthYear      CHECK (DateOfBirth LIKE '[1-2][0-9][0-9][0-9]'),
    CONSTRAINT  ValidDeathYear      CHECK (DateDeceased LIKE '[1-2][0-9][0-9][0-9]')
    );

CREATE TABLE WORK (
    WorkID              Int                 NOT NULL AUTO_INCREMENT,
    Title               Char(35)            NOT NULL,
    Copy                Char(12)            NOT NULL,
    Medium              Char(35)            NULL,
    Description         Varchar(1000)       NULL DEFAULT 'Unknown provenance',
    ArtistID            Int                 NOT NULL,

    CONSTRAINT  WorkPK              PRIMARY KEY(WorkID),
    CONSTRAINT  WorkAK1             UNIQUE(Title, Copy),
    CONSTRAINT  ArtistFK            FOREIGN KEY(ArtistID)
                REFERENCES ARTIST(ArtistID)
                    ON UPDATE NO ACTION
                    ON DELETE NO ACTION
    );

CREATE TABLE CUSTOMER (
    CustomerID          Int                 NOT NULL AUTO_INCREMENT,
    LastName            Char(25)            NOT NULL,
    FirstName           Char(25)            NOT NULL,
    Street              Char(30)            NULL,
    City                Char(35)            NULL,
    State               Char(2)             NULL,
    ZipPostalCode       Char(9)             NULL,
    Country             Char(50)            NULL,
    AreaCode            Char(3)             NULL,
    PhoneNumber         Char(8)             NULL,
    Email                Varchar(100)        NULL,

    CONSTRAINT  CustomerPK          PRIMARY KEY(CustomerID),
    CONSTRAINT  EmailAK1            UNIQUE(Email)
    );

CREATE TABLE TRANS (
    TransactionID       Int                 NOT NULL AUTO_INCREMENT,
    DateAcquired        Datetime            NOT NULL,
    AcquisitionPrice    Numeric(8,2)        NOT NULL,
    DateSold            Datetime            NULL,
    AskingPrice         Numeric(8,2)        NULL,
    SalesPrice          Numeric(8,2)        NULL,
    CustomerID          Int                 NULL,
    WorkID              Int                 NOT NULL,

    CONSTRAINT  TransPK             PRIMARY KEY(TransactionID),
    CONSTRAINT  TransWorkFK         FOREIGN KEY(WorkID)
                REFERENCES WORK(WorkID)
                    ON UPDATE NO ACTION
                    ON DELETE NO ACTION,
    CONSTRAINT  TransCustomerFK     FOREIGN KEY(CustomerID)
                REFERENCES CUSTOMER(CustomerID)
                    ON UPDATE NO ACTION
                    ON DELETE NO ACTION,
    CONSTRAINT  SalesPriceRange     CHECK ((SalesPrice > 0) AND (SalesPrice <= 500000)),
    CONSTRAINT  ValidTransDate      CHECK (DateAcquired <= DateSold)
    );

CREATE TABLE CUSTOMER_ARTIST_INT(
    ArtistID            Int                 NOT NULL,
    CustomerID          Int                 NOT NULL,

    CONSTRAINT  CAIntPK             PRIMARY KEY(ArtistID, CustomerID),
    CONSTRAINT  CAInt_ArtistFK      FOREIGN KEY(ArtistID)
                REFERENCES ARTIST(ArtistID)
                    ON UPDATE NO ACTION
                    ON DELETE CASCADE,
    CONSTRAINT  CAInt_CustomerFK    FOREIGN KEY(CustomerID)
                REFERENCES CUSTOMER(CustomerID)
                    ON UPDATE NO ACTION
                    ON DELETE CASCADE
    );

CREATE TABLE PRICELIST (
    TransactionID       Int                 NOT NULL,
    AskingPrice         Numeric(8,2)        NOT NULL,

    CONSTRAINT  PriceListPK         PRIMARY KEY(TransactionID),
    CONSTRAINT  TransPriceListFK    FOREIGN KEY(TransactionID)
                REFERENCES TRANS(TransactionID)
                    ON UPDATE NO ACTION
                    ON DELETE NO ACTION
);


/****************************************************************************/
/*                                                                          */
/*    Kroenke and Auer - Database Processing (11th Edition) Chapter 10C     */
/*                                                                          */
/*    The View Ridge Gallery (VRG) Database - Insert Data                   */
/*                                                                          */
/*    These are the MySQL 5.6 SQL code solutions                            */
/*                                                                          */
/****************************************************************************/
/*                                                                          */
/*    This file contains the initial data for each table.                   */
/*    This file also sets the AUTO_INCREMENT property for each table, but   */
/*    only after tge con-consecutive surrogate key values are entered.      */
/*                                                                          */
/****************************************************************************/
 
 
/****************************************************************************/
/*    INSERT data for CUSTOMER                                              */
 
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1000, 'Janes', 'Jeffrey', '123 W. Elm St', 'Renton', 'WA', '98055', 'USA', '425', '543-2345', 'Jeffrey.Janes@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1001, 'Smith', 'David', '813 Tumbleweed Lane', 'Loveland', 'CO', '81201', 'USA', '970', '654-9876', 'David.Smith@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1015, 'Twilight', 'Tiffany', '88 1st Avenue', 'Langley', 'WA', '98260', 'USA', '360', '765-5566', 'Tiffany.Twilight@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1033, 'Smathers', 'Fred', '10899 88th Ave', 'Bainbridge Island', 'WA', '98110', 'USA', '206', '876-9911', 'Fred.Smathers@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1034, 'Frederickson', 'Mary Beth', '25 South Lafayette', 'Denver', 'CO', '80201', 'USA', '303', '513-8822', 'MaryBeth.Frederickson@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1036, 'Warning', 'Selma', '205 Burnaby', 'Vancouver', 'BC', 'V6Z 1W2', 'Canada', '604', '988-0512', 'Selma.Warning@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1037, 'Wu', 'Susan', '105 Locust Ave', 'Atlanta', 'GA', '30322', 'USA', '404', '653-3465', 'Susan.Wu@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1040, 'Gray', 'Donald','55 Bodega Ave', 'Bodega Bay', 'CA', '94923', 'USA', '707', '568-4839', 'Donald.Gray@somewhere.com');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber)
    VALUES (1041, 'Johnson', 'Lynda', '117 C Street', 'Washington', 'DC', '20003', 'USA', '202', '438-5498');
INSERT INTO CUSTOMER
    (CustomerID, LastName, FirstName, Street, City, State, ZipPostalCode, Country, AreaCode, PhoneNumber, Email)
    VALUES (1051, 'Wilkens', 'Chris', '87 Highland Drive', 'Olympia', 'WA', '98508', 'USA', '360', '765-7766', 'Chris.Wilkens@somewhere.com');

/*    Set AUTO_INCREMENT for the CUSTOMER table                             */

ALTER TABLE TRANS
    DROP FOREIGN KEY TransCustomerFK;

ALTER TABLE CUSTOMER_ARTIST_INT
    DROP FOREIGN KEY CAInt_CustomerFK;

ALTER TABLE CUSTOMER
    MODIFY COLUMN CustomerID INTEGER NOT NULL AUTO_INCREMENT;

ALTER TABLE CUSTOMER AUTO_INCREMENT = 1052;

ALTER TABLE TRANS
    ADD CONSTRAINT    TramsCustomerFK        FOREIGN KEY(CustomerID)
                    REFERENCES CUSTOMER(CustomerID)
                        ON UPDATE NO ACTION
                        ON DELETE NO ACTION;

ALTER TABLE CUSTOMER_ARTIST_INT
    ADD CONSTRAINT    CAINT_CustomerFK    FOREIGN KEY(CustomerID)
                    REFERENCES CUSTOMER(CustomerID)
                        ON UPDATE NO ACTION
                        ON DELETE CASCADE;
 
/****************************************************************************/
/*    INSERT data for ARTIST                                                */

INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (1, 'Miro', 'Joan', 'Spanish', 1893, 1983);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (2, 'Kandinsky', 'Wassily', 'Russian', 1866, 1944);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (3, 'Klee', 'Paul', 'German', 1879, 1940);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (4, 'Matisse', 'Henri', 'French', 1869, 1954);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (5, 'Chagall', 'Marc', 'French', 1887, 1985);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (11, 'Sargent', 'John Singer', 'United States', 1856, 1925);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (17, 'Tobey', 'Mark', 'United States', 1890, 1976);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (18, 'Horiuchi', 'Paul', 'United States', 1906, 1999);
INSERT INTO ARTIST
    (ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
    VALUES (19, 'Graves', 'Morris', 'United States', 1920, 2001);
 
/*    Set AUTO_INCREMENT for the ARTIST table                             */

ALTER TABLE WORK
    DROP FOREIGN KEY ArtistFK;

ALTER TABLE CUSTOMER_ARTIST_INT
    DROP FOREIGN KEY CAInt_ArtistFK;

ALTER TABLE ARTIST
    MODIFY COLUMN ArtistID INTEGER NOT NULL AUTO_INCREMENT;

ALTER TABLE ARTIST AUTO_INCREMENT = 20;

ALTER TABLE WORK
    ADD CONSTRAINT    ArtistFK            FOREIGN KEY(ArtistID)
                    REFERENCES ARTIST(ArtistID)
                        ON UPDATE NO ACTION
                        ON DELETE NO ACTION;

ALTER TABLE CUSTOMER_ARTIST_INT
    ADD CONSTRAINT    CAINT_ArtistFK        FOREIGN KEY(ArtistID)
                    REFERENCES ARTIST(ArtistID)
                        ON UPDATE NO ACTION
                        ON DELETE CASCADE;

/****************************************************************************/
/*    INSERT data for CUSTOMER_ARTIST_INT                                   */
 
INSERT INTO CUSTOMER_ARTIST_INT VALUES (1, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (1, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (2, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (2, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (4, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (4, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1034);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (5, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1001);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (11, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (17, 1051);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (18, 1051);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1000);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1015);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1033);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1036);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1040);
INSERT INTO CUSTOMER_ARTIST_INT VALUES (19, 1051);
  
/****************************************************************************/
/*    INSERT data for WORK                                                  */
 
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (500, 'Memories IV', 'Unique', 'Casein rice paper collage', '31 x 24.8 in.', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (511, 'Surf and Bird', '142/500', 'High Quality Limited Print', 'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (521, 'The Tilled Field', '788/1000', 'High Quality Limited Print', 'Early Surrealist style', 1);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (522, 'La Lecon de Ski', '353/500', 'High Quality Limited Print', 'Surrealist style', 1);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (523, 'On White II', '435/500', 'High Quality Limited Print', 'Bauhaus style of Kandinsky', 2);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (524, 'Woman with a Hat', '596/750', 'High Quality Limited Print', 'A very colorful Impressionist piece', 4);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (537, 'The Woven World', '17/750', 'Color lithograph', 'Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (548, 'Night Bird', 'Unique', 'Watercolor on Paper', '50 x 72.5 cm. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (551, 'Der Blaue Reiter', '236/1000', 'High Quality Limited Print', 'The Blue Rider-Early Pointilism influence', 2);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (552, 'Angelus Novus', '659/750', 'High Quality Limited Print', 'Bauhaus style of Klee', 3);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (553, 'The Dance', '734/1000', 'High Quality Limited Print', 'An Impressionist masterpiece', 4);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (554, 'I and the Village', '834/1000', 'High Quality Limited Print', 'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (555, 'Claude Monet Painting', '684/1000', 'High Quality Limited Print', 'Shows French Impressionist influence of Monet', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (561, 'Sunflower', 'Unique', 'Watercolor and ink', '33.3 x 16.1 cm. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (562, 'The Fiddler', '251/1000', 'High Quality Limited Print', 'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (563, 'Spanish Dancer', '583/750', 'High Quality Limited Print', 'American realist style - From work in Spain', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (564, 'Farmer''s Market #2',    '267/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (565, 'Farmer''s Market #2',    '268/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (566, 'Into Time', '323/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (570, 'Untitled Number 1', 'Unique', 'Monotype with tempera', '4.3 x 6.1 in. Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (571, 'Yellow Covers Blue', 'Unique', 'Oil and collage', '71 x 78 in. - Signed', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (578, 'Mid-Century Hibernation', '362/500', 'High Quality Limited Print', 'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (580, 'Forms in Progress I', 'Unique', 'Color aquatint', '19.3 x 24.4 in. - Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (581, 'Forms in Progress II', 'Unique', 'Color aquatint', '19.3 x 24.4 in. - Signed', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (585, 'The Fiddler', '252/1000', 'High Quality Limited Print', 'Shows Belarusian folk-life themes and symbology', 5);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (586, 'Spanish Dancer', '588/750', 'High Quality Limited Print', 'American Realist style - From work in Spain', 11);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (587, 'Broadway Boggie', '433/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (588, 'Universal Field', '114/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (589, 'Color Floating in Time', '487/500', 'High Quality Limited Print', 'Northwest School Abstract Expressionist style', 18);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (590, 'Blue Interior', 'Unique', 'Tempera on card', '43.9 x 28 in.', 17);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (593, 'Surf and Bird', 'Unique', 'Gouache', '26.5 x 29.75 in. - Signed', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (594, 'Surf and Bird', '362/500', 'High Quality Limited Print', 'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (595, 'Surf and Bird', '365/500', 'High Quality Limited Print', 'Northwest School Expressionist style', 19);
INSERT INTO WORK (WorkID, Title, Copy, Medium, Description, ArtistID)
    VALUES (596, 'Surf and Bird', '366/500', 'High Quality Limited Print', 'Northwest School Expressionist style', 19);
 
/*    Set AUTO_INCREMENT for the WORK table                                 */

ALTER TABLE TRANS
    DROP FOREIGN KEY TransWorkFK;

ALTER TABLE WORK
    MODIFY COLUMN WorkID INTEGER NOT NULL AUTO_INCREMENT;

ALTER TABLE WORK AUTO_INCREMENT = 597;

ALTER TABLE TRANS
    ADD CONSTRAINT    TransWorkFK            FOREIGN KEY(WorkID)
                    REFERENCES WORK(WorkID)
                        ON UPDATE NO ACTION
                        ON DELETE NO ACTION;
 
/****************************************************************************/
/*    INSERT data for TRANS                                                 */
 
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (100, '2005-11-04', 30000.00, 45000.00, '2005-12-14', 42500.00, 1000, 500);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (101, '2005-11-07', 250.00, 500.00, '2005-12-19', 500.00, 1015, 511);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (102, '2005-11-17', 125.00, 250.00, '2006-01-18', 200.00, 1001, 521);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (103, '2005-11-17', 250.00, 500.00, '2006-12-12', 400.00, 1034, 522);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (104, '2005-11-17', 250.00, 250.00, '2006-01-18', 200.00, 1001, 523);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (105, '2005-11-17', 200.00, 500.00, '2006-12-12', 400.00, 1034, 524);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (115, '2006-03-03', 1500.00, 3000.00, '2006-06-07', 2750.00, 1033, 537);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (121, '2006-09-21', 15000.00, 30000.00, '2006-11-28', 27500.00,    1015, 548);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (125, '2006-11-21', 125.00, 250.00, '2006-12-18', 200.00, 1001, 551);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (126, '2006-11-21', 200.00, 400.00, 552);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (127, '2006-11-21', 125.00, 500.00, '2006-12-22', 400.00, 1034, 553);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (128, '2006-11-21', 125.00, 250.00, '2007-03-16', 225.00, 1036, 554);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (129, '2006-11-21', 125.00, 250.00, '2007-03-16', 225.00, 1036, 555);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (151, '2007-05-07', 10000.00, 20000.00, '2007-06-28', 17500.00, 1036, 561);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (152, '2007-05-18', 125.00, 250.00, '2007-08-15', 225.00, 1001, 562);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (153, '2007-05-18', 200.00, 400.00, '2007-08-15', 350.00, 1001, 563);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (154, '2007-05-18', 250.00, 500.00, '2007-09-28', 400.00, 1040, 564);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (155, '2007-05-18', 250.00, 500.00, 565);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (156, '2007-05-18', 250.00, 500.00, '2007-09-27', 400.00, 1040, 566);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (161, '2007-06-28', 7500.00, 15000.00, '2007-09-29', 13750.00, 1033, 570);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (171, '2007-08-23', 35000.00, 60000.00, '2007-09-29', 55000.00, 1000, 571);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (175, '2007-08-23', 40000.00, 75000.00, '2007-12-18', 72500.00, 1036, 500);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (181, '2007-10-11', 250.00, 500.00, 578);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (201, '2008-02-28', 2000.00, 3500.00, '2008-04-26', 3250.00,    1040, 580);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (202, '2008-02-28', 2000.00, 3500.00, '2008-04-26', 3250.00, 1040, 581);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (225, '2008-06-08', 125.00, 250.00, '2008-09-27', 225.00, 1051, 585);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (226, '2008-06-08', 200.00, 400.00, 586);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (227, '2008-06-08', 250.00, 500.00, '2008-09-27', 475.00, 1051, 587);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (228, '2008-06-08', 250.00, 500.00, 588);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (229, '2008-06-08', 250.00, 500.00, 589);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, DateSold, SalesPrice, CustomerID, WorkID)
    VALUES (241, '2008-08-29', 2500.00, 5000.00, '2008-09-27', 4750.00,    1015, 590);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (251, '2008-10-25', 25000.00, 50000.00, 593);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (252, '2008-10-27', 250.00, 500.00, 594);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (253, '2008-10-27', 250.00, 500.00, 595);
INSERT INTO TRANS (TransactionID, DateAcquired, AcquisitionPrice, AskingPrice, WorkID)
    VALUES (254, '2008-10-27', 250.00, 500.00, 596);
 
/*    Set AUTO_INCREMENT for the WORK table                                 */

ALTER TABLE TRANS
    MODIFY COLUMN TransactionID INTEGER NOT NULL AUTO_INCREMENT;

ALTER TABLE WORK AUTO_INCREMENT = 255;

/****************************************************************************/
/*    INSERT data for PRICELIST                                             */

INSERT INTO PRICELIST (TransactionID, AskingPrice)
SELECT TransactionID, AskingPrice
FROM TRANS;
 
/****************************************************************************/
/* CREATE PROCEDURE InsertCustomerAndInterests                              */
/****************************************************************************/

DELIMITER //

CREATE PROCEDURE InsertCustomerAndInterests ( 
    IN newLastName Char(25),
    IN newFirstName Char(25),
    IN newAreaCode Char(3),
    IN newPhoneNumber Char(8),
    IN newEmail Varchar(100),
    IN newNationality Char(30))

BEGIN

DECLARE varRowCount Int;
DECLARE varArtistID Int;
DECLARE varCustomerID Int;
DECLARE done Int DEFAULT 0;

DECLARE ArtistCursor CURSOR FOR
    SELECT  ArtistID
    FROM    ARTIST
    WHERE   Nationality = newNationality;

DECLARE continue HANDLER FOR NOT FOUND SET done = 1;

# Check to see if Customer already exists in database
SELECT  COUNT(*)
FROM    CUSTOMER
WHERE   LastName = newLastName
    AND FirstName = newFirstName
    AND AreaCode = newAreaCode
    AND PhoneNumber = newPhoneNumber
    AND Email = newEmail
INTO varRowCount;

# IF varRowCount > 0 THEN Customer already exists.
IF (varRowCount > 0)
    THEN 
        ROLLBACK;
        SELECT 'Customer already exists';
    ELSE 
    # Customer does not exist, if we get here
    INSERT INTO CUSTOMER (LastName, FirstName, AreaCode, PhoneNumber, Email)
    VALUES(newLastName, newFirstName, newAreaCode, newPhoneNumber, newEmail);

    # GET the CustomerID surrogate key value.
    SET varCustomerID = LAST_INSERT_ID();

    # Create intersection record for each appropriate Artist.
    OPEN ArtistCursor;

    REPEAT
        FETCH ArtistCursor INTO varArtistID;
        IF NOT done THEN
            INSERT INTO CUSTOMER_ARTIST_INT (ArtistID, CustomerID)
            VALUES(varArtistID, varCustomerID);
        END IF;
    UNTIL done END REPEAT;

    CLOSE ArtistCursor;

    SELECT 'New customer and artist interest data added to database.' 
        AS InsertCustomerAndInterestsResults;
END IF;
END
//
DELIMITER ;

/****************************************************************************/
/* CREATE PROCEDURE InsertCustomerWithTransaction                           */ 
/****************************************************************************/


DELIMITER //
CREATE PROCEDURE InsertCustomerWithTransaction (
    IN newCustomerLastName Char(25),
    IN newCustomerFirstName Char(25),
    IN newCustomerAreaCode Char(3),
    IN newCustomerPhoneNumber Char(8),
    IN newCustomerEmail Varchar(100),
    IN transArtistLastName Char(25),
    IN transWorkTitle Char(35),
    IN transWorkCopy Char(12),
    IN transTransSalesPrice Numeric(8,2))
BEGIN

DECLARE varRowCount Int;
DECLARE varArtistID Int;
DECLARE varCustomerID Int;
DECLARE varWorkID Int;
DECLARE varTransactionID Int;

# Check to see if Customer already exists in database
SELECT  COUNT(*)
FROM    CUSTOMER
WHERE   LastName = newCustomerLastName
    AND FirstName = newCustomerFirstName
    AND AreaCode = newCustomerAreaCode
    AND PhoneNumber = newCustomerPhoneNumber
    AND Email = newCustomerEmail
INTO varRowCount;

# IF varRowCount > 0 THEN Customer already exists.
IF (varRowCount > 0)
    THEN
        ROLLBACK;
        SELECT 'Customer already exists';
ELSE 

    # Customer does not exist, if we get here
    # Start transaction - Rollback everything if unable to complete
    START TRANSACTION;
    # Insert new Customer data
    INSERT INTO CUSTOMER (LastName, FirstName, AreaCode, PhoneNumber, Email)
    VALUES(newCustomerLastName, newCustomerFirstName, newCustomerAreaCode, newCustomerPhoneNumber, newCustomerEmail);

    # GET the CustomerID surrogate key value.
    SET varCustomerID = LAST_INSERT_ID();

    # Get ArtistID surrogate key value, and check for validity
    SELECT  ArtistID
    FROM    Artist
    WHERE   LastName = transArtistLastName
    INTO varArtistID;

    IF varArtistID IS NULL then
        SELECT 'Invalid ArtistID';
        ROLLBACK;
    END IF;

    # Get WorkID surrogate key value, and check for validity
    SELECT  WorkID
    FROM    WORK
    WHERE   ArtistID = varArtistID
        AND Title = transWorkTitle
        AND Copy = transWorkCopy
    INTO varWorkID;

    IF varWorkID IS NULL then
        SELECT 'Invalid WorkID';
        ROLLBACK;
    END IF;

    # Get TransID surrogate key value, and check for validity
    SELECT  TransactionID
    FROM    TRANS
    WHERE   WorkID = varWorkID
        AND SalesPrice IS NULL
    INTO varTransactionID;

    IF varTransactionID IS NULL then
        SELECT 'Invalid TransactionID';
        ROLLBACK;
    END IF;

    # All surrogate key values are OK, complete the transaction
    # Update TRANS row
    UPDATE TRANS
    SET DateSold = CURRENT_DATE,
        SalesPrice = transTransSalesPrice,
        CustomerID = varCustomerID
    WHERE TransactionID = varTransactionID;

    # Commit the transaction
    COMMIT;

    INSERT INTO CUSTOMER_ARTIST_INT(CustomerID, ArtistID)
    VALUES(varCustomerID, varArtistID);

    # Print message
    SELECT 'New customer and transaction data added to database.' 
        AS InsertCustomerWithTransactionResults;
END IF;
END
//
DELIMITER ;

/****************************************************************************/
/* CREATE TRIGGER TRANS_AfterInsertSetAskingPrice                           */ 
/****************************************************************************/ 

DELIMITER //  
CREATE TRIGGER TRANS_AfterInsertSetAskingPrice
AFTER INSERT ON TRANS
FOR EACH ROW

BEGIN

    DECLARE varRowCount             Int;
    DECLARE varPriorRowCount        Int;
    DECLARE varWorkID               Int;
    DECLARE varTransactionID        Int;
    DECLARE varAcquisitionPrice     Numeric(8,2);
    DECLARE varNewAskingPrice       Numeric(8,2);
    DECLARE varSumNetProfit         Numeric(8,2);
    DECLARE varAvgNetProfit         Numeric(8,2);

    SET varTransactionID = NEW.TransactionID;
    SET varAcquisitionPrice = NEW.AcquisitionPrice;
    SET varWorkID = NEW.WorkID;
    
    # First find if work has been here before. 
    SELECT       COUNT(*) INTO varRowCount
        FROM     TRANS
        WHERE    WorkID = varWorkID;

    # The variable varPriorRowCount is set to varRowCount minus one because this is an AFTER trigger, 
    # and the new row will already be in the database.    
    SET varPriorRowCount = (varRowCount - 1);

    # If varPriorRowCount = 0 this is a new acquistion. 
    IF (varPriorRowCount = 0) THEN
        # Set @NewAskingPrice to twice the acquisition cost. 
        SET varNewAskingPrice = (2 * varAcquisitionPrice);
    ELSE
        # The work has been here before
        # We have to determine the value of varNewAskingPrice

        SELECT       SUM(NetProfit) INTO varSumNetProfit
        FROM         ArtistWorkNetView AS AWNV
        WHERE        AWNV.WorkID = varWorkID
        GROUP BY     AWNV.WorkID;
        
        SET varAvgNetProfit = (varSumNetProfit / varPriorRowCount);
        
        # Now choose larger value for the new AskingPrice.
        IF ((varAcquisitionPrice + varAvgNetProfit)
                > (2 * varAcquisitionPrice)) THEN
            SET varNewAskingPrice = (varAcquisitionPrice + varAvgNetProfit); 
        ELSE

            SET varNewAskingPrice = (2 * varAcquisitionPrice); 
        END IF;
    END IF;
    
    # Update PRICELIST with the value of AskingPrice
    INSERT INTO PRICELIST VALUES (varTransactionID, varNewAskingPrice);

END //
DELIMITER ;

/****************************************************************************/
/* CREATE VIEW ArtisWorkNetView                                             */
/****************************************************************************/

CREATE OR REPLACE VIEW ArtistWorkNetView AS
    SELECT  W.WorkID, LastName AS ArtistLastName, FirstName AS ArtistFirstName, 
            Title, Copy, DateSold, AcquisitionPrice, SalesPrice, (SalesPrice - AcquisitionPrice) AS NetProfit
    FROM    TRANS AS T, WORK AS W, ARTIST AS A
    WHERE   T.WorkID = W.WorkID
        AND W.ArtistID = A.ArtistID;

/****************************************************************************/ 
/* CREATE VIEW CustomerInterestsView                                        */
/****************************************************************************/

CREATE VIEW CustomerInterestsView AS
    SELECT  C.LastName AS CustomerLastName, 
            C.FirstName AS CustomerFirstName, 
            A.LastName AS ArtistName
    FROM    CUSTOMER AS C JOIN 
            CUSTOMER_ARTIST_INT AS CI ON C.CustomerID = CI.CustomerID JOIN 
            ARTIST AS A ON CI.ArtistID = A.ArtistID;