VenueTracker Database

Here is the SQL for the venue database

Use master
go
if exists
 (select name from sys.databases 
  where name = 'VenueTracker')
Begin
Drop Database VenueTracker
End
Go
Create Database VenueTracker

Use VenueTracker

Create table Venue
(
  VenueID int identity(1,1) primary key,
  VenueName nvarchar(255) not null,
  VenueAddress1 nvarchar(255),
  VenueAddress2 nvarchar(255) not null,
  VenueCity nvarchar(255) not null,
  VenueState nchar(2) not null,
  VenuePhone nchar(10) not null,
  VenueCapacity int,
  VenueAgeRestriction bit,
  VenueDescription xml,
  VenueURL nvarchar(255)
  
)

Go

Create Table Artist
(
   ArtistID int identity(1,1) primary key,
   ArtistName nvarchar(255) not null unique,
   ArtistBio xml,
   ArtistURL nvarchar(255)
)

Go
Create Table Show
(
 ShowID int identity(1,1) primary key,
 VenueID int foreign key references Venue(VenueID),
 ArtistID int foreign key references Artist(ArtistID),
 TourName nvarchar(255),
 ShowDate Date not null,
 ShowTime Time not null,
 ShowPosition nvarchar(255),
 ShowDescription nvarchar(max),
 Constraint ck_position check (ShowPosition in('Headline', 'Opening','festival'))
)

Go

Create table TicketOutlet
(
  TicketOutletID int identity(1,1),
  ShowID int foreign Key references Show(ShowID),
  constraint PK_Ticket primary Key (ticketOutletID, ShowID),
  TicketOutletURL nvarchar(255) not null,
  TicketPrice decimal(5,2) not null,
  TicketsRemaining int
  
)

Go

Create Table Genre
(
 GenreID int identity(1,1) primary key,
 GenreName nvarchar(255) unique
)

Go

Create Table ArtistGenre
(
 ArtistID int,
 GenreID int,
 Constraint pk_ArtistGenre Primary Key (artistID, GenreID)
)

Go

Create Table Customer
(
  CustomerID int identity(1,1) primary key,
  CustomerLastName nvarchar(255) not null,
  CustomerFirstName nvarchar(255),
  CustomerEmail nvarchar(255) not null unique,
  CustomerPassword nvarchar(255) not null unique
)

go

Create Table CustomerArtist
(
  CustomerID int foreign key references Customer(CustomerID),
  ArtistID int foreign Key references Artist(ArtistID),
  Constraint Pk_CustomerArtist primary key (CustomerID, ArtistID),
  ArtistAlert bit default 1
  
)