Всё для Учёбы — студенческий файлообменник
1 монета
doc

Контрольная «Разработка базы данных для ресторана» по Базам данных (Марков А. А.)

Описание базы данных

База данных разработана для автоматизации работы с данными ресторана. Она содержит информацию о блюдах, столиках, заказах и персонале. База данных позволяет просматривать нужную информацию (меню, количество заказов за день, среднюю цена блюд по определенному виду, информацию об официанте, обслуживающего данный столик и др.), осуществлять заказы блюд и отменять их, добавлять и удалять данные о блюдах, считать сумму заказа с учетом цены за резервирование столика и 10% скидки.

ER – модель

База данных разработана с использованием модели «сущность – связь» (entity – relationship model, «ER – модель»). ER – модель является средством абстрактного представления структур, т.е. дает возможность описать информацию, которая должна быть представлена в базе данных, и указать взаимосвязь между элементами информации.

ER-модель является графической, поэтому схема базы данных представлена в виде ER-диаграммы (ERD, entity-relationship diagram — «диаграмма сущностей и связей»). ER-диаграмма состоит из элементов трех основных типов: множеств сущностей, атрибутов, связей. Множество сущностей — набор однородных абстрактных объектов определенного вида. Атрибуты — свойства сущностей. Связи — соединения между двумя или большим числом множеств сущностей.

БД содержит 5 сущностей.

Сущность «dishes» — меню ресторана. Имеет атрибуты: «ID_dish» - номер блюда, «name» - название, «descr» - описание, «price» - цена, «kind» - вид блюда. Связь «order_dishes» между сущностью «dishes» и сущностью «orders» типа «многие-ко-многим», т.е. несколько блюд могут принадлежать одному заказу и несколько заказов могут включать одинаковое блюдо, имеет атрибут «quantity» - количество заказанных блюд, определенного номера.

Сущность «firm» включает в себя фирменные блюда, имеет 1 атрибут «ID_firm» - номер блюда, являющегося фирменным.

Сущность «orders» включает в себя информацию о заказах. Имеет атрибуты: «ID_order» - номер заказа, «date» - дата, «ID_table» - номер столика, «reserve» - был ли зарезервирован столик, «discount» - есть ли 10%-ая скидка.

Сущность «tables» содержит информацию о столиках. Имеет атрибуты: «ID_table» - номер столика, «type» - тип столика (ord-обычный, vip), «smoking» - курящая зона либо нет, «busy» - занят столик или нет. Имеет связь «waiter_tables» типа «многие-к-одному» с сущностью «waiters», т. к. один официант обслуживает несколько столиков.

Сущность «waiters» содержит данные об официантах. Имеет атрибуты: «ID_waiter» - ID официанта, «name» - имя, «surname» - фамилия, «address» - адрес, «tel» - телефон.

ER – Модель базы данных представлена в приложении №1.

Модель ANSI-SPARC

Модель ANSI – SPARC определяет три уровня абстракции: внешний, концептуальный и внутренний. Внешний уровень — уровень, на котором данные воспринимаются пользователями (интерфейсная часть). Внутренний уровень — уровень, на котором воспринимают данные операционная система и СУБД (данные в виде структур и таблиц сохраняются с использованием файлов). Концептуальный уровень представления данных предназначен для отображения внешнего уровня на внутренний и обеспечения независимости друг от друга.

Рис. 1. Модель ANSI-SPARC.

На внешнем уровне находится единый интерфейс пользователя — «База данных ресторана».

На концептуальном уровне хранятся нормализованные таблицы:

orders (ID_order, date, ID_table, reserve, discount);

order_dishes (ID_order, ID_dish, quantity);

dishes (ID_dish, name, descr, price, kind);

firm (ID_firm); tables (ID_tables, type, smoking, busy);

waiters (ID_waiter, name, surname, address, tel);

waiter_tables (ID_waiter, ID_table);

Внутренний уровень содержит таблицы, реализованные в БД, которые будут подробно описаны ниже в разделе «Описание структуры таблиц».

Описание структуры таблиц

Разработанная ER-диаграмма была преобразована в набор таблиц. В результате множества сущностей и связи были представлены отношениями. Каждой сущности или связи на ER-диаграмме соответствует таблица, всего таблиц 7. Ниже приводится описание таблиц.

Таблица «orders» («Заказы»)

ID_order date ID_table reserve discount

Уникальный идентификатор дата Уникальный

идентификатор Резерви-

рование Скидка

Таблица содержит информацию о заказах — номер заказа, дата, номер занятого столика, информация о резервировании и о скидке.

Таблица «dishes» («Блюда»)

ID_dish name descr price kind

Уникальный идентификатор

Название

Описание Цена вид

Таблица содержит информацию о блюдах ресторана (меню) – номер блюда, его название, описание, цена и вид. Атрибут «kind» может принимать значения: «drink» – безалкогольный напиток, «beer» - пиво, «wine» - вино, «alcohol» - др. алкогольный напиток, «dessert» - десерт, «shashlick» - шашлык, «cold snack» - холодная закуска, «hot snack» - горячая закуска, «for_beer» - закуска к пиву, «salad» - салат, «soup» - суп, «dish» - горячее блюдо, «garnish» - гарнир, «sauce» - соус.

Таблица «order_dishes» («состав заказа»)

ID_order ID_dish quantity

Уникальный идентификатор Уникальный идентификатор Количество

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

Таблица «firm» («Фирменные блюда»)

ID_firm Уникальный идентификатор

Таблица содержит номера фирменных блюд. Остальная информация об этих блюдах содержится в таблице «dishes» («Блюда») под теми же номерами.

Таблица «tables» («места»)

ID_table type smoking busy

Уникальный идентификатор Тип Зона занятость

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

Таблица «waiters» («Официанты»)

ID_waiter name surname address tel

Уникальный идентификатор Имя Фамилия адрес телефон

Таблица содержит информацию об официантах – имя, фамилия, адрес и телефон.

Таблица «waiter_tables» («официант_столики»)

ID_waiter ID_table

Уникальный

идентификатор Уникальный идентификатор

Таблица содержит определяет связь между официантами и столиками, которые они обслуживают. Один официант обслуживает 4 столика.

Исходные коды для создания таблиц находятся в приложении №2.

Функциональные зависимости

(пояснения по выбору первичных ключей)

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

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

В таблицах «orders», «dishes», «firm», «tables», «waiters» первичными ключами являются «ID_order», «ID_dish», «ID_firm», «ID_table», «ID_waiter» соответственно. Использование ID в этих таблицах удобно и необходимо, так как значения других атрибутов повторяются, и они не могут использоваться как первичные ключи. В таблице «dishes» можно было бы принять атрибут «name» («название блюда») в качестве первичного ключа, потому что оно уникально. Но его не удобно было бы использовать в таблице связи заказов с блюдами («order_dishes»). Поэтому в этой таблице был введен атрибут «ID_dish» («номер блюда») – первичный ключ.

В таблице «order_dishes» («состав заказа») номер заказа «ID_order» связан с номером блюда «ID_dish». Но т.к. заказ может включать несколько блюд, то «ID_order» повторяется, и поэтому не может быть первичным ключом. Одно блюдо может входить в состав нескольких заказов, поэтому «ID_dish» тоже не может быть первичным ключом. Первичный ключ в данной таблице составной и включает «ID_order» и «ID_dish». Сочетание номеров повториться не может.

Таблица «waiter_tables» («официант_столики») осуществляет связь официанта с номером столика. Один официант обслуживает несколько столиков, поэтому в этой таблице составной ключ («ID_waiter», «ID_table»).

Начальное заполнение таблиц

Для демонстрации особенностей базы данных, работы типовых запросов, триггеров и хранимых процедур созданные таблицы были заполнены тестовыми данными. Ниже приведены таблицы с примером хранящихся в них данных. Исходные коды пакетного файла для заполнения базы находятся в приложении №3.

1. Таблица «dishes»:

……………………………………………………………………………………

2. Таблица «firm»:

3. Таблица «orders»:

4. Таблица «order_dishes»:

5. Таблица «tables»:

6. Таблица «waiters»:

7. Таблица «waiter_tables»:

Запросы

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

1. Найти в базе данных все заказы со скидкой, сделанные 4 мая 2008 года, и вывести на экран их номера.

Результат:

2. Найти все свободные столики в некурящей зоне.

Результат:

3. Вывести количество заказов, сделанных 5 мая 2008 года.

Результат:

4. Вывести меню ресторана. Блюда отсортированы по виду и внутри групп - по номеру.

Результат:

5. Выбрать из меню алкогольные напитки, кроме пива. Вина вывести отдельно. Напитки выводить, начиная с самых дорогих.

\

6. Найти в меню блюда дешевле 100 рублей и вывести, начиная с самых дешевых.

Результат:

7. Выбрать из меню фирменные блюда ресторана. Отсортировать по виду.

Результат:

8. Найти в меню самое дорогое горячее блюдо.

Результат:

9. Подсчитать среднюю цену каждого вида блюда.

Результат:

10. Вывести личные данные официантки, обслуживающей 12 столик.

Результат:

11. Вывести номер столика, за которым закреплен 12 заказ, тип столика, курящая зона либо нет, был ли зарезервирован столик, есть ли скидка на заказ.

Результат:

12. Вывести заказ №12, а именно: номер блюда, название, цену, количество.

Результат:

Хранимые процедуры и функции.

Для базы данных было написано 5 процедур.

1. Процедура add_dish.

Create procedure add_dish(

In n varchar(100),

in d text,

in p int unsigned,

in k varchar(20))

insert into dishes values(null, n, d, p, k);

Добавляет новое блюдо в таблицу «dishes»:

Рис. 2. Блок-схема процедуры add_dish;

Пример выполнения:

……………………………………………………………….

2. Процедура add_order.

Create procedure add_order(

In d date, in i int unsigned,

in r varchar(10),

in dis varchar(10))

insert into orders values(null, d, i, r, dis);

Добавляет новый заказ в таблицу «orders».

Рис. 3. Блок-схема процедуры add_order;

Пример выполнения:

3. Процедура del_order.

Удаляет заказ под номером ID_o из таблицы «orders».

Create procedure del_order(

In ID_o int unsigned)

delete from orders where ID_order=ID_o;

Рис. 4. Блок-схема процедуры del_order;

Пример выполнения:

4. Процедура add_order_dishes;

Добавляет блюдо ID_d к заказу ID_o в количестве q штук.

Create procedure add_order_dishes(

In ID_o int unsigned,

in ID_d int unsigned,

in q int unsigned)

insert into order_dishes values(ID_o, ID_d, q);

Рис. 5. Блок-схема процедуры add_order_dishes;

Пример выполнения:

5. Процедура del_order_dishes.

Удаляет из заказа под номером ID_o блюдо под номером ID_d.

Create procedure del_order_dishes(

In ID_o int unsigned,

in ID_d int unsigned)

delete from order_dishes where ID_order=ID_o and ID_dish=ID_d;

Рис. 6. Блок-схема процедуры del_order_dishes.

Пример выполнения:

Функция sum_order.

Функция подсчитывает сумму заказа, с учетом резервирования столика различного типа (обычный – 200 руб., vip – 350 руб.) и скидки 10% , если такая имеется.

drop function if exists sum_order;

delimiter |

create function sum_order (id int unsigned)

returns real begin

DECLARE bill real;

DECLARE res int;

DECLARE summa int;

DECLARE dis real;

DECLARE r varchar(10);

DECLARE t varchar(10);

DECLARE d varchar(10);

set res=0; set dis=0.0;

set r=( select reserve from orders where ID_order=id);

set t=( select tables.type from tables, orders where orders.ID_table=tables.ID_table and orders.ID_order=id);

set d=( select discount from orders where ID_order=id);

set summa=( select sum( dishes.price * order_dishes.quantity) from dishes, order_dishes where order_dishes.ID_order=id and order_dishes.ID_dish=dishes.ID_dish);

if r="yes" and t="ord" then set res=200;

elseif r="yes" and t="vip" then set res=350;

end if;

if d="yes" then set dis = summa * 0.1;

end if; set bill = summa + res - dis;

return bill; end;

delimiter|

Рис. 9. Блок-схема функции sum_order.

Пример выполнения: подсчет суммы заказа №5.

Триггеры

Для базы данных были разработаны 2 триггера.

1. Триггер after_del.

create trigger after_del

after delete on orders

for each row

delete from order_dishes where ID_order=old.ID_order;

Триггер срабатывает при удалении записи из таблицы «orders» и удаляет все записи под этим номером заказа из таблицы «order_dishes».

Рис. 7. Блок-схема триггера after_del.

Пример выполнения:

2. Триггер busy.

Триггер срабатывает после добавления нового заказа в таблицу «orders» и устанавливает значение атрибута busy=«yes» (столик занят) в таблице «tables».

create trigger busy

after insert on orders

for each row update tables set tables.busy='yes' where tables.ID_table=new.ID_table;

Рис. 8. Блок-схема триггера busy.

Пример выполнения:

Литература

1. MySQL. Полное и исчерпывающее руководство по применению и администрированию баз данных, а также программированию приложений. Второе издание. П. Дюбуа, Москва — Санкт-Петербург — Киев, 2004

2. Практическое введение в базы данных, часть 1-3, А. А. Марков; Москва – 2004

3. Базы и банки данных. Методические указания по выполнению курсовой работы. Лазутченко О. В., МГУП, 2007.

4. MySQL. Учебное пособие. Л. Веллинг, Л. Томсон; Москва — Санкт-Петербург — Киев, 2004

5. Системы баз данных. Г. Гарсиа-Молина, Дж. Д. Ульман, Д. Уидом; Москва — Санкт-Петербург — Киев, 2003.

Приложение №1. ER – Модель базы данных

Приложение №2. Исходный код создания таблиц

create database restourant;

use restourant; drop table if exists dishes;

create table dishes

( ID_dish int unsigned not null auto_increment primary key,

name varchar(100) not null,

descr text, price int unsigned not null,

kind varchar(20) not null);

drop table if exists firm;

create table firm

( ID_firm int unsigned not null primary key);

drop table if exists orders;

create table orders

( ID_order int unsigned not null auto_increment primary key,

date date not null,

ID_table int unsigned not null,

reserve varchar(10) not null,

discount varchar(10) not null);

drop table if exists order_dishes;

create table order_dishes

( ID_order int unsigned not null,

ID_dish int unsigned not null,

primary key (ID_order, ID_dish),

quantity int unsigned not null);

drop table if exists tables;

create table tables

( ID_table int unsigned not null auto_increment primary key,

type varchar(10) not null,

smoking varchar(20) not null,

busy varchar(10) not null);

drop table if exists waiters;

create table waiters

( ID_waiter int unsigned not null auto_increment primary key,

name varchar(50) not null,

surname varchar(50) not null,

address varchar(100) not null,

tel varchar(50));

drop table if exists waiter_tables;

create table waiter_tables

(ID_waiter int unsigned not null,

ID_table int unsigned not null,

primary key (ID_waiter, ID_table));

Приложение №3. Исходный код занесения данных

load data local infile 'D:/xampp/mysql/bin/dishes.txt' into table dishes;

load data local infile 'D:/xampp/mysql/bin/orders.txt' into table orders;

load data local infile 'D:/xampp/mysql/bin/orders_dishes.txt' into table order_dishes;

load data local infile 'D:/xampp/mysql/bin/tables.txt' into table tables;

load data local infile 'D:/xampp/mysql/bin/waiters.txt' into table waiters;

load data local infile 'D:/xampp/mysql/bin/waiter_tables.txt' into table waiter_tables;

load data local infile 'D:/xampp/mysql/bin/firm.txt' into table firm;

Показать полностью…
Похожие документы в приложении