- •Министерство образования Российской Федерации Пензенский государственный университет
- •Пенза 2004
- •Введение
- •1. Архитектура баз данных ms sql Server 2000
- •Физическая архитектура базы данных sql Server 2000
- •1.1.1. Файлы данных и группы файлов
- •1.1.2. Страничная организация файлов данных
- •ЛоГическая архитектура базы данных sql Server 2000
- •1.3. Системные базы данных sql server 2000
- •Создание и сопровождение баз данных средствами enterprise manager
- •3. Создание и сопровождение таблиц баз данных средствами enterprise manager
- •3.1. Основные принципы создания таблиц
- •3.2. Создание, модификация и удаление таблиц средствами Enterprise Manager
- •3.3. Управление диаграммами
- •4. Создание и управление индексами средствами Enterprise manager
- •5. Создание представлений средствами Enterprise manager
- •6. Основы программирования на языке Transact-sql
- •6.1. Средства языка Transact–sql
- •6.2. Константы, имена, идентификаторы, переменные, выражения в языке Transact–sql
- •6.3. Типы данных sql Server 2000
- •6.4. Создание и удаление баз данных, таблиц и представлений
- •6.4.1. Создание и удаление баз данных
- •6.4.2. Создание и удаление таблиц
- •6.4.3. Создание представлений
- •6.5. Создание и управление индексами
- •6.6. Вставка, удаление и изменение данных
- •6.7. Выборка данных
- •Раздел into позволяет создать новую таблицу для хранения результатов запроса, структура которой будет определяться списком выбора, т.Е. Динамически при выполнении запроса.
- •6.8. Функции sql Server 2000
- •6.8.1. Встроенные функции sql Server 2000
- •6.8.2. Функции пользователя
- •6.9. Хранимые процедуры sql Server 2000
- •6.9.1. Создание хранимых процедур
- •6.10. Триггеры
- •6.11. Создание и управление транзакциями
- •6.12. Создание, отладка и оптимизация sql–модулей
- •7. Лабораторный практикум
- •Создание диаграммы базы данных;
- •Рекомендуемые источники Печатные издания
- •Интернет-ресурсы1
- •Приложение 1. Примеры операторов языка Transact-sql
- •1.1. Создание баз данных
- •Create database Archive
- •Use master
- •Filegroup SalesGroup1
- •Filegroup SalesGroup2
- •Create database Sales
- •Create database Employees
- •1.2. Создание таблиц баз данных
- •1.3. Создание индексов
- •1.4. Создание представлений
- •From dbo. Authors
- •1.5. Добавление, модификация и удаление данных в таблицах
- •Insert Into TabF default values
- •Insert Into TabG (Col1,Col2)
- •1.6. Выборка данных из таблиц
- •If update (Phone)
- •Insert into #aaa values (111)
- •Insert into #aaa values (222)
- •Insert into #aaa values (333)
- •1.9. Создание пользовательских функций
- •1.10. Создание хранимых процедур пользователя
- •Приложение 2. Описание предметных областей, схем баз данных и запросов для лабораторного практикума
- •2.1. Предметная область «Летопись острова Санта-Белинда»
- •2.2. Предметная область «Скачки»
- •2.3. Предметная область «Хроника восхождений в альпинистском клубе»
- •2.4. Предметная область базы данных медицинского кооператива
- •2.5. Предметная область базы данных «Городская Дума»
- •2.6. Предметная область базы данных рыболовной фирмы
- •2.7. Предметная область база данных фирмы, проводящей аукционы
- •2.8. Предметная область база данных библиотеки
- •Предметная область базы данных для обслуживания работы конференции
- •2.10. Предметная область базы данных для обслуживания склада
2.7. Предметная область база данных фирмы, проводящей аукционы
Словесное описание предметной области. Фирма занимается продажей с аукциона антикварных изделий и произведений искусства. Владельцы вещей, выставляемых на проводимых фирмой аукционах, юридически являются продавцами. Лица, приобретающие эти вещи, именуются покупателями. Получив от продавцов партию предметов, фирма решает, на каком из аукционов выгоднее представить конкретный предмет. Перед проведением очередного аукциона каждой из выставляемых на нем вещей присваивается отдельный номер лота. Две вещи, продаваемые на различных аукционах, могут иметь одинаковые номера лотов.
В книгах фирмы делается запись о каждом аукционе. Там отмечаются дата, место и время его проведения, а также специфика (например, выставляются картины, написанные маслом и не ранее 1900 г.). Заносятся также сведения о каждом продаваемом предмете: аукцион, на который он заявлен, номер лота, продавец, отправная цена. Продавцу разрешается выставлять любое количество вещей, а покупатель имеет право приобретать любое их количество. Одно и то же лицо или фирма может выступать и как продавец, и как покупатель. После аукциона служащие фирмы, проводящей аукционы, записывают фактическую цену, уплаченную за проданный предмет, и фиксируют данные покупателя.
Схема базы данных в нотации IDEF1X
Описание таблиц базы данных
№ п.п |
Название |
Идентификатор |
Назначение |
Тип связи |
Атрибуты для связи |
1. |
Продавцы |
Seller |
Список продавцов |
1:М Изделия |
Ид. № продавца |
2. |
Изделия |
Products |
Список изделий |
М:1 Продавцы |
Ид. № продавца |
1:М Изделия на аукционах |
Ид. № изделия |
||||
3. |
Аукционы |
Auctions |
Список аукционов |
1:М Изделия на аукционах |
Ид. № аукциона |
4. |
Покупатели |
Buyers |
Список покупателей |
1:М Изделия на аукционах |
Ид. № покупателя |
5. |
Изделия на аукционах |
ProductsA |
Список изделий на аукционах |
М:1 Изделия |
Ид. № изделия |
М:1 Аукционы |
Ид. № аукциона |
||||
М:1 Покупатели |
Ид. № покупателя |
Описание атрибутов таблицы Продавцы
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № продавца |
ID_Seller |
N |
3 |
1÷999 |
– |
+ |
PK |
2. |
ФИО продавца |
FIO_Seller |
С |
35 |
Только буквы |
– |
+ |
AK |
3. |
Адрес продавца |
Address_ Seller |
С |
40 |
– |
– |
+ |
– |
4. |
Телефон продавца |
Phone_Sel-ler |
С |
15 |
– |
– |
– |
– |
5. |
Пол продавца |
Sex_Seller |
B |
1 |
0 или 1 |
1 |
+ |
– |
Описание атрибутов таблицы Изделия
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № изделия |
ID_ Pro-duct |
N |
5 |
1÷99999 |
– |
+ |
PK |
2. |
Наименование |
Name |
С |
35 |
Только буквы |
– |
+ |
– |
3. |
Тип |
Type |
С |
40 |
– |
– |
+ |
– |
4. |
Дата поступления |
Date_D |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
5. |
Признак - продано |
Sold |
B |
1 |
0 или 1 |
1 |
– |
– |
6. |
Ид. № продавца |
ID_Seller |
N |
3 |
1÷999 |
– |
+ |
FK |
Описание атрибутов таблицы Аукционы
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № аукциона |
ID_Auctions |
N |
3 |
1÷999 |
– |
+ |
PK |
2. |
Дата проведения |
Date_U |
D |
8 |
01.01.1980÷01.01.2010 |
– |
+ |
– |
3. |
Место |
Place |
С |
40 |
– |
– |
– |
– |
4. |
Специфика |
Specifics |
С |
60 |
– |
– |
– |
– |
Описание атрибутов таблицы Покупателя
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № покупателя |
ID_Buyer |
N |
3 |
1÷999 |
– |
+ |
PK |
2. |
ФИО покупателя |
FIO_Buyer |
С |
35 |
Только буквы |
– |
+ |
AK |
3. |
Адрес покупателя |
Address_ Buyer |
С |
40 |
– |
– |
+ |
– |
4. |
Телефон покупателя |
Phone_ Buy-er |
С |
15 |
– |
– |
– |
– |
5. |
Пол покупателя |
Sex_Buyer |
B |
1 |
0 или 1 |
1 |
+ |
– |
Описание атрибутов таблицы Изделия на аукционах
№ п/п |
Название |
Идентификатор |
Тип |
Размер |
Ограничения |
Знач. по умолчанию |
Обязательное поле? |
Признак ключа |
1. |
Ид. № изделия |
ID_ Pro-duct |
N |
5 |
1÷99999 |
– |
+ |
FK |
2. |
Ид. № аукциона |
ID_Auctions |
N |
3 |
1÷999 |
– |
+ |
FK |
3. |
№ лота |
Lot |
N |
3 |
1÷999 |
– |
+ |
– |
4. |
Стартовая цена |
Price_S |
R |
11,2 |
1÷99999999 |
– |
+ |
– |
5. |
Цена покупки |
Price_B |
R |
11,2 |
1÷99999999 |
– |
– |
– |
6. |
Ид. № покупателя |
ID_Buyer |
N |
3 |
1÷999 |
– |
– |
FK |
Запросы на выборку данных
Выдать список всех изделий, которые были получены в заданную дату. Список упорядочить по типу изделия по убыванию, по наименованию – по возрастанию.
Для заданного продавца выдать список всех изделий, которые он когда-либо выставлял на аукцион, их наименование, тип, дату поступления и все характеристики продавца.
Вывести список всех выставляемых на аукцион изделий, стартовая цена которых ниже средней по изделиям.
Вывести список всех продавцов и покупателей, проживающих в заданном городе. Наименование города является частью адреса.
Выдать список всех продавцов, которые в разное время покупали изделия у заданного покупателя. В результирующей таблице должны быть указаны характеристики продавца и покупателя, а также характеристики изделия.
Представления
Создать представление, в котором для каждого аукциона выдать все характеристики выставляемых на них изделий, их стартовая цена и цена покупки.
Создать представление, в котором для каждого покупателя хранились бы все купленные им изделия, их характеристики и характеристики покупателя.
Хранимые процедуры
Написать хранимую процедуру, которая оформляет покупку некоторого выставленного на аукцион изделия. Необходимо в таблице Изделия установить признак Продано. В таблице Изделия на аукционах задать значения атрибутов Цена покупки и Ид. № покупателя.
Написать хранимую процедуру, которая для каждого покупателя рассчитает суммарную стоимость всех его покупок за указанный период.
Триггеры
При удалении записи о покупателе проверять, были ли совершены им какие-либо покупки. Покупатель, совершивший хотя бы одну покупку, не может быть удален.
При изменении записи об изделиях на аукционах проверять, чтобы цена покупки не стала ниже стартовой цены.
При добавлении нового изделия необходимо проверить, не превысило ли число непроданных изделий данного продавца заданного значения. Новое изделие не может быть принято при наличие некоторого количество ранее принятых, но до сих пор не проданных изделий.