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

 

СОДЕРЖАНИЕ

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

. Построение DFD-диаграммы3

.1 Создание диаграммы нулевого уровня3

.2 Создание диаграммы первого уровня3

. Описание семантики DFD-диаграммы5

.1 Подготовка диаграммы нулевого уровня5

.2 Подготовка диаграммы первого уровня5

. Построение ER-диаграммы7

. Обоснование физической организации данных9

. Нормализация базы данных10

. Создание базы данных и таблиц в ней11

. Бизнес правила13

. Основы DML. Базовый набор команд14

. Расширенные возможности DML15

. Представление c использованием Create Viev Designer16

.1 Использование представлений18

.2 Включение представление в запрос20

.3 Управление представлениями24

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

.1 Простые хранимые процедуры28

.2 Простые хранимые процедуры с входным параметром29писок литературы30

ПОСТАНОВКА ЗАДАЧИ

база данный create viev designer

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

Спроектируйте приложение баз данных, реализующее учет аренды.

1. ПОСТРОЕНИЕ DFD-ДИАГРАММЫ

1.1 Создание диаграммы нулевого уровня

Запустить среду построения DFD-диаграмм.

1.Щелкнем на иконке Terminator и добавим новый terminator к нашей схеме потоков данных.

2.Затем щелкаем правой кнопкой мыши на Terminator и выбираем пункт Edit Terminator. Изменим имя.

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

.Создадим необходимое количество процессов и переименуем их.

.С помощью иконки Data Flow добавляем новый поток данных к диаграмме.

.Надписываем потоки. Для этого щелкнем правой кнопкой мыши и выберем меню Edit Data Flow.

.Изменим цвет связей. Для этого щелкнем на контекстное меню и выберем пункт Color.

.Прямая линия связок не всегда удобная. Мы можем сделать их ломаными. Для этого надо нажать клавишу Ctrl+левая кнопка мыши и нажать на линию потока. Убрать их можно через контекстное меню, пункт remove break-points или CTRL+клик на точке.

1.2 Создание диаграммы первого уровня

9.Редактируем главный процесс. Для этого щелкнем правой кнопкой мыши на процессе и выберем пункт Edit Process. Убираем флажок на пункте Lowest Level.

10.Перейдем на подуровень Main process и создадим новый процесс. Соединим его с нашими потоками.

11.Добавим хранилище данных. Для этого щелкнем левой кнопкой мыши на значке Data Store и помещаем его на нашу диаграмму.

12.На панели инструментов выберем пункт Data Modeler, который автоматически упорядочит элементы диаграммы.

2. ОПИСАНИЕ СЕМАНТИКИ DFD-ДИАГРАММЫ

2.1 Подготовка диаграммы нулевого уровня:

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

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

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

.После обработки заявки и проверки договора («Контроль договора»), договор аренды вступает в силу.

2.2 Подготовка диаграммы первого уровня

.Во время подготовки договора клерк указывает данные о недвижимости: тип дома, плата за квартиру, которая зависит от типа, срок аренды и стороны, между которыми заключается договор и ищет владельца недвижимости;

.После этого заключаеться договор, скрепляется печатью, и клиент стает арендатором и вступает в права временного владения недвижимостью.

.Диаграмма построена в предположении, что круг клиентов конторы приблизительно стабилен, по этому храним их в двух хранилищах: «Арендатор» и «Владелец».

3. ПОСТРОЕНИЕ ER-ДИАГРАММЫ

. Запускаем среду построения Case Studio.

2. Используя команду меню File => New Model, создадим модель.

. В окне "Target database selection" выберем формат базы данных.

. Добавим на экран новую сущность, для этого нажмем левую кнопку мыши на иконке Entity и кликаем снова на рабочую область.

. Нажатием правой кнопки мыши откроем свойства сущности и в появившемся окне выбираем иконку Edit Entity.

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

. В поле Attribute name вводим имя, выбираем тип данных, ставим галочку напротив Key и нажимаем OK. В появившемся окне добавляем необходимое количество атрибутов, нажимая кнопку Add и вводя имя в необходимом поле.

. Добавим в модель новую сущность с тремя атрибутами, для этого нажимаем на иконку Entity.

. После добавления новой сущности кликаем по ней правой кнопкой мыши и выбираем Edit Entity.

. В появившемся окне нажимаем на кнопку Add, вводим имя атрибута в поле Attribute name, выбираем тип данных. Мы можем использовать кнопку Ok+Add для добавления новых атрибутов, при этом, не закрывая это диалоговое окно.

. Добавим новые связи в нашу диаграмму отношений сущностей.

. Нажимаем на иконку Non-identifying relationship и устанавливаем новые связи.

. Toad Data Modeler позволяет нам создавать определяющие взаимосвязи, неопределяющие взаимосвязи, M:N взаимосвязи.

. Соединяем наши сущности и подписуем связи.

Рис.1

.Нажмем на иконку Model verification и проверим сущность диаграммы взаимосвязи. Мы увидим возможные ошибки, предупреждения и подсказки.

.Включаем appropriate options и кликаем кнопку Run для запуска проверки.

.Для генерирования SQL скриптов кликаем по иконке Generate script. Запускается окно генерации скрипта.

.В закладке How to generate определяем правила и опции генерации

.В закладке Advanced мы можем включить или выключить опции зависимостей базы данных.

.В закладке Entity List мы можем выбирать главную модель или существующую подмодель или выбрать ту, что нам необходима.

.Нажимаем кнопку Generate чтобы сгенерировать SQL-скрипт. После того как скрипт будет сгенерирован нажимаем кнопку View.

4. ОБОСНОВАНИЕ ФИЗИЧЕСКОЙ ОРГАНИЗАЦИИ ДАННЫХ

СущностьАтрибутСтолбецМотивацияТип данныхПримечаниеДоговорАrАrФИОChar(10)ДоговорVLVLФИОChar(10)ДоговорTipTipТип недвижимостиChar(20)NN (FK)Договорid_dogovorid_dogovorНомер договораIntegerNN (PK)НедвижимостьPLPLДенежная суммаMoneyNNНедвижимостьТipТipТип недвижимостиChar(20)NN (PK)

5. НОРМАЛИЗАЦИЯ БАЗЫ ДАННЫХ

Преобразуем ER-диаграмму (рис. 1) в отношения Dogovor(VL, Ar, Tip, id_dogovor) и Nedvigimost(Tip, PL).

Определим ФЗ, которые соответствуют отношениям. Отношению Dogovor соответствует полная ФЗ id_dogovor ® Ar, VL, Tip, а отношению Nedvigimost соответствует полная ФЗ Tip ® PL.

Как видно, каждое из рассмотренных отношений содержит только одну полную ФЗ, следовательно, отношения нормализованы.

6. СОЗДАНИЕ БАЗЫ ДАННЫХ И ТАБЛИЦ В НЕЙ

1.Запускаем SQL Server Enterprise Manager и подключаемся к нужному серверу.

.Нажимаем иконку New Database и введем имя базы данных и её параметры.

3.Выбираем меню Tools -SQL Query Analyzer, где пишем наш SQL код и запускаем его на выполнение.

Код:

/*.10.2007.11.2007SQL 2000

*/

table [Dogovor]

(

[id_dogovor] Integer NOT NULL,

[VL] Char(10) NULL,

[AR] Char(10) NULL,

[Tip] Char(20) NOT NULL,Key ([id_dogovor]))

Create table [Nedvigimost]

(

[Tip] Char(20) NOT NULL,

[PL] Money NOT NULL,Key ([Tip])

) table [Dogovor] add foreign key([Tip],[PL]) references [Nedvigimost] ([Tip],[PL]) on update no action on delete no action quoted_identifier onquoted_identifier off

7. БИЗНЕС ПРАВИЛА

1.Стоимость недвижимости должна быть меньше 10000. Это правило реализуется с помощью ограничения СHEK.

Create table [Nedvigimost]

(

[Tip] Char(20) NOT NULL,

[PL] Money NOT NULL СHEK ( PL<10000)

Primary Key ([Tip])

)

.Название типа недвижимости не должно превышать 20 символов и должно быть задано. Это правило реализуется с помощью ограничений Char(20) и NOT NULLtable [Dogovor]

(

[id_dogovor] Integer NOT NULL,

[Tip] Char(20) NOT NULL,

[VL] Char(10) NULL,

[Ar] Char(10) NULL,Key ([id_dogovor])

)

.Для запрещения удаления данных таблицы key([Tip]) и [Nedvigimost] ([Tip]) используем запрещающую ссылочную целостность:

Alter table [Dogovor] add foreign key([Tip]) references [Nedvigimost] ([Tip]) on update no action on delete no action

Теперь нельзя изменить значения ключа записи родителя key([Tip]) если ей соответствуют записи в до черней таблице [Nedvigimost] ([Tip]).

8. ОСНОВЫ DML. БАЗОВЫЙ НАБОР КОМАНД

.Определить число объектов, цена которых меньше 10000.

select *NedvigimostPL<10000

.Вывести фамилии людей, которые арендовали дом.

select ArDogovorTip='Дом'

9. РАСШИРЕННЫЕ ВОЗМОЖНОСТИ DML

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

select *Dogovor as djoin c as nd.Tip=n.TipBY PL DESC

10. ПРЕДСТАВЛЕНИЕ C ИСПОЛЬЗОВАНИЕМ CREATE VIEV DESIGNER

1.В дереве консоли Console Tree Enterprise Manager перейдем к папке Views базы данных Olya. Enterprise Manager отобразит список имеющихся представлений.

2.Нажмем New (Создать) в панели инструментов Enterprise Manager. Enterprise Manager откроет конструктор представлений View Designer с отображением всех панелей.

.

4.Щелкнем на кнопке Add Table (Добавить таблицу) в панели инструментов конструктора представлений. Конструктор представлений View Designer отобразит диалоговое окно Add Table (Добавление таблицы).

5.На вкладке Tables (Таблицы) добавим запрос Dogovor, Nedvigimost а затем нажмите Close (Закрыть), чтобы закрыть диалоговое окно.

6.В панели сетки Grid Pane выберем столбцы Ar, PL для отображения их в результате.

7.Нажмем кнопку Save (Сохранить). Конструктор запросов Query Designer отобразит диалоговое окно Save As (Сохранить как).

8.Введем Куць Ольга в качестве имени представления.

9.Нажмем ОК. Конструктор представлений View Designer создаст новое представление.

10.Закройте конструктор представлений View Designer.

10.1 Использование представлений

Создав представление, мы можем использовать его точно так же, как таблицу. Мы можем открыть его в Enterprise Manager с использованием конструктора представлений View Designer, либо включить его в другие запросы.

Если мы откроем конструктор представлений View Designer для существующего представления, Enterprise Manager трактует его как виртуальную таблицу. Базовым оператором SQL является либо SELECT * FROM <имя представления>, либо SELECT TOP n FROM <имя представления>, но не оператор запроса, создавший представление.

Откроем конструктор View Designer для представления

1.Перейдем к папке Views базы данных Olya в дереве консоли Console Tree. Enterprise Manager отобразит список представлений для базы данных.

2.Щелкнем правой кнопкой мыши на Куць Ольга в рабочей панели Details Pane, укажем на Open View (Открытие представления) и выберим Return All Rows (Показать все строки). Enterprise Manager откроет конструктор представлений View Designer для данного представления.

3.Щелкнем на кнопке SQL Pane (Панель SQL) в панели инструментов конструктора представлений. Конструктор представлений View Designer отобразит панель SQL Pane, содержащую оператор SELECT для представления.

. Закроем конструктор представлений View Designer.

10.2 Включение представление в запрос

1.Перейдем к папке Tables базы данных Olya в дереве консоли Console Tree. Enterprise Manager отобразит список таблиц в рабочей панели Details Pane.

2.Откроем конструктор запросов Query Designer для таблицы Dogovor, щелкнув правой кнопкой мыши на имени таблицы, указав на Open Table (Открытие таблицы) и выбрав Return All Rows (Показать все строки). Конструктор запросов Query Designer откроет таблицу Dogovor.

3.Щелкнем на кнопке Diagram Pane (Панель диаграмм) в панели инструментов, чтобы отобразить панель диаграмм.

4.Щелкнем на кнопке Add Table (Добавить таблицу). Конструктор запросов Query Designer отобразит диалоговое окно Add Table (Добавление таблицы).

.Откроем вкладку Views (Представления) в диалоговом окне Add Table (Добавление таблицы).

6.Выделим Куць Ольга в списке представлений, нажмем кнопку Add (Добавить), чтобы добавить представление в запрос, а затем нажмите кнопку Close (Закрыть), чтобы закрыть диалоговое окно. Конструктор запросов Query Designer добавит представление в запрос.

7.Щелкнем на кнопке SQL Pane (Панель SQL). Конструктор запросов Query Designer отобразит оператор SELECT для запроса.

. В панели SQL Pane заменим * на Dogovor.Ar, Nedvigimost.Tip, Nedvigimost.PL.

9. Щелкнем на кнопке SQL Pane (Панель SQL), чтобы скрыть панель SQL Pane.

.Нажмем кнопку Run (Выполнить) в панели инструментов конструктора запросов, чтобы исполнить запрос. Конструктор запросов Query Designer отобразит столбцы Dogovor, Nedvigimost.

.Закроем конструктор запросов Query Designer.

10.3 Управление представлениями

Как и для других объектов базы данных, вполне вероятно, что может возникнуть необходимость изменить представление. Это легко можно сделать средствами Enterprise Manager.

Переименование представление

1.Перейдем к папке Views базы данных Olya в дереве консоли Console Tree. Enterprise Manager отобразит список представлений для базы данных.

2.Щелкнем правой кнопкой мыши на представлении Куць Ольга в рабочей панели Details Pane и выберем Rename (Переименовать). Enterprise Manager выделит имя представления.

3.Изменим имя представления на Olya и нажмите клавишу Enter. Enterprise Manager отобразит диалоговое окно, предупреждающее, что изменение имени представления сделает использование любых других объектов, которые на него ссылаются, некорректным.

4.Нажмите Yes (Да). Enterprise Manager подтвердит изменение имени представления.

Модификация представления

1.Щелкнем правой кнопкой мыши на Olya в рабочей панели Details Pane и выберите Design View (Конструирование представления). Enterprise Manager откроет конструктор View Designer для представления.

2. Скроем панель SQL Pane, щелкнув на кнопке SQL Pane (Панель SQL), и скроем панель результатов Results Pane, щелкнув на кнопке Results Pane (Панель результатов).

2.Добавим столбец Tip в таблицу Nedvigimost представления.

3.Нажмем кнопку Save (Сохранить) в панели инструментов конструктора представлений. Конструктор представлений View Designer сохранит новое определение представления.

4.Закройте конструктор представлений View Designer.

Результат представления:

11. ХРАНИМЫЕ ПРОЦЕДУРЫ

11.1 Простые хранимые процедуры

1.Открываем Qvery Analizer и подключаемся к серверу.

2.На панели Editor вводим следующий код.

CREATE PROCEDURE plata_za_nedvigimos *NedvigimostPL<10000

3.Щелкаем мышкой на кнопке «Исполнение запроса», для создания хранимой процедуры.

4.Щелкаем мышкой на кнопке «Новый запрос».

5.В появившемся пустом окне вводим команду «Исполнение хранимой процедуры».

EXECUTE < plata_za_nedvigimos >

6.Для исполнения команды щелкаем на кнопке исполнения.

11.2 Простые хранимые процедуры с входным параметром

1.Щелкаем на значке «Новый запрос».

.Вводим в появившемся окне код процедуры

CREATE PROCEDURE Dogovor

@Arendator CHAR(20)*Dogovor as djoin Nedvigimost as nd.Tip=n.Tipd.Ar=@Arendator

CПИСОК ЛИТЕРАТУРЫ

1. Bob Swart. Delphi 6, ADO, and XML - #"justify">. Codd E. F. Relational completeness of data base sublanguages. - Ibid. 1972, p. 65-98.

3. Вендров А.М. CASE-технологии. Современные методы и средства проек-тирования информационных систем - #"justify">. Дейт К. Введение в системы баз данных. - М.: Издательский дом "Вильямс", 2001. - 1072 с.

. Ульман Дж. Основы систем баз данных /Пер. c англ. М. Р. Когаловского. - М.: Финансы и статистика, 1983. - 334 с.

. Чекалов А. Базы данных: от проектирования до разработки приложений. - СПб.: БХВ-Петербург, 2003. - 384 с.

. Чен П. Модель "сущность-связь" - шаг к единому представлению о данных //СУБД №3, 1995.

СОДЕРЖАНИЕ Постановка задачи2 . Построение DFD-диаграммы3 .1 Создание диаграммы нулевого уровня3 .2 Создание диаграммы первого уровня3 . Опис

Больше работ по теме: