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

INSERT INTO  events(event_type, price, number_of_interested_guests) values
('Webinar', 200, 10),
('Birthday', 200, 15),
('Party', 300, 20),
('Conference', 350, 15),
('Team Building', 700, 25);

INSERT INTO  beverage(beverage_id, beverage_type, price) values
(1, 'homemade juice', 2),
(2, 'brandy glass', 2),
(3, 'wine glass', 2),
(4, 'wine bottle', 15),
(5, 'beer', 3);

INSERT INTO payment(total_payment, pay_date, rec_id, customer_id, user_id) values
(300, to_date('20220713','yyyymmdd'), '12345678910', '1234', 15),
(300, to_date('20220823','yyyymmdd'), '12345678910', '2345', 16),
(300, to_date('20220303','yyyymmdd'), '12345678910', '3456', 17),
(300, to_date('20221017','yyyymmdd'), '12345678910', '4567', 18),
(300, to_date('20220513','yyyymmdd'), '12345678910', '5678', 19);

INSERT INTO  prepared_meal(meal_id,user_id) values
(1, 15),
(2, 18),
(3, 18),
(4, 19),
(5, 16);

INSERT INTO log_in(vreme_na_najava, user_id) VALUES 
('15:45', 1),
('16:45', 2),
('17:45', 3),
('17:00', 4),
('18:00', 5),
('19:00', 6),
('20:00', 7),
('21:00', 8),
('22:00', 9),
('13:30', 10),
('14:30', 11),
('15:50', 12),
('16:50', 13),
('17:50', 14),
('15:15', 15),
('16:22', 16),
('17:17', 17),
('20:20', 18),
('21:21', 19);


insert into villa(villa_id,villa_location, name, villa_view_type) values
(1, 'Nikola Karev 12, Dihovo','Villa 1','Mountain View'),
(2, 'Nikola Karev 13, Dihovo','Villa 2','Garden View'),
(3, 'Nikola Karev 14, Dihovo','Villa 3','Mountain View'),
(4, 'Nikola Karev 16, Dihovo','Villa 4','Mountain View'),
(5, 'Nikola Karev 15, Dihovo','Villa 5','Garden View');


insert into rooms(room_id,room_type, price, availability, villa_id) values
(1,'Single', 25, True, 1),
(2,'Double', 40, True, 1),
(3,'Single', 25, True, 2),
(4,'Double', 40, True, 2),
(5,'Single', 25, True, 3),
(6,'Double', 40, True, 3),
(7,'Single', 25, True, 4),
(8,'Double', 40, True, 4),
(9,'Single', 25, True, 5),
(10,'Double', 40, True, 5);

insert into product(product_id,number_of_products, product_type) values
(1, 10, 'Apples'),
(2, 13, 'Potatoes'),
(3, 4, 'Cheese'),
(4, 30, 'Tortillas'),
(5, 10, 'Ham')
;

insert into reservation(start_date, end_date, number_guests, adults, children, villa_id, payment_id, room_id) values
(to_date('01012022','DDMMYYYY'),to_date('11012022','DDMMYYYY'), 2, 2, 0, 1, 7, 2),
(to_date('01022022','DDMMYYYY'),to_date('20022022','DDMMYYYY'), 1, 1, 0, 3, 10, 3),
(to_date('11112022','DDMMYYYY'),to_date('20112022','DDMMYYYY'), 2, 2, 0, 4, 6, 4),
(to_date('10082022','DDMMYYYY'),to_date('1508022','DDMMYYYY'), 2, 1, 1, 5, 8, 5),
(to_date('01122022','DDMMYYYY'),to_date('21122022','DDMMYYYY'), 2, 1, 1, 2, 9, 1)
;

insert into receptionist(user_id, shift, salary) values
(7, 1, 800),
(8, 2, 800),
(9, 3, 950),
(10, 1, 800),
(11, 3, 950)
;

insert into guests(user_id, passport_number) values
(15,'C1265083'),
(16,'C2889657'),
(17,'C1223435'),
(18,'C2335427'),
(19,'C2331109')
;

insert into online(payment_id, card_number) values 
(6, '2235668798762341'),
(7, '2235668798762541'),
(8, '9835668798762341')
;

insert into activities (activity_type, price, number_of_interested_guests, activity_location) values
('Aerobic', 25, 15, 'Dihovo'),
('Jogging', 5, 10, 'Baba Planina'),
('Bicycle riding', 15, 5, 'Baba Planina'),
('Yoga', 25, 7, 'Dihovo' ),
('Hiking', 25, 12, 'Baba Planina');
 
 insert into meal (price, meal_type) values
( 5, 'Polneti piperki'),
( 5, 'Tavche gravche'),
( 5, 'Sarmi'),
( 55, 'Musaka' ),
( 3, 'Shopska Salata');
 
 insert into user_table (EMBG, address, email,username, password, full_name ,phone_number) values
( '223757757768', 'Gjorgji Zekovski 29, Skopje', 'matea@yahoo.com','Matea_Cvetkovska','12345','Matea Cvetkovska','075/984-518'),
( '053758555669', 'Nikola Karev 39, Skopje', 'elena@gmail.com','Elena_Shulevska', '12345','Elena Shulevska', '076/717-525'),
( '273758555660', 'Venjamin Macukovski 40, Ohrid', 'jovana@yahoo.com','Jovana_Serdarska', '12345', 'Jovana Serdarska', '077/235-515'),
( '253744455661', 'Kuzman Josifovski Pitu 32, Bitola', 'mihail@hotmail.com','Mihajlo_Ristevski', '12345', 'Mihajlo Ristevski', '071/437-526' ),
( '213758925666', 'Partizanska 46 , Kumanovo', 'jovan@yahoo.com', 'Jovana_Cvetkovska','12345', 'Jovana Cvetkovska', '075/984-444'),
( '123456788888', 'Belasica 23, Skopje', 'violeta@gmail.com', 'Violeta_Shulevska', '12345', 'Violeta Shulevska', '076/720-000'),
( '123456787777', 'Dimce Karevski 24, Ohrid', 'silvana@yahoo.com', 'Silvana_Trajkovska', '12345', 'Silvana Trajkovska', '077/235-111'),
( '123456784444', 'Dimce Acev 15, Bitola', 'filip@hotmail.com','Filip_Trajkovik', '12345', 'Filip Trajkovik','071/437-222' ),
( '123456785555', 'Partizanska 50 , Kumanovo', 'petar@yahoo.com','Petar_Stojanovik', '12345', 'Petar Stojanovik', '072/996-333'),
( '123456711111', 'Jane Sandanski 29, Skopje', 'angel@yahoo.com', 'Angel_Nasev', '12345', 'Angel Nasev', '075/984-444'),
( '123456722222', 'Nikola Karev 40, Prilep', 'katerina@gmail.com','Katerina_Trajcevska', '12345','Katerina Trajcevska', '076/717-555'),
( '273755666666', 'Kukuska 2, Shtip', 'stefan@yahoo.com','Stefan_Andonov', '12345','Stefan Andonov', '077/235-666'),
( '253744777777', 'Lazo Popovski 32, Bitola', 'evgenija@hotmail.com','Evgenija_Shulevska', '12345','Evgenija Shulevska', '071/437-700' ),
( '213758212121', 'Mavrvka 46 , Kavadarci', 'aleksandra@yahoo.com', 'Aleksandra_Cvetkovska', '12345','Aleksandra Cvetkovska', '072/996-800'),
( '223757232323', 'Mara Josifovska 29, Veles', 'mihajlo@yahoo.com','Mihajlo_Cvetkovski', '12345', 'Mihajlo Cvetkovski', '075/984-801'),
( '053758524242', 'Margara 39, Kocani', 'dzaner@gmail.com','Dzaner_Ramadan', '12345', 'Dzaner Ramadan', '076/717-802'),
( '273758565656', 'Mariovska 40, Kocani', 'boris@yahoo.com','Boris_Panevski', '12345','Boris Panevski','077/235-515'),
( '253744457474', 'Mende Ivanovski  32, Kocani', 'denis@hotmail.com','Deniz_Gadza', '12345','Denis Gadza', '071/437-803' ),
( '213758921212', 'Vasko Karangelevski 46 , Gevgelija', 'pavlina@yahoo.com','Pavlina_Vasilevska', '12345','Pavlina Vasilevska', '072/996-806');


 insert into waiter (user_id, shift, salary) values
(1,1,500),
(2,1,500),
(3,2,600),
(4,2,600),
(5,3,700),
(6,3,700);

 insert into chef (user_id, shift, salary) values
(12,1,1000),
(13,2,1500),
(14,3,1200);

insert into on_site(payment_id, currency, payment_type, user_id) values
(9,'euros', 'card', 15),
(10,'euros','cash',16);


insert into service(service_type, price, user_id, room_id) values
('Room service', 15, 1, 5),
('Pool service', 20, 2, 6),
('Masage service', 10, 5, 7),
('Self service', 0 , 4, 8),
('Catering service',20, 3, 9);

insert into reservation_for_activity(reservation_id, activity_id) values
(6,5),
(7,4),
(8,3),
(9,2),
(10,1);

insert into reservation_for_events(reservation_id, event_id) values
(6,5),
(7,4),
(7,5),
(8,2),
(9,1);

insert into reservation_for_prepared_meal(reservation_id, prepared_meal_id, meal_id, quantity) values
(6,6,1,1),
(7,7,2,3),
(8,8,3,2),
(10,9,4,5),
(9,10,5,1);

insert into service_for_beverages(service_id, beverage_id, quantity) values
(1,4,2),
(2,5,3),
(1,2,2),
(3,3,3),
(4,4,4);

insert into meal_made_of_product(meal_id, product_id) values
(1,2),
(2,5),
(3,5),
(4,2),
(5,3);

insert into guests_make_reservation(user_id, reservation_id) values
(15,6),
(16,7),
(17,8),
(18,9),
(19,10);

insert into reservation_for_service values
(6,1),
(7,2),
(8,3),
(9,4),
(10,5);