Database: CeilInn1
-- =============================================
-- Database: CeilInn1
-- Author: FunctionX
-- Date: Sunday 08 November 2009
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'CeilInn1'
)
DROP DATABASE CeilInn1
GO
CREATE DATABASE CeilInn1
GO
USE CeilInn1
GO
-- =========================================
-- Table: Employees
-- =========================================
IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL
DROP TABLE dbo.Employees
GO
CREATE TABLE dbo.Employees
(
EmployeeNumber nchar(10) NOT NULL,
FirstName nvarchar(24),
LastName nvarchar(24) not null,
Title nvarchar(50),
Constraint PK_Employees Primary Key (EmployeeNumber)
)
GO
INSERT INTO Employees
VALUES(N'22958', N'Andrew', N'Laskin', N'General Manager'),
(N'70429', N'Lynda', N'Fore', N'Shift Manager'),
(N'27049', N'Harriett', N'Dovecot', N'Associate'),
(N'28405', N'Peggy', N'Thompson', N'Associate'),
(N'24095', N'Fred', N'Barclay', N'Associate'),
(N'72947', N'Sheryl', N'Shegger', N'Intern');
GO
-- =========================================
-- Table: Customers
-- =========================================
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
CREATE TABLE dbo.Customers
(
AccountNumber nchar(12) not null,
FullName nvarchar(60) not null,
PhoneNumber nchar(16),
EmergencyName nvarchar(50),
EmergencyPhone nchar(16),
Constraint PK_Customers Primary Key (AccountNumber)
)
GO
INSERT INTO Customers
VALUES(N'294209', N'Doris Wilson', N'703-416-0934', N'Gabriela Dawson', N'703-931-1000'),
(N'608502', N'Caroline Lomey', N'301-652-0700', N'Albert Lomey', N'301-412-5055'),
(N'208405', N'Peter Carney', N'990-585-1886', N'Spencer Miles', N'990-750-8666'),
(N'284085', N'Lucy Chen', N'425-979-7413', N'Edward Lamb', N'425-720-9247'),
(N'629305', N'Joan Davids', N'202-789-0500', N'Rebecca Boiron', N'202-399-3600'),
(N'180204', N'Randy Whittaker', N'703-631-1200', N'Bryan Rattner', N'703-506-9200'),
(N'204795', N'Juliette Beckins', N'410-944-1440', N'Bernard Brodsky', N'410-385-2235'),
(N'608208', N'Alfred Owens', N'804-798-3257', N'Jane Owens', N'240-631-1445'),
(N'902840', N'Daniel Peters', N'624-802-1686', N'Grace Peters', N'877-490-9333'),
(N'660820', N'Anne Sandt', N'953-172-9347', N'William Sandt', N'953-279-2475'),
(N'946090', N'Peter Carney', N'990-585-1886', N'Spencer Miles', N'990-750-8666'),
(N'100752', N'Caroline Lomey', N'301-652-0700', N'Albert Lomey', N'301-412-5055');
GO
-- =========================================
-- Table: Rooms
-- =========================================
IF OBJECT_ID(N'dbo.Rooms', N'U') IS NOT NULL
DROP TABLE dbo.Rooms
GO
IF OBJECT_ID('Rooms', 'U') IS NOT NULL
DROP TABLE Rooms
GO
CREATE TABLE Rooms
(
RoomNumber nchar(10) not null,
RoomType nvarchar(20) default N'Bedroom',
BedType nvarchar(40) default N'Queen',
Rate money default 85.95,
Available bit default 1,
Constraint PK_Rooms Primary Key (RoomNumber)
);
GO
INSERT INTO Rooms(RoomNumber)
VALUES(N'104');
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'105', N'King', 95.50, 1),
(N'106', N'King', 95.50, 1);
GO
INSERT INTO Rooms(RoomNumber, Available)
VALUES(N'107', 0);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate)
VALUES(N'108', N'King', 95.50);
GO
INSERT INTO Rooms(RoomNumber, Available)
VALUES(N'109', 1);
GO
INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'112', N'Studio', N'King', 98.95, 0),
(N'113', N'Bedroom', N'Queen', 88.85, 0),
(N'114', N'Studio', N'King', 98.95, 1),
(N'115', N'Bedroom', N'King', 92.75, 1);
GO
INSERT INTO Rooms(RoomNumber, RoomType, Rate, Available)
VALUES(N'118', N'Conference Roome', 450, 0),
(N'120', N'Conference Roome', 650, 1);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'201', N'Queen', 90, 1),
(N'202', N'Queen', 90, 0),
(N'203', N'Queen', 90, 1);
GO
INSERT INTO Rooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'204', N'Studio', N'King', 112.95, 0),
(N'205', N'Studio', N'King', 110.85, 1),
(N'206', N'Bedroom', N'King', 98.75, 1);
GO
INSERT INTO Rooms(RoomNumber, BedType, Rate, Available)
VALUES(N'208', N'King', 98.95, 0),
(N'210', N'Queen', 88.85, 0),
(N'212', N'King', 92.75, 1);
GO
-- =========================================
-- Table: Occupancies
-- =========================================
IF OBJECT_ID(N'dbo.Occupancies', N'U') IS NOT NULL
DROP TABLE dbo.Occupancies
GO
CREATE TABLE dbo.Occupancies
(
OccupancyID int identity(1, 1) NOT NULL,
ProcessedBy nchar(10) Constraint FK_Employees References dbo.Employees(EmployeeNumber), -- The employee who processed the transaction
DateOccupied Date not null,
ProcessedFor nchar(12) Constraint FK_Customers References dbo.Customers(AccountNumber), -- The customer who rented the room
RoomRented nchar(10) Constraint FK_Rooms References dbo.Rooms(RoomNumber), -- The room that was rented
RateApplied money default 0,
PhoneUse money default 0,
InternetFee money default 0,
Notes text,
Constraint PK_Occupancies Primary Key (OccupancyID)
)
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'27049', N'20100510', N'294209', N'104', 85.75);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, PhoneUse)
VALUES(N'28405', N'20100511', N'294209', N'105', 85.75, 5.35);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, PhoneUse, InternetFee)
VALUES(N'70429', N'20100511', N'608502', N'118', 450.00, 8.75, 3.25);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'70429', N'20100512', N'294209', N'106', 85.75);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, PhoneUse, InternetFee)
VALUES(N'24095', N'20100512', N'208405', N'109', 75.85, 3.45, 3.25);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, PhoneUse)
VALUES(N'28405', N'20100513', N'208405', N'108', 75.85, 2.65),
(N'28405', N'20100514', N'208405', N'107', 75.85, 3.15),
(N'27049', N'20100515', N'208405', N'115', 75.85, 1.95);
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'28405', N'20100515', N'284085', N'205', 75.85);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, PhoneUse)
VALUES(N'24095', N'20100516', N'208405', N'108', 75.85, 5.50);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'24095', N'20100517', N'629305', N'112', 98.95),
(N'70429', N'20100518', N'629305', N'112', 98.85);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, InternetFee)
VALUES(N'28405', N'20100616', N'180204', N'105', 94.50, 3.25);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'72947', N'20100616', N'660820', N'204', 115.95);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied, InternetFee)
VALUES(N'28405', N'20100616', N'608208', N'206', 94.50, 3.25);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, Notes)
VALUES(N'72947', N'20100616', N'204795', N'204', N'Two people rented the room');
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'28405', N'20100616', N'902840', N'203', 104.50),
(N'24095', N'20100617', N'180204', N'105', 94.50),
(N'24095', N'20100617', N'660820', N'204', 115.95),
(N'24095', N'20100617', N'608208', N'206', 94.50);
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, Notes)
VALUES(N'24095', N'20100617', N'204795', N'204', N'Two people occupied the room');
GO
INSERT INTO Occupancies(ProcessedBy, DateOccupied, ProcessedFor, RoomRented, RateApplied)
VALUES(N'72947', N'20100617', N'902840', N'203', 104.50);
GO