Logical and Physical Design - Database creation (with SQL Scripts): DDL1.sql

--Villa(villa_id, villa_location, name, villa_view_type)
create table villa(
        villa_id integer primary key,
        villa_location varchar(255) not null,
        name varchar(255) not null,
        villa_view_type varchar(255) 
);



--Events(event_id, event_type, price, number_of_interested_guests)
create table events(
        event_id serial primary key,
        event_type varchar(255) not null,
        price float not null,
        number_of_interested_guests integer not null
);


--Activities(activity_id, activity_type, price, number_of_interested_guests, location)
create table activities(
        activity_id serial primary key,
        activity_type varchar(255) not null,
        price float not null,
        number_of_interested_guests integer not null,
        activity_location varchar(255) not null
);



--Rooms(room_id, room_type, price,availability, villa_id*)
create table rooms(
        room_id integer primary key,
        room_type varchar(255) not null,
        price integer not null,
        availability boolean not null,
        villa_id integer not null,
        constraint fk_rooms_villa foreign key(villa_id)
        references villa(villa_id)
);


--Beverage(beverage_id, beverage_type, price)
create table beverage(
        beverage_id integer primary key,
        beverage_type varchar(255) not null,
        price float not null
);

--Product(product_id, number_of_products, product_type)
create table product(
        product_id integer primary key, 
        number_of_products integer not null,
        product_type varchar(255) not null
);




--Meal(meal_id, price, meal_type)
create table meal(
        meal_id serial primary key,
        price float not null, 
        meal_type varchar(255) not null
);



--User_table(user_id, EMBG, address, email, username, password, full_name, phone_number)
create table user_table(
        user_id serial primary key,
        embg char(20) unique not null,
        address varchar(255) not null,
        email varchar(255) not null,
        password varchar(255) not null, 
        username varchar(255) not null, 
        full_name varchar(255) not null,
        phone_number varchar(255) not null
);


--Payment(payment_id, total_payment, pay_date, rec_id, customer_id, user_id*)
create table payment(
        payment_id serial primary key,
        total_payment float not null,
        pay_date date not null,
        rec_id varchar(255) not null,
        customer_id varchar(255) not null,
        user_id integer not null,
        constraint fk_payment_user foreign key (user_id)
        references user_table(user_id)
);


--Reservation(reservation_id, start_date, end_date, number_guests, adults, children, villa_id*, payment_id*, room_id*)
create table reservation(
        reservation_id serial primary key,
        start_date date not null,
        end_date date not null,
        number_guests integer not null,
        adults integer not null,
        children integer,
        villa_id integer not null,
        payment_id integer not null,
        room_id integer not null,       
        constraint fk_reservation_villa foreign key (villa_id)
        references villa(villa_id),
        constraint fk_reservation_payment foreign key (payment_id)
        references payment(payment_id),
        constraint fk_resrvation_room foreign key (room_id)
        references rooms(room_id)
);


--Log in(login_id, vreme_na_najava, user_id*)
create table log_in(
        login_id serial primary key,
        vreme_na_najava varchar(255) not null,
        user_id integer not null,
        constraint fk_login_user foreign key(user_id)
        references user_table(user_id)
);



--Service(service_id, service_type, price, user_id*, room_id*)
create table service(
        service_id serial primary key,
        service_type varchar(255) not null,
        price float not null,
        user_id integer not null,
        room_id integer not null,
        constraint fk_service_user foreign key(user_id)
        references user_table(user_id),
        constraint fk_service_rooms foreign key(room_id)
        references rooms(room_id)
);



--Waiter(user_id*, shift, salary)
create table waiter(
        user_id integer primary key,
        constraint fk_waiter_user foreign key(user_id)
                references user_table(user_id),
        shift integer not null,
        salary float not null
);


--Receptionist(user_id*, shift, salary)
create table receptionist(
        user_id integer primary key,
        constraint fk_receptionist_user foreign key(user_id)
                references user_table(user_id),
        shift integer not null,
        salary float not null
);


--Chef(user_id*, shift, salary)
create table chef(
        user_id integer primary key,
        constraint fk_chef_user foreign key(user_id)
                references user_table(user_id),
        shift integer not null,
        salary float not null
);


--Guests(user_id*, passport_number)
create table guests(
        user_id integer primary key,
        constraint fk_guests_user foreign key(user_id)
                references user_table(user_id),
        passport_number varchar(225) not null
);


--Prepared_Meal(prepared_meal_id, meal_id*,  user_id*)
create table prepared_meal(
        prepared_meal_id serial,
        meal_id integer not null,
        user_id integer not null,
        constraint fk_prepared_meal_meal foreign key(meal_id)
                references meal(meal_id),
        constraint fk_prepared_meal_user foreign key(user_id)
                references user_table(user_id),
        constraint pk_prepared_meal primary key(meal_id, prepared_meal_id)
);



--On_Site(payment_id*, currency, payment_type, user_id* )
create table on_site(
        payment_id integer primary key,
        constraint fk_onsite_payment foreign key(payment_id)
                references payment(payment_id),
        currency varchar(255) not null,
        payment_type varchar(255) not null,
        user_id integer not null,
        constraint fk_onsite_user foreign key(user_id)
                references user_table(user_id)
);

--Online(payment_id*,  card_number)
create table online(
        payment_id integer primary key,
        constraint fk_online_payment foreign key(payment_id)
                references payment(payment_id),
        card_number varchar(255) unique not null
);


--reservation_for_events (reservation_id*, event_id*)
create table  reservation_for_events(
        reservation_id integer,
        event_id integer,
        constraint pk_reservation_for_events primary key(reservation_id, event_id),
        constraint fk_reservation_for_events_r foreign key(reservation_id)
                references reservation(reservation_id),
        constraint fk_reservation_for_events_e foreign key(event_id)
                references events(event_id)
);

--reservation_for_activity(reservation_id*, activity_id*)
create table reservation_for_activity(
        reservation_id integer,
        activity_id integer,
        constraint pk_reservation_for_activity primary key(reservation_id, activity_id),
        constraint fk_reservation_for_activity_r foreign key(reservation_id)
                references reservation(reservation_id),
        constraint fk_reservation_for_activity_a foreign key(activity_id)
                references activities(activity_id)
);



--reservation_for_prepared_meal(reservation_id*, (prepared_meal_id*, meal_id*), quantity) 
create table reservation_for_prepared_meal(
        reservation_id integer,
        prepared_meal_id integer,
        meal_id integer,
        quantity integer not null
        constraint ck_quantity_gt_0 check (quantity >= 0),
        constraint pk_reservation_for_prepared_meal primary key(reservation_id, prepared_meal_id, meal_id),
        constraint fk_reservation_for_prepared_meal_r foreign key(reservation_id)
                references reservation(reservation_id),
        constraint fk_reservation_for_prepared_meal_pm foreign key(prepared_meal_id, meal_id)
                references prepared_meal(prepared_meal_id, meal_id)
);

--service_for_beverages(service_id*, beverage_id*, quantity) 
create table service_for_beverages(
        service_id integer,
        beverage_id integer,
        quantity integer not null
        constraint ck_quantity_gt_0 check (quantity >= 0),
        constraint pk_service_for_beverages primary key(service_id, beverage_id),
        constraint fk_service_for_beverages_s foreign key(service_id)
                references service(service_id),
        constraint fk_service_for_beverages_b foreign key(beverage_id)
                references beverage(beverage_id)
);

--meal_madeof_product(meal_id*,product_id* )
create table meal_made_of_product(
        meal_id integer,
        product_id integer,
        constraint pk_meal_madeof_product primary key(meal_id, product_id),
        constraint fk_meal_madeof_product_m foreign key(meal_id)
                references meal(meal_id),
        constraint fk_meal_madeof_product_p foreign key(product_id)
                references product(product_id)
);

-- guests_make_reservation(user_id*,reservation_id* )
create table guests_make_reservation(
        user_id integer,
        reservation_id integer,
        constraint pk_guests_make_reservation primary key(user_id,reservation_id),
        constraint fk_guests_make_reservation_u foreign key(user_id)
                references user_table(user_id),
        constraint fk_guests_make_reservation_r foreign key(reservation_id)
                references reservation(reservation_id)
);

--reservation_for_service(reservation_id*, service_id*)
create table reservation_for_service
(
        reservation_id integer,
        service_id integer,
        constraint pk_reservation_for_service primary key(reservation_id, service_id),
        constraint fk_reservation_for_service_s foreign key(service_id)
        references service(service_id),
        constraint fk_reservation_for_service_r foreign key(reservation_id)
        references reservation(reservation_id)
);