create database BarberShop
go
use Barbershop
go

create table barbers(
id int identity primary key,
f_name nvarchar(50),
l_name nvarchar(50),
gender nvarchar(10),
phone nvarchar(20),
email nvarchar(50),
birth_date date,
hire_date date,
position_id int)
go
insert barbers(f_name,l_name,gender,phone,email,birth_date,hire_date,position_id)values
(N'Jack',N'Black',N'male',N'099-045-36-23',N'black@gmail.com','1988-12-01','2018-11-25',2),
(N'Black',N'Jack',N'male',N'095-347-36-89',N'jack@gmail.com','1987-05-25','2017-03-10',1),
(N'James',N'Bond',N'male',N'068-032-87-44',N'bond@gmail.com','1991-06-19','2019-03-14',2),
(N'Julia',N'Simon',N'female',N'066-045-55-33',N'julia@gmail.com','1999-09-22','2020-09-11',3),
(N'Jim',N'Beam',N'male',N'067-789-12-65',N'jim@gmail.com','1995-08-12','2020-08-25',3)
go
create table positions(
id int identity primary key,
[name] nvarchar(100))
go
insert positions([name])values
(N'Chief'),(N'Senior'),(N'Junior')
go
create table [services](
id int identity primary key,
[name] nvarchar(50),
price money,
duration time)
go
insert [services]([name],price,duration)values
(N'Beard trim',10,'00:25:00'),(N'Haircut',15,'00:30:00'),
(N'Mustash cut',5,'00:10:00'),(N'Straight razor shaving',8,'00:10:00'),
(N'Gray camuffling',7,'00:15:00'),(N'Haircut for children',10,'00:25:00'),
(N'Haircut by clipper',8,'00:15:00')
go
create table barber_service(
barber_id int,
service_id int)
go
insert barber_service(barber_id,service_id)values
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,1),
(2,2),(2,3),(2,4),(2,5),(2,6),(2,7),(3,1),(3,2),
(3,4),(3,5),(3,6),(4,2),(4,5),(4,6),(4,7),(5,3),
(5,5),(5,6),(5,7)
go
create table feedbacks(
id int identity primary key,
client_id int,
barber_id int,
[description] nvarchar(max),
mark_id int,
[feedback_date] date)
go
insert feedbacks(client_id,barber_id,[description],mark_id,[feedback_date])values
(1,2,N'The best barber ever',5,'2019-09-06'),(3,1,N'No comments',4,'2019-12-27'),
(5,3,N'I like it',4,'2020-02-08'),(6,4,N'Not bad for junior',5,'2020-10-07'),
(8,5,N'It is awfull',1,'2020-11-21'),(2,3,N'No comments',2,'2020-05-19'),
(7,2,N'No comments',3,'2020-11-11'),(4,5,N'Not bad',4,'2020-09-24'),
(9,3,N'Awesome',5,'2020-10-17'),(10,4,N'No comments',3,'2020-11-27')
go
create table marks
(
id int identity primary key,
[name] nvarchar(50))
go
insert marks([name])values
(N'Very bad'),(N'Bad'),(N'Fine'),(N'Good'),(N'Excellent')
go
create table clients(
id int identity primary key,
f_name nvarchar(50),
l_name nvarchar(50),
phone nvarchar(20),
email nvarchar(50))
go
insert clients(f_name,l_name,phone,email)values
(N'Austin',N'Norton',N'050-562-23-56',N'austin@gmail.com'),
(N'Mark',N'Campbell',N'050-896-54-78',N'mark@gmail.com'),
(N'Edward',N'Green',N'050-456-98-15',N'edward@yahoo.com'),
(N'Nick',N'Fox',N'050-562-23-56',N'austin@gmail.com'),
(N'Peter',N'Kennedy',N'098-268-43-92',N'peter@gmail.com'),
(N'Brad',N'Pitt',N'067-562-23-56',N'brad@yahoo.com'),
(N'Justin',N'Sutton',N'050-562-25-56',N'justin@gmail.com'),
(N'John',N'Rich',N'095-256-56-56',N'rich@gmail.com'),
(N'Arthur',N'Bradley',N'066-089-45-37',N'arthur@yahoo.com'),
(N'Leslie',N'Mug',N'097-852-91-73',N'leslie@gmail.com')
go
create table visits(
id int identity primary key,
client_id int,
barber_id int,
visit_date date,
visit_cost money,
feedback_id int)
go
insert visits(client_id,barber_id,visit_date,visit_cost,feedback_id)values
(1,2,'2019-09-06',15,1),(3,1,'2019-12-27',15,2),(5,3,'2020-02-08',15,3),
(6,4,'2020-10-07',10,4),(8,5,'2020-11-21',15,5),(2,3,'2020-05-19',15,6),
(7,2,'2020-11-11',30,7),(4,5,'2020-09-24',23,8),(9,3,'2020-10-17',15,9),
(10,4,'2020-11-27',22,10)
go
create table visit_service(
visit_id int,
service_id int)
go
insert visit_service(visit_id,service_id)values
(1,1),(1,3),(2,2),(3,4),(3,5),(4,6),(5,3),(5,6),
(6,2),(7,1),(7,2),(7,3),(8,3),(8,6),(8,7),(9,3),(9,6),
(10,2),(10,5)
go
create table week_days(
id int identity primary key,
[name] nvarchar(30))
go
insert week_days([name])values
(N'Sunday'),(N'Monday'),(N'Tuesday'),(N'Wednesday'),
(N'Thursday'),(N'Friday'),(N'Saturday')
go
create table schedule(
barber_id int not null,
week_day_id int not null,
start_time time,
end_time time)
go
insert schedule(barber_id,week_day_id,start_time,end_time)values
(1,2,'09:00','17:00'),(1,4,'09:00','17:00'),(1,6,'10:00','16:00'),
(2,2,'09:00','17:00'),(2,3,'09:00','17:00'),(2,4,'09:00','17:00'),
(2,5,'09:00','17:00'),(2,6,'09:00','17:00'),(3,3,'09:00','17:00'),
(3,5,'09:00','17:00'),(3,7,'10:00','16:00'),(3,1,'10:00','16:00'),
(4,2,'12:00','20:00'),(4,4,'12:00','20:00'),(4,6,'12:00','20:00'),
(4,1,'12:00','20:00'),(5,3,'12:00','20:00'),(5,5,'12:00','20:00'),
(5,7,'12:00','20:00')
go
create table bookings(
id int identity primary key,
client_id int,
barber_id int,
week_day_id int,
start_time time)
go
insert bookings(client_id,barber_id,week_day_id,start_time)values
(1,1,2,'15:00'),(2,2,4,'14:00'),(3,3,7,'13:00'),(4,4,6,'17:00'),
(5,5,3,'18:00'),(6,1,4,'14:00'),(7,4,2,'17:30'),(8,3,5,'12:00'),
(9,5,5,'18:30'),(10,2,6,'15:00')
go
create table booking_service(
booking_id int,
service_id int)
go
insert booking_service(booking_id,service_id)values
(1,1),(1,3),(2,5),(3,6),(3,2),(4,6),(5,7),
(6,1),(6,2),(6,3),(6,5),(7,2),(7,5),(8,2),(8,4),(8,5),
(9,3),(9,5),(9,6),(10,1),(10,3),(10,7)
go
alter table barbers
add foreign key(position_id) references positions(id)
go 
alter table barber_service
add foreign key(barber_id) references barbers(id),
foreign key(service_id) references [services](id)
go
alter table schedule 
add primary key(barber_id, week_day_id),
foreign key(barber_id) references barbers(id),
foreign key(week_day_id) references week_days(id)
go
alter table feedbacks
add foreign key(client_id) references clients(id),
foreign key(barber_id) references barbers(id),
foreign key(mark_id) references marks(id) 
go
alter table visits
add foreign key(client_id) references clients(id),
foreign key(barber_id) references barbers(id),
foreign key(feedback_id) references feedbacks(id)
go
alter table visit_service
add foreign key(visit_id) references visits(id),
foreign key(service_id) references [services](id)
go
alter table bookings
add foreign key(client_id) references clients(id),
foreign key(barber_id) references barbers(id),
foreign key(week_day_id) references week_days(id)
go
alter table booking_service
add foreign key(booking_id) references bookings(id),
foreign key(service_id) references [services](id)
go