HW 4

IT 240 Introduction to DBs Spring 2020 Page 1 of 2

Erd Modeling using Vertabelo (

http://www.vertabelo.com/

)

Submit:

· This Word document updated with content for Parts I and II

· Create a zip archive containing a copy of the final SQLite3 database (JRJProject.db) and the various SQL DDL & DML statements for both Parts I and II

Part I

1. Create a Word document to document your work for this activity.

Make certain to copy and paste your results into this document.

2. If you have not already done so, sign up for a free Student Vertabelo account at http://www.vertabelo.com/

Make certain to go to Pricing Academic Student account

3. Use Create a New Document | Physical Model diagramming tool to create all three ER models for the James River Jewelry Project Case Study:

Conceptual

Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

Logical

Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

Physical

Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

3. Use your knowledge of SQL and DML statements (e.g. update etc.) to create an SQLite 3 DB—name it JRJProject.db—populate the database with the data from our exercises.

4. Verify the database works properly by creating queries similar to those that we know works.

5. Summarize your work in this Word document with appropriate screenshots; also create a zip archive containing a copy of the final SQLite3 database and the various SQL DDL & DML statements.

Part II

1. Answer questions A and B for the Writer’s Patrol Correction ticketing database as discussed on pp. 344–345 in the text.

In addition, make certain to also include all three models and the generated SQL code for each:

Conceptual
Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

Logical
Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

Physical
Copy and Paste the model into your Word document; create the DDL SQL for this model, save it, and copy and paste the results here.

IT 240 Introduction to DBs Spring 2020 Page 1 of 2

Erd Modeling using Vertabelo (

http://www.vertabelo.com/

)

Pet DB Example from Text

Read:

Conceptual, Logical and Physical Data Model

https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.html

If you have not already done so, sign up for a free Student Vertabelo account at http://www.vertabelo.com/

Make certain to go to Pricing Academic Student account

Pet Case Study

Examine Functional dependencies

OWNER(LastName, FirstName, Phone, Email)

PET(Name, Breed, DOB, Weight)

SERVICE(Date, Charge)

PET_TYPE(pet, type)

Pet_SERVICES(Date, Charge)

Conceptual

— Created by Vertabelo (http://vertabelo.com)

— Last modification date: 2020-05-19 18:53:22.4

— tables

— Table: OWNER

CREATE TABLE OWNER (

LastName NOT NULL,

FirstName NOT NULL,

Phone NOT NULL,

Email NOT NULL

);

— Table: PET

CREATE TABLE PET (

Name NOT NULL,

Breed NOT NULL,

DOB NOT NULL,

Weight NOT NULL

);

— Table: PET_SERVICES

CREATE TABLE PET_SERVICES (

Date NOT NULL,

Charge NOT NULL

);

— Table: PET_TYPE

CREATE TABLE PET_TYPE (

Pet NOT NULL,

Type NOT NULL

);

— Table: SERVICE

CREATE TABLE SERVICE (

Service NOT NULL,

Charge NOT NULL
);

— End of file.

Logical

— Created by Vertabelo (http://vertabelo.com)

— Last modification date: 2020-05-19 19:06:42.486

— tables
— Table: OWNER
CREATE TABLE OWNER (

OwnerID integer NOT NULL,

LastName text NOT NULL,

FirstName text NOT NULL,

Phone text NOT NULL,

Email text NOT NULL

);
— Table: PET
CREATE TABLE PET (

PetID integer NOT NULL,

Name text NOT NULL,

Breed text NOT NULL,

DOB date NOT NULL,

Weight real NOT NULL

);
— Table: PET_SERVICES
CREATE TABLE PET_SERVICES (

TransactionID integer NOT NULL,

Date date NOT NULL,

Charge real NOT NULL

);
— Table: PET_TYPE
CREATE TABLE PET_TYPE (

Pet-TypeID integer NOT NULL,

PetType text NOT NULL

);
— Table: SERVICE
CREATE TABLE SERVICE (

ServiceID integer NOT NULL,

Service text NOT NULL,

Charge real NOT NULL
);
— End of file.

Physical Model for Access

LastName FirstName, Phone, Email

….

OWNER(OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET(PetID, PetName, PetBreed, PetDOB, PetWeight, OwnerID, PetTypeID)

SERVICE(ServiceID, Service, Charge)

PET_TYPE(PetTypeID, PetType)

PET_SERVICES(TransactionID, TransactionLineNumber, Date, Charge, ServiceID, PetID)

Contraints

1. OwnerID in PET must exist in OWNER

2. PetTypeID in PET must exist in PET_TYPE

3. ServiceID in PET_SERVICES must exist in SERVICE

4. PetID in PET_SERVICES MUST EXIST IN PET

Assumptions

1. An owner can have more than one pet, or no pets

…….

— Created by Vertabelo (http://vertabelo.com)

— Last modification date: 2020-05-19 19:25:52.586

— tables
— Table: OWNER
CREATE TABLE OWNER (

OwnerID integer NOT NULL CONSTRAINT OWNER_pk PRIMARY KEY,

OwnerLastName text NOT NULL,

OwnerFirstName text NOT NULL,

OwnerPhone text NOT NULL,

OwnerEmail text NOT NULL

);
— Table: PET
CREATE TABLE PET (

PetID integer NOT NULL CONSTRAINT PET_pk PRIMARY KEY,

Name text NOT NULL,
Breed text NOT NULL,
DOB date NOT NULL,

Weight real NOT NULL,

OWNER_OwnerID integer NOT NULL,

PET_TYPE_PetTypeID integer NOT NULL,

CONSTRAINT PET_OWNER FOREIGN KEY (OWNER_OwnerID)

REFERENCES OWNER (OwnerID),

CONSTRAINT PET_PET_TYPE FOREIGN KEY (PET_TYPE_PetTypeID)

REFERENCES PET_TYPE (PetTypeID)

);
— Table: PET_SERVICES
CREATE TABLE PET_SERVICES (

TransactionID integer NOT NULL CONSTRAINT PET_SERVICES_pk PRIMARY KEY,

Date date NOT NULL,

Charge real NOT NULL,

PET_PetID integer NOT NULL,

SERVICE_ServiceID integer NOT NULL,

CONSTRAINT PET_SERVICES_PET FOREIGN KEY (PET_PetID)

REFERENCES PET (PetID),

CONSTRAINT PET_SERVICES_SERVICE FOREIGN KEY (SERVICE_ServiceID)

REFERENCES SERVICE (ServiceID)

);
— Table: PET_TYPE
CREATE TABLE PET_TYPE (

PetTypeID integer NOT NULL CONSTRAINT PET_TYPE_pk PRIMARY KEY,

PetType text NOT NULL
);
— Table: SERVICE
CREATE TABLE SERVICE (

ServiceID integer NOT NULL CONSTRAINT SERVICE_pk PRIMARY KEY,

Service text NOT NULL,
Charge real NOT NULL
);
— End of file.

?½??ļ???/April 21 James River.accdb

ItemNumber Appraiser AppraisalDate Value Comments

9 James Johnson 9/1/15 ¤ 180.00 Unique piece

13 James Johnson 9/2/15 ¤ 220.00 Not very exciting

5 Donna Jackson 4/22/16 ¤ 175.00 Exquisite workmanship

18 Donna Jackson 11/3/16 ¤ 210.00

13 Ralph Isaacson 3/17/17 ¤ 300.00

1 Ralph Isaacson 3/17/17 ¤ 200.00 Underpriced

CustomerID LastName FirstName Phone EmailAddress

1 Stanley Elizabeth 555-236-7789 Elizabeth.Stanley@somewhere.com

2 Price Fred 555-236-0091 Fred.Price@somewhere.com

3 Becky Linda 555-236-0392 Linda.Becky@somewhere.com

4 Birch Pamela 555-236-4493 Pamela.Birch@somewhere.com

5 Romez Ricardo 555-236-3334 Ricardo.Romez@somewhere.com

6 Jackson Samantha 555-236-1095 Samantha.Jackson@somewhere.com

ItemNumber ItemDescription Cost ArtistLastName ArtistFirstName

1 Gold Bracelet 120.0 Josephson Mary

2 Gold Necklace 160.0 Baker Samantha

3 Bead Earrings 50.0 Josephson Mary

4 Gold Bracelet 180.0 Baker Samantha

5 Silver Necklace 135.0 Baxter Sam

6 Bead Earrings 25.0 Josephson Mary

7 Bead Earrings 22.5 Josephson Mary

8 Gold Earrings 50.0 Lintz John

9 Gold Necklace 160.0 Lintz John

10 Bead Earrings 20.0 Josephson Mary

11 Bead Earrings 35.0 Josephson Mary

12 Bead Earrings 45.0 Josephson Mary

13 Gold Necklace 225.0 Lintz John

14 Silver Earrings 55.0 Lintz John

15 Gold Bracelet 200.0 Lintz John

16 Bead Earrings 25.0 Josephson Mary

17 Bead Earrings 45.0 Josephson Mary

18 Gold Bracelet 210.0 Baker Samantha

19 Silver Necklace 165.0 Baxter Sam

InvoiceDate PreTaxAmount CustomerID InvoiceNumber

5/5/19 155.0 1 1001

5/7/19 203.0 2 1002

5/11/19 75.0 3 1003

5/15/19 67.0 4 1004

5/15/19 330.0 5 1005

5/16/19 25.0 1 1006

5/25/19 45.0 3 1007

6/6/19 445.0 1 1008

6/7/19 72.0 6 1009

ItemNumber RetailPrice InvoiceLineNumber InvoiceNumber

1 155.0 1 1001

2 203.0 1 1002

3 75.0 1 1003

6 35.0 1 1004

7 32.0 2 1004

4 240.0 1 1005

8 90.0 2 1005

10 25.0 1 1006

11 45.0 1 1007

5 175.0 1 1008

9 215.0 2 1008

12 55.0 3 1008

14 72.0 1 1009

CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL,
LastName Char(35) NOT NULL,
FirstName Char(25) NOT NULL,
Phone Char(12) NULL,
EmailAddress VarChar(100) NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)
);

CREATE TABLE ITEM(
ItemNumber Int NOT NULL,
ItemDescription VarChar(255) NOT NULL,
Cost Numeric NOT NULL,
ArtistLastName Char(35) NULL,
ArtistFirstName Char(25) NULL,
CONSTRAINT ITEM_PK PRIMARY KEY(ItemNumber)
);

CREATE TABLE PURCHASE(
InvoiceNumber Int NOT NULL,
InvoiceDate DateTime NOT NULL,
PreTaxAmount Numeric NOT NULL,
CustomerID Int NOT NULL,
CONSTRAINT PURCHASE_PK PRIMARY KEY(InvoiceNumber),
CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID)
);

CREATE TABLE PURCHASE_ITEM (
InvoiceNumber Int NOT NULL,
InvoiceLineNumber Int NOT NULL,
ItemNumber Int NOT NULL,
RetailPrice Numeric NOT NULL,
CONSTRAINT PURCHASE_ITEM_PK PRIMARY KEY (InvoiceNumber, InvoiceLineNumber),
CONSTRAINT PURCHASE_ITEM_PURCHASE_FK FOREIGN KEY (InvoiceNumber)
REFERENCES PURCHASE(InvoiceNumber),
CONSTRAINT PURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber)
REFERENCES ITEM(ItemNumber)
);

SELECT *
FROM viewCustomerPurchase
ORDER BY LastName, FirstName;
SELECT *
FROM viewPurchaseItemItem
ORDER BY InvoiceNumber, ItemNumber;
SELECT InvoiceNumber, SUM(Cost) AS TotalItemCost, SUM(RetailPrice) AS TotalRetailSales
FROM viewPurchaseItemItem
GROUP BY InvoiceNumber;
SELECT C.CustomerID, LastName, FirstName, InvoiceNumber, InvoiceDate, PreTaxAmount
FROM CUSTOMER AS C INNER JOIN PURCHASE AS P ON C.CustomerID=P.CustomerID;
SELECT P.InvoiceNumber, PI.ItemNumber, ArtistLastName, ArtistFirstName, ItemDescription, Cost, RetailPrice
FROM (PURCHASE AS P INNER JOIN PURCHASE_ITEM AS PI ON P.InvoiceNumber=PI.InvoiceNumber) INNER JOIN ITEM AS I ON PI.ItemNumber=I.ItemNumber;
ALTER TABLE CUSTOMER
ADD ReferredBy Int NULL;

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUST_FK_CUST FOREIGN KEY(ReferredBy) REFERENCES
CUSTOMER (CustomerID);

UPDATE CUSTOMER
SET ReferredBy = 1
WHERE CustomerID = 2;
UPDATE CUSTOMER
SET ReferredBy = 2
WHERE CustomerID = 4;
UPDATE CUSTOMER
SET ReferredBy = 3
WHERE CustomerID = 5;
ALTER TABLE ITEM
ADD ArtistHasBeenPaid Varchar(25) NULL;

UPDATE ITEM
SET ArtistHasBeenPaid = ‘Paid’;
UPDATE ITEM
SET ArtistHasBeenPaid = ‘In process’
WHERE ItemNumber IN (11,13,14,16);
UPDATE ITEM
SET ArtistHasBeenPaid = ‘Waiting for invoice’
WHERE ItemNumber IN (15,17,18,19);
ALTER TABLE ITEM
ALTER COLUMN ArtistHasBeenPaid Varchar(25) NOT NULL;

SELECT C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName
FROM CUSTOMER AS C INNER JOIN CUSTOMER AS R ON C.ReferredBy = R.CustomerID;
SELECT C.FirstName AS CustomerFirstName, C.LastName AS CustomerLastName, R.FirstName AS ReferrerFirstName, R.LastName AS ReferrerLastName
FROM CUSTOMER AS C LEFT JOIN CUSTOMER AS R ON C.ReferredBy = R.CustomerID;
SELECT C1.CustomerID, C1.FirstName, C1.LastName
FROM CUSTOMER AS C1
WHERE EXISTS (SELECT *
FROM CUSTOMER C2
WHERE C1.FirstName = C2.FirstName AND
C1.LastName = C2.LastName AND
C1.CustomerID <> C2.CustomerID);
ALTER TABLE APPRAISAL
ADD CONSTRAINT APPRAISAL_PK
PRIMARY KEY (ItemNumber, AppraisalDate);

ALTER TABLE APPRAISAL
ADD CONSTRAINT APPR_ITEM_FK FOREIGN KEY(ItemNumber)
REFERENCES ITEM(ItemNumber);

?½??ļ???/April 23 James River.accdb

ItemNumber Appraiser AppraisalDate Value Comments

1 Ralph Isaacson 3/17/17 ¤ 200.00 Underpriced

5 Donna Jackson 4/22/16 ¤ 175.00 Exquisite workmanship

9 James Johnson 9/1/15 ¤ 180.00 Unique piece

13 James Johnson 9/2/15 ¤ 220.00 Not very exciting

13 Ralph Isaacson 3/17/17 ¤ 300.00

18 Donna Jackson 11/3/16 ¤ 210.00

CustomerID LastName FirstName Phone EmailAddress

1 Stanley Elizabeth 555-236-7789 Elizabeth.Stanley@somewhere.com

2 Price Fred 555-236-0091 Fred.Price@somewhere.com

3 Becky Linda 555-236-0392 Linda.Becky@somewhere.com

4 Birch Pamela 555-236-4493 Pamela.Birch@somewhere.com

5 Romez Ricardo 555-236-3334 Ricardo.Romez@somewhere.com

6 Jackson Samantha 555-236-1095 Samantha.Jackson@somewhere.com

ItemNumber ItemDescription Cost ArtistLastName ArtistFirstName

1 Gold Bracelet 120.0 Josephson Mary

2 Gold Necklace 160.0 Baker Samantha

3 Bead Earrings 50.0 Josephson Mary

4 Gold Bracelet 180.0 Baker Samantha

5 Silver Necklace 135.0 Baxter Sam

6 Bead Earrings 25.0 Josephson Mary

7 Bead Earrings 22.5 Josephson Mary

8 Gold Earrings 50.0 Lintz John

9 Gold Necklace 160.0 Lintz John

10 Bead Earrings 20.0 Josephson Mary

11 Bead Earrings 35.0 Josephson Mary

12 Bead Earrings 45.0 Josephson Mary

13 Gold Necklace 225.0 Lintz John

14 Silver Earrings 55.0 Lintz John

15 Gold Bracelet 200.0 Lintz John

16 Bead Earrings 25.0 Josephson Mary

17 Bead Earrings 45.0 Josephson Mary

18 Gold Bracelet 210.0 Baker Samantha

19 Silver Necklace 165.0 Baxter Sam

InvoiceNumber InvoiceDate PreTaxAmount CustomerID

1001 5/5/19 155.0 1

1002 5/7/19 203.0 2

1003 5/11/19 75.0 3

1004 5/15/19 67.0 4

1005 5/15/19 330.0 5

1006 5/16/19 25.0 1

1007 5/25/19 45.0 3

1008 6/6/19 445.0 1

1009 6/7/19 72.0 6

InvoiceNumber InvoiceLineNumber ItemNumber RetailPrice

1001 1 1 155.0

1002 1 2 203.0

1003 1 3 75.0

1004 1 6 35.0

1004 2 7 32.0

1005 1 4 240.0

1005 2 8 90.0

1006 1 10 25.0

1007 1 11 45.0

1008 1 5 175.0

1008 2 9 215.0

1008 3 12 55.0

1009 1 14 72.0

CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL,
LastName Char(35) NOT NULL,
FirstName Char(25) NOT NULL,
Phone Char(12) NULL,
EmailAddress VarChar(100) NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)
);

CREATE TABLE ITEM(
ItemNumber Int NOT NULL,
ItemDescription VarChar(255) NOT NULL,
Cost Numeric NOT NULL,
ArtistLastName Char(35) NULL,
ArtistFirstName Char(25) NULL,
CONSTRAINT ITEM_PK PRIMARY KEY(ItemNumber)
);

CREATE TABLE PURCHASE(
InvoiceNumber Int NOT NULL,
InvoiceDate DateTime NOT NULL,
PreTaxAmount Numeric NOT NULL,
CustomerID Int NOT NULL,
CONSTRAINT PURCHASE_PK PRIMARY KEY(InvoiceNumber),
CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID)
);

CREATE TABLE PURCHASE_ITEM (
InvoiceNumber Int NOT NULL,
InvoiceLineNumber Int NOT NULL,
ItemNumber Int NOT NULL,
RetailPrice Numeric NOT NULL,
CONSTRAINT PURCHASE_ITEM_PK PRIMARY KEY (InvoiceNumber, InvoiceLineNumber),
CONSTRAINT PURCHASE_ITEM_PURCHASE_FK FOREIGN KEY (InvoiceNumber)
REFERENCES PURCHASE(InvoiceNumber),
CONSTRAINT PURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber)
REFERENCES ITEM(ItemNumber)
);

SELECT *
FROM CUSTOMER;
SELECT *
FROM ITEM;
SELECT ItemNumber, ItemDescription
FROM ITEM
WHERE Cost > 100;
SELECT ItemNumber, ItemDescription
FROM ITEM
WHERE Cost > 100
AND RTRIM(ArtistLastName) LIKE ‘*son’;
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM PURCHASE
WHERE PreTaxAmount > 200);
SELECT LastName, FirstName
FROM CUSTOMER INNER JOIN PURCHASE ON CUSTOMER.CustomerID = PURCHASE.CustomerID
WHERE PreTaxAmount > 200;
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM PURCHASE
WHERE InvoiceNumber IN
(SELECT InvoiceNumber
FROM PURCHASE_ITEM
WHERE RetailPrice > 50));
SELECT DISTINCT LastName, FirstName
FROM (CUSTOMER INNER JOIN PURCHASE ON CUSTOMER.CustomerID = PURCHASE.CustomerID) INNER JOIN PURCHASE_ITEM ON PURCHASE.InvoiceNumber = PURCHASE_ITEM.InvoiceNumber
WHERE RetailPrice > 50;
SELECT LastName, FirstName
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM PURCHASE
WHERE InvoiceNumber IN
(SELECT InvoiceNumber
FROM PURCHASE_ITEM
WHERE ItemNumber in
(SELECT ItemNumber
FROM ITEM
WHERE ArtistLastName LIKE ‘J*’ )));
ALTER TABLE APPRAISAL
ADD CONSTRAINT APPRAISAL_PK
PRIMARY KEY (ItemNumber, AppraisalDate);

ALTER TABLE APPRAISAL
ADD CONSTRAINT APPR_ITEM_FK FOREIGN KEY(ItemNumber)
REFERENCES ITEM(ItemNumber);

?½??ļ???/Fixed InClassJamesRiverJewelry.accdb

LastName FirstName Phone EmailAddress CustomerID

Stanley Elizabeth 555-236-7789 Elizabeth.Stanley@somewhere.com 1

Price Fred 555-236-0091 Fred.Price@somewhere.com 2

Becky Linda 555-236-0392 Linda.Becky@somewhere.com 3

Birch Pamela 555-236-4493 Pamela.Birch@somewhere.com 4

Romez Ricardo 555-236-3334 Ricardo.Romez@somewhere.com 5

Jackson Samantha 555-236-1095 Samantha.Jackson@somewhere.com 6

ItemDescription Cost ArtistLastName ArtistFirstName ItemNumber

Gold Bracelet 120.0 Josephson Mary 1

Bead Earrings 50.0 Josephson Mary 2

Gold Bracelet 180.0 Baker Samantha 3

Silver Necklace 135.0 Baxter Sam 4

Bead Earrings 25.0 Josephson Mary 5

Bead Earrings 22.5 Josephson Mary 6

Gold Earrings 50.0 Lintz John 7

Gold Necklace 160.0 Lintz John 8

Bead Earrings 20.0 Josephson Mary 9

Bead Earrings 45.0 Josephson Mary 10

Gold Necklace 225.0 Lintz John 11

Silver Earrings 55.0 Lintz John 12

Gold Bracelet 200.0 Lintz John 13

Bead Earrings 25.0 Josephson Mary 14

Bead Earrings 45.0 Josephson Mary 15

Gold Bracelet 210.0 Baker Samantha 16

Silver Necklace 165.0 Baxter Sam 17

InvoiceDate PreTaxAmount CustomerID InvoiceNumber

5/5/19 155.0 1 1001

5/7/19 203.0 2 1002

5/11/19 75.0 3 1003

5/15/19 67.0 4 1004

5/15/19 330.0 5 1005

5/16/19 25.0 1 1006

5/25/19 45.0 3 1007

6/6/19 445.0 1 1008

6/7/19 72.0 6 1009

InvoiceNumber InvoiceLineNumber ItemNumber RetailPrice

1001 1 1 155.0

1002 1 2 203.0

1003 1 3 75.0

1004 1 6 35.0

1004 2 7 32.0

1005 1 4 240.0

1005 2 8 90.0

1006 1 10 25.0

1007 1 11 45.0

1008 1 5 175.0

1008 2 9 215.0

1008 3 12 55.0

1009 1 14 72.0

CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL,
LastName Char(35) NOT NULL,
FirstName Char(25) NOT NULL,
Phone Char(12) NULL,
EmailAddress VarChar(100) NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)
);

CREATE TABLE ITEM(
ItemNumber Int NOT NULL,
ItemDescription VarChar(255) NOT NULL,
Cost Numeric NOT NULL,
ArtistLastName Char(35) NULL,
ArtistFirstName Char(25) NULL,
CONSTRAINT ITEM_PK PRIMARY KEY(ItemNumber)
);

CREATE TABLE PURCHASE(
InvoiceNumber Int NOT NULL,
InvoiceDate DateTime NOT NULL,
PreTaxAmount Numeric NOT NULL,
CustomerID Int NOT NULL,
CONSTRAINT PURCHASE_PK PRIMARY KEY(InvoiceNumber),
CONSTRAINT PURCHASE_CUSTOMER_FK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID)
);

ALTER TABLE PURCHASE
ALTER COLUMN InvoiceNumber COUNTER (1001,1)

CREATE TABLE PURCHASE_ITEM (
InvoiceNumber Int NOT NULL,
InvoiceLineNumber Int NOT NULL,
ItemNumber Long NOT NULL,
RetailPrice Numeric NOT NULL,
CONSTRAINT PURCHASE_ITEM_PK
PRIMARY KEY (InvoiceNumber, InvoiceLineNumber),
CONSTRAINT PURCHASE_ITEM_PURCHASE_FK
FOREIGN KEY (InvoiceNumber)
REFERENCES PURCHASE(InvoiceNumber),
CONSTRAINT PURCHASE_ITEM_ITEM_FK FOREIGN KEY (ItemNumber)
REFERENCES ITEM(ItemNumber)
);

INSERT INTO CUSTOMER (LastName, FirstName, Phone, EmailAddress)
VALUES (‘Jackson’, ‘Samantha’, ‘555-236-1095’, ‘Samantha.Jackson@somewhere.com’);