<< >>


3.5

, , 3.1. create_tables.sql SQL*Plus start create_tables.

3.1.

CREATE TABLE CUSTOMER(
CustomerlD int NOT NULL,
Name char(25) NOT NULL,
Street char(30) NULL,
City char(35) NULL,
State char(2) NULL,
ZipPostalCode char(5) NULL,
Country varchar(50) NULL,
AreaCode char(3) NULL,
PhoneNumber var(8) NULL,
Email varchar(100) NULL,
CONSTRAINT CustomerPK
PRIMARY KEY (CustomerlD));
CREATE TABLE ARTIST(
ArtistID int NOT NULL,
Name char(25) NOT NULL,
Nationality varchar(30) NULL,
BirthDate numeric(4,0) NULL,
DeceasedDate numeric(4,0) NULL,
CONSTRAINT ArtistPK PRIMARY KEY (ArtistID),
CONSTRAINT ArtistAKl UNIQUE (Name),
CONSTRAINT NationalityValues
CHECK (Nationality IN
('Canadian', 'English', 'French', 'German', 'Mexican', 'Russian', 'Spanish', 'US')),
CONSTRAINT BirthValuesCheck
CHECK (BirthDate < DeceasedDate),
CONSTRAINT ValidBirthYear
CHECK ((BirthDate > 1000) AND
(BirthDate < 2100)),
CONSTRAINT ValidDeathYear
CHECK ((DeceasedDate > 1000) AND (DeceasedDate < 2100)));

CREATE TABLE CUSTOMER_ARTIST_INT(
ArtistID int NOT NULL,
CustomerlD int NOT NULL,
CONSTRAINT CustomerArtistPK
PRIMARY KEY (ArtistID, CustomerlD),
CONSTRAINT Customer_Artist_Int_ArtistFK
FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID)
ON DELETE CASCADE,
CONSTRAINT Customer_Artist_Int_CustomerFK
FOREIGN KEY (CustomerlD)
REFERENCES CUSTOMER (CustomerlD)
ON DELETE CASCADE);

CREATE TABLE WORK(
WorkID int NOT NULL,
Title varchar(25) NOT NULL,
Description varchar(1000) NULL, Copy varchar(8) NOT NULL,
ArtistID int NOT NULL,
CONSTRAINT WorkPK
PRIMARY KEY (WorkID), CONSTRAINT WorkAKl
UNIQUE (Title, Copy), CONSTRAINT ArtistFK
FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID));
 
CREATE TABLE TRANSACTION(
TransactionID int NOT NULL,
DateAcquired Date NOT NULL,
AcquisitionPrice Numeric(8,2) NULL, PurchaseDate Date NULL,
SalesPrice Numeric(8,2) NULL,
AskingPrice Numeric(8,2) NULL, CustomerlD int NULL,
WorkID int NOT NULL,
CONSTRAINT TransactionPK
PRIMARY KEY (TransactionID),
CONSTRAINT SalesPriceRange
CHECK ((SalesPrice > 1000) AND (SalesPrice <= 200000)),
CONSTRAINT ValidTransDate
CHECK (DateAcquired <= PurchaseDate), CONSTRAINT TransactionWorkFK
FOREIGN KEY (WorkID) REFERENCES WORK (WorkID),
CONSTRAINT TransactionCustomerFK
FOREIGN KEY (CustomerlD)

REFERENCES CUSTOMER (CustomerlD));

, DESCRIBE. . 3.9 DESCRIBE CUSTOMER. , : DESCRIBE DESC.

. 3.9. DESCRIBE

, CUSTOMER_ARTIST_INT, . Oracle . .

 



<< >>