Галерия има нужда от създаване на база данни. За целта трябва да бъдат спазени следните изисквания:
Продуктите, с които галерията разполага са: картини, рамки, сувенири. Услугите, които галерията предлага са: монтаж на рамка за гоблен или за картина, рисуване на портрет с или без рамка. Галерията работи с много клиенти и с много художници. Сама изработва и монтира рамките. Ще трябва да пазите информация за клиентите на галерията- име, адрес и телефон, за художниците – същото. Също така ще трябва да пазите информация за наличните продукти на галерията – тип(1-картини, 2- рамки, 3- сувенири), уникален номер, цена, размери, година на производство, автор (ако е известен). Относно услугите, които тя предлага, ще трябва да записвате номер на поръчка, тип услуга(монтаж  на рамка– 1, рисуване – 2, 3 – продажба на артикул), от кой клиент е направена, дата на поръчката, крайна дата, готова или не, получена или не, коментар по поръчката, име на служител, който я е приел, художник, който ще я рисува, ако е портрет, номер на рамка, размери и цена в брой.

Задача 1:

На дадената ЕR диаграма няма нанесени кардиналности на връзките. Определете ги и ги нанесете. Определете внимателно кои са първични ключове за вашите таблици, кои са уникални ключове и подберете нужните ограничения. Напишете CREATE TABLE код.

Задача 2:

Изведете всички услуги, които е ползвал клиент на име “Ivan Ivanov”, но само такива, които са рисуване и са приключени(готови и предадени), както и имената на художниците, които са рисували портретите.

Задача 3:

Изведете имената на всички клиенти, общата сума на сметките, които са направили те в галерията, но само ако сумата им е по-голяма от средната сума от всички поръчки в галерията. Изведете само първите 6 записа, подредени по азбучен ред на имената им и само за приключени сметки(готови и предадени).

 

Решение:

Задача 1:

Съгласно така означените кардиналности на връзките, създаваме базата данни:

drop database if exists gallery;
create database gallery;
use gallery;
create table goods(
good_no int primary key auto_increment,
name varchar(100),
price decimal null default null,
size varchar(100) null default null,
type ENUM('1','2','3') not null,
year year null default null,
artist_id int null default null
)Engine = InnoDb;

create table person(
id int primary key auto_increment,
name varchar(255) not null,
address varchar(255),
phone varchar(15),
isArtist boolean default 0
)Engine = InnoDb;

ALTER table goods
add constraint foreign key (artist_id) references person(id);

create table services(
id int primary key auto_increment,
finalPrice decimal not null default 0,
type ENUM('1','2','3') not null default 3,
dateCreated datetime not null,
endDate datetime null default null,
comment varchar(255) null default null,
size varchar(200) null default null,
empl_name varchar(200) not null,
isReady boolean default 0,
isReceived boolean default 0,
good_id int null default null,
constraint foreign key (good_id) references goods(good_no),
customer_id int not null,
constraint foreign key (customer_id) references person(id),
artist_id int null default null,
constraint foreign key (artist_id) references person(id)
)Engine = InnoDb;

Не е необходимо, но с тестови цели вкарваме някаква информация в базата. Вижте тук:

INSERT INTO – код

insert into

Задача 2: За да изкарате и имена на хора, и имена на художници, ще трябва два пъти да присъедините таблицата person, тоест да направите SELF JOIN(един път като клиенти и втори път като художници):

use gallery;

SELECT  serv.*,
		customer.name as custName, 
		artist.name as artistName
FROM person as customer JOIN services as serv
ON serv.customer_id = customer.id
JOIN person AS artist
ON serv.artist_id = artist.id
where serv.type = 2
and customer.name = 'Ivan Ivanov'
AND serv.isReady = 1 
AND serv.isReceived = 1;

Задача 3: Абсолютно тривиална задача, при която трудността се свежда най-много до това да се сетите, че трябва да ползвате HAVING, за да филтрирате крайния резултат.

use gallery;

SELECT customer.name as CustomerName,
		SUM(serv.finalPrice) as sumByCust
from person as customer JOIN  services as serv
ON customer.id = serv.customer_id
where serv.isReady = 1
AND serv.isReceived = 1
group by serv.customer_id
having sumByCust > (SELECT AVG(finalPrice)
					from services)
order by CustomerName
limit 6;

Даниел Джолев