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;