|
База данных. Курсовой проект по базам данных. Назипова Д. Р. Проверил Хайруллин А. Ф. Казань 2017 первая часть
Казанский (Приволжский) федеральный университет
Курсовой проект по базам данных.
Выполнила: Назипова Д.Р.
Проверил: Хайруллин А.Ф.
Казань - 2017
ПЕРВАЯ ЧАСТЬ.
1)*Созданы три таблицы
*Описание таблиц включает использование (хотя бы по одному разу): NOTNULL, DEFAULT, PRIMARYKEY, CHECK и IDENTITY.
Исходные данные:
Файл01. Материалы
Содержит сведения о материалах, которые используются при изготовлении деталей или в электромонтажных работах при сборке изделий.
код материала;
наименование;
единица измерения;
плановая цена за единицу.
Файл16а. Склады
номер склада;
фамилия материально ответственного лица.
Файл16б. Наличие материалов на складах
номер склада;
код материала;
единица измерения;
количество, имеющееся на складе;
дата последней операции.
16а:Склады <–>> 16б:Наличие материалов <<–> 01:Материалы.
CREATE TABLE WAREHOUSE
(
ID_WHOUSE INT IDENTITY(1,1) NOT NULL,
SURNAME VARCHAR(30) NOT NULL,
PRIMARY KEY (ID_WHOUSE)
);
CREATE TABLE MATERIALE
(
KOD_MATER INT IDENTITY(100,1) NOT NULL,
NAME VARCHAR(30) NOT NULL,
ED_IZM VARCHAR(5),
PLAN_PRICE INT,
CHECK(PLAN_PRICE>0),
CONSTRAINT PK_MATERIAL PRIMARY KEY(KOD_MATER)
);
CREATE TABLE MATER_WHOUSE
(
ID_WHOUSE INT NOT NULL,
KOD_MATER INT NOT NULL,
ED_IZM VARCHAR(5),
COL_WHOUSE INT NOT NULL,
DATE_OPER DATETIME DEFAULT(GETDATE),
CONSTRAINT PK_MATER_WHOUSE PRIMARY KEY(ID_WHOUSE,KOD_MATER),
CONSTRAINT FK_ID_WHOUSE FOREIGN KEY(ID_WHOUSE) REFERENCES WAREHOUSE(ID_WHOUSE),
CONSTRAINT FK_KOD_MATER FOREIGN KEY(KOD_MATER) REFERENCES MATERIALE(KOD_MATER)
ON DELETE CASCADE ON UPDATE CASCADE,
CHECK(COL_WHOUSE>0)
);
CREATE TRIGGER ID_WH ON WAREHOUSE
INSTEAD OF DELETE AS
*Параметр NOT NULL вводится для контроля начального заполнения поля при его обработке
*Параметр DEFAULT задает значение соответствующего поля по умолчанию.
*PRIMARY KEY – ограничение, указывающее, что в данной таблице данное поле представляет собой первичный ключ.
*Каждому полю со свойством IDENTITY SQL Server ставит в соответствие свой счетчик и будет следить за нумерацией вновь создаваемых записей.
2)Описаны две межтабличные связи:
Одна без использования системного каскадного удаления и обновления. К этой связи определены два триггера, один из них на каскадное удаление в дочерней таблице.
Другая с использованием системного каскадного удаления и обновления (ON DELETE CASCADE ON UPDATE CASCADE).
Создана хранимая процедура с выходными параметрами.
Триггер 1 (каскадное удаление):
CREATE TRIGGER ID_WH ON WAREHOUSE
INSTEAD OF DELETE AS
BEGIN
DELETE FROM MATER_WHOUSE
WHERE ID_WHOUSE IN (SELECT ID_WHOUSE FROM DELETED)
DELETE FROM WAREHOUSE
WHERE ID_WHOUSE IN (SELECT ID_WHOUSE FROM DELETED)
END
Триггер 2 (изменение единиц измерения):
CREATE TRIGGER ID_M ON MATERIALE
AFTER UPDATE AS
BEGIN
UPDATE MATER_WHOUSE
SET ED_IZM= (SELECT ED_IZM FROM INSERTED) WHERE MATER_WHOUSE.KOD_MATER=(SELECT KOD_MATER FROM INSERTED)
END
Хранимая процедура (подсчет общей суммы определенного товара):
CREATE PROCEDURE SUM_COL_WHOUSE
@NAME VARCHAR(30),
@COL_WHOUSE int out
AS
BEGIN
Select sum(COL_WHOUSE)
From MATER_WHOUSE w join MATERIALE m on w.KOD_MATER=m.KOD_MATER
WHERE m.NAME= @NAME
END
3)Подготовлен SQL-script для загрузки данных в таблицы и данные загружены (не менее пяти строк в каждой таблице). База данных корректна в смысле явно описанных ограничений целостности.
USE [Job_1(1)];
DELETE FROM WAREHOUSE;
DELETE FROM MATER_WHOUSE;
DELETE FROM MATERIALE;
SET IDENTITY_INSERT WAREHOUSE ON
INSERT INTO WAREHOUSE(ID_WHOUSE,SURNAME)
VALUES('1','Abramson'),
('2','Aleksu'),
('3','Kirk'),
('4','Miers');
SET IDENTITY_INSERT WAREHOUSE OFF
SET IDENTITY_INSERT MATERIALE ON
INSERT INTO MATERIALE(KOD_MATER,NAME,ED_IZM, PLAN_PRICE)
VALUES ('1000','bracken', 'sh', '1200'),
('1001','ivy', 'sh', '1400'),
('1002','bush', 'sh', '1600'),
('1003','cactus', 'sh', '1800'),
('1004','fern', 'sh', '1000'),
('1005','ivy', 'sh', '999'),
('1006','moss', 'sh', '99'),
('1007','buttercup', 'sh', '1550');
SET IDENTITY_INSERT MATERIALE OFF
INSERT INTO MATER_WHOUSE(ID_WHOUSE,KOD_MATER,ED_IZM,COL_WHOUSE,DATE_OPER)
VALUES
('1','1000','sh','1000','2013-12-09 20:00:00.000'),
('2','1001','kg','1000','2012-11-09 20:00:00.000'),
('3','1002','sh','200','2010-12-06 20:00:00.000'),
('4','1003','sh','3000','2011-12-11 20:00:00.000'),
('2','1003','sh','1000','2013-12-09 20:00:00.000'),
('2','1000','kg','1000','2012-11-09 20:00:00.000'),
('3','1000','sh','200','2010-12-06 20:00:00.000'),
('4','1000','sh','3000','2011-12-11 20:00:00.000');
SELECT*FROM MATER_WHOUSE;
ВТОРАЯ ЧАСТЬ.
1)Клиентская часть:
*Visual Studio-проект должен включать: главную экранную форму, визуализирующую таблицы ранее созданной базы данных (DataSet, TableAdapter, DataGridView, BindingSource).
Моделью данных будет «Набор данных» - Job_1(1) – снимок части нашей базы данных, необходимой в клиентском приложении.
Он хранит коллекцию таблиц, каждая из них является объектом расширения класса DataTable.
Для каждой таблицы кроме этого создается соответствующий класс TableAdapter.
Если мы хотим сами контролировать, откуда что появилось, добавим на форму элемент из «Панели элементов» DataGridView.
BindingSource связывает элементы на форме с источниками данных.
Для упрощения навигации по таблице добавим компонент BindingNavigator на форму приложения.
Реализовать алтернативную форму с динамическим отображением всех таблиц в одном DataGridView.
Отобразить результат выполнения хранимой процедуры в метке (Label) на основной или альтернативной форме-1
Connection – подключение к БД
CommandType – тип команды: SQL-команда, хранимая процедура или таблица.
CommandText – собственно текст команды, имя хранимой процедуры или таблицы.
Parameters – коллекция параметров команды.
Задача-1. Сведения о наличии материалов в малом (<10) количестве: наименование материала; номер склада; количество.
1)В первом варианте решения поставленной задачи воспользуемся Set-ориентированными средствами SQL для работы с таблицами и объектом типа TableAdapter для связи с SQL-сервером.
private void btnFill_Click(object sender, EventArgs e)
{
int kolvo = Convert.ToInt32(txtKolvo.Text);
this.zadacha1TableAdapter.Fill(_Job_1_1_DataSet.Zadacha1, kolvo);
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
dataGridView1.AutoGenerateColumns = true;
this.zadacha1TableAdapter.Fill(_Job_1_1_DataSet.Zadacha1, 0);
this.Text = "Запрос на основании объекта TableAdapter";
}
2)Во втором варианте решения поставленной задачи воспользуемся Record-ориентированными средствами Visual Studio для работы с таблицами, наиболее близкими к традиционным процедурным средствам обработки файлов.
public void Set_zadacha2()
{
_Job_1_1_DataSet.Zadacha1.Clear();
foreach (_Job_1_1_DataSet.MATER_WHOUSERow rRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
if (rRow.COL_WHOUSE < Convert.ToInt32(textBox1.Text))
{
foreach (_Job_1_1_DataSet.MATERIALERow n2 in _Job_1_1_DataSet.MATERIALE.Rows)
if (n2.KOD_MATER == rRow.KOD_MATER)
{
_Job_1_1_DataSet.Zadacha1Row zRow = _Job_1_1_DataSet.Zadacha1.NewZadacha1Row();
zRow.NAME = n2.NAME;
zRow.ID_WHOUSE = rRow.ID_WHOUSE;
zRow.COL_WHOUSE = rRow.COL_WHOUSE;
_Job_1_1_DataSet.Zadacha1.Rows.Add(zRow);
}
}
}
dataGridView1.Refresh();
}
3)В третьем варианте решения поставленной задачи, воспользуемся первичным ключами для поиска в таблице с интересующим нас кодом товара. Поиск строки в таблице по её ключу можно выполнить с помощью метода Find объекта DataRowCollection. У этого метода две перегрузки для одинарного и для составного первичного ключа.
public void Set_zadacha3()
{ //очистим таблицу от предыдущих значений
_Job_1_1_DataSet.Zadacha1.Clear();
foreach (_Job_1_1_DataSet.MATER_WHOUSERow rRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
if (rRow.COL_WHOUSE < Convert.ToInt32(textBox1.Text))
{
{
_Job_1_1_DataSet.MATERIALERow n2 = _Job_1_1_DataSet.MATERIALE.Rows.Find(rRow.KOD_MATER) as _Job_1_1_DataSet.MATERIALERow;
_Job_1_1_DataSet.Zadacha1Row zRow = _Job_1_1_DataSet.Zadacha1.NewZadacha1Row();
zRow.NAME = n2.NAME;
zRow.ID_WHOUSE = rRow.ID_WHOUSE;
zRow.COL_WHOUSE = rRow.COL_WHOUSE;
_Job_1_1_DataSet.Zadacha1.Rows.Add(zRow);
}
}
}
dataGridView1.Refresh();
}
4) В четвертом варианте решения поставленной задачи воспользуемся операционной связью DataRelation для таблиц в наборе данных.
public void Set_zadacha4()
{ //очистим таблицу от предыдущих значений
_Job_1_1_DataSet.Zadacha1.Clear();
foreach (_Job_1_1_DataSet.MATER_WHOUSERow rRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
if (rRow.COL_WHOUSE < Convert.ToInt32(textBox1.Text))
{
{
_Job_1_1_DataSet.MATERIALERow n = rRow.GetParentRow("FK_KOD_MATER") as _Job_1_1_DataSet.MATERIALERow;
_Job_1_1_DataSet.Zadacha1Row zRow = _Job_1_1_DataSet.Zadacha1.NewZadacha1Row();
zRow.ID_WHOUSE = rRow.ID_WHOUSE;
zRow.COL_WHOUSE = rRow.COL_WHOUSE;
zRow.NAME = n.NAME;
_Job_1_1_DataSet.Zadacha1.Rows.Add(zRow);
}
}
}
dataGridView1.Refresh();
}
5) В пятом варианте решения поставленной задачи будем использовать присоединенный режим работы с базой данных. То есть сами открываем соединение с БД, выполняем команду SQL и закрываем соединение. Основной объект получения данных SqlDataReader.
private void FillGridByReader()
{
SqlConnection c = new SqlConnection(Properties.Settings.Default.Job_1_1_ConnectionString);
c.Open();
String str = "SELECT NAME, ID_WHOUSE, COL_WHOUSE FROM MATER_WHOUSE, MATERIALE " +
"WHERE MATER_WHOUSE.KOD_MATER= MATERIALE.KOD_MATER AND " +
" MATER_WHOUSE.COL_WHOUSE< " + textBox1.Text;
SqlCommand cmd = new SqlCommand(str, c);
SqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);
c.Close();
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dataGridView1.DataSource = bs;
dataGridView1.Refresh();
}
ТРЕТЬЯ ЧАСТЬ.
Создать представление (VIEW), которое показывает в виде одной таблицы данные из трех таблиц, соединенных в соответствии с межтабличными связями межу ними.
К задаче-2 задания создать SQL-запрос с группировкой и решение с помощью record-ориентированных средств.
Задача-2. Для каждого материала: наименование; сколько складов, на которых этот материал имеется в количестве > 100.
Запрос:
SELECT COUNT(MATER_WHOUSE.COL_WHOUSE) AS ALLCOL, MATERIALE.KOD_MATER
FROM MATERIALE , MATER_WHOUSE
WHERE MATERIALE.KOD_MATER=MATER_WHOUSE.KOD_MATER AND
MATER_WHOUSE.COL_WHOUSE>=@kolvo
GROUP BY MATERIALE.KOD_MATER
Record-ориентированный подход:
public void QueryZ2()
{
_Job_1_1_DataSet.Zadacha2.Clear();
int kod = -1, kol = 0;
//код товара и сумма продаж этого товара
_Job_1_1_DataSet.Zadacha2Row zRow = null;
//строка результирующей таблицы
foreach (_Job_1_1_DataSet.MATER_WHOUSERow rRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
if (rRow.COL_WHOUSE >= Convert.ToInt32(textBox1.Text))
{
if (zRow == null)//если первая запись
{
kod = rRow.KOD_MATER;
kol = 1;
zRow = _Job_1_1_DataSet.Zadacha2.NewZadacha2Row();
}
else if (kod == rRow.KOD_MATER) //если тот же товар
{
kol += 1;
}
else //если новый товар
{
//если выполняется условие записываем строку
zRow.ALLCOL = kol;
zRow.KOD_MATER = _Job_1_1_DataSet.MATERIALE.FindByKOD_MATER(kod).KOD_MATER;
_Job_1_1_DataSet.Zadacha2.Rows.Add(zRow);
kod = rRow.KOD_MATER;//подготовливаем новую строку
kol= 1;
zRow = _Job_1_1_DataSet.Zadacha2.NewZadacha2Row();
}
}
}
if (zRow != null)//проверяем последний товар
if (kol > 0)
{
zRow.ALLCOL = kol;
zRow.KOD_MATER = _Job_1_1_DataSet.MATERIALE.FindByKOD_MATER(kod).KOD_MATER;
_Job_1_1_DataSet.Zadacha2.Rows.Add(zRow);
}
dataGridView1.Refresh();
}
К задаче-3 задания написать две программы: решение с помощью кванторного SQL-запроса с подзапросами и решение с помощью record-ориентированных средств.
Задача-3. Все материалы с ценой > 200, такие что: на каждом складе этот материал имеется в наличии в количестве > 100.
Запрос:
SELECT KOD_MATER, NAME
FROM MATERIALE
WHERE (PLAN_PRICE >= @price) AND (NOT EXISTS
(SELECT ID_WHOUSE
FROM WAREHOUSE
WHERE (NOT EXISTS
(SELECT ID_WHOUSE, KOD_MATER, ED_IZM, COL_WHOUSE, DATE_OPER
FROM MATER_WHOUSE
WHERE (NOT (COL_WHOUSE <= @kolvo)) AND (WAREHOUSE.ID_WHOUSE = ID_WHOUSE) AND (MATERIALE.KOD_MATER = KOD_MATER)))))
Решение с помощью record-ориентированных средств:
void ZADACHA3()
{
bool aT, eR;
this.Text = " Запрос, содержащий подзапросы (Record)";
_Job_1_1_DataSet.Zadacha3.Clear();
foreach (_Job_1_1_DataSet.MATERIALERow pRow in _Job_1_1_DataSet.MATERIALE.Rows)
{
if (Convert.ToInt32(pRow["PLAN_PRICE"]) >= Convert.ToInt32(textBox1.Text))
{
aT = true; //любой товар
foreach (_Job_1_1_DataSet.WAREHOUSERow tRow in _Job_1_1_DataSet.WAREHOUSE.Rows)
{
{
eR = false;
foreach (_Job_1_1_DataSet.MATER_WHOUSERow rRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
eR = (Convert.ToInt32(rRow["COL_WHOUSE"]) >= Convert.ToInt32(textBox2.Text)) && (rRow["KOD_MATER"].ToString() == pRow["KOD_MATER"].ToString());
if (eR)
break;
}
aT = eR;
}
if (!aT)
break;
}
if (aT)
{
_Job_1_1_DataSet.Zadacha3Row zRow = _Job_1_1_DataSet.Zadacha3.NewZadacha3Row();
zRow["NAME"] = pRow["NAME"];
zRow["KOD_MATER"] = pRow["KOD_MATER"];
_Job_1_1_DataSet.Zadacha3.Rows.Add(zRow);
}
}
}
dataGridView1.Refresh();
}
Решение с помощью кванторного SQL-запроса с подзапросами:
void ZADACHA3()
{
bool t = false;
_Job_1_1_DataSet.Zadacha3.Clear();
foreach (_Job_1_1_DataSet.MATERIALERow mRow in _Job_1_1_DataSet.MATERIALE.Rows)
{
t = false;
foreach (_Job_1_1_DataSet.WAREHOUSERow cRow in _Job_1_1_DataSet.WAREHOUSE.Rows)
{
if (mRow.PLAN_PRICE >= Convert.ToInt32(textBox1.Text))
{
t = true;
foreach (_Job_1_1_DataSet.MATER_WHOUSERow dRow in _Job_1_1_DataSet.MATER_WHOUSE.Rows)
{
if ((cRow.ID_WHOUSE == dRow.ID_WHOUSE) && (dRow.KOD_MATER == mRow.KOD_MATER) && (dRow.COL_WHOUSE >= Convert.ToInt32(textBox2.Text)))
{
t = true;
break;
}
else if ((cRow.ID_WHOUSE != dRow.ID_WHOUSE) || (dRow.KOD_MATER != mRow.KOD_MATER) || (dRow.COL_WHOUSE <= Convert.ToInt32(textBox2.Text)))
{
t = false;
continue;
}
}
}
if (!t) break;
}
if (t)
{
_Job_1_1_DataSet.Zadacha3Row zRow = _Job_1_1_DataSet.Zadacha3.NewZadacha3Row();
zRow.KOD_MATER = mRow.KOD_MATER;
zRow.NAME = mRow.NAME;
_Job_1_1_DataSet.Zadacha3.AddZadacha3Row(zRow);
}
}
dataGridView1.Refresh();
}
|
|
|
Скачать 229.5 Kb.