
--Data definition statements for tables and views in Inventory database

CREATE TABLE MusicTypes
( TypeID INT, TypeName VARCHAR (20) NOT NULL,
CONSTRAINT un_TypeName UNIQUE (TypeName),
CONSTRAINT pk_MusicTypes PRIMARY KEY (TypeID) ) ;

CREATE TABLE CDLabels
( LabelID INT, CompanyName VARCHAR (60) DEFAULT 'Independent' NOT NULL,
CONSTRAINT pk_CDLabels PRIMARY KEY (LabelID) ) ;

CREATE TABLE CompactDiscs
( CompactDiscID INT, CDTitle VARCHAR (60) NOT NULL,
LabelID INT NOT NULL, InStock INT NOT NULL,
CONSTRAINT pk_CompactDiscs PRIMARY KEY (CompactDiscID),
CONSTRAINT fk_LabelID FOREIGN KEY (LabelID) REFERENCES CDLabels,
CONSTRAINT ck_InStock CHECK ( InStock > 0 AND InStock < 50 ) ) ;

CREATE TABLE CompactDiscTypes
( CompactDiscID INT, MusicTypeID INT,
CONSTRAINT pk_CompactDiscTypes PRIMARY KEY ( CompactDiscID, MusicTypeID ),
CONSTRAINT fk_CompactDiscID_01 FOREIGN KEY (CompactDiscID)
   REFERENCES CompactDiscs,
CONSTRAINT fk_MusicTypeID FOREIGN KEY (MusicTypeID)
   REFERENCES MusicTypes ) ;

CREATE TABLE Artists
( ArtistID INT, ArtistName VARCHAR (60) NOT NULL,
PlaceOfBirth VARCHAR (60) DEFAULT 'Unknown' NOT NULL,
CONSTRAINT pk_Artists PRIMARY KEY (ArtistID) ) ;

CREATE TABLE ArtistCDs
( ArtistID INT, CompactDiscID INT,
CONSTRAINT pk_ArtistCDs PRIMARY KEY ( ArtistID, CompactDiscID ),
CONSTRAINT fk_ArtistID FOREIGN KEY (ArtistID) REFERENCES Artists,
CONSTRAINT fk_CompactDiscID_02 FOREIGN KEY (CompactDiscID)
   REFERENCES CompactDiscs ) ;

CREATE VIEW CDsInStock
AS SELECT CDTitle, InStock FROM CompactDiscs
WHERE InStock > 10 WITH CHECK OPTION ;

CREATE VIEW CDPublishers ( CDTitle, Publisher )
AS SELECT CompactDiscs.CDTitle, CDLabels.CompanyName
FROM CompactDiscs, CDLabels
WHERE CompactDiscs.LabelID = CDLabels.LabelID ;

--Insert data into the CDLabels table
INSERT INTO CDLabels VALUES ( 827, 'Private Music' ) ;
INSERT INTO CDLabels VALUES ( 828, 'Reprise Records' ) ;
INSERT INTO CDLabels VALUES ( 829, 'Asylum Records' ) ;
INSERT INTO CDLabels VALUES ( 830, 'Windham Hill Records' ) ;
INSERT INTO CDLabels VALUES ( 831, 'Geffen' ) ;
INSERT INTO CDLabels VALUES ( 832, 'MCA Records' ) ;
INSERT INTO CDLabels VALUES ( 833, 'Decca Record Company' ) ;
INSERT INTO CDLabels VALUES ( 834, 'CBS Records' ) ;
INSERT INTO CDLabels VALUES ( 835, 'Capitol Records' ) ;
INSERT INTO CDLabels VALUES ( 836, 'Sarabande Records' ) ;
--End inserts for the CDLabels table

--Insert data into the CompactDiscs table
INSERT INTO CompactDiscs VALUES ( 101, 'Famous Blue Raincoat', 827, 13 ) ;
INSERT INTO CompactDiscs VALUES ( 102, 'Blue', 828, 42 ) ;
INSERT INTO CompactDiscs VALUES ( 103, 'Court and Spark', 829, 22 ) ;
INSERT INTO CompactDiscs VALUES ( 104, 'Past Light', 830, 17 ) ;
INSERT INTO CompactDiscs VALUES ( 105, 'Kojiki', 831, 6 ) ;
INSERT INTO CompactDiscs VALUES
  ( 106, 'That Christmas Feeling', 832, 8 ) ;
INSERT INTO CompactDiscs VALUES
  ( 107, 'Patsy Cline: 12 Greatest Hits', 832, 32 ) ;
INSERT INTO CompactDiscs VALUES
  ( 108, 'Carreras Domingo Pavarotti in Concert', 833, 27 ) ;
INSERT INTO CompactDiscs VALUES
  ( 109, 'After the Rain: The Soft Sounds of Erik Satie', 833, 21 ) ;
INSERT INTO CompactDiscs VALUES
  ( 110, 'Out of Africa', 832, 29 ) ;
INSERT INTO CompactDiscs VALUES
  ( 111, 'Leonard Cohen The Best Of', 834, 12 ) ;
INSERT INTO CompactDiscs VALUES
  ( 112, 'Fundamental', 835, 34 ) ;
INSERT INTO CompactDiscs VALUES
  ( 113, 'Bob Seger and the Silver Bullet Band Greatest Hits', 835, 16 ) ;
INSERT INTO CompactDiscs VALUES
  ( 114, 'Blues on the Bayou', 832, 27 ) ;
INSERT INTO CompactDiscs VALUES
 ( 115, 'Orlando', 836, 5 ) ;
--End inserts for the CompactDiscs table

--Insert data into the MusicTypes table
INSERT INTO MusicTypes VALUES ( 11, 'Blues' ) ;
INSERT INTO MusicTypes VALUES ( 12, 'Jazz' ) ;
INSERT INTO MusicTypes VALUES ( 13, 'Pop' ) ;
INSERT INTO MusicTypes VALUES ( 14, 'Rock' ) ;
INSERT INTO MusicTypes VALUES ( 15, 'Classical' ) ;
INSERT INTO MusicTypes VALUES ( 16, 'New Age' ) ;
INSERT INTO MusicTypes VALUES ( 17, 'Country' ) ;
INSERT INTO MusicTypes VALUES ( 18, 'Folk' ) ;
INSERT INTO MusicTypes VALUES ( 19, 'International' ) ;
INSERT INTO MusicTypes VALUES ( 20, 'Soundtracks' ) ;
INSERT INTO MusicTypes VALUES ( 21, 'Christmas' ) ;
--End inserts for the MusicTypes table

--Insert data into the CompactDiscTypes table
INSERT INTO CompactDiscTypes VALUES ( 101, 18 ) ;
INSERT INTO CompactDiscTypes VALUES ( 101, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 102, 11 ) ;
INSERT INTO CompactDiscTypes VALUES ( 102, 18 ) ;
INSERT INTO CompactDiscTypes VALUES ( 102, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 103, 18 ) ;
INSERT INTO CompactDiscTypes VALUES ( 103, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 104, 16 ) ;
INSERT INTO CompactDiscTypes VALUES ( 105, 16 ) ;
INSERT INTO CompactDiscTypes VALUES ( 106, 21 ) ;
INSERT INTO CompactDiscTypes VALUES ( 107, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 107, 17 ) ;
INSERT INTO CompactDiscTypes VALUES ( 108, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 108, 15 ) ;
INSERT INTO CompactDiscTypes VALUES ( 109, 15 ) ;
INSERT INTO CompactDiscTypes VALUES ( 110, 20 ) ;
INSERT INTO CompactDiscTypes VALUES ( 111, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 111, 18 ) ;
INSERT INTO CompactDiscTypes VALUES ( 112, 11 ) ;
INSERT INTO CompactDiscTypes VALUES ( 112, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 113, 13 ) ;
INSERT INTO CompactDiscTypes VALUES ( 113, 14 ) ;
INSERT INTO CompactDiscTypes VALUES ( 114, 11 ) ;
INSERT INTO CompactDiscTypes VALUES ( 115, 20 ) ;
--End inserts for the CompactDiscTypes table

--Insert data into the Artists table
INSERT INTO Artists VALUES
  ( 2001, 'Jennifer Warnes', 'Seattle, Washington, USA' ) ;
INSERT INTO Artists VALUES
  ( 2002, 'Joni Mitchell', 'Fort MacLeod, Alberta, Canada' ) ;
INSERT INTO Artists VALUES
  ( 2003, 'William Ackerman', 'Germany' ) ;
INSERT INTO Artists VALUES
  ( 2004, 'Kitaro', 'Toyohashi, Japan' ) ;
INSERT INTO Artists VALUES
  ( 2005, 'Bing Crosby', 'Tacoma, Washington, USA' ) ;
INSERT INTO Artists VALUES
  ( 2006, 'Patsy Cline', 'Winchester, Virginia, USA' ) ;
INSERT INTO Artists VALUES
  ( 2007, 'Jose Carreras', 'Barcelona, Spain' ) ;
INSERT INTO Artists VALUES
 ( 2008, 'Luciano Pavarotti', 'Modena, Italy' ) ;
INSERT INTO Artists VALUES
  ( 2009, 'Placido Domingo', 'Madrid, Spain' ) ;
INSERT INTO Artists VALUES
  ( 2010, 'Pascal Roge', 'Unknown' ) ;
INSERT INTO Artists VALUES
  ( 2011, 'John Barry', 'Unknown' ) ;
INSERT INTO Artists VALUES
  ( 2012, 'Leonard Cohen', 'Montreal, Quebec, Canada' ) ;
INSERT INTO Artists VALUES
  ( 2013, 'Bonnie Raitt', 'Burbank, California, USA' ) ;
INSERT INTO Artists VALUES
  ( 2014, 'Bob Seger', 'Dearborn, Michigan, USA' ) ;
INSERT INTO Artists VALUES
  ( 2015, 'Silver Bullet Band', 'Does not apply' ) ;
INSERT INTO Artists VALUES
  ( 2016, 'B.B. King', 'Indianola, Mississippi, USA' ) ;
INSERT INTO Artists VALUES
  ( 2017, 'David Motion', 'Unknown' ) ;
INSERT INTO Artists VALUES
  ( 2018, 'Sally Potter', 'Unknown' ) ;
--End inserts for the Artists table

--Insert data into the ArtistCDs table
INSERT INTO ArtistCDs VALUES ( 2001, 101 ) ;
INSERT INTO ArtistCDs VALUES ( 2002, 102 ) ;
INSERT INTO ArtistCDs VALUES ( 2002, 103 ) ;
INSERT INTO ArtistCDs VALUES ( 2003, 104 ) ;
INSERT INTO ArtistCDs VALUES ( 2004, 105 ) ;
INSERT INTO ArtistCDs VALUES ( 2005, 106 ) ;
INSERT INTO ArtistCDs VALUES ( 2006, 107 ) ;
INSERT INTO ArtistCDs VALUES ( 2007, 108 ) ;
INSERT INTO ArtistCDs VALUES ( 2008, 108 ) ;
INSERT INTO ArtistCDs VALUES ( 2009, 108 ) ;
INSERT INTO ArtistCDs VALUES ( 2010, 109 ) ;
INSERT INTO ArtistCDs VALUES ( 2011, 110 ) ;
INSERT INTO ArtistCDs VALUES ( 2012, 111 ) ;
INSERT INTO ArtistCDs VALUES ( 2013, 112 ) ;
INSERT INTO ArtistCDs VALUES ( 2014, 113 ) ;
INSERT INTO ArtistCDs VALUES ( 2015, 113 ) ;
INSERT INTO ArtistCDs VALUES ( 2016, 114 ) ;
INSERT INTO ArtistCDs VALUES ( 2017, 115 ) ;
INSERT INTO ArtistCDs VALUES ( 2018, 115 ) ;
--End inserts for the ArtistCDs table
