Designing Database Script for SUN Hotel Chain Data Mart Project

This assignment presents a database script designed for a data mart solution tailored to the SUN hotel chain. The script includes the creation of database tables such as Agent, Booking, Checkout, Customer, Hotel, Room, and Time, along with the insertion of sample data. The objective is to facilitate analysis of bookings versus checkouts and assess potential revenue against net revenue. The data mart aims to provide insights into daily, monthly, and yearly income, addressing queries such as room occupancy rates by country and month, and checkout percentages. The provided SQL script sets up the necessary database schema and populates it with initial data to support these analyses. The solution demonstrates the structure and data population for a data mart designed to meet the hotel chain's specific analytical requirements.

Two tasks have been completed. Task 4 and Task 6
Task 4
-- SCRIPT TO CREATE DATABASE, TABLES, ROWS
/****** Object: Table [dbo].[Agent] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Agent]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Agent]
GO
/****** Object: Table [dbo].[Booking] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Booking]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Booking]
GO
/****** Object: Table [dbo].[Checkout] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Checkout]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Checkout]
GO
/****** Object: Table [dbo].[Customer] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Customer]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Customer]
GO
/****** Object: Table [dbo].[Hotel] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Hotel]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Hotel]
GO
/****** Object: Table [dbo].[Room] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Room]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Room]
GO
/****** Object: Table [dbo].[Time] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Time]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Time]
GO
/****** Object: Table [dbo].[Time] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Time]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Time] (
[time_id] [int] NOT NULL
,[day] [int] NULL
,[month] [int] NULL
,[year] [int] NULL
,CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED ([time_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
1
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
2
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
3
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
4
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]

)
VALUES (
5
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
6
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
7
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
8
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
9
,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
10
,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
11

,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
12
,1
,2
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
13
,1
,1
,2017
)
/****** Object: Table [dbo].[Room] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Room]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Room] (
[room_id] [int] NOT NULL
,[hotel_id] [int] NOT NULL
,[no_of_beds] [int] NULL
,[tv] [bit] NULL
,[whirlpool_bath] [bit] NULL
,[promotion] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[rate] [decimal](18, 2) NULL
,CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([room_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
1
,5
,NULL
,NULL
,NULL
,NULL
,NULL

)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
2
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
3
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
4
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
5
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]

,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
6
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
7
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
8
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
9
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]

)
VALUES (
10
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
11
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
12
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
13
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
/****** Object: Table [dbo].[Hotel] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Hotel]')
AND type IN (N'U')
)

BEGIN
CREATE TABLE [dbo].[Hotel] (
[hotel_id] [int] NOT NULL
,[city] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[province] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[country] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[category] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,CONSTRAINT [PK_Hotel] PRIMARY KEY CLUSTERED ([hotel_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
1
,N'calcutta '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
2
,N'calcutta '
,NULL
,N'india '
,N'2 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
3
,N'delhi '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
4
,N'mumbai '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]

,[province]
,[country]
,[category]
)
VALUES (
5
,NULL
,NULL
,N'brazil '
,N'1 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
6
,NULL
,NULL
,N'brazil '
,N'2 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
7
,NULL
,NULL
,N'brazil '
,N'3 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
8
,NULL
,NULL
,N'brazil '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
9
,NULL
,NULL
,N'albania '
,N'1 '
)
/****** Object: Table [dbo].[Customer] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *

FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Customer]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Customer] (
[customer_id] [int] NOT NULL
,[name] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([customer_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
1
,N'a '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
2
,N'b '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
3
,N'c '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
4
,N'd '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
5
,N'e '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
6
,N'f '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
7
,N'g '
)

INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
8
,N'h '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
9
,N'i '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
10
,N'j '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
11
,N'k '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
12
,N'l '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
13
,N'm '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
14
,N'n '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
15
,N'o '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
16
,N'p '
)

/****** Object: Table [dbo].[Checkout] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Checkout]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Checkout] (
[checkout_id] [int] NOT NULL
,[room_id] [int] NULL
,[hotel_id] [int] NULL
,[customer_id] [int] NULL
,[time_id] [int] NULL
,[revenue] [decimal](18, 2) NULL
,CONSTRAINT [PK_Checkout] PRIMARY KEY CLUSTERED ([checkout_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
1
,1
,1
,1
,1
,CAST(500.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
2
,2
,1
,2
,2
,CAST(600.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
3
,3
,1
,3
,3