Проектирование реляционных баз данных

Предмет: Базы данных

Раздел: Проектирование реляционных баз данных

Решение:

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

1. Определение сущностей и их атрибутов

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

1.1. Таблица "Аптеки"

Хранит информацию о каждой аптеке сети.
Поля:

• id_аптеки (уникальный идентификатор, первичный ключ)
• адрес (текст)
• график_работы (текст)
• телефон (текст)
• руководитель (ФИО, текст)

1.2. Таблица "Персонал"

Хранит информацию о сотрудниках аптек.
Поля:

• id_сотрудника (уникальный идентификатор, первичный ключ)
• ФИО (текст)
• должность (текст)
• id_аптеки (внешний ключ, связь с таблицей "Аптеки")
• график_работы (текст)

1.3. Таблица "Лекарства"

Хранит информацию о лекарствах.
Поля:

• id_лекарства (уникальный идентификатор, первичный ключ)
• название (текст)
• категория (текст)
• дозировка (текст)
• форма_выпуска (таблетки, капсулы и т. д.)
• страна_производитель (текст)
• фирма_производитель (текст)
• срок_годности (дата)

1.4. Таблица "Поставки"

Хранит информацию о поставках лекарств.
Поля:

• id_поставки (уникальный идентификатор, первичный ключ)
• id_лекарства (внешний ключ, связь с таблицей "Лекарства")
• id_поставщика (внешний ключ, связь с таблицей "Поставщики")
• дата_поставки (дата)
• цена_поставки (число)
• количество (число)

1.5. Таблица "Продажи"

Хранит информацию о продажах лекарств.
Поля:

• id_продажи (уникальный идентификатор, первичный ключ)
• id_аптеки (внешний ключ, связь с таблицей "Аптеки")
• id_лекарства (внешний ключ, связь с таблицей "Лекарства")
• дата_продажи (дата и время)
• цена_продажи (число)
• id_скидки (внешний ключ, связь с таблицей "Скидочные карты", если применена скидка)

1.6. Таблица "Скидочные карты"

Хранит информацию о скидочных картах.
Поля:

• id_карты (уникальный идентификатор, первичный ключ)
• номер_карты (уникальный номер)
• ФИО_владельца (текст)
• телефон (текст)
• накопленные_баллы (число)

1.7. Таблица "Поставщики"

Хранит информацию о поставщиках лекарств.
Поля:

• id_поставщика (уникальный идентификатор, первичный ключ)
• название_компании (текст)
• страна (текст)
• контактный_телефон (текст)

2. Запросы к БД

Запрос 1: Вывести список всех лекарств с их поставщиками

SELECT Лекарства.название, Поставщики.название_компании, Поставки.цена_поставки
FROM Лекарства
JOIN Поставки ON Лекарства.id_лекарства = Поставки.id_лекарства
JOIN Поставщики ON Поставки.id_поставщика = Поставщики.id_поставщика;

Запрос 2: Найти все продажи определенного лекарства за последний месяц

SELECT Лекарства.название, Продажи.дата_продажи, Продажи.цена_продажи
FROM Продажи
JOIN Лекарства ON Продажи.id_лекарства = Лекарства.id_лекарства
WHERE Продажи.дата_продажи >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

Запрос 3: Вывести список клиентов со скидочными картами и их накопленными баллами

SELECT ФИО_владельца, номер_карты, накопленные_баллы
FROM Скидочные_карты
ORDER BY накопленные_баллы DESC;

Вывод:

Мы спроектировали реляционную базу данных для сети аптек, включив в нее 7 таблиц с необходимыми полями. Также были составлены несколько SQL-запросов для получения информации о лекарствах, продажах и скидочных картах.