Задания

, № группы

 

№ задания	1	2	3	ИТОГО
Максимальный балл	15	20	35	70
Результат

1. E/R диаграммы и отношения (15 баллов)

 

 

На приведенной диаграмме объектов-связей (E/R диаграмме) представлена информация о моделях пианино (Piano Model), производителях пианино (Manf), типах пианино (Piano Type). Тип характеризуется названием (Name) и размерами (Hight, Length, Width). Модели пианино подразделяются на традиционные и электронные (одна и та же модель может быть и традиционной и электронной, или только электронной, или только традиционной). Для традиционной модели указывается тип древесины, из которого эта модель изготовлена (Wood), для электронной – мощность (Power) и тип процессора (Processor).

а) (10 баллов) На основе приведенной диаграммы постройте реляционную (ые) модель (и). Не забудьте подчеркнуть ключи у каждого отношения.

1ый вариант модели:

Manf(Name, addr)

PianoType(Name, height, length, width)

PianoModel(ManfName, TypeName, price)

Electronic(ManfName, TypeName, power, processor)

Traditional(ManfName, TypeName, wood)

2ой вариант модели:

Manf(Name, addr)

PianoType(Name, height, length, width)

PianoModel(ManfName, TypeName, price, power, processor, wood)

б) (5 баллов) Учитывает ли приведенная диаграмма тот факт, что один и тот же производитель может выпускать 2 различные модели одного типа. Например, производитель S выпускает 2 модели типа BDP, которые отличаются друг от друга размерами и (или) ценой? Если да, то поясните как, если нет, то поясните, как изменить диаграмму, чтобы учесть этот факт.

Не учитывает, т. к. ключ сущности PianoModel, где должна храниться цена –

{ManfName, TypeName}. Чтобы учесть факт выпуска моделей одного типа, отличающихся ценой и (или) размерами, можно ввести суррогатный ключ, порядковый номер, в сущность PianoType. В этом случае наименование будет неключевым атрибутом.

2. Функциональные зависимости (20 баллов, по 5 баллов за каждый пункт задания):

Пусть есть отношение R(A, B,C, D,E) с функциональными зависимостями A -> B, B -> C, C -> A, D -> E и E -> D.

а) Определите ключи отношения R. {A,D}, {A,E}, {B,D}, {B,E}, {C,D}, {C,E}.

б) Сколько суперключей имеет отношение R? {A,D}, {A,E}, {B,D}, {B,E}, {C,D}, {C,E},
{A,B,D}, {A,C,D}, {A,D,E}, {A,B,E}, {A,C,E}, {B,D,E}, {B,C,D}, {C,D,E}, {B,C,E},
{A,B,C,D}, {A,B,D,E}, {A,C,D,E}, {A,B,C,E}, {B,C,D,E},
{A,B,C,D,E}.

в) Разобьем отношение R на 2 отношения R1(A, B) и R2(A, C, D, E). Определите, какие функциональные зависимости будут выполняться для отношений R1 и R2. В ответ включите функциональные зависимости с единственным атрибутом в левой части, из которых следуют все остальные зависимости.

Для R1(A, B): {B->A, A->B}

Для R2(A, C, D, E): {A->C, C->A, D->E, E->D}

г) Приведите пример отношения R, для которого выполняются исходные функциональные зависимости, но не выполняется многозначная зависимость AB ->> CD.

A	B	C	D	E
1	1	1	1	1
1	1	1	0	0

3. Реляционная алгебра и SQL (35 баллов, по 5 баллов за каждый пункт):

Пусть имеется база данных с отношениями:

Articles (ID, dateline, headline, author, text)

Keywords (ID, keyword)

Отношение Articles содержит информацию о новых статьях: уникальный ID, дату написания статьи (dateline), название (headline), ФИО автора (author) и текст статьи (text).

Отношение Keywords – это список терминов (ключевых слов) статьи. Это отношение содержит 0 или более терминов для каждой статьи.

а) Напишите на языке SQL-стандарт команду определения структуры отношения Articles, в которой учитывается следующее ограничение dateline, author->ID, т. е. ни один автор не может разместить сразу несколько статей в одну дату. Сформулируйте это ограничение, как ограничение CHECK уровня отношения.

SELECT. . .

CHECK(NOT EXISTS(

SELECT *

FROM Articles aa

WHERE dateline = aa. dateline AND author = aa. author

));

б) Напишите на языке SQL-стандарт запрос для поиска названий статей, в которых присутствует термин ‘SQL’.

SELECT headline

FROM Articles, Keywords

WHERE Articles. ID = Keywords. ID AND keyword = 'SQL';

в) Напишите на языке РЕЛЯЦИОННОЙ АЛГЕБРЫ запрос для поиска ID статей, для которых в базе данных есть, по крайней мере, 3 термина (ключевых слова).

pID(s k>=3 (gID, COUNT(keyword)->k (Keywords))).

г) Напишите на языке SQL-стандарт запрос для поиска ID тех статей, в тексте которых появляется, в любом контексте, слово “СУЩНОСТЬ”.

SELECT ID

FROM Articles

WHERE text LIKE '%СУЩНОСТЬ %';

д) Постройте представление, состоящее из 3 атрибутов (автор, термин, дата). Отберите из исходных отношений авторов, для которых какой-либо термин появляется более 3 раз в разных статьях. Включите в представление наименьшую из дат отобранных статей. Определение представления приведите на языке SQL.

CREATE VIEW AS

SELECT author, keyword, MIN(dateline)

FROM Articles, Keywords

WHERE Articles. ID = Keywords. ID

GROUP BY author, keyword

HAVING COUNT(*) >= 3;

е) Напишите на языке РЕЛЯЦИОННОЙ АЛГЕБРЫ запрос для получения ФИО авторов, в статьях которых не присутствует термин ”RELATION”.

p author

 

 

s keyword<>’relation’

 

Keywords Articles

ж) Напишите на языке SQL команду для изменения теста статьи на заголовок, если в столбце текст занесено значение NULL.

UPDATE Articles

SET text = headline

WHERE text IS NULL;