dbms_commands.txt

CREATE TABLE room (
	room_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
	room_no NUMBER NOT NULL, 
	capacity NUMBER NOT NULL, 
	status NUMBER NOT NULL, 
	price FLOAT NOT NULL,
	amenities VARCHAR2(526),
	room_type VARCHAR2(526) NOT NULL,
	floor_no NUMBER NOT NULL,
	room_view VARCHAR2(526),
	PRIMARY KEY (room_id)
);

CREATE TABLE reservation (
	reservation_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
	customer_id NUMBER NOT NULL,
	room_id NUMBER NOT NULL,
	check_in_date DATE NOT NULL,
	check_out_date DATE NOT NULl,
	no_adults NUMBER,
	no_children NUMBER,
	PRIMARY KEY (reservation_id),
	FOREIGN KEY (room_id) REFERENCES room(room_id) ON DELETE CASCADE,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE
);

CREATE TABLE services (
	service_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
	name VARCHAR2(100) NOT NULL,
	price FLOAT NOT NULL,
	reservation_id NUMBER NOT NULL,
	employee_id NUMBER,
	PRIMARY KEY (service_id),
	FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id) ON DELETE CASCADE
);

CREATE TABLE payment (
	payment_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
	customer_id NUMBER NOT NULL,
	reservation_id NUMBER NOT NULL,
	currency VARCHAR2(50) NOT NULL,
	method VARCHAR2(50) NOT NULL,
	amount FLOAT NOT NULL,
	PRIMARY KEY (payment_id),
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (reservation_id) REFERENCES reservation(reservation_id)
);

CREATE TABLE customer (
	customer_id  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
	first_name VARCHAR2(50) NOT NULL,
	last_name VARCHAR2(50) NOT NULL,
	email VARCHAR2(100) NOT NULL,
	address VARCHAR2(500) NOT NULL,
	id_type VARCHAR2(100) NOT NULL,
	id_number NUMBER NOT NULL,
	PRIMARY KEY (customer_id)
);

INSERT INTO customer (first_name, last_name, email, address, id_type, id_number) VALUES ('alan', 'alan', 'alan', 'alan', 'alan', 123);
INSERT INTO customer(first_name, last_name, email, address, id_type, id_number) VALUES ('Sujoy', 'Dchuna', 'sujoydchuna@gmail.com', 'golden nest', 'adhar', 420);