ПРАКТИЧЕСКИЕ РАБОТЫ
Проектирование базы данных с использованием ER-технологии
Особенности диалекта SQL в СУБД MySQL рассмотрим на примере учебной базы данных book Интернет-магазина, торгующего компьютерной литературой. В базе данных должна поддерживаться следующая информация:
· тематические каталоги, по которым сгруппированы книги;
· предлагаемые книги (название, автор, год издания, цена, имеющееся на складе количество);
· зарегистрированные покупатели (имя, отчество, фамилия, телефон, адрес электронной почты, статус – авторизованный, неавторизованный, заблокированный, активный с хорошей кредитной историей);
· покупки, совершенные в магазине (время совершения покупки, число приобретенных экземпляров книги).
Логическая модель данных предметной области в стандарте IDEF1X представлена на рис. 1. Выделены сущности КАТАЛОГ, КНИГА, КЛИЕНТ, ЗАКАЗ, между которыми установлены неидентифицирующие связи мощностью oдин-ко-многим, определенные спецификой предметной области.
Рис. 1. Логическая модель данных предметной области
Физическая модель данных предметной области в стандарте IDEF1X для целевой СУБД MySQL представлена на рис. 2.
Рис. 2. Физическая модель предметной области
База данных book состоит из четырех таблиц:
· catalogs – список торговых каталогов;
· books – список предлагаемых книг;
· users – список зарегистрированных пользователей магазина;
· orders – список заказов (осуществленных сделок).
Таблица catalogs состоит из двух полей:
· cat_ID – уникальный код каталога;
· cat_name – имя каталога.
Оба поля должны быть снабжены атрибутом not null, поскольку неопределенное значение для них недопустимо.
Таблица books состоит из семи полей:
· book_ID – уникальный код книги;
· b_name – название книги;
· b_author – автор книги;
· b_year – год издания;
· b_price – цена книги;
· b_count – количество книг на складе;
· b_cat_ID – код каталога из таблицы catalogs.
Цена книги b_price и количество экземпляров на складе b_count могут иметь атрибут null. На момент доставки часто неизвестны количество товара и его цена, но отразить факт наличия товара в прайс-листе необходимо.
Поле b_cat_ID устанавливает связь между таблицами catalogs и books. Это поле должно быть объявлено как внешний ключ (FK)с правилом каскадного удаления и обновления. Обновление таблицы catalogs вызовет автоматическое обновление таблицы books. Удаление каталога в таблице catalogs приведет к автоматическому удалению всех записей в таблице books, соответствующих каталогу.
Таблица users состоит из семи полей:
· user_ID – уникальный код покупателя;
· u_name – имя покупателя;
· u_patronymic – отчество покупателя;
· u_surname – фамилия покупателя;
· u_phone – телефон покупателя (если имеется);
· u_email – e-mail покупателя (если имеется);
· u_status – статус покупателя.
Статус покупателя представлен полем типа enum, которое может принимать одно из четырех значений:
· active – авторизованный покупатель, который может осуществлять покупки через Интернет;
· passive – неавторизованный покупатель (значение по умолчанию), который осуществил процедуру регистрации, но не подтвердил ее и пока не может осуществлять покупки через Интернет, однако ему доступны каталоги для просмотра;
· lock – заблокированный покупатель, не может осуществлять покупки и просматривать каталоги магазина;
· gold – активный покупатель с хорошей кредитной историей, которому предоставляется скидка при следующих покупках в магазине.
Поля u_phone и u_email могут быть снабжены атрибутом null. Остальные поля должны получить атрибут not null.
Таблица orders включает пять полей:
· order_ID – уникальный номер сделки;
· o_user_ID – номер пользователя из таблицы users;
· o_book_ID – номер товарной позиции из таблицы books;
· o_time – время совершения сделки;
· o_number – число приобретенных товаров.
Поля таблицы orders должны быть снабжены атрибутом not null, т. к. при совершении покупки вся информация должна быть занесена в таблицу.
В таблице orders устанавливается связь с таблицами users (за счет поля o_user_id) и books (за счет поля o_book_id). Эти поля объявлены как внешние ключи (FK)с правилом каскадного удаления и обновления. Обновление таблиц users и books приведет к автоматическому обновлению таблицы orders. Удаление любого пользователя в таблице users приведет к автоматическому удалению всех записей в таблице orders, соответствующих этому пользователю.