БД вариант 12
.docМинистерство образования Республики Беларусь
Учреждение образования
БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ
Контрольная работа по курсу
"Базы данных"
Вариант 12
Витебск 2011
Содержание
|
Содержание |
|
|
1. Задание |
|
|
2. Логическая структура базы данных. |
|
|
3. Получение файла базы данных в формате Access путем прямого проектирования |
|
|
4. Создание сценария, с помощью которого можно сгенерировать базу данных в формате MS SQL Server |
|
|
Заключение |
|
|
Список используемой литературы |
|
-
Задание
Вариант 10: Создать базу данных «УПРАВЛЕНИЕ РАБОТОЙ НЕФТЕБАЗЫ».
1. Разработать с помощью Erwin логическую и физическую модели данных.
2. Путем прямого проектирования получить файл базы данных в формате Access.
3. Создать сценарий, с помощью которого можно сгенерировать базу данных в формате MS SQL Server.
-
Логическая структура базы данных
При разработке логической структуры данных необходимо выделить сущности. В нашем случае их будет три:
- переработка;
- приход;
- расход;
Исходя из назначения базы данных, в перечисленных ранее сущностях можно задать следующие поля:
-
Сущность «Переработка»:
- «КодПереработки» - первичный ключ, целое, обязательный атрибут, содержит уникальный код, позволяющий однозначно идентифицировать запись;
- «КодПрихода» - внешний ключ (неидентифицирующая связь), целое, обязательный атрибут, связано с полем «КодПрихода» сущности «Приход»;
- «Количество» - целое;
- «Затраты» - целое.
-
Сущность «Приход»:
- «КодПрихода» - первичный ключ, целое, обязательный атрибут, содержит уникальный код, позволяющий однозначно идентифицировать запись;
- «Затраты» - целое;
- «Количество» - целое;
-
Сущность «Расход»:
- «КодРасхода» - первичный ключ, целое, обязательный атрибут, содержит уникальный код, позволяющий однозначно идентифицировать запись;
- «КодПрихода» - внешний ключ (неидентифицирующая связь), целое, обязательный атрибут, связано с полем «КодПрихода» сущности «Приход»;
- «КодПереработки» - внешний ключ (неидентифицирующая связь), целое, обязательный атрибут, связано с полем «КодПереработки» сущности «Переработка»;
- «Сумма» - целое;
- «Количество» - целое;
На основании вышеприведенных соображений в системе Erwin была создана новая модель данных с типом «Логическая/Физическая», в ней были созданы все вышеприведенные сущности (рисунок 2.1).
Рисунок 2.1 – сущности логической структуры базы данных
Далее были созданы необходимые идентифицирующие связи «один ко многим» между сущностями (рисунок 2.2).
Рисунок 2.2 – созданные связей между сущностями
Для созданных идентифицирующих связей были установлены правила ссылочной целостности RESTRICT (запрет вставки и изменения строк для дочерних сущностей, запрет изменения и удаления строк для родительской сущности).
Были созданы правила валидации для первичных ключевых полей всех сущностей (рисунок 2.3).
Рисунок 2.3 – настройка правил валидации
В результате получена модель Erwin, которая является графическим представлением логической структуры разрабатываемой базы данных (рисунок 2.4).
Рисунок 2.4 – логическая структура базы данных
3. Получение файла базы данных в формате Access путем прямого проектирования.
Запускаем СУБД MS Access и создаем новую пустую базу данных NBase.mdb, после чего закрываем Access.
Переключаемся в Erwin в режим показа физической модели, убираем пробелы в названиях полей и корректируем типы и длины полей, ориентируясь на специфику типов данных СУБД Access (рисунок 3.1).
Рисунок 3.1 – физическая модель базы данных
В окне Columns физической модели данных создаем правила валидации для ключевых полей всех сущностей (рисунок 3.2).
Рисунок 3.2 – правила валидации в физической модели данных
В Erwin с помощью команды меню Tools->Forward Engineer/Schema Generation открываем окно Access Schema Generation, нажимаем кнопку Generate и вводим данные, изображенные на рисунке 3.3 в появившееся окно Access Connection.
Рисунок 3.3 – окно Access Connection
Далее нажимаем кнопку «Connect».
При помощи команды Database->Database Connection открываем окно Access Connection и разрываем соединение с базой данных Метео.mdb нажатием кнопки «Disconnect».
Открываем в СУБД MS Access файл базы данных Метео.mdb, нажимаем кнопку «Схема данных» и получаем схему, изображенную на рисунке 3.4.
Рисунок 3.4 – схема данных
4. Создание сценария, с помощью которого можно сгенерировать базу данных в формате MS SQL Server.
Для экспорта полученной логической структуры в СУБД MS SQL Server в системе Erwin были выполнены следующие действия:
1) При помощи команды Select target server была изменена целевая база данных с Access на SQL Server (рисунок 4.1).
Рисунок 4.1 – смена целевой базы данных
При этом физическая модель данных приобрела вид, изображенный на рисунке 4.2.
Рисунок 4.2 – физическая модель данных
В SQL Server Enterprise Manager создаем новую базу данных «meteo».
В Erwin выполняем команду меню Tools->Forward Engineer/Schema Generation и в появившемся окне «SQL Server Schema Generation» нажимаем кнопку «Generate», на экране появляется еще одно окно «SQL Server Connection», в котором указываем название базы данных и сервера баз данных (рисунок 4.3) и нажимаем кнопку «Connect».
Рисунок 4.3 – окно SQL Server Connection
Полученную в результате базу данных можно увидеть в SQL Server Enterprise Manager (рисунок 4.4).
Рисунок 4.4 – созданные таблицы базы данных
Полученный SQL-сценарий создания базы данных приведен ниже:
CREATE RULE Положительное
AS >=0
Line 2: Incorrect syntax near '>'.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
CREATE TABLE Переработка (
Затраты int NULL,
КодПереработки int NOT NULL,
КодПрихода int NOT NULL,
Количество int NULL
)
There is already an object named 'Переработка' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
ALTER TABLE Переработка
ADD PRIMARY KEY (КодПереработки ASC, КодПрихода ASC)
Table 'Переработка' already has a primary key defined on it.
Could not create constraint. See previous errors.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
exec sp_bindrule Положительное, 'Переработка.КодПереработки'
exec sp_bindrule Положительное, 'Переработка.КодПрихода'
The rule 'Положительное' does not exist.
The rule 'Положительное' does not exist.
Execution Failed!
CREATE TABLE Приход (
КодПрихода int NOT NULL,
Затраты int NULL,
Количество int NULL
)
There is already an object named 'Приход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
ALTER TABLE Приход
ADD PRIMARY KEY (КодПрихода ASC)
Table 'Приход' already has a primary key defined on it.
Could not create constraint. See previous errors.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
exec sp_bindrule Положительное, 'Приход.КодПрихода'
The rule 'Положительное' does not exist.
Execution Failed!
CREATE TABLE Расход (
Количество int NULL,
КодРасхода int NOT NULL,
КодПереработки int NOT NULL,
Сумма int NULL,
КодПрихода int NOT NULL
)
There is already an object named 'Расход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
ALTER TABLE Расход
ADD PRIMARY KEY (КодРасхода ASC, КодПереработки ASC,
КодПрихода ASC)
Table 'Расход' already has a primary key defined on it.
Could not create constraint. See previous errors.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
exec sp_bindrule Положительное, 'Расход.КодРасхода'
exec sp_bindrule Положительное, 'Расход.КодПереработки'
exec sp_bindrule Положительное, 'Расход.КодПрихода'
The rule 'Положительное' does not exist.
The rule 'Положительное' does not exist.
The rule 'Положительное' does not exist.
Execution Failed!
ALTER TABLE Переработка
ADD FOREIGN KEY (КодПрихода)
REFERENCES Приход (КодПрихода)
Execution Successful
ALTER TABLE Расход
ADD FOREIGN KEY (КодПереработки, КодПрихода)
REFERENCES Переработка (КодПереработки,
КодПрихода)
Execution Successful
create trigger tD_Переработка on Переработка for DELETE as
/* ERwin Builtin Thu Jan 05 03:10:32 2012 */
/* DELETE trigger on Переработка */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 05 03:10:32 2012 */
/* Переработка R/12 Расход ON PARENT DELETE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Переработка"
CHILD_OWNER="", CHILD_TABLE="Расход"
P2C_VERB_PHRASE="R/12", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_12", FK_COLUMNS="КодПереработки""КодПрихода" */
if exists (
select * from deleted,Расход
where
/* %JoinFKPK(Расход,deleted," = "," and") */
Расход.КодПереработки = deleted.КодПереработки and
Расход.КодПрихода = deleted.КодПрихода
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Переработка because Расход exists.'
goto error
end
/* ERwin Builtin Thu Jan 05 03:10:32 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tD_Переработка' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tI_Переработка on Переработка for INSERT as
/* ERwin Builtin Thu Jan 05 03:10:34 2012 */
/* INSERT trigger on Переработка */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 05 03:10:34 2012 */
/* Приход R/11 Переработка ON CHILD INSERT RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Приход"
CHILD_OWNER="", CHILD_TABLE="Переработка"
P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_11", FK_COLUMNS="КодПрихода" */
if
/* %ChildFK(" or",update) */
update(КодПрихода)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Приход
where
/* %JoinFKPK(inserted,Приход) */
inserted.КодПрихода = Приход.КодПрихода
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT Переработка because Приход does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:34 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tI_Переработка' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tU_Переработка on Переработка for UPDATE as
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
/* UPDATE trigger on Переработка */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insКодПереработки int,
@insКодПрихода int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
/* Переработка R/12 Расход ON PARENT UPDATE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Переработка"
CHILD_OWNER="", CHILD_TABLE="Расход"
P2C_VERB_PHRASE="R/12", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_12", FK_COLUMNS="КодПереработки""КодПрихода" */
if
/* %ParentPK(" or",update) */
update(КодПереработки) or
update(КодПрихода)
begin
if exists (
select * from deleted,Расход
where
/* %JoinFKPK(Расход,deleted," = "," and") */
Расход.КодПереработки = deleted.КодПереработки and
Расход.КодПрихода = deleted.КодПрихода
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Переработка because Расход exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
/* Приход R/11 Переработка ON CHILD UPDATE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Приход"
CHILD_OWNER="", CHILD_TABLE="Переработка"
P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_11", FK_COLUMNS="КодПрихода" */
if
/* %ChildFK(" or",update) */
update(КодПрихода)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Приход
where
/* %JoinFKPK(inserted,Приход) */
inserted.КодПрихода = Приход.КодПрихода
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE Переработка because Приход does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tU_Переработка' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tD_Приход on Приход for DELETE as
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
/* DELETE trigger on Приход */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
/* Приход R/11 Переработка ON PARENT DELETE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Приход"
CHILD_OWNER="", CHILD_TABLE="Переработка"
P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_11", FK_COLUMNS="КодПрихода" */
if exists (
select * from deleted,Переработка
where
/* %JoinFKPK(Переработка,deleted," = "," and") */
Переработка.КодПрихода = deleted.КодПрихода
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Приход because Переработка exists.'
goto error
end
/* ERwin Builtin Thu Jan 05 03:10:36 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tD_Приход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tU_Приход on Приход for UPDATE as
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
/* UPDATE trigger on Приход */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insКодПрихода int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
/* Приход R/11 Переработка ON PARENT UPDATE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Приход"
CHILD_OWNER="", CHILD_TABLE="Переработка"
P2C_VERB_PHRASE="R/11", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_11", FK_COLUMNS="КодПрихода" */
if
/* %ParentPK(" or",update) */
update(КодПрихода)
begin
if exists (
select * from deleted,Переработка
where
/* %JoinFKPK(Переработка,deleted," = "," and") */
Переработка.КодПрихода = deleted.КодПрихода
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Приход because Переработка exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tU_Приход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tI_Расход on Расход for INSERT as
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
/* INSERT trigger on Расход */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
/* Переработка R/12 Расход ON CHILD INSERT RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Переработка"
CHILD_OWNER="", CHILD_TABLE="Расход"
P2C_VERB_PHRASE="R/12", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_12", FK_COLUMNS="КодПереработки""КодПрихода" */
if
/* %ChildFK(" or",update) */
update(КодПереработки) or
update(КодПрихода)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Переработка
where
/* %JoinFKPK(inserted,Переработка) */
inserted.КодПереработки = Переработка.КодПереработки and
inserted.КодПрихода = Переработка.КодПрихода
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT Расход because Переработка does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:37 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tI_Расход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
create trigger tU_Расход on Расход for UPDATE as
/* ERwin Builtin Thu Jan 05 03:10:38 2012 */
/* UPDATE trigger on Расход */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insКодРасхода int,
@insКодПереработки int,
@insКодПрихода int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 05 03:10:38 2012 */
/* Переработка R/12 Расход ON CHILD UPDATE RESTRICT */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="Переработка"
CHILD_OWNER="", CHILD_TABLE="Расход"
P2C_VERB_PHRASE="R/12", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_12", FK_COLUMNS="КодПереработки""КодПрихода" */
if
/* %ChildFK(" or",update) */
update(КодПереработки) or
update(КодПрихода)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Переработка
where
/* %JoinFKPK(inserted,Переработка) */
inserted.КодПереработки = Переработка.КодПереработки and
inserted.КодПрихода = Переработка.КодПрихода
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE Расход because Переработка does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 05 03:10:38 2012 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
There is already an object named 'tU_Расход' in the database.
General SQL Server error: Check messages from the SQL Server.
Execution Failed!
Schema Generation Complete
2 queries succeeded. 17 queries failed.
Заключение
Для выполнения работы использовались следующие программные средства:
- Erwin 4.1;
- MS Access 2000;
- MS SQL Server 2000.
В ходе контрольной работы была создана база данных «УПРАВЛЕНИЕ РАБОТОЙ НЕФТЕБАЗЫ».
При помощи средства Erwin были разработаны логическая и физическая модели данных.
Путем прямого проектирования был получен файл базы данных в формате Access NBase.mdb.
Также в контрольной работе был получен SQL-сценарий для создания базы данных в формате MS SQL-Server, текст сценария находится в третьем разделе данной пояснительной записки.
Список используемой литературы
-
Бутов А. А., Орешко И. Г., Шестаков Е. А. Визуальные и программные средства проектирования реляционных баз данных. Лабораторный практикум для студ.– Минск : БГУИР, 2009. – 108 с.
-
Артемов Д., Microsoft SQL Server 2000: профессионалы для профессионалов. – Русская редакция, 2005г. – 512 с.