create table [DimCustomer](
	[CustomerKey] int identity(1,1) not null,
	[CustomerId] int not null,
	[CustomerEmail] nvarchar(50) not null,
	[CustomerContactTitle] nvarchar(5) not null,
	[CustomerFirstName] nvarchar(30) not null,
	[CustomerLastName] nvarchar(30) not null,
	[CustomerAddress] nvarchar(60) not null,
	[CustomerCity] nvarchar(30) not null,
	[CustomerCountry] nvarchar(30) not null,
	[CustomerPostalCode] nvarchar(30 not null,
	[CustomerPhone] nvarchar(20) not null,
	[Start] date not null,
	[End] date null,
	constraint PK_DimCustomer primary key ([CustomerKey])
)

create table [DimBookedSeat](
	[BookedSeatKey] int not null,
	[BookedSeatId] int not null,
	[BookedSeatRowNr] int not null,
	[BookedSeatSeatNr] int not null,
	constraint PK_DimBookedSeat primary key ([BookedSeatKey])
)

create table [DimGenre](
	[GenreKey] int not null,
	[GenreId] int not null,
	[GenreDescription] nvarchar(50),
	[GenreNrOfMovies] int,
	constraint PK_DimGenre primary key ([GenreKey])
)

create table [DimMovie](
	[MovieKey] int not null,
	[MovieId] int not null,
	[MovieName] nvarchar(50) not null,
	constraint PK_DimMovie primary key ([MovieKey])
)

/*create table [DimBooking](
	[BookingKey] int not null,
	[BookingId] int not null,
	[BookingDate] datetime not null,
	[ShowingDate] int not null,
	[BookingPaid] bit not null,
	constraint PK_DimBooking primary key ([BookingKey])
)*/

CREATE TABLE [DimDate] (
    [DateKey] INT NOT NULL,
    [FullDateAlternateKey] DATE NOT NULL,
    [EnglishDayNameOfWeek] NVARCHAR(50) NOT NULL,
    [DutchDayNameOfWeek] NVARCHAR(50) NOT NULL,
    [MonthNumber] TINYINT NOT NULL,
    [EnglishMonthName] NVARCHAR(50) NOT NULL,
    [DutchMonthName] NVARCHAR(50) NOT NULL,
    [CalendarQuarter] TINYINT NOT NULL,
    [CalendarYear] SMALLINT NOT NULL
    CONSTRAINT PK_DimDate PRIMARY KEY(DateKey)
)

/*create table [DimMovieShowing](
	[MovieShowingKey] int not null,
	[MovieShowingId] int not null,
--	[ShowTime] time not null,
--	[ShowDatesStart] date not null,
--	[ShowDatesEnd] date not null,
	[MovieShowingPrice] float not null,
	constraint PK_DimMovieShowing primary key ([MovieShowingKey])
)*/

create table [BookingFact](
	[CustomerKey] int not null,
	[DateKey] int not null,
	[MovieKey] int not null,
	[GenreKey] int not null,
	[UnitPrice] float not null,
	[BookedSeats] int not null,
	[Turnover] float not null,

	constraint PK_BookingFact primary key ([CustomerKey], [MovieKey], [DateKey]),

	constraint FK_DimMovie foreign key ([MovieKey])references DimMovie(MovieKey),
	constraint FK_DimCustomer foreign key ([CustomerKey]) references DimCustomer(CustomerKey),
	constraint FK_DimDate foreign key ([DateKey]) references DimDate(DateKey),
	constraint FK_DimGenre foreign key ([GenreKey]) references DimGenre(GenreKey)
)