use master
if exists
	(Select [name] 
	 from sys.databases
	 Where [Name]='SongDepot')
Begin
Drop database SongDepot
End
Create Database SongDepot
Go
Use SongDepot
GO 
Create table Band
(
	BandName nvarchar(255) primary Key,
	BandDescription nvarchar(255),
	BandAddedDate DateTime
)
Go
Create Table BandMembers
(

  BandMemberEmail nvarchar(255) primary key,
  BandMemberLastName nvarchar(255),
  BandMemberFirstName nvarchar(255),
  BandName nvarchar(255) foreign key references Band(BandName) on update cascade
)
Go
Create Table Customer
(
   CustomerEmail nvarchar(255) primary key,
   CustomerLastName nvarchar(255) not null,
   CustomerFirstName nvarchar(255),
   CustomerPassword nvarchar(50)
)
GO
Create table Song
(
   SongID int identity(1,1) primary key,
   BandName nvarchar(255) foreign key references Band(BandName) on update cascade,
   SongTitle nvarchar(255) not null,
   SongDescription Nvarchar(255) null,
   SongPrice money,
   songURL nvarchar(255) 
)
Go
Create table Download
(
  DownLoadID int identity(1,1) primary key,
  DownloadDate datetime not null,
  CustomerEmail nvarchar(255) foreign key references Customer(CustomerEmail) on update cascade
  
)
GO
Create Table DownloadDetail
(
  DownLoadDetailID int identity(1,1) primary key,
  DownLoadID int foreign key references Download(downloadID),
  SongID int foreign key references Song(songID)
)
GO
Create table Concert
(
   ConcertID int identity(1,1) primary key,
   BandName nvarchar(255) foreign key references Band(BandName) on update Cascade,
   ConcertDate Datetime not null,
   ConcertVenue nvarchar(255),
   ConcertTicketPrice money,
   ConcertDescription nvarchar(max)
)
Go


--insert band names
Insert into Band(BandName,BandDescription,BandAddedDate)
Values('Last of the Mohicans','Punk folk with a touch of bluegrass', '4/1/2009')
Insert into Band(BandName,BandDescription,BandAddedDate)
Values('Lazy Waters','Acoustic Rock', '4/12/2009')
Insert into Band(BandName,BandDescription,BandAddedDate)
Values('Undead Dreams','Progressive Roc', '5/1/2009')
Insert into Band(BandName,BandDescription,BandAddedDate)
Values('Desperate Hours','Country folk', '5/2/2009')

--Insert BandMembers
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('joe@Mohicans.com','Joe','Sanderson','Last of the Mohicans')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('sue@Mohicans.com','Susan','Sanderson','Last of the Mohicans')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('tammy@Mohicans.com','Tammy','Robinson','Last of the Mohicans')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('lester@Mohicans.com','Lester','Tate','Last of the Mohicans')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('amber@UndeadDreams.com','Amber','Larson','Undead Dreams')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('sidney@UndeadDreams.com','Sidney','Carleton','Undead Dreams')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('tom@UndeadDreams.com','Thomas','Masters','Undead Dreams')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('gh@lazywaters.com','George','Hero','Lazy Waters')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('tracy@lazywaters.com','Tracy','Louie','Lazy Waters')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('edward@desperatehours.com','Edward','James','Desperate Hours')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('mark@desperatehours.com','Mark','James','Desperate Hours')
Insert into BandMembers(BandMemberEmail,BandMemberFirstName,BandMemberLastName,BandName)
Values('brandon@desperatehours.com','brandon','James','Desperate Hours')

--insert Customers
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('martin47@hotmail.com','Martin','Brown','blue44')
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('songster@yahoo.com','Aimee','Devlin','P@ssw0rd1')
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('lesley@hotmail.com','Lesley','Anderson','lagirl')
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('toto@gmail.com','Dorthy','Morgan','kansas')
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('jj@hotmail.com','Jane','Jameson','jj402')
Insert into Customer(CustomerEmail,CustomerFirstName,CustomerLastName,CustomerPassword)
Values('tracy@msn.com','Tracy','Li','shadow')

--Insert songs (abt 3 per band)
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Last one home tonight', null, 1.34,'Last of the Mohicans', 'songs/lastonehome.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('The end of nothing', null, 1.00,'Last of the Mohicans', 'songs/endofnothing.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Dog day', null, 1.00,'Last of the Mohicans', 'songs/dogday.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Zombie walking', 'Zombies on death row', 2.00,'Undead Dreams', 'songs/zombiwalking.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Brains to eat', 'hungry zombies', .99,'Undead Dreams', 'songs/brainstoeat.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Long after dawn', 'Not a zombie song', 1.25,'Undead Dreams', 'songs/longafterdawn.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Walk away', null, 1.50,'Lazy Waters', 'songs/walkaway.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('Scrambled eggs for brains', null, 1.50,'LazyWaters', 'songs/scrambledeggs.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('too far south', 'A song about texas', 1.50,'LazyWaters', 'songs/toofarsouth.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('If you stick around I will drive you away', null, 1.50,'Desperate Hours', 'songs/ifyoustickaround.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('I have never been to New Orleans', null, 1.50,'Desperate Hours', 'songs/neverbeentono.mp3')
Insert into Song(SongTitle, SongDescription, SongPrice, BandName, songURL)
Values('The best will never come', 'A pessimists lullaby', 1.50,'Desperate Hours', 'songs/neverbest.mp3')

--insert download and download detail

Insert into Download(DownloadDate,CustomerEmail)
Values('4/5/2009','songster@yahoo.com')
Declare @dlID int
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,3)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,5)

Insert into Download(DownloadDate,CustomerEmail)
Values('4/6/2009','jj@hotmail.com')
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,1)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,4)

Insert into Download(DownloadDate,CustomerEmail)
Values('4/6/2009','tracy@msn.com')
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,6)

Insert into Download(DownloadDate,CustomerEmail)
Values('4/8/2009','lesley@hotmail.com')
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,1)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,3)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,7)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,11)

Insert into Download(DownloadDate,CustomerEmail)
Values('4/8/2009','toto@gmail.com')
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,10)

Insert into Download(DownloadDate,CustomerEmail)
Values('4/9/2009','martin47@hotmail.com')
Set @dlID=@@IDENTITY
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,4)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,6)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,8)
Insert into DownloadDetail(DownLoadID, SongID)
Values(@dlID,11)

--insert into concert
Insert into Concert(ConcertDate,BandName,ConcertDescription,ConcertVenue, ConcertTicketPrice)
Values('6/23/2009','Undead Dreams','A night of slobbering, brain eating fun','West side tavern',25.95)
Insert into Concert(ConcertDate,BandName,ConcertDescription,ConcertVenue, ConcertTicketPrice)
Values('6/30/2009','Undead Dreams','When we are done you will be one of the walking dead','East side tavern',15.95)
Insert into Concert(ConcertDate,BandName,ConcertDescription,ConcertVenue, ConcertTicketPrice)
Values('7/4/2009','Lazy Waters','our fourth fourth of July','Just off Central Park',0.00)
Insert into Concert(ConcertDate,BandName,ConcertDescription,ConcertVenue, ConcertTicketPrice)
Values('7/15/2009','Desperate Hours','Three hours of desperate fun','The Late night Club',25.95)