Муниципальное общеобразовательное учреждение
МОУ СОШ № 15
«Методическая разработка по созданию баз данных
на диалекте "Transact-SQL" для СУБД "Microsoft SQL Server"
Тема проекта:
Создание базы данных «Склад» -- создания таблиц, обеспечения целостности на уровне сущности и отношения, заполнения таблиц и запросов на выборку данных.
Глушков Николай Васильевич – учитель высшей категории
средней общеобразовательной
школы № 15 г. Подольска Московской области , выпускник МОПИ
им. Н.К. Крупской физмат ф-т 1964 г.,
с окончанием аспирантуры в 1970 г
по кафедре теоретическая физика.
Подольск 2016
Оглавление
18
Введение
(Обоснование выбора темы и ее актульность)
В процессе практической деятельности современный человек неизбежно сталкивается с проблемой обработки больших объемов информации, необходимой для решения различных социальных, военных и хозяйственных задач. В настоящее время эта проблема решается с помощью информационных систем, представляющих собой совокупность взаимосвязанных данных, а также средств и методов управления ими. Информационные системы в первую очередь должны облегчать труд человека и быть ориентированы на хранение, выбор и поддержание в актуальном состоянии взаимосвязанных данных. В связи с этим необходимо, чтобы основной компонент информационной системы – база данных (БД) как можно более полно соответствовал обычно довольно сложной модели реального мира, для управления которой эта система предназначена. Однако создание современных информационных систем представляет собой сложнейшую задачу и требует применения специальных методик и инструментов.
SQL: универсальный язык для работы с базами данных
Введение в управление реляционными базами данных
sql часто называют языком эсперанто для систем управления базами данных (СУБД). Действительно, в мире нет другого языка для работы с базами данных (БД), который бы настолько широко использовался в программах. Программисты, администраторы СУБД, бизнес-аналитики - все они с успехом обрабатывают данные с помощью sql. Знание этого языка полезно всем, кому приходится иметь дело с БД.
Однако для обычного пользователя совсем не обязательно знать sql целиком и полностью. В sql - зная немногое, можно получать множество полезных результатов. В итоге можно самостоятельно создавать таблицы и вводить в них информацию, составлять запросы и работать с отчетами. Эти знания могут стать базой для дальнейшего самостоятельного освоения sql.
Что такое sql?
sql - это специализированный непроцедурный язык, позволяющий описывать данные, осуществлять выборку и обработку информации из реляционных СУБД. Специализированность означает, что sql предназначен лишь для работы с БД; нельзя создать полноценную прикладную систему только средствами этого языка -- для этого потребуется использовать другие языки, в которые можно встраивать sql-команды. Поэтому sql еще называют вспомогательным языковым средством для обработки данных. Вспомогательный язык используется только в комплексе с другими языками.
В прикладном языке общего назначения обычно имеются средства для создания процедур, а в sql их нет. С его помощью нельзя указать, каким образом должна выполняться некоторая задача, а можно лишь определить, в чем именно она заключается. Другими словами, при работе с sql нас интересуют результаты, а не процедуры для их получения.
Наиболее существенным свойством sql является возможность доступа к реляционным БД. Многие даже считают, что выражения "БД, обрабатываемая средствами sql" и "реляционная БД" - синонимы. Однако скоро вы убедитесь, что между ними имеется разница. В стандарте sql-92 даже нет термина отношение (relation).
Что такое реляционная СУБД?
Если не вдаваться в подробности, то реляционная СУБД - это система, основанная на реляционной модели управления данными.
Понятие реляционной модели было впервые предложено в работе д-ра Е. Ф. Кодда, опубликованной в 1970 г. В ней был описан математический аппарат для структуризации данных и управления ими, а также предложена абстрактная модель для представления любой реальной информации. До этого при использовании БД требовалось учитывать конкретные особенности хранения в ней информации. Если внутренняя структура БД изменялась (например, с целью повышения быстродействия), приходилось перерабатывать прикладные программы, даже если на логическом уровне никаких изменений не происходило. Реляционная модель позволила отделить частные особенности хранения данных от уровня прикладной программы. В самом деле, модель никак не описывает способы хранения информации и доступа к ней. Учитывается лишь то, как эта информация воспринимается пользователем. Благодаря появлению реляционной модели качественно изменился подход к управлению данными: из искусства оно превратилось в науку, что привело к революционному развитию отрасли.
Основные понятия реляционной модели
Согласно реляционной модели, отношение (relation) - это некоторая таблица с данными. Отношение может иметь один или несколько атрибутов (признаков), соответствующих столбцам этой таблицы, и некоторое множество (возможно, пустое) данных, представляющих собой наборы этих атрибутов (их называют n-арными кортежами, или записями) и соответствующих строкам таблицы.
.
Отношения обладают и другими свойствами. Наиболее значимое из них - математическое свойство замкнутости операций. Это означает, что в результате выполнения любой операции над отношением должно появляться новое отношение. Это свойство позволяет при выполнении математических операций над отношениями получать предсказуемые результаты. Кроме того, появляется возможность представлять операции в виде абстрактных выражений с разными уровнями вложенности.
sql и реляционная модель
Понятия таблица sql и отношение не являются равнозначными, потому что в таблицах может быть сразу несколько одинаковых строк, тогда как в отношениях появление идентичных кортежей не разрешено. К тому же в sql не предусмотрено использование реляционных доменов, хотя в некоторой степени их роль играют типы данных (некоторые влиятельные сторонники реляционной модели предпринимают сейчас попытку добиться включения в будущий стандарт sql реляционных доменов).
Целью проекта является совершенствование методики обучения школьников по разработке баз данных в sql –сервере .
Задачи проекта:- рассмотреть роль и место изучения темы «Склад, создания таблиц, обеспечения целостности на уровне сущности и отношения, заполнения таблиц и запросов на выборку данных» в разделе «Программирование» школьного курса информатики.
Создание базы даных "Склад".
В основу создания базы данных «Склад» положены отношения современного рынка и этот опыт работы Поставщиков и Заказчиков наиболее близок и понятен современным школьникам.
Схема данных таблиц в базе данных представлена с помощью программы access :
Код создания искомой базы данных в sql-сервере имеет вид:
USE master
GO
CREATE DATABASE Склад
ON
(
NAME = Склад_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Склад.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = Склад_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Склад.ldf',
SIZE = 2,
MAXSIZE = 5,
FILEGROWTH = 1
) GO
Схема данных позволила установить связи между таблицами:
Поставщики, Заказчик, Асортимент, Точки_продаж,Заказ
[pic]
Создание таблицы «Поставщики» базы даных "Склад".
USE Склад
CREATE TABLE Поставщики
(
kdID int primary key IDENTITY(1,1) NOT NULL,
familij VARCHAR(30) NOT NULL,
adres VARCHAR(30) NOT NULL,
telefon VARCHAR(15)NOT NULL,
naimen VARCHAR(30) NOT NULL
)
INSERT INTO Поставщики values ('Пушков', 'Москва','86945612345','мясные п ')
INSERT INTO Поставщики values ('Сушков', 'Москва','86945612346','молочные п')
INSERT INTO Поставщики values ('Рожков', 'Подольск','86945678123','хозтовары ')
INSERT INTO Поставщики values ('Пронин', 'Дубна','86945634345','Птица ')
INSERT INTO Поставщики values ('Ольхин', 'Тула','86945612345','Стройматериалы ')
INSERT INTO Поставщики values ('Зюзин', 'Урал','86976123451','Металл лист')
INSERT INTO Поставщики values ('Бранин', 'Урал','86946612345','Металлпрофиль')
INS ERT INTO Поставщики values ('Топин', 'Сибирь','88945612345','Топливо ')
[pic]
4 Создание таблицы «Заказчик» базы даных "Склад".
USE Склад
CREATE TABLE Заказчик
(
zakID int primary key IDENTITY(1,1) NOT NULL,
fam VARCHAR(30) NOT NULL,
adres VARCHAR(30) NOT NULL,
telefon VARCHAR(15) NOT NULL,
kdID int REFERENCES Поставщики(kdID)
)
INSERT INTO Заказчик values('Ларионов', 'Москва','86948812345','1')
INSERT INTO Заказчик values('Ларин', 'Москва','86948812345','2')
INSERT INTO Заказчик values('Добров', 'Климовск','86977812345','3')
INSERT INTO Заказчик values('Бобров', 'Климовск','86977812345','4')
INSERT INTO Заказчик values('Егоров1', 'Иваново','86577812345','5')
INSERT INTO Заказчик values('Воробьев1', 'Щербинка','86977814445','6')
INSERT INTO Заказчик values('Егоров2', 'Иваново','86577812345','7')
INSERT INTO Заказчик values('Воробьев2', 'Щербинка','86977814445','8')
[pic]
Создание таблицы «Ассортимент» базы даных "Склад".
USE Склад
CREATE TABLE Ассортимент
(
acID int primary key IDENTITY(1,1) NOT NULL,
naim VARCHAR(30) NOT NULL,
zena DECIMAL (6,1) not NULL,
kdID int REFERENCES Поставщики(kdID)
)
INSERT Ассортимент values ('баранина', '300','1')
INSERT Ассортимент values ('свинина', '250', '1')
INSERT Ассортимент values ('говядина', '250','1')
INSERT Ассортимент values ('колбаса', '320','1')
INSERT Ассортимент values ('сосиски', '270','1')
INSERT Ассортимент values ('молоко', '60','2')
INSERT Ассортимент values ('кефир', '45','2')
INSERT Ассортимент values ('творог', '170','2')
INSERT Ассортимент values ('сметана', '200','2')
INSERT Ассортимент values ('сыр', '350','2')
INSERT Ассортимент values ('посуда', '150','3')
INSERT Ассортимент values ('ведра', '100','3')
INSERT Ассортимент values ('инвентарь', '170','3')
INSERT Ассортимент values ('газплита', '3000',3')
INSERT Ассортимент values ('элплита', '2500','3')
INSERT Ассортимент values ('цыплята', '120','4')
INSERT Ассортимент values ('куры', '150','4')
INSERT Ассортимент values ('индейка', '200','4')
INSERT Ассортимент values ('гуси', '250','4')
INSERT Ассортимент values ('куропатка', '240','4')
INSERT Ассортимент values ('доска', '1500','5')
INSERT Ассортимент values ('брус', '2000','5')
INSERT Ассортимент values ('фанера', '500','5')
INSERT Ассортимент values ('тес', '800','5')
INSERT Ассортимент values ('бревно', '2400','5')
INSERT Ассортимент values ('нж.сталь', '400','6')
INSERT Ассортимент values ('оцинковка', '250','6')
INSERT Ассортимент values ('рефленка', '300','6')
INSERT Ассортимент values ('уголок', '200','7')
INSERT Ассортимент values ('швелер', '150','7')
INSERT Ассортимент values ('плоская труба', '150','7')
INSERT Ассортимент values ('труба', '200','7')
INSERT Ассортимент values ('полоса', '80','7')
INSERT Ассортимент values ('бензин -98', '38','8')
INSERT Ассортимент values ('бензин-95', '36','8')
INSERT Ассортимент values ('бензин-92', '35','8')
INSERT Ассортимент values ('ДТ', '34','8')
[pic]
[pic]
Создание таблицы «Точки продаж» базы даных "Склад".
USE Склад
CREATE TABLE Точки_продаж
(
prID int primary key IDENTITY(1,1) NOT NULL,
naim VARCHAR(30) NOT NULL,
zakID int REFERENCES Заказчики(zakID)
)
INSERT Точки_продаж values ('палатка', '1')
INSERT Точки_продаж values ('магазин', '1')
INSERT Точки_продаж values ('рынок', '1')
INSERT Точки_продаж values ('палатка', '2')
INSERT Точки_продаж values ('магазин', '2')
INSERT Точки_продаж values ('рынок', '2')
INSERT Точки_продаж values ('палатка', '3')
INSERT Точки_продаж values ('магазин', '3')
INSERT Точки_продаж values ('рынок', '3')
INSERT Точки_продаж values ('палатка', '4')
INSERT Точки_продаж values ('магазин', '4')
INSERT Точки_продаж values ('рынок', '4')
INSERT Точки_продаж values ('палатка', '5')
INSERT Точки_продаж values ('магазин', '5')
INSERT Точки_продаж values ('рынок', '5')
INSERT Точки_продаж values ('палатка', '6')
INSERT Точки_продаж values ('магазин', '6')
INSERT Точки_продаж values ('рынок', '6')
INSERT Точки_продаж values ('палатка', '7')
INSERT Точки_продаж values ('магазин', '7')
INSERT Точки_продаж values ('рынок', '7')
INSERT Точки_продаж values ('палатка', '8')
INSERT Точки_продаж values ('магазин', '8')
INSERT Точки_продаж values ('рынок', '8')
[pic]
[pic]
Создание таблицы «Заказ» базы даных "Склад".
USE Склад
CREATE TABLE Заказ
(
zakasID int primary key IDENTITY(1,1) NOT NULL,
acID int REFERENCES Ассортимент(acID),
zakID int REFERENCES Заказчики(zakID),
kdID int REFERENCES Поставщики(kdID),
prID int REFERENCES Точки_продаж (prID)
)
INSERT into Заказ values ('1', '1','1','1')
INSERT into Заказ values ('2', '1','1','2')
INSERT into Заказ values ('3', '1','1','3')
INSERT into Заказ values ('4', '1','1','4')
INSERT into Заказ values ('5', '1','1','5')
INSERT into Заказ values ('6', '2','2','6')
INSERT into Заказ values ('7', '2','2','7')
INSERT into Заказ values ('8', '2','2','8')
INSERT into Заказ values ('9', '2','2','9')
INSERT into Заказ values ('10', '2','2','10')
INSERT into Заказ values ('11', '3','3','11')
INSERT into Заказ values ('12', '3','3','12')
INSERT into Заказ values ('13', '3','3','13')
INSERT into Заказ values ('14', '3','3','14')
INSERT into Заказ values ('15', '3','3','15')
INSERT into Заказ values ('16', '4','4','16')
INSERT into Заказ values ('17', '4','4','17')
INSERT into Заказ values ('18', '4','4','18')
INSERT into Заказ values ('19', '4','4','19')
INSERT into Заказ values ('20', '4','4','20')
INSERT into Заказ values ('21', '5','5','21')
INSERT into Заказ values ('22', '5','5','22')
INSERT into Заказ values ('23', '5','5','23')
INSERT into Заказ values ('24', '5','5','24')
INSERT into Заказ values ('25', '5','5','1')
INSERT into Заказ values ('26', '6','6','2')
INSERT into Заказ values ('27', '6','6','3')
INSERT into Заказ values ('28', '6','6','4')
INSERT into Заказ values ('29', '7','7','5')
INSERT into Заказ values ('30', '7','7','6')
INSERT into Заказ values ('31', '7','7','7')
INSERT into Заказ values ('32', '7','7','8')
INSERT into Заказ values ('33', '7','7','9')
INSERT into Заказ values ('34', '8','8','10')
INSERT into Заказ values ('35', '8','8','11')
INSERT into Заказ values ('36', '8','8','12')
INSERT into Заказ values ('37', '8','8','13')
INSERT into Заказ values ('38', '8','8','14')
[pic]
Создание простых запросов в БД «Склад»
/*Запрос № 1*/
USE Склад
select naim, zena from Ассортимент
where kdID=8
/*Запрос № 2*/
USE Склад
select * from Ассортимент where zena<250 and kdid=4
/*Запрос № 3*/
USE Склад
select * from Ассортимент where Naim='цыплята'
/*Запрос № 4*/
USE Склад
select zena from Ассортимент where naim='уголок'
(select familij from Поставщики where kdid=7)
Перекрестные запросы в БД «Склад»
*Запрос № 5*/ Выбор фамилии заказчика, поставщика, ассортимента , заказ на определенную сумму, указана точка продажи
USE Склад
Select zk.zakasid,z.fam,p.familij,a.naim,a.zena,a.zena*250 as summa,t.naim
from Заказ zk,Заказчики z,Поставщики p,Ассортимент a,Точки_продаж t
where (zk.zakasid=p.kdID)
and (z.zakID=p.kdID)
and (zk.zakasid=a.acid)
and (zk.zakasid=t.prID)
[pic]
/*Запрос № 6*/ Выбор ассортимента на сумму по вводу фамилии заказчика
USE Склад
select a.naim ,a.zena,a.zena*150 as summa from Ассортимент a Right JOIN Заказ zk on
zk.zakasid=a.acid where zakid in
(
select zakid from Заказчики where fam= 'Ларин'
)
[pic]
/*Запрос № 7*/Вывол реквизитов поставщика по наименованию ассортимента
USE Склад
Select p.Familij ,p.naimen ,p.adres from Поставщики p
where kdID in
(Select kdID from Заказ where acid =
(Select acid from Ассортимент where naim ='колбаса'))
[pic]
/*Запрос № 8 заказчик-поставщики*/
Вывод фамилии и адреса заказчика и поставщика по запросу в ассортименте выбрать дизельное топливо.
USE Склад
Select z.Fam ,z.adres from Заказчик z
where zakID in
(Select zakID from Заказ where acid =
(Select acid from Ассортимент where naim ='ДТ'))
Select p.familij ,p.adres from Поставщики p
where kdID in
(Select kdID from Заказ where acid =
(Select acid from Ассортимент where naim ='ДТ'))
[pic]
Вывод фамилии и адреса заказчика по запросу в ассортименте выбрать уголок- профиль.
/*Запрос № 9 заказчик*/
[pic]
Заключение.
Данный проект является методическим пособием для учащихся, которые будут продолжать изучать базы данных, создавая свои разработки. В данной работе на примерах показано создание таблиц, первичных и вторичных ключей, связи между ними, установление запросов простых и вложенных, объединение таблиц.
Литература:
1.Методические материалы МГОУ.
2.www.illati.ru SQL: Габриэль Ганьон .»Универсальный язык для работы с БД» статья
17
