Avis notes

Thoughts, stories and ideas.

Заметки по книге - Системы баз данных Полный курс. Введение в системы баз данных

Sep 29, 2019

 book  database

 Toc
 Reference
 Related

ERWin - tutorial

Постановка задачи

В пункте обмена валют создается локальная информационная система, призванная автоматизировать процесс учета сделок купли – продажи валюты.
Создаваемая система должна обеспечивать ввод, хранение и поиск информации о сделках, совершенных в данном пункте обмена.

Каждой сделке присваивается уникальный цифровой код. Информация о сделке должна содержать сведения о дате и времени сделки, суммах покупаемой и продаваемой валют, фамилии, имени, отчестве и номере паспорта клиента, а так же о фамилии, инициалах и учетном номере личного дела кассира в отделе кадров.

Система должна позволять вычислить денежный оборот за один или несколько дней, а так же осуществлять поиск информации о сделках по номеру паспорта клиента.

Задача состоит в проектировании структуры баз данных разрабатываемой автоматизированной ИС

0) Кандидаты в сущности

  • Валюта
  • Сделка
  • Клиент
  • Кассир

1) Связи

Для создания связей между сущностями сначала составим описание данной предметной области при помощи ряда истинных высказываний на естественном языке:

  • Любой КЛИЕНТ должен совершить одну или несколько СДЕЛОК.
  • Каждую СДЕЛКУ должен совершить один КЛИЕНТ.
  • Любой КАССИР может обслуживать одну или несколько СДЕЛОК, но может не обслужить и ни одной.
  • Каждую СДЕЛКУ должен обслуживать только один КАССИР.
  • Любая ВАЛЮТА может покупаться и продаваться при разных СДЕЛКАХ.
  • При совершении СДЕЛКИ должна покупаться одна ВАЛЮТА и продаваться другая ВАЛЮТА.

Таким образом, сформируем имена связей:

  • КЛИЕНТ совершает СДЕЛКУ.
  • КАССИР обслуживает СДЕЛКУ.
  • ВАЛЮТА покупается при СДЕЛКЕ.
  • ВАЛЮТА продается при СДЕЛКЕ. Все четыре связи являются связями «один-ко-многим». Во всех четырех случаях сущность СДЕЛКА является дочерней

2) Атрибуты

СДЕЛКА:

  • Код сделки PK
  • дата и временя сделки
  • суммах покупаемой валюты
  • валют продаваемой валюты

КЛИЕНТ:

  • Номер клиента PK
  • фамилия
  • имя
  • отчество
  • номере паспорта AK

КАССИР

  • Номер кассира PK
  • фамилия
  • инициалы
  • учетный номер личного дела

ВАЛЮТА:

  • Код валюты PK
  • Название валюты

Лаба мифи

Некоторая торговая фирма “Ирис” осуществляет продажу товаров(E/2) по образцам. Покупателями(E/1) товаров(E/2) могут быть как частные(E/7), так и юридические(E/6) лица.

В штат торговой фирмы входят консультат, продавец, кассир и заведующий фирмой. Работа фирмы осущетвляется в соответствии со след схемой:

Консультат - отвечает за работу с покупателями(E/1) (юр.(E/6) и физ.(E/7) Лиц). Покупатель(E/1) обращается к менеджеру с целью оформить заказ(E/3) на приобретение товаров(E/2) из числа имеющихся в ассортименте. При первом обращении покупателя(E/1) консультант регистрирует его. При оформлении заказа(E/3) в нем указывается дата его оформления, а также перечисляются требуемые товары, кол-во и отпускная цена каждого товара(E/2). Консультатн примимает заказ без ограничений.

Продавец - отвечает за работу с товарами(E/2). Регистрация товара(E/2) осуществляется при каждом поступлении товара(E/2) на склад. При первом поступлении товара(E/2) заноситься вся необходимая информация о нем, при повторном - только корректируется кол-во товара на складе.

При поступлении товаров(E/2) продавец оформляет товарную накладную(E/6), в которой указывается кол-во поступившего товара и дата поступления, а также корректирует общее кол-во товаров на складе.

Продавец рассылает покупателям(E/1) в порядке поступления заказов(E/3) оповещения(E/4), в котором указывается крайний срок выкупа товара, и отмечает в заказе(E/3) дату отправки первого оповещения, а также корректирует кол-во товара на складе.

При обращению покупателя(E/1) по предъявленному оповещению(E/4), в котором есть отметка об оплате товара и номер заказа, по которому пришло оповещение(E/4), продавец отпускает товар(E/2), делая соответствующую отметку в заказе. Покупатель может отказаться от покупки, тогда продавец делает в заказе(E/3) соответствующую отметку в заказе. Кроме того, во всех заказах(E/3), для которых истек срок отпуска товара со склада, продавец делает отметку об аннулировании заказа. В этих случаях продавец корректирует кол-во товаров на складе.

Если поступило меньше товаров(E/2), чем требовалось, часть покупателей(E/1) остается неудовлетворенной: при этом, если товар(E/2) поступает в магазин повторно, или остается не проданный по заказам(E/3) с истекшим сроком, оповещение(E/4) посылается неудовлетворенным покупателям(E/1).

Кассир - отвечает за оформление оплаты товаров(E/2). Для оплаты товара(E/2), покупатель(E/1) предъявляет кассиру оповещение(E/4), в соответствии с которым определяется стоимость покупки. Оплата товара(E/2) оформляется, если не истек срок выкупа товара. В этом случае кассир оформляет документ об оплате(E/5) (приходный кассовый ордер для физ. или входящее банковское платежное поручение для юр. Лиц.) в котором указывается сумма оплаты. Кроме того, в оповещении(E/4) кассир фиксирует даты и время оплаты, а также номер документа об оплате.

Заведующий - управляет работой отдела. Он осуществляет проверку состояния дел фирмы. Так, еже квартально выявляются клиенты, оставшиеся неудовлетворенными.

Итого:

  • Покупатель - Юр. Или част. Лицо, желающее преобрести или постоянно приобретающее какие-либо товары. Задаются категория покупателя (Юр. или Физ. лицо), дата регистрации.
  • Товар - Любое изделие промышленного производсва, когда-либо продаваемого отделом продаж. Указываются наименование и артикул товара, номер сертификата качества, упаковка, название фирмы производителя товара, кол-во товара на складе
  • Заказ - Документ, фиксирующий факт заказа товара на фирме. Содержит дату и время оформления заказа, дату и время отправки первого оповежения, дату и время оплаты товара, дату и время отпуска товара со склада или метку об аннулировании.
  • Оповещение - Документ, фиксирующий факт отправки покупателю сообщения о поступлении интересующего его товара. Содержит дату и время отправки оповещения, номер заказа, по которому оформлено оповещение.
  • Документ об оплате - Платежный документ, фиксирующий факт оплаты товара покупателем. Содержит тип документа (приходный кассовый ордер для физ. или входящее банковское платежное поручение для юр. Лиц.), дату и время оплаты, а также сумму оплаты.
  • Товарная накладная - Документ, фиксирующий поступление товара на склад. Содержит дату поступления товаров.
  • Юр. лицо - Любая комерческая фирма зарегестрированная в системе. Задаются название фирмы, юр. адрес, телефон, номер лицезии, банковские реквизиты, категория фирмы (магаз, оптовик, лоток и т.д.)
  • Физ. лицо - Любой гражданин Российской Империи. Задаются Фамилия, имя, отчетсво, год рождения, паспортные данные, адрес, телефон, email.

0) Кандидаты в сущности

  • Покупатель (E/1)
  • Товар (E/2)
  • Заказ (E/3)
  • Оповещение (E/4)
  • Документ об оплате (E/5)
  • Товарная накладная (E/6)
  • Юр. лицо (E/7)
  • Физ. лицо (E/8)

1) Связи

  • Любой ПОКУПАТЕЛЬ может оформить один или несколько ЗАКАЗОВ

  • ЗАКАЗ может оформить только один ПОКУПАТЕЛЬ

  • ЗАКАЗ содержит один или несколько ТОВАРОВ

  • ТОВАР находиться в одном или нескольких ЗАКАЗАХ

  • ТОВАРНАЯ НАКЛАДНАЯ содежит один или несколько ТОВАРОВ

  • ТОВАР находиться в одном или нескольких ТОВАРНЫХ НАКЛАДНЫХ

  • ПОКУПАТЕЛЬ может получить одно или несколько ОПОВЕЩЕНИЙ

  • ОПОВЕЩЕНИЕ высылается только одному ПОКУПАТЕЛЬЮ

  • ЗАКАЗ находиться в одном или в нескольких ОПОВЕЩЕНИЯХ

  • ОПОВЕЩЕНИЕ содержит информацию об одном ЗАКАЗЕ

  • ДОКУМЕНТ ОБ ОПЛАТЕ содержит информацию об одном ЗАКАЗЕ

  • ЗАКАЗ оплачивается по одному ДОКУМЕНТУ ОБ ОПЛАТЕ

  • ОПОВЕЩЕНИЕ может содержать информацию об одном ДОКУМЕНТЕ ОБ ОПЛАТЕ

  • ДОКУМЕНТ ОБ ОПЛАТЕ содержит информацию об одном ОПОВЕЩЕНИИ

  • ПОКУПАТЕЛЬ может быть либо ЮР. лицом либо ФИЗ. лицом.

2) Атрибуты

Покупатель (customer):

  • номер покупателя (customer.customer_id) (PK)
  • тип покупателя (customer.type_id)
  • ФИО (customer_fio)
  • дата регистрации (customer.date_registration)

Товар (product):

  • код товара (product.product_id)
  • наименование (product.name)
  • артикул (product.article)
  • номер сертификата качества (product.number_quality_certificate)
  • упаковка (product.package)
  • название фирмы производителя (product.manufacturer_name)
  • кол-во на складе (product.count_in_storage)
  • отпускная цена (product.selling_price)

Товарная накладная (waybill):

  • номер накладной (waybill.waybill_number)
  • дата поступления товара (waybill.datetime)
  • кол-во поступивщего товара (waybill.total_product)
  • всего отпущено на сумму (waybill.total_cost)

Поставка (delivery):

  • номер поставки (delivery.delivery_id) PK
  • номер накладной (waybill.waybill_number) FK
  • код товара (product.product_id) FK
  • дата и время поставки (delivery_datetime)
  • кол-во товаров (order.count_products)

Позиция товара (position):

  • номер позиции (position.position_id) PK
  • номер заказа (order.order_id) FK
  • код товара (product.product_id) FK

Заказ (order):

  • номер заказа (order.order_id) PK
  • номер покупателя (customer_id) FK
  • дата и время оформления (order.datetime_registration)
  • дата отправки первого оповещения (order.datetime_first_notify)
  • дата и время оплаты (order.datetime_payment)
  • крайний срок отпуска товара со склада (product.datetime_product_end)
  • кол-во товаров (order.count_products)
  • отметка о продаже(отмене) покупки товара (order.payment_status)
  • стоимость покупки (order.cost)

Оповещение (notify):

  • номер оповещения (notify.notify_id) PK
  • номер заказа (notify.order_id) FK
  • дата и время отправки оповещения (notify.datetime)
  • крайний срок выкупа товара (notify.date_end_purchase)
  • отметка об оплате товара (notify.is_paid)

Документ об оплате (quittance):

  • номер документа об оплате (quittance.quittance_number)
  • тип документа (quittance.type)
  • дата и время оплаты (quittance.payment_datetime)
  • сумма оплаты (quittance.payment_amount)

Юр. лицо (legal):

  • название фирмы (legal.company_name)
  • адрес (legal.address)
  • телефон (legal.phone)
  • номер лицезии (legal.license_number)
  • банковские реквизиты (legal.bank_details)
  • категория фирмы (legal.company_category)

Физ. лицо (person):

  • фамилия (person.last_name)
  • имя (person.first_name)
  • отчетсво (person.middle_name)
  • год рождения (person.date_birth)
  • паспортные данные (person.passport_data)
  • адрес (person.address)
  • телефон (person.phone)
  • email (person.email)

3) триггеры

1)

Упражнения

Элементы ER-модели

2.1.1

Расмотрим проект БД банка, содежращей информацию о клиентах (назовем это множество сущностей Clients) и состоянии их счетов (Accounts).

Данные о клиенте включают его имя (name), адрес (address), номер телефона (phone) и код полиса социального страхования (ssnumber).

Счет описывается атрибутами номера (number), типа (например “накопительный” “чековый”, и т.п. (type)) и остатка (balance).

Необходимо также отобразить в БД факт принадлежности счета определенному клиенту.

Начертите ER-диаграмму, соответвующую такой БД. Незабудте описать типы связей

0) Кандидаты в сущности

  • Клиент (Client)
  • Счет (Account)

1) Связи

  • Любой КЛИЕНТ может иметь (или может не иметь) СЧЕТ в банке
  • СЧЕТ должен принадлежать одному КЛИЕНТУ

Итого

  • КЛИЕНТ имеет СЧЕТ
  • СЧЕТ принадлежит КЛИЕНТУ

2) Атрибуты

Клиент:

  • Номер клиента (client.id)
  • Имя клиента (client.name)
  • Адрес клиента (client.address)
  • Номер телефона клиента (client.phone)
  • Полис клиента (client.ssnumber)

Счет:

  • Код счета (account.id)
  • Номер счета (account.number)
  • Тип счета (account.type)
  • Баланс счета (account.balance)

2.1.2

Исправте решение упр. 2.1.1 след. образом:

  • измените диаграмму в предположении, что счет может принадлежать только одному клиенту;
  • измените диаграмму, предусматривая, что клиент может открыть в банке только один счет

  • измените исходную диаграмму, полученную в результате упраженния 2.1.2, в предположении, что клиент может иметь несколько адресов, описываемых кортежами вида “улица-город-страна”, и множество номеров телефонов. Имейте в виду, что в рамках ER-модели вы не вправе использовать атрибуты типов, таких как множества, не являющимися атомарными.

git

2.1.3

Представьте в виде ER-диаграммы структуру “фудбольной” базы данных, охватывающую информацию о командах, об игроках и о болельщиках, включая след. атрибуты

  1. Для каждой команды - название, перечень имен игроков, имя капитана (из числа игроков), цвета формы
  2. Для каждого игрока - имя
  3. Для каждогов больельщика - имя, название комманды, имя любимого игрока и предпочитаемый цвет.

0) Кандидаты в сущности

  • Игрок
  • Команда
  • Цвет формы
  • Болельщик

1) Связи

  • ИГРОК может находится в одной КОМАНДЕ

  • КОМАНДА состоит из несольких ИГРОКОВ

  • у КОМАНДЫ может быть ИГРОК капитан

  • ИГРОК может быть капитаном КОМАНДЫ

  • КОМАНДА должна иметь ЦВЕТ ФОРМЫ

  • ЦВЕТ ФОРМЫ может принадлежать одной КОМАНДЕ

  • БОЛЕЛЬЩИК болеет за одну КОМАНДУ
  • за одну КОМАНДУ болеет нескольно БОЛЕЛЬЩИКОВ
  • у БОЛЕЛЬЩИКА может быть любимый ИГРОК
  • ИГРОК может быть любимым несколькими БОЛЬЕЛЬЩИКАМИ
  • у БОЛЕЛЬЩИКА может быть любимый ЦВЕТ ФОРМЫ
  • ЦВЕТ ФОРМЫ может быть любимым несколькими БОЛЬЕЛЬЩИКАМИ

2) Атрибуты

Игрок:

  • код игрока (PK)
  • команда (FK)
  • имя

Команда:

  • код команды (PK)
  • цвет формы (FK)
  • капитан (FK)
  • название

Цвет формы:

  • код цвета (PK)
  • цвет

Болельщик:

  • код болельщика (PK)
  • команда (FK)
  • любимый игрок (FK)
  • цвет формы (FK)
  • имя

git

2.1.4

Предположим, что в диаграмме решения упр. 2.1.3 следует предусмотреть связь led-by (“играл под руководством”), соединяющую двух игроков и команду. Множество данных для связи led-by состоит из кортежей вида (player1, player2, team), свидетельствующих о том, что игрок “player1” играл в команде “team” в тот период, когда игрок “player2” был ее капитаном.

  1. Внесите в ER-диаграмму необходимые изменения.
  2. Замените тернарную связь led-by новым множеством сущьностей и набором бинарных связей.

2.1.5

Дополните диаграмму упр. 2.1.3 информацией о том, в каких командах игрок выступал прежде, включая даты зачисления в состав каждой команды и увольнения из нее.

2.1.6

Расмотрим задачу описания генералогического дерева семьи. Нам понадобиться одно множество сущьностей, People (“люди”). Информация, которую необходимо отобразить в модели, включает атрибут name (“имя”) и след. связи: MotherOf (“является матерью”), FatherOf (“является отцом”), и ChildOf (“является ребенком”).
Постройте ER-диаграмму, состоящую из множества сущьностей People и при соединенных к нему соответствующих связей.
Не забудьте воспрользоваться ролями, если множество сущьностей в контексте связи учавствует многократно.

2.1.7

Измените проект БД, созданный в упр. 2.1.6, включив в него описание след. спец. множеств сущьности “человек”:

  1. Женщины
  2. Мужчины
  3. Родители

2.1.8

Альтернативный способ решения задания упр. 2.1.6 состоит в применениии тернарной связи Family (“семья”) в предложении, что кортеж множества данных для Family, выглядит как (person, mother, father)

  1. Начертите ER-диаграмму
  2. Замените тернарную связь Family множеством сущьностей и набором бинарных связей.

2.1.9

Спроектируйте структуру, адекватно описывающую универститетскую БД, которая должна включать информацию о студентах, факультетах, профессорах, курсах, а также о том, какие студенты слушают какие курсы и какие профессора преподают эти курсы, какие курсы читаются на том или ином факультете, об отметках студентов и лучих достижениях студентов по каждому курсу.

0) Кандидаты в сущности

  • Студент
  • Факультет
  • Профессор
  • Курс

1) Связи

  • ПРОФЕССОР преподает один или несколько КУРСОВ

  • один КУРС может преподаваться несколькими ПРЕПОДАВАТЕЛЯМИ

  • СТУДЕНТ слушает один или несколько КУРСОВ

  • один КУРС может слушать несколько СТУДЕНТОВ

2) Атрибуты

2.1.10

TODO

Принципы проектирования

2.2.1

На рис. представлена диаграмма, описывающая структуру БД банка, в которой предуматривается хранение информации о клиентах (Customers) и их счетах (Accounts). Поскольку в общем случае клиент вправе открыть в банке несколько счетов, а счетом могут совместно распоряжаться несколько клиентов, клиентам ставиться в соответствии “наборы счетов” (AccountSets), и каждый счет может быть членом (Member-of) одного или нескольких “наборов счетов”. Остальные связи

  • owner-address - адрес владельца
  • Has - обладает
  • name - имя
  • number - номер счета
  • balance - баланс
  • Lives-as - проживает
  • Adresses - адреса
  • address - адрес

Начертить ER-диаграмму

2.2.2

При каких условиях два множества сущностей - Studios (“киностудии”) и Presidents (“президенты”) - и связи Runs (“возглавляет”), образующие диаграмму, могут быть заменены единственным множеством сущностей с соответсвующими атрибутами?

2.2.3

Предположим, что атрибут address (“адрес”), множества сущностей Studios (“киностудии”) на диаграмме - удален. Покажите каким образом множество Studios теперь может быть заменено некоторым атрибутом. Как именно следовало бы расположить этот атрибут?

2.2.4

TODO

2.2.5

TODO
Рождение человека

2.2.6

TODO

2.2.7

TODO

Моделирование ограничений

2.3.1

done

Обративщись к ER-диаграммам, построенным вами при решении заданий из упр.

  • 2.1.1
  • 2.1.3
  • 2.1.6

выполните след.

  • выберите и обозначьте ключевые атрибуты для каждого множества сущьностей

  • отметьте ограничения ссылочной целостности, если таковые необходимы

2.3.2

TODO

2.3.3

TODO

2.3.4

TODO

Слабые множества сущностей

2.4.1

done

Один из способов представления сведений о студентах и оценках, полученными ими по завершении изучения тех или иных курсов, связан с созданием множеств сущностей студенты, курсы и регистрационные записи. Сущности регистрационные записи формируют множество, “соединяющие” множества студенты и курсы, которое может быть использовано не только для регистации того факта, что определенный студент сдал экзамен по конкретному курсу, но и для хранения данных о полученных студентами оценках. Начертите ER-диаграмму, представляющую описанную ситуацию, и отметьте на ней слабые множества сущьностей и ключи для всех множеств.

0) Кандидаты в сущности

  • Студент
  • Курс
  • Оценка

1) Связи

  • Каждый СТУДЕНТ может иметь только одну ОЦЕНКУ по определенному КУРСУ
  • По одному КУРСУ оценку могут иметь несколько студентов

2) Атрибуты

Студент:

  • код студента (PK)
  • ФИО

Курс:

  • код курса (PK)
  • название

Оценка:

  • код студента (PK)
  • код курса (FK)
  • оценка

2.4.2

Дополните решение упр. 2.4.1 таким образом, чтобы обеспечить хранение промежуточных оценок, получаемых студентом в процессе изучения курса.

2.4.3

TODO

2.4.4

TODO

Основы реляционной модели

3.1.1

На рис. показаны два отношения - Accounts (“счета”) и Customers (“клиенты”), - которые могут служить частью БД банка. Назовите

  • атрибуты каждого отношения

  • кортежи кадого отношения

  • компоненты одного кортежа из каждого отношения

  • реляционную схему каждого отношения

  • схему БД

    • Accounts (AcctNo, type, balance)
    • Customers (firstName, lastName, idNo, account)
  • домен, соответвующий каждому атрибуту
    • AcctNo - int
    • type - str
    • balance - int
    • firstName - str
    • lastName - str
    • idNo - str
    • account - int
  • альтернативный способ представления каждого отношения
    • поменять строки местами

3.1.2

TODO

От ER-Диаграмм к Реляционным схемам

3.2.1

Преобразуйте ER-диаграмму рис. в реляционную схему БД, имея в виду след. семантику наименований множеств сущностей и атрибутов

0) Кандидаты в сущности

  • Билет (booking)
  • Пассажир (customer)
  • Рейс (flight)

1) Связи

  • ПАССАЖИР может лететь несколькими РЕЙСАМИ

  • РЕЙС может содержать несколько ПАССАЖИРОВ

  • Каждый ПАССАЖИР может иметь БИЛЕТ

  • БИЛЕТ может принадлежать только одному ПАССАЖИРУ

  • РЕЙС может содержать несколько пассажиров согласно БИЛЕТАМ
  • по одному БИЛЕТУ может лететь только один ПАССАЖИР

2) Атрибуты

Билеты

  • номер пассажира (customer_id)
  • номер рейса (flight_id)
  • ряд (row)
  • место (seat)

Пассажиры

  • номер пассажира (customer_id)
  • номер социального страхования (SSNo)
  • номер телефона (phone)
  • адрес (address)
  • имя (name)

Рейсы

  • номер рейса (flight_id)
  • номер (number)
  • самолет (aircraft)
  • дата вылета (day)


CREATE TABLE customers (
    customer_id INTEGER NOT NULL,
    SSNo TEXT NOT NULL,
    phone TEXT,
    address TEXT,
    name TEXT,
    PRIMARY KEY (customer_id)
);


CREATE TABLE flights (
    flight_id INTEGER NOT NULL,
    number INTEGER NOT NULL,
    aircraft TEXT NOT NULL,
    day TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (flight_id)
);


CREATE TABLE bookings (
    customer_id INTEGER NOT NULL,
    flight_id INTEGER NOT NULL,
    row INTEGER,
    seat INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (flight_id) REFERENCES flights (flight_id),
    PRIMARY KEY (customer_id, flight_id)
)

3.2.2

TODO

3.2.3

TODO

3.2.4

TODO

Преобразование структур подклассов в отношения

3.3.1

TODO

3.3.2

TODO

3.3.3

TODO

3.3.4

TODO

Упражнения v2

Введение в реляционные базы данных

3.1

TODO

Дайте определение след. терминам

  • автоматическая навигация
  • базовая переменная отношения
  • внешний ключ
  • высказывание
  • замкнутость
  • каталог
  • операция на уровне множества
  • оптимизация
  • откат
  • первичный ключ
  • предикат
  • представление
  • проекция
  • производная переменная отношения
  • реляционная база данных
  • реляционная модель
  • реляционная СУБД
  • соедниение
  • сокращение
  • фиксация транзакций

3.2

TODO

Опишите содержимое переменных отношения каталога TABLE и COLUMN для БД поставщиков и делатей

3.3

TODO

Как пояснялось в разделе 3.6, каталог должен описывать самого себя, т.е. включать записи о переменных отношения самого каталога. Дополните рис. 3.6 так, чтобы он включал необходимые записи о самих переменных отношения TABLE И COLUMN.

3.4

TODO

Ниже приведен запрос к базе данных поставщиков и деталей. Что получится в результате его выполнения? Какой предикат соответствует этому результату?


( S JOIN SP ) WHERE Р# = Р# С Р2 ) ) { S#, CITY }

3.5

TODO

Предположим, что выражение, применяемое в запросе из упр. 3.4. используется для определения представления.


CREATE VIEW V AS
( ( S JOIN SP ) WHERE P# = P# ('P2') ) { S#, CITY } ;

Теперь рассмотрим следующий запрос.


( V WHERE CITY = 'London' ) { S# }

Что получится в результате его выполнения? Какой предикат соответствует этому результату? Поясните, какой компонент используется со стороны СУБД при выполнении запроса.

3.6

TODO

Как вы понимаете термины, характеризующие свойства транзакций: неразрывность, сохранность результатов, изолированность и упорядочиваемость операций параллельных транзакций.

3.7

TODO

Сформулируйте информационный принцип.

3.8

TODO

Если вы знакомы с иерархической моделью данных, укажите все известные вам различия между ней и реляционной моделью, которая кратко описана в данной главе.

Введение в SQL

4.1

DONE

На рис. 4.5 показаны примеры значений данных для расширенной формы базы данных поставщиков и деталей, которая называется базой данных поставщиков, деталей и проектов.

  • Поставщики (S) (suppliers)
    • номером поставщика (s#) supplier_id
  • детали (р) (products)
    • номером детали (р#) (product_id)
  • проекты (J) (projects)
    • номером проекта (J#) (project_id)

Значение предиката для отношения SPJ (поставки) таково:
определенный поставщик S# поставляет определенную деталь Р# для определенного проекта J# в определенном количестве QTY (причем комбинация значений столбцов {S#, Р#, J#} представляет собой первичный ключ).

Запишите соответствующие определения данных на языке SQL для этой базы данных.

Примечание. Эта база данных будет использоваться во многих упражнениях в госледующих главах.

psql -h localhost -U postgres -d test


DROP TABLE suppliers cascade;
CREATE TABLE suppliers (
    supplier_id serial primary key,
    sname text not null,
    status integer,
    city text not null
);


truncate suppliers;
insert into suppliers (sname, status, city) values
('Smith', 20, 'London'),
('Jones', 10, 'Paris'),
('Blake', 30, 'Paris'),
('Clark', 20, 'London'),
('Adams', 30, 'Athens');


drop table products cascade;
create table products (
    product_id serial primary key,
    pname text not null,
    color text not null,
    weight decimal (3,1) not null,
    city text not null
);


truncate products;
insert into products (pname, color, weight, city) values
('Nut',      'Red',      12.0, 'London'),
('Bolt',     'Green',    17.0, 'Paris'),
('Screw',    'Blue',     17.0, 'Oslo'),
('Screw',    'Red',      14.0, 'London'),
('Cam',      'Blue',     12.0, 'Paris'),
('Cog',      'Red',      19.0, 'London');


drop table projects;
create table projects (
    project_id serial primary key,
    jname text not null,
    city text not null
);


truncate projects;
insert into projects (jname, city) values
('Sorter',   'Paris'),
('Display',  'Rome'),
('OCR',      'Athens'),
('Console',  'Athens'),
('RAID',     'London'),
('EDS',      'Oslo'),
('Tape',     'London');


drop table spj;
create table spj (
    supplier_id integer not null references suppliers(supplier_id),
    product_id integer not null references products(product_id),
    project_id integer not null references projects(project_id),
    qty integer not null, 
    primary key (supplier_id, product_id, project_id)
);


truncate spj cascade;
insert into spj values
(
    (select supplier_id     from suppliers   where   sname = 'Smith'),
    (select product_id      from products    where   pname = 'Nut'),
    (select project_id      from projects    where   jname = 'Sorter'),
    200
),
(
    (select supplier_id     from suppliers   where   sname = 'Smith'),
    (select product_id      from products    where   pname = 'Nut'),
    (select project_id      from projects    where   jname = 'Console'),
    700
),
(
    (select supplier_id     from suppliers   where   sname = 'Jones'),
    (select product_id      from products    where   pname = 'Screw' and color = 'Blue'),
    (select project_id      from projects    where   jname = 'Sorter'),
    400
),
(
    (select supplier_id     from suppliers   where   sname = 'Jones'),
    (select product_id      from products    where   pname = 'Screw' and color = 'Blue'),
    (select project_id      from projects    where   jname = 'Display'),
    200
),
(
    (select supplier_id     from suppliers   where   sname = 'Jones'),
    (select product_id      from products    where   pname = 'Screw' and color = 'Blue'),
    (select project_id      from projects    where   jname = 'OCR'),
    200
);


TABLE suppliers;
TABLE products;
TABLE projects;
TABLE spj;
...
 supplier_id | sname | status |  city  
-------------+-------+--------+--------
           1 | Smith |     20 | London
           2 | Jones |     10 | Paris
           3 | Blake |     30 | Paris
           4 | Clark |     20 | London
           5 | Adams |     30 | Athens
(5 строк)

 product_id | pname | color | weight |  city  
------------+-------+-------+--------+--------
          1 | Nut   | Red   |   12.0 | London
          2 | Bolt  | Green |   17.0 | Paris
          3 | Screw | Blue  |   17.0 | Oslo
          4 | Screw | Red   |   14.0 | London
          5 | Cam   | Blue  |   12.0 | Paris
          6 | Cog   | Red   |   19.0 | London
(6 строк)

 project_id |  jname  |  city  
------------+---------+--------
          1 | Sorter  | Paris
          2 | Display | Rome
          3 | OCR     | Athens
          4 | Console | Athens
          5 | RAID    | London
          6 | EDS     | Oslo
          7 | Tape    | London
(7 строк)

 supplier_id | product_id | project_id | qty 
-------------+------------+------------+-----
           1 |          1 |          1 | 200
           1 |          1 |          4 | 700
           2 |          3 |          1 | 400
           2 |          3 |          2 | 200
           2 |          3 |          3 | 200
(5 строк)

4.2

NOPE

В разделе 4.2 был описан оператор CREATE TABLE так, как он определен в стандарте языка SQL. Однако многие коммерческие продукты поддерживают допол нительные опции этого оператора, обычно связанные с индексами, размещением на дисковом пространстве и другими вопросами реализации, что противоречит требованиям обеспечения физической независимости от данных и междусистемной совместимости. Исследуйте доступный вам продукт, поддерживающий язык SQL. Относятся ли предыдущие замечания к этому продукту? В частности, какие дополнительные опции оператора CREATE TABLE поддерживаются в этом продукт.

4.3

NOPE

И снова исследуйте доступный вам продукт, поддерживающий язык SQL. Под держивается ли в нем информационная схема? Если нет, то каким образом поддерживается каталог?

4.4

DONE

Сформулируйте на языке SQL следующие операции обновления для базы данных поставщиков, деталей и проектов.

  • Вставить данные нового поставщика S10 в таблицу S;
    • имя поставщика - Smith, город - New York, статус еще не известен.

insert into suppliers (sname, city, status) values
('Smith', 'New York', NULL);
  • Удалить все проекты, для которых нет поставок.

delete from projects where project_id not in (select project_id from spj group by project_id) returning *;
...
 project_id | jname |  city  
------------+-------+--------
          5 | RAID  | London
          6 | EDS   | Oslo
          7 | Tape  | London
(3 строки)
  • Изменить цвет всех деталей красного цвета (red) на оранжевый (orange).

update products set color = 'Orange' where color = 'Red' returning *;
...
 product_id | pname | color  | weight |  city  
------------+-------+--------+--------+--------
          1 | Nut   | Orange |   12.0 | London
          4 | Screw | Orange |   14.0 | London
          6 | Cog   | Orange |   19.0 | London
(3 строки)

UPDATE 3

4.5

DONE

Используя базу данных поставщиков, деталей и проектов, напишите программу с внедренными выражениями SQL для выдачи списка всех строк поставщиков по порядку их номеров. За каждой строкой поставщика должны непосредственно следовать строки проектов, обеспечиваемых этим поставщиком, по порядку номеров проектов.


#!/usr/local/env perl

use uni::perl       qw| :dumper |;

use DBI;

my $dbh = DBI->connect(
    'dbi:Pg:dbname=test;host=localhost;port=5432',
    'postgres',
    1234
);

my @header = qw/ID sname status city/;
my $format = ( "%s\t" x (scalar @header) )."\n";
printf $format, @header;

my @header_p = qw/ID jname city/;
my $format_p = "\t".( "%s\t" x (scalar @header_p) )."\n";

foreach my $supplier ( $dbh->selectall_array('select * from suppliers order by supplier_id asc', { Slice => {} } ) ) {
    printf $format,
        $supplier->{'supplier_id'},
        $supplier->{'sname'},
        $supplier->{'status'},
        $supplier->{'city'};

    my @projects = $dbh->selectall_array('
        select projects.* from projects
        join spj on spj.project_id = projects.project_id and supplier_id = ?
        group by projects.project_id
        order by projects.project_id asc
    ', { Slice => {} }, $supplier->{'supplier_id'} );

    printf $format_p, @header_p;
    foreach my $project ( @projects ) {
        printf $format_p, $project->{'project_id'},
            $project->{'jname'},
            $project->{'city'};
    };
}

exit;
...
$ perl script.pl
ID      sname   status  city 
1       Smith   20      London
        ID      jname   city
        1       Sorter  Paris
        4       Console Athens
2       Jones   10      Paris
        ID      jname   city
        1       Sorter  Paris
        2       Display Rome  
        3       OCR     Athens
3       Blake   30      Paris
        ID      jname   city  
4       Clark   20      London
        ID      jname   city
5       Adams   30      Athens                                           
        ID      jname   city

4.6

NOPE

Даны таблицы PART И PART_STRUCTURE, определенные следующим образом.


CREATE TABLE PART (
    Р# Р#,
    DESCRIPTION CHAR(1OO),
    PRIMARY KEY ( P# )
);


CREATE TABLE PART_STRUCTURE (
    MAJOR_P# P#,
    MINOR_P# P#,
    QTY QTY,
    PRIMARY KEY ( MAJOR_P#, MINOR_P# ),
    FOREIGN KEY ( MAJOR_P# ) REFERENCES PART,
    FOREIGN KEY ( MINOR_P# ) REFERENCES PART
);

В таблице PART_STRUCTURE показано, какие детали (MAJOR_P#) содержат другие детали (MINOR_P#) как компоненты первого уровня. Напишите на языке SQL программу для получения списка всех компонентов данной детали на всех имеющихся уровнях (задача разузлования деталей).

Примечание. Значения, показанные в качестве примера на рис. 4.6, могут помочь вам более наглядно представить предложенную выше задачу. Следует отметить, что таблица PART_STRUCTURE демонстрирует, как информация о составе изделий (см. главу 1, раздел 1.3, подраздел “Сущности и связи”) обычно представляется в реляционных системах.