111 lines
2.8 KiB
SQL
111 lines
2.8 KiB
SQL
DROP TABLE BookingGuest;
|
|
DROP TABLE BookingRoom CASCADE CONSTRAINTS;
|
|
DROP TABLE Booking;
|
|
DROP TABLE CleaningClerk;
|
|
DROP TABLE CleaningPlan CASCADE CONSTRAINTS;
|
|
DROP TABLE Client;
|
|
DROP TABLE Clerk;
|
|
DROP TABLE Person;
|
|
DROP TABLE Room;
|
|
DROP TABLE Address;
|
|
DROP SEQUENCE address_seq;
|
|
DROP SEQUENCE room_seq;
|
|
DROP SEQUENCE person_seq;
|
|
DROP SEQUENCE cleaningPlan_seq;
|
|
DROP SEQUENCE booking_seq;
|
|
|
|
CREATE TABLE Address
|
|
(
|
|
addressId NUMBER PRIMARY KEY,
|
|
street VARCHAR2(50) NOT NULL,
|
|
houseNumber NUMBER NOT NULL,
|
|
postalCode NUMBER NOT NULL,
|
|
city VARCHAR2(50) NOT NULL,
|
|
country VARCHAR2(50) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE Room
|
|
(
|
|
roomNumber NUMBER PRIMARY KEY,
|
|
floor NUMBER NOT NULL,
|
|
roomTyp VARCHAR2(50) NOT NULL,
|
|
"size" NUMBER NOT NULL,
|
|
accessibility NUMBER NOT NULL,
|
|
beds NUMBER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE Person
|
|
(
|
|
personId NUMBER PRIMARY KEY,
|
|
name VARCHAR2(50) NOT NULL,
|
|
lastName VARCHAR2(50) NOT NULL,
|
|
age NUMBER NOT NULL,
|
|
addressId NUMBER NOT NULL,
|
|
FOREIGN KEY (addressId) REFERENCES Address(addressId)
|
|
);
|
|
|
|
CREATE TABLE Clerk
|
|
(
|
|
personId NUMBER PRIMARY KEY,
|
|
staff NUMBER NOT NULL UNIQUE,
|
|
salary NUMBER NOT NULL,
|
|
FOREIGN KEY (personId) REFERENCES Person(personId)
|
|
);
|
|
|
|
CREATE TABLE Client
|
|
(
|
|
personId NUMBER PRIMARY KEY,
|
|
bankDetails VARCHAR2(22) NOT NULL,
|
|
FOREIGN KEY (personId) REFERENCES Person(personId)
|
|
);
|
|
|
|
CREATE TABLE CleaningPlan
|
|
(
|
|
cleaningPlanId NUMBER PRIMARY KEY,
|
|
"date" DATE NOT NULL,
|
|
duration NUMBER NOT NULL,
|
|
roomNUMBER NUMBER NOT NULL,
|
|
FOREIGN KEY (roomNUMBER) REFERENCES ROOM(roomNumber)
|
|
);
|
|
|
|
CREATE TABLE CleaningClerk
|
|
(
|
|
clerkId NUMBER,
|
|
cleaningPlanId NUMBER,
|
|
PRIMARY KEY (clerkId,cleaningPlanId),
|
|
FOREIGN KEY (clerkId) REFERENCES Clerk(personId),
|
|
FOREIGN KEY (cleaningPlanId) REFERENCES CleaningPlan(cleaningPlanId)
|
|
);
|
|
|
|
CREATE TABLE Booking (
|
|
bookingId NUMBER PRIMARY KEY,
|
|
arrivalDate DATE NOT NULL,
|
|
departureDate DATE NULL,
|
|
cost NUMBER NULL,
|
|
pensionType VARCHAR2(50) NOT NULL,
|
|
lateCheckout NUMBER NULL,
|
|
clientId NUMBER NOT NULL,
|
|
FOREIGN KEY (clientId) REFERENCES Client(personId)
|
|
);
|
|
|
|
CREATE TABLE BookingGuest (
|
|
bookingId NUMBER NOT NULL,
|
|
personId NUMBER NOT NULL,
|
|
PRIMARY KEY(bookingId, personId),
|
|
FOREIGN KEY (bookingId) REFERENCES Booking(bookingId) ON DELETE CASCADE,
|
|
FOREIGN KEY (personId) REFERENCES Person(personId)
|
|
);
|
|
|
|
CREATE TABLE BookingRoom (
|
|
bookingId NUMBER NOT NULL,
|
|
roomNumber NUMBER NOT NULL,
|
|
PRIMARY KEY(bookingId, roomNumber),
|
|
FOREIGN KEY (bookingId) REFERENCES Booking(bookingId) ON DELETE CASCADE,
|
|
FOREIGN KEY (roomNumber) REFERENCES Room(roomNumber)
|
|
);
|
|
|
|
CREATE SEQUENCE address_seq;
|
|
CREATE SEQUENCE room_seq;
|
|
CREATE SEQUENCE person_seq;
|
|
CREATE SEQUENCE cleaningPlan_seq;
|
|
CREATE SEQUENCE booking_seq; |