gistfile1.txt

USE Musical;

CREATE TABLE dbo.Customer(
	CustomerID INT PRIMARY KEY NOT NULL,
	Title NVARCHAR(8),
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
);

CREATE TABLE dbo.CustomerAddress(
	CustomerID INT NOT NULL,
	AddressID INT NOT NULL,
	AddressTypeID INT NOT NULL,
);

CREATE TABLE dbo.[Address](
	AddressID INT PRIMARY KEY NOT NULL,
	AddressLine1 NVARCHAR(60) NOT NULL,
	AddressLine2 NVARCHAR(60),
	City NVARCHAR(50) NOT NULl,
	County NVARCHAR(60) NOT NULl,
	PostalCode NVARCHAR(15) NOT NULL,
);

CREATE TABLE dbo.AddressType(
	AddressTypeID INT PRIMARY KEY NOT NULl,
	Name NVARCHAR(50) NOT NULL
);

ALTER TABLE dbo.CustomerAddress
	ADD CONSTRAINT PK_CustomerAddress PRIMARY KEY (CustomerID, AddressID, AddressTypeID);

ALTER TABLE dbo.CustomerAddress
	ADD CONSTRAINT FK_Customer_Customer FOREIGN KEY(CustomerID) REFERENCES dbo.Customer(CustomerID);

ALTER TABLE dbo.CustomerAddress
	ADD CONSTRAINT FK_Address_Address FOREIGN KEY(AddressID) REFERENCES dbo.[Address](AddressID);

ALTER TABLE dbo.CustomerAddress
	ADD CONSTRAINT FK_AddressType_AddressType FOREIGN KEY(AddressTypeID) REFERENCES dbo.AddressType(AddressTypeID);

CREATE TABLE dbo.CreditCard(
	CreditCardID INT PRIMARY KEY NOT NULL,
	CardType NVARCHAR(50) NOT NULL,
	CardNumber NVARCHAR(25) NOT NULL,
	ExpMonth TINYINT NOT NULL,
	ExpYear SMALLINT NOT NULL
);

CREATE TABLE dbo.CustomerCreditCard(
	CustomerID INT NOT NULL,
	CreditCardID INT NOT NULL,
);

ALTER TABLE dbo.CustomerCreditCard
	ADD CONSTRAINT FK_CreditCard_Customer FOREIGN KEY(CustomerID) REFERENCES dbo.Customer(CustomerID);

ALTER TABLE dbo.CustomerCreditCard
	ADD CONSTRAINT FK_CreditCard_CreditCard FOREIGN KEY(CreditCardID) REFERENCES dbo.CreditCard(CreditCardID);

ALTER TABLE dbo.CustomerCreditCard
	ADD CONSTRAINT PK_CustomerCreditCard PRIMARY KEY (CustomerID, CreditCardID);

CREATE TABLE dbo.SeatState(
	SeatStateID INT PRIMARY KEY NOT NULL,
	Name VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Seat(
	RowNumber INT NOT NULL,
	SeatNumber INT NOT NULL,
	SeatStateID INT NOT NULL,
);

ALTER TABLE dbo.Seat
	ADD CONSTRAINT PK_Seat PRIMARY KEY (RowNumber, SeatNumber);

ALTER TABLE dbo.Seat
	ADD CONSTRAINT FK_SeatState_SeatState FOREIGN KEY(SeatStateID) REFERENCES dbo.SeatState(SeatStateID);

CREATE TABLE Booking(
	BookingID INT PRIMARY KEY NOT NULL,
	CustomerID INT NOT NULL,
	BookingMadeDate DATETIME NOT NULL,
	NumberOfSeats INT NOT NULL
);

ALTER TABLE dbo.Booking
	ADD CONSTRAINT FK_CustomerID FOREIGN KEY(CustomerID) REFERENCES dbo.Customer(CustomerID);


CREATE TABLE Ticket(
	TicketNumber INT PRIMARY KEY NOT NULL,
	BookingID INT NOT null,
	RowNumber INT NOT NULL,
	SeatNumber INT NOT NULL,
);

ALTER TABLE dbo.Ticket
	ADD CONSTRAINT FK_Ticket_BookingID FOREIGN KEY(BookingID) REFERENCES dbo.Booking(BookingID);

ALTER TABLE dbo.Ticket
	ADD CONSTRAINT FK_Ticket_Seat FOREIGN KEY(RowNumber, SeatNumber) REFERENCES dbo.Seat(RowNumber, SeatNumber);