CREATE TABLE Languages (
LanguageID NUMBER NOT NULL,
LanguageDescription VARCHAR2(20) NOT NULL
);
ALTER TABLE LANGUAGES
ADD CONSTRAINT PK_Languages Primary Key (LanguageID);
CREATE TABLE PublishersAndLabels (
COMPANYID NUMBER NOT NULL,
COMPANYNAME VARCHAR2(50) NOT NULL
);
ALTER TABLE PublishersAndLabels
ADD CONSTRAINT PK_PublishersAndLabels Primary Key (CompanyID);
CREATE TABLE RecordingFormats (
RecordingFormatID NUMBER NOT NULL,
RecordingFormatDescription VARCHAR2(30) NOT NULL);
ALTER TABLE RecordingFormats
ADD CONSTRAINT PK_RecordingFormats Primary Key (RecordingFormatID);
CREATE TABLE Recordings (
PublisherID NUMBER NOT NULL,
RecordingID NUMBER NOT NULL,
RecordingTitle VARCHAR2(50) NULL,
CatalogNumber VARCHAR2(20) NULL,
RecordingFormatID NUMBER DEFAULT 1 NOT NULL,
ReleaseDate DATE NULL,
LanguageID NUMBER DEFAULT 1 NOT NULL,
ListPrice FLOAT(126) NULL,
CoverImageFileSpec VARCHAR2(50) NULL
);
ALTER TABLE Recordings
ADD CONSTRAINT PK_RecordingID PRIMARY KEY (RecordingID);
ALTER TABLE RECORDINGS
ADD CONSTRAINT FK_Recordings_Languages Foreign Key (LanguageID)
REFERENCES Languages (LanguageID);
ALTER TABLE Recordings
ADD CONSTRAINT FK_Recordings_PubsAndLabels Foreign Key (PublisherID)
REFERENCES PublishersAndLabels (CompanyID);
ALTER TABLE Recordings
ADD CONSTRAINT FK_Recordings_RecordingFormats Foreign Key(RecordingFormatID)
REFERENCES RecordingFormats (RecordingFormatID);
CREATE TABLE AudioStyles (
AudioStyleID NUMBER NOT NULL,
AudioStyleDescription VARCHAR2(25) NOT NULL
);
ALTER TABLE AudioStyles
ADD CONSTRAINT PK_AudioStyleID PRIMARY KEY (AudioStyleID);
CREATE TABLE ArtistsAndPerformers (
ArtistID NUMBER NOT NULL,
ArtistName VARCHAR2(50) NOT NULL,
ArtistImageFileSpec VARCHAR2(50) NULL,
ArtistBioText VARCHAR2(3000) NULL
);
ALTER TABLE ArtistsAndPerformers
ADD CONSTRAINT PK_ArtistsAndPerformers Primary Key (ArtistID);
CREATE TABLE TRACKS (
RecordingID NUMBER NOT NULL,
StyleID NUMBER NULL,
TrackNumber NUMBER NOT NULL,
TrackTitle VARCHAR2(50) NULL,
ArtistID NUMBER NULL,
SampleFileSpec VARCHAR2(50) NULL
);
ALTER TABLE TRACKS
ADD CONSTRAINT PK_Tracks Primary Key (RecordingID, TrackNumber);
ALTER TABLE TRACKS
ADD CONSTRAINT FK_Tracks_ArtistsAndPerformers Foreign Key (ArtistID)
REFERENCES ArtistsAndPerformers (ArtistID);
ALTER TABLE TRACKS
ADD CONSTRAINT FK_Tracks_AudioStyles Foreign Key (StyleID)
REFERENCES AudioStyles (AudioStyleID);
ALTER TABLE TRACKS
ADD CONSTRAINT FK_Tracks_Recordings Foreign Key (Recordingid)
REFERENCES Recordings (RecordingID);
CREATE TABLE Reviews (
ReviewID NUMBER NOT NULL,
RecordingID NUMBER NOT NULL,
ReviewerName VARCHAR2(40) NOT NULL,
ReviewText VARCHAR2(1000),
Rating NUMBER,
ReviewDate DATE NOT NULL
);
ALTER TABLE Reviews
ADD CONSTRAINT PK_ReviewID PRIMARY KEY (ReviewID);
ALTER TABLE Reviews
ADD CONSTRAINT FK_Reviews_Recordings FOREIGN KEY (RecordingID)
REFERENCES Recordings (RecordingID);
CREATE TABLE Countries (
CountryID NUMBER NOT NULL,
CountryAbbrev VARCHAR2(3) NULL,
CountryName VARCHAR2(50) NOT NULL
);
ALTER TABLE Countries
ADD CONSTRAINT PK_Countries Primary Key (CountryID);
CREATE TABLE PostalCodes (
PostalCode VARCHAR2(12) NOT NULL,
City VARCHAR2(30) NOT NULL,
StateProv VARCHAR2(30) NOT NULL,
CountryID NUMBER DEFAULT 1 NOT NULL
);
ALTER TABLE POSTALCODES
ADD CONSTRAINT PK_PostalCodes Primary Key (PostalCode, CountryID);
ALTER TABLE POSTALCODES
ADD CONSTRAINT FK_PostalCodes_Countries Foreign Key (CountryID)
REFERENCES Countries (CountryID);
CREATE TABLE StoreTypes (
StoreTypeID NUMBER NOT NULL,
StoreTypeDescription VARCHAR2(50) NOT NULL
);
ALTER TABLE StoreTypes
ADD CONSTRAINT PK_StoreTypes Primary Key (StoreTypeID);
CREATE TABLE Stores (
StorePostalCode VARCHAR2(12) NOT NULL,
StoreID NUMBER NOT NULL,
StoreDescription VARCHAR2(30) NOT NULL,
CountryID NUMBER DEFAULT 1 NOT NULL,
StoreTypeID NUMBER DEFAULT 1 NOT NULL,
StoreAddress1 VARCHAR2(50),
StoreAddress2 VARCHAR2(50),
StoreCity VARCHAR2(30)
);
ALTER TABLE Stores
ADD CONSTRAINT PK_Stores Primary Key (StoreID);
ALTER TABLE Stores
ADD CONSTRAINT FK_Stores_StoreTypes Foreign Key (StoreTypeID)
REFERENCES StoreTypes(StoreTypeID);
ALTER TABLE STORES
ADD CONSTRAINT
FK_Stores_PostalCodes Foreign Key (StorePostalCode, CountryID)
REFERENCES PostalCodes(PostalCode, CountryID);
CREATE TABLE Inventories (
ProductID NUMBER NOT NULL,
StoreID NUMBER NOT NULL,
QtyOnHand NUMBER DEFAULT 0 NOT NULL
);
ALTER TABLE Inventories
ADD CONSTRAINT PK_Inventories Primary Key (StoreID, ProductID);
ALTER TABLE INVENTORIES
ADD CONSTRAINT FK_Inventories_Recordings Foreign Key (ProductID)
REFERENCES Recordings(RecordingID) ;
ALTER TABLE INVENTORIES
ADD CONSTRAINT FK_Inventories_Stores Foreign Key (StoreID)
REFERENCES Stores (StoreID);
/* The CustomerTypes table */
CREATE TABLE CustomerTypes (
CustomerTypeID NUMBER NOT NULL,
CustomerTypeDescription VARCHAR2(30) NOT NULL
);
ALTER TABLE CustomerTypes
ADD CONSTRAINT PK_CustomerTypes Primary Key (CustomerTypeID);
/* The CustomerTitles table */
CREATE TABLE CustomerTitles (
CustomerTitleID NUMBER NOT NULL,
CUstomerTitleAbbrev VARCHAR2(5) NOT NULL
);
ALTER TABLE CustomerTitles
ADD CONSTRAINT PK_CustomerTitles Primary Key (CustomerTitleID);
/* The PaymentTerms table */
CREATE TABLE PaymentTerms(
PaymentTermsID NUMBER NOT NULL,
PaymentTermsDescription VARCHAR2(25) NOT NULL
);
ALTER TABLE PaymentTerms
ADD CONSTRAINT PK_PaymentTerms Primary Key (PaymentTermsID);
/* The CreditCardTypes table */
CREATE TABLE CreditCardTypes (
CreditCardTypeID NUMBER NOT NULL,
CreditCardDescription VARCHAR2(30) NOT NULL
);
ALTER TABLE CreditCardTypes
ADD CONSTRAINT PK_CreditCardTypes Primary Key (CreditCardTypeID);
CREATE TABLE CUSTOMERS (
CustomerID NUMBER NOT NULL,
CreditCardTypeID NUMBER NULL,
CustomerTypeID NUMBER DEFAULT 0 NOT NULL,
CustomerFirstName VARCHAR2(25) NULL,
CustomerLastName VARCHAR2(30) NULL,
CUstomerTitleID NUMBER NULL,
CUstomerAddress1 VARCHAR2(50) NULL,
CustomerAddress2 VARCHAR2(50) NULL,
CustomerCity VARCHAR2(30) NULL,
CustomerPostalCode VARCHAR2(12) NULL,
PaymentTermsID NUMBER DEFAULT 0 NOT NULL,
CreditLimit FLOAT(126) NULL,
CreditCardHolder VARCHAR2(50) NULL,
CreditCardNumber VARCHAR2(20) NULL,
CreditCardexpiryMonth NUMBER NULL,
CreditCardExpiryYear NUMBER NULL,
EmailAddress VARCHAR2(50) NULL,
Password VARCHAR2(15) NULL,
PasswordReminderText VARCHAR2(25) NULL
);
ALTER TABLE Customers
ADD CONSTRAINT PK_Customers Primary Key (CustomerID);
ALTER TABLE Customers
ADD CONSTRAINT FK_Customers_CCTypes Foreign Key (CreditCardTypeID)
REFERENCES CreditCardTypes (CreditCardTypeID);
ALTER TABLE CUSTOMERS
ADD CONSTRAINT FK_Customers_CustTitles Foreign Key (CustomerTitleID)
REFERENCES CustomerTitles (CustomerTitleID);
ALTER TABLE Customers
ADD CONSTRAINT FK_Customers_CustTypes Foreign Key (CustomerTypeID)
REFERENCES CustomerTypes (CustomerTypeID);
ALTER TABLE CUSTOMERS
ADD CONSTRAINT FK_Customers_PayTerms Foreign Key (PaymentTermsID)
REFERENCES PaymentTerms (PaymentTermsID);
CREATE TABLE OrderSources (
OrderSourceID NUMBER NOT NULL,
OrderSourceDescription VARCHAR2(10) NOT NULL
);
ALTER TABLE OrderSources
ADD CONSTRAINT PK_OrderSources Primary Key (OrderSourceID);
CREATE TABLE OrderStatuses (
OrderStatusID NUMBER NOT NULL,
OrderStatusDescription VARCHAR2(25) NOT NULL
);
ALTER TABLE OrderStatuses
ADD CONSTRAINT PK_OrderStatuses Primary Key (OrderStatusID);
CREATE TABLE CustomerOrders (
OrderID NUMBER NOT NULL,
OrderDate DATE DEFAULT SYSDATE NOT NULL,
CustomerID NUMBER NOT NULL,
OrderStatusID NUMBER DEFAULT 0 NOT NULL,
ShipToName VARCHAR2(50) NULL,
ShipToaddress1 VARCHAR2(50) NULL,
ShipToAddress2 VARCHAR2(50) NULL,
ShipToCity VARCHAR2(50) NULL,
ShipToPostalCode VARCHAR2(12) NULL,
OrderSourceID NUMBER NOT NULL,
CreditCardHolder VARCHAR2(50) NULL,
CreditCardNumber VARCHAR2(20) NULL,
CreditCardTypeID NUMBER NULL,
CreditCardExpiryMonth NUMBER NULL,
CreditCardExpiryYear NUMBER NULL
);
ALTER TABLE CustomerOrders
ADD CONSTRAINT PK_CustomerOrders Primary Key (OrderID);
ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustOrders_CCTypes Foreign Key (CreditCardTypeID)
REFERENCES CreditCardTypes (CreditCardTypeID);
ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustOrders_Customers Foreign Key (CustomerID)
REFERENCES Customers (CustomerID);
ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustOrders_OrderSources Foreign Key (OrderSourceID)
REFERENCES OrderSources (OrderSourceID);
ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustOrders_OrderStatuses Foreign Key (OrderStatusID)
REFERENCES OrderStatuses (OrderStatusID);
CREATE TABLE CUstomerOrderItems (
OrderID NUMBER NOT NULL,
ProductID NUMBER NULL,
LineNumber NUMBER DEFAULT 1 NOT NULL,
Quantity NUMBER NOT NULL,
PricePerItem FLOAT(126) NOT NULL,
ProvidingStore NUMBER DEFAULT 3 NOT NULL
);
ALTER TABLE CustomerOrderItems
ADD CONSTRAINT PK_CustomerOrderItems Primary Key (OrderID, LineNumber);
ALTER TABLE CustomerOrderItems
ADD CONSTRAINT FK_CustOrdItems_Recordings Foreign Key (ProductID)
REFERENCES Recordings (RecordingID);
ALTER TABLE CustomerOrderItems
ADD CONSTRAINT FK_CustOrdItems_CustOrders Foreign Key (OrderID)
REFERENCES CustomerOrders (OrderID);
CREATE TABLE SALESCAMPAIGNS (
SALESCAMPAIGNID NUMBER NOT NULL,
SALESCAMPAIGNDESCRIPTION VARCHAR2(20) NULL,
MINQTY NUMBER NULL,
MAXQTY NUMBER NULL,
PERCENTDISCOUNT FLOAT(126) NULL
);
ALTER TABLE SALESCAMPAIGNS
ADD CONSTRAINT PK_SalesCampaigns PRIMARY KEY (SALESCAMPAIGNID);
