Conclusion
The ability to search about specific information or detail before and after doing hotel reservation or to organize hotel's room in easy way with customizing customer detail and checking the valid credit card are some purpose to build system which makes dealing with these requirement possible with easy and fast way. Hotel management system is built to find suitable solution for reservation and customization of rooms, customers, payments and credit cards. This system deal with the database as a end back which based on Oracle and its interface based on ASP and Visual basic. The interface aims to make reservation and using other tools easy to every one without needing to learn how to use. The Visual basic has the responsibility of checking valid credit card, exist of customer ID , finding the available room .
Appendix A
CODES
8.1 VB And ASP
8.1.1 The code for the text box:
<asp:TextBoxID="TextBox5"runat="server">
8.1.2 Connection between the web and database:
Dim Conn As New OleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=localhost;User
ID=HR;Password=amanigeama;Unicode=True")
8.1.3 Insertion from the web to the database
Dim Conn As New OleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=localhost;User
ID=HR;Password=amanigeama;Unicode=True")
Dim MyCmd As New OleDbCommand Conn.Open()
Dim InsertCommand As New OleDbCommand Dim CMD As New OleDbCommand
CMD.CommandType = CommandType.Text
CMD.CommandText = "SELECT RCID FROM RoomC WHERE RCID = '"& TextBox2.Text &"'" Dim reader As OleDbDataReader
reader = CMD.ExecuteReader If reader.Read Then
InsertCommand.Connection = Conn
InsertCommand.CommandType = CommandType.Text
InsertCommand.CommandText = "INSERT INTO RoomS(RID,RCID) VALUES(?,?)" InsertCommand.Parameters.Clear() InsertCommand.Parameters.AddWithValue("?", TextBox1.Text) InsertCommand.Parameters.AddWithValue("?", TextBox2.Text) InsertCommand.ExecuteNonQuery() InsertCommand.Dispose() Conn.Close() MsgBox(" OK ", MsgBoxStyle.Information) ElseIf Not reader.Read Then
MsgBox(" The room category not exist in Room Category table ", MsgBoxStyle.Information) End If
8.1.4 Deleting from the database by using the web
Dim Conn As New OleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=localhost;User
ID=HR;Password=amanigeama;Unicode=True")
Conn.Open()
Dim DeleteCommand As New OleDbCommand DeleteCommand.Connection = Conn
Dim CMD As New OleDbCommand CMD.Connection = Conn
CMD.CommandType = CommandType.Text
CMD.CommandText = "SELECT RID FROM RoomS WHERE RID = '"& TextBox1.Text &"'" Dim reader As OleDbDataReader
reader = CMD.ExecuteReader If reader.Read Then
DeleteCommand.CommandType = CommandType.Text
DeleteCommand.CommandText = " DELETE FROM RoomS WHERE RID = ? " DeleteCommand.Parameters.Clear() DeleteCommand.Parameters.AddWithValue("?", TextBox1.Text) DeleteCommand.ExecuteNonQuery() DeleteCommand.Dispose() Conn.Close() MsgBox(" OK ", MsgBoxStyle.Information) ElseIf Not reader.Read Then
End If
8.1.5 Updating the data
Dim Conn AsNewOleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=localhost;User ID=HR;Password=amanigeama;Unicode=True")
Conn.Open()
Dim UpdateCommand As New OleDbCommand UpdateCommand.Connection = Conn
Dim CMD As New OleDbCommand CMD.Connection = Conn
CMD.CommandType = CommandType.Text
CMD.CommandText = "SELECT RCID FROM RoomC WHERE RCID = '"& TextBox2.Text &"'" Dim reader As OleDbDataReader
reader = CMD.ExecuteReader If reader.Read Then
UpdateCommand.CommandType = CommandType.Text
UpdateCommand.CommandText = "UPDATE RoomS set RCID=? WHERE RID =? " UpdateCommand.Parameters.Clear()
UpdateCommand.Parameters.AddWithValue("?", TextBox2.Text) UpdateCommand.Parameters.AddWithValue("?", TextBox1.Text) UpdateCommand.ExecuteNonQuery()
Conn.Close()
MsgBox(" OK ", MsgBoxStyle.Information) ElseIf Not reader.Read Then
MsgBox(" The room category does not exist in Room Category table ", MsgBoxStyle.Information) End If
8.1.6 Adding the Calendar Extender
For Adding calendar extender, the toolkitScriptManager has to be added. Then the colander drags from the Ajax toolbox:
<asp:ToolkitScriptManagerID="ToolkitScriptManager1" runat="server"> </asp:ToolkitScriptManager>
<asp:TextBoxID="txtStartDate"runat="server"></asp:TextBox>
<asp:CalendarExtenderID="CalendarExtender1" TargetControlID="txtStartDate" runat="server" Format="yyyy/MM/dd" OnClientDateSelectionChanged="checkDate" />
8.1.7 Transfer value form page to another page session that is used for saving the value of the text box:
Session("field88") = txtStartDate.Text Session("field99") = TextEndDate.Text
8.1.8 Search Code
This following code is for doing the search for the available room:
Dim Conn As New OleDbConnection("Provider=OraOLEDB.Oracle.1;Data Source=localhost;User
Dim MyCmd As New OleDbCommand Dim myDataSet As New DataSet myDataSet.Clear()
Conn.Open()
MyCmd.Connection = Conn
MyCmd.CommandType = CommandType.Text
Dim currentDate As Date
currentDate = System.DateTime.Now
If txtStartDate.Text<TextEndDate.Text Then
If System.DateTime.Now<txtStartDate.Text Or System.DateTime.Now<TextEndDate.Text Then If DropDownList2.Items(0).Selected = True Then
If DropDownList1.Items(0).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(1).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.category = '"& DropDownList1.Items(1).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(2).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.category = '"& DropDownList1.Items(2).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(3).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.category = '"& DropDownList1.Items(3).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(4).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.category = '"& DropDownList1.Items(4).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(5).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.category = '"& DropDownList1.Items(5).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
End If
ElseIf DropDownList2.Items(1).Selected = True Then If DropDownList1.Items(0).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(1).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(1).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(1).Value
&"'AND RC.category = '"& DropDownList1.Items(1).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(2).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(1).Value
&"' AND RC.category = '"& DropDownList1.Items(2).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(3).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview
FROM RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"&
RS.ridNOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
ElseIf DropDownList1.Items(4).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(1).Value
&"' AND RC.category = '"& DropDownList1.Items(4).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(5).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(1).Value
&"' AND RC.category = '"& DropDownList1.Items(5).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
End If
ElseIf DropDownList2.Items(2).Selected = True Then If DropDownList1.Items(0).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND '"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"'AND RC.category = '"& DropDownList1.Items(1).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(2).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"' AND RC.category = '"& DropDownList1.Items(2).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(3).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"' AND RC.category = '"& DropDownList1.Items(3).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(4).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"' AND RC.category = '"& DropDownList1.Items(4).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)" ElseIf DropDownList1.Items(5).Selected = True Then
MyCmd.CommandText = "Select RS.rid, RC.RCID, RC.Category, RC.Rprice, RC.RFloor, RC.Rview FROM
RoomC RC , RoomS RS WHERE RC.RCID = RS.RCID AND RC.Rview = '"& DropDownList2.Items(2).Value
&"' AND RC.category = '"& DropDownList1.Items(5).Value &"' AND RS.rid NOT IN (SELECT RS.rid FROM RoomC RC, BOOKINGDETAIL BD WHERE ( '"&txtStartDate.Text&"' <= BD.CheckIn AND
'"&TextEndDate.Text&"' >= BD.CheckOut) AND RS.rid = BD.rid)"
End If End If MyCmd.Parameters.Clear() MyCmd.Parameters.AddWithValue("?", txtStartDate.Text) MyCmd.Parameters.AddWithValue("?", TextEndDate.Text)
Dim adapter As New OleDb.OleDbDataAdapter(MyCmd.CommandText, Conn) adapter.Fill(myDataSet, "0")
GridView1.DataSource = myDataSet.Tables("0") GridView1.DataBind()
Else
MsgBox(" you have chose a previous data") End If
MsgBox(" the checkin data is bigger than the check out date") End If Conn.Close() 8.1.9 Log in code
<asp:LoginViewID="LoginView1" runat="server"> <LoggedInTemplate>
<spanclass="input">You are logged in. Welcome </span> <asp:LoginNameID="LoginName1" runat="server" /> </LoggedInTemplate>
<AnonymousTemplate>
You are not logged in. Click the Login link to sign in.
<asp:ButtonID="button7" runat="server" onclick="button7_Click" Text="Reminder" />
</AnonymousTemplate> </asp:LoginView> <br />
<asp:LoginStatusID="LoginStatus1" runat="server" />
8.1.10 Checking exit of the Customer ID Code
Using reader to check if the customer ID is exit or not, before doing insert to the Guest detail. Dim CMD As New OleDbCommand
CMD.CommandType = CommandType.Text
CMD.CommandText = "SELECT CID FROM CUSTM WHERE CID = '"& TextBox7.Text &"'" Dim reader As OleDbDataReader
reader = CMD.ExecuteReader
'INSERT INTO THE CUSTOMER TABLE
If Not reader.Read Then
'INSERT INTO CUTOMER TABLE Dim InsertCUST As New OleDbCommand InsertCUST.Connection = Conn
InsertCUST.CommandType = CommandType.Text
InsertCUST.CommandText = "INSERT INTO CUSTM(CID,CFirstName,CLastName,Address,PhoneNUM,Email) VALUES(?,?,?,?,?,?)" InsertCUST.Parameters.Clear() InsertCUST.Parameters.AddWithValue("?", TextBox7.Text) InsertCUST.Parameters.AddWithValue("?", TextBox2.Text) InsertCUST.Parameters.AddWithValue("?", TextBox3.Text) InsertCUST.Parameters.AddWithValue("?", TextBox4.Text) InsertCUST.Parameters.AddWithValue("?", TextBox6.Text) InsertCUST.Parameters.AddWithValue("?", TextBox5.Text) InsertCUST.ExecuteNonQuery()
InsertCUST.Dispose() End If reader.Close() MsgBox(" OK ", MsgBoxStyle.Information)
8.1.11 Calculating the total cost Dim day As Int32
Dim chi As Date Dim cho As Date Dim price As Int16
price = 0
chi = CheckIn.Text cho = Checkout.Text
day = DateDiff(DateInterval.Day, chi, cho) Dim RomPrice As New OleDbCommand
RomPrice.Connection = Conn
RomPrice.CommandType = CommandType.Text
RomPrice.CommandText = "SELECT Rprice from RoomC WHERE RCID = '"& TextBox19.Text &"'" Dim Room As New OleDb.OleDbDataAdapter(RomPrice.CommandText, Conn)
price = myDataSet.Tables("R1").Rows(0).Item("Rprice") price = price * day
8.2 Database codes
8.2.1 Tables
8.2.1.1CUSTOMER TALBE
CREATE TABLE CUSTM (
CID VARCHAR(25) NOT NULL, CFirstName VARCHAR(120) NOT NULL, CLastName VARCHAR(120) NOT NULL, Address VARCHAR(255),
GENDER CHAR(2),
PhoneNUM VARCHAR(15),
Email VARCHAR(25) NOT NULL,
-- updata to set a city in this table CONSTRAINT CUST_ID_PK PRIMARY KEY ( CID ),
CONSTRAINT CUST_ID_NN CHECK ( "CID" IS NOT NULL ),
--Foreign Key (IDNUM) REFERENCES IdentityCustomer( IDNUM ) );
ALTER TABLE CUSTM ADD CONSTRAINT CUST_GENDER CHECK (Gender = 'M' OR Gender = 'F' )
8.2.1.2 ROOM Category TALBE
CREATE TABLE RoomC (
RCID CHAR(5) NOT NULL, Category VARCHAR(30), Rprice NUMBER,
RFloor VARCHAR(15), Rview VARCHAR(15),
CONSTRAINT ROOM_CATE_ID_PK PRIMARY KEY ( RCID ),
CONSTRAINT ROOM_CATE_ID_NN CHECK ( "RCID" IS NOT NULL )
);
8.2.1.3 ROOMs TALBE CREATE TABLE RoomS (
RID CHAR(6) NOT NULL,
RCID CHAR(5) NOT NULL, -- we have to put 5 here because the R are 5
CONSTRAINT ROOM_STAT_ID_PK PRIMARY KEY ( RID ),
CONSTRAINT ROOM_STAT_ID_NN CHECK ( "RID" IS NOT NULL ),
CONSTRAINT ROOM_STAT_CATE_ID_NN CHECK ( "RCID" IS NOT NULL )
);
ALTER TABLE RoomS ADD CONSTRAINT ROOM_STAT_CATE_ID_FN FOREIGN KEY ( RCID )
REFERENCES RoomC( RCID )
;
8.2.1.4 BOOKING TALBE
CREATE TABLE Booking (
BID VARCHAR(10) NOT NULL, CheckIn DATE NOT NULL,
CheckOut DATE NOT NULL, TotalRoom NUMBER,
TotalPrice NUMBER,
CID VARCHAR(20) NOT NULL,
PayStatueVARCHAR(15) DEFAULT 'Not Payed' NOT NULL,
CONSTRAINT B_ID_PK PRIMARY KEY (BID),
CONSTRAINT B_ID_NN CHECK ( "BID" IS NOT NULL ),
CONSTRAINT B_CHECKIN_NN CHECK ( "CheckIn" IS NOT NULL ),
CONSTRAINT B_CheckOut_NN CHECK ( "CheckOut" IS NOT NULL ),
CONSTRAINT B_CID_NN CHECK ( "CID" IS NOT NULL ),
CONSTRAINT B_PayStatue _NN CHECK ( "PayStatue" IS NOT NULL )
);
8.2.1.5 BOOKINGDETAIL TALBE
CREATE TABLE BOOKINGDETAIL ( RBID VARCHAR(10) NOT NULL, BID VARCHAR(10) NOT NULL, RID CHAR(6) NOT NULL, RCID CHAR(5) NOT NULL, CheckIn DATE NOT NULL, CheckOut DATE NOT NULL, TotalDays NUMBER,
TotalPrice NUMBER,
CONSTRAINT ROOM_BOOK_ID_PK PRIMARY KEY ( RBID),
CONSTRAINT ROOM_BOOK_ID_NN CHECK ("RBID" IS NOT NULL ),
CONSTRAINT BOOK_RooM_ID_NN CHECK ( "BID" IS NOT NULL ),
CHECK ( "RID" IS NOT NULL ),
CONSTRAINT ROOM_CET_BOOK_ID_NN CHECK ( "RCID" IS NOT NULL )
);
ALTER TABLE BOOKINGDETAIL ADD CONSTRAINT R_BOOING_ID_FN FOREIGN KEY ( BID )
REFERENCES Booking( BID ) ;
ALTER TABLE BOOKINGDETAIL ADD CONSTRAINT R_BOOK_ROOM_FN FOREIGN KEY ( RID )
REFERENCES RoomS( RID ) ;
ALTER TABLE BOOKINGDETAIL ADD CONSTRAINT R_CET_BOOK_ROOM_FN FOREIGN KEY ( RCID )
REFERENCES RoomC( RCID ) ;
8.2.1.6 CreditCard TALBE
CREATE TABLE CreditCard (
CreditCardNo VARCHAR(25) NOT NULL, CardName VARCHAR(20) NOT NULL, CardExpir VARCHAR(20) NOT NULL,
CONSTRAINT CREADIT_CARD_ID_PK PRIMARY KEY (CreditCardNo),
CONSTRAINT CREADIT_CARD_ID_NN CHECK ( CreditCardNo IS NOT NULL ),
CONSTRAINT CREADIT_CardName_NN CHECK ( CardName IS NOT NULL ),
CONSTRAINT CREADIT_CardExpir_NN CHECK ( CardExpir IS NOT NULL )
);
8.2.1.7 Payment TALBE
CREATE TABLE Payment (
PaymentID CHAR(7) NOT NULL, PaymentDate DATE NOT NULL, TotalPrice NUMBER,
PaymentMode VARCHAR(20) NOT NULL, BID VARCHAR(10) NOT NULL, CreditCardNo VARCHAR(25) NOT NULL,
CONSTRAINT PAYMENT_ID_PK PRIMARY KEY (PaymentID),
CONSTRAINT PAYMENT_ID_NN CHECK ( PaymentID IS NOT NULL ),
CONSTRAINT PAYMENT_PAY_MOD_NN CHECK ( "PMID" IS NOT NULL ),
CONSTRAINT PAY_CREADIT_CARD_NO_NN CHECK ( CreditCardNo IS NOT NULL ),
CONSTRAINT PAY_BID_NN CHECK ( BID IS NOT NULL ),
CONSTRAINT PAY_PaymentDate_NN CHECK ( PaymentDate IS NOT NULL )
);
ALTER TABLE Pyment ADD CONSTRAINT PAY_BOOKINGID_FN FOREIGN KEY ( BID )
REFERENCES Booking( BID )
;
ALTER TABLE Pyment ADD CONSTRAINT PAY_CREDIT_NO_FN FOREIGN KEY ( CreditCardNo )
REFERENCES CreditCard( CreditCardNo )
8.2.2 Insertion into table
8.2.2.1 Some of insertion into Room category
Insert INTO RoomC( RCID , Category , Rprice , RFloor , Rview)
VALUES ( 'RC11' , 'Standard Room' , 110 , 'First Floor' , 'Sea')
;
Insert INTO RoomC
VALUES ( 'RC12' , 'Superior Room' , 130 , 'First Floor' , 'Sea' )
;
Insert INTO RoomC
VALUES ( 'RC13' , 'Standard Room' , 100 , 'First Floor' , 'PMU' )
;
Insert INTO RoomC
VALUES ( 'RC14' , 'Superior Room' , 120 , 'First Floor' , 'PMU' )
;
Insert INTO RoomC
VALUES ( 'RC21' , 'Standard Room' , 120 , 'Secon Floor' , 'Sea' )
;
VALUES ( 'RC22' , 'Standard Room' , 110 , 'Secon Floor' , 'PMU' )
;
Insert INTO RoomC
VALUES ( 'RC23' , 'Superior Room' , 130 , 'Secon Floor' , 'Sea' )
;
Insert INTO RoomC
VALUES ( 'RC24' , 'Superior Room' , 120 , 'Secon Floor' , 'PMU' )
;
Insert INTO RoomC
VALUES ( 'RC25' , 'Deluxe Room' , 150 , 'Secon Floor' , 'Sea' )
;
Insert INTO RoomC
8.2.2.2 Some of insertion into RoomS
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R111' , 'RC11' )
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R110' , 'RC12')
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R120' , 'RC13')
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R130' , 'RC14')
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R140' , 'RC14')
;
Insert INTO RoomS (RID ,RCID)
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R160' , 'RC11' );
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R170' , 'RC11')
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R180' , 'RC13')
;
Insert INTO RoomS (RID ,RCID)
VALUES ( 'R190' , 'RC12' )
8.2.3 Trigger
8.2.3.1 Creating Sequence
CREATE SEQUENCE RB_ID MINVALUE 1
START WITH 1 INCREMENT BY 1 CACHE 333
CREATE SEQUENCE PM_ID MINVALUE 1
START WITH 1 INCREMENT BY 1 CACHE 333
8.2.3.2 Creating Trigger
create or replace trigger PAYMENT_trigger before insert on Pyment for each row
begin
if :new.PaymentID is null then
selectPM_ID.nextval into :new.PaymentID from dual; end if;
end;
before insert on Booking for each row begin
if :new.BID is null then
selectB_ID.nextval into :new.BID from dual; end if;
end;
create or replace trigger BD_trigger
before insert on BOOKINGDETAIL for each row begin
if :new.RBID is null then
selectRB_ID.nextval into :new.RBID from dual; end if;
end;
create or replace trigger BDID_trigger