use master
go

create database movies
go
Use movies
go
print 'current database is movies'

create table Director
(DirID char(5) constraint pk_Director primary key,
FirstName varchar(50) not null,
LastName varchar(50),
DOB datetime,
Background varchar(255))
print 'Director table Created'

create table Producer
(ProdID char(5) constraint pk_Producer primary key,
Name varchar(25) not null)
print 'Producer table Created'

create table Movie
(MovID char(5) constraint pk_Movie primary key,
MovTitle varchar(40) not null,
DirID char(5)constraint fk_MovieDirector foreign key references Director(DirID),
ProdID char(5)constraint fk_MovieProducer foreign key references Producer(ProdID),
Duration int,
Description varchar(255),
Category char(20),
ReleaseYear int)
print 'Movie table created'

create table Customer
(CustID smallint IDENTITY (1,1) constraint pk_Customer primary key,
FirstName varchar(50) not null,
LastName varchar(50),
Address varchar(25),
City varchar(25),
State char(15),
Zip char(7),
Phone char(10),
EMail varchar(50),
DOB datetime,
CreditCardNum char(16) not null,
CreditCardValidUpto datetime not null)
print 'Customer table Created'

create table Video
(VideoID char(5) constraint pk_Video primary key,
MovID char(5) constraint fk_VideoMovie foreign key references Movie(MovID),
Format char(4) not null,
Price money not null)
print 'Video table Created'

create table Orders
(OrderID char(5) constraint pk_Orders primary key,
OrderDate datetime not null,
CustID smallint constraint fk_OrdersCustomer foreign key references Customer(CustID),
TotalQtyOrdered int not null,
OrderValue money not null)
print 'Orders able Created'

create table Actor
(ActorID char(5) constraint pk_Actor primary key,
FirstName varchar(50) not null,
LastName varchar(50),
DOB DateTime,
Background varchar(255))
print 'Actor table Created'

create table OrderDetail
(OrderID char(5) constraint fk_OrderDetailOrders foreign key references Orders(OrderID),
VideoID char(5)constraint fk_OrderDetailVideo foreign key references Video(VideoID),
Qty int not null)
print 'OrderDetail table created'

create table ActorMovie
(ActorID char(5) constraint fk_ActorMovieActor foreign key references Actor(ActorID),
MovID char(5) constraint fk_ActorMovieMovie foreign key references Movie(MovID),
Role char(1) not null)
print 'ActorMovie table created'

