Hotel_DB

an anonymous user · June 11, 2021 · SQL
CREATE TABLE Hotels (
    HotelID int PRIMARY KEY NOT NULL,
	Name varchar(20) NOT NULL,
	Address varchar(50) NOT NULL,
	ManagerID int NOT NULL,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID));


CREATE TABLE Employees (
	EmployeeID int PRIMARY KEY NOT NULL,
	FirstName varchar(20) NOT NULL,
    LastName varchar(20) NOT NULL,
	Phone varchar(20),
	JobDescription varchar(50),
	Salary int NOT NULL,
	HotelID int NOT NULL,
	ManagerID int,
	DepartmentID int,
	Address varchar(100) NOT NULL,
	FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID),
	FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID),
	FOREIGN KEY (HotelID) REFERENCES Hotels(HotelID));
	
CREATE TABLE Department (
	DepartmentID int PRIMARY KEY NOT NULL,
	DepartmentName varchar(20) NOT NULL,
	Number_of_employee int);


CREATE TABLE Rooms (
	RoomID int NOT NULL,
	HotelID int NOT NULL,
	R_Status varchar(20) NOT NULL,
	Roomtype int NOT NULL,
	RoomFloor int NOT NULL,
	PricePerNight int NOT NULL,
	FOREIGN KEY (HotelID) REFERENCES Hotels(HotelID),
	PRIMARY KEY (RoomID, HotelID));
	
	

CREATE TABLE Reservation (
	ReservationID int PRIMARY KEY NOT NULL,
	GuestID int NOT NULL,
	RoomID int NOT NULL,
	HotelID int NOT NULL,
	Check_in_date data NOT NULL,
	Check_Out_date data NOT NULL,
	Reservation_date data NOT NULL,
	FOREIGN KEY (GuestID) REFERENCES Guest(GuestID),
	FOREIGN KEY (RoomID, HotelID) REFERENCES Rooms(RoomID, HotelID));

CREATE TABLE Product (
	ProductID int PRIMARY KEY NOT NULL,
	ProductName varchar(20) NOT NULL,
	ProductPrice int NOT NULL);

CREATE TABLE Service (
	ServiceID int PRIMARY KEY NOT NULL,
	EmployeeID int,
	ProductID int,
	GuestID int,
	FOREIGN KEY (ProductID) REFERENCES Product(ProductID),
	FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
	FOREIGN KEY (GuestID) REFERENCES Guest(GuestID));
	
	CREATE TABLE Bill(
	Bill_id int PRIMARY KEY NOT NULL,
	Date datetime NOT NULL,
	PaymentMethod varchar(20) NOT NULL,
	ReservationID int NOT NULL,
	EmployeeID int NOT NULL,
	price int,
	FOREIGN KEY(EmployeeID)REFERENCES Employees(EmployeeID),
	FOREIGN KEY(ReservationID)REFERENCES Reservation(ReservationID));
	
	CREATE TABLE Guest (
    GuestID int PRIMARY KEY NOT NULL,
	FirstName varchar(20) NOT NULL,
    LastName varchar(20) NOT NULL,
    Email varchar(50),
	Nationality varchar(10),
	Address varchar(50),
	Phone varchar(20),
	Age int,
	Job varchar(20),
	PassPortNumber int NOT NULL);
	
	
	  insert into Guest
	  ([GuestID],[FirstName],[LastName],[Email],[Nationality],[Phone],[Age],[Job],[PassPortNumber])
	  values
(1,'mohamed','eldeeb','wiliamdavid504@yahoo.com','egyption','01027942396',21,'student',38473),
(2,'Abdelaziz','abuzaid','abdo504@yahoo.com','egyption','0122749957359',21,'student',384),
(3,'hasen','dmmdmdm','hasen504@yahoo.com','egyption','102385739535',21,'student',38204);

select * from Guest;

Comments

Please sign up or log in to contribute to the discussion.