1. Спроектировать базу данных для ведения бухгалтерско-складского учета фирмы "Росинка", занимающейся продажей воды и сопутствующих товаров.
2. Представить проект базы данных в виде структурной схемы.
3. Создать базу данных с помощью предложенной СУБД, используя командный процессор СУБД.
4. Представить созданный код на языке DDL и DCL.
5. Заполнить таблицы базы данных в соответствии с информацией, содержащейся в детализации.
6. Представить созданный код на языке DML.
7. Продемонстрировать работоспособность созданной базы данных, и то, что она отвечает всем рекомендациям, приведенным в детализации.


Д Е Т А Л И З А Ц И Я

На рынок водоснабжения населения города N выходит фирма "Росинка", предлагающая следующую продукцию:
- вода родниковая (в бутылях по 19 л) – 50 руб./бут.
- вода серебряная (в бутылях по 19 л) – 75 руб./бут.
- вода дистиллированная (в бутылях по 19 л) – 100 руб./бут.
- насос водяной помповый – 250 руб./шт.
- диспенсер для воды – 1000 руб./шт.
- кулер для воды – 3000 руб./шт.
Чтобы не потерять клиентов из-за возможности выполнения заказа ввиду отсутствия необходимого товара на складе решено, что сотрудники закупают реализуемую продукцию, как только количество единиц по указанной позиции станет меньше установленного уровня.
Этим уровнем было определено, что на складе должно всегда находиться не менее:
- 15 бутылок родниковой воды;
- 10 бутылок серебряной воды;
- 5 бутылок дистиллированной воды;
- 10 водяных помповых насосов;
- 5 диспенсеров для воды;
- 2 кулера для воды.
При достижении каким-либо пунктом указанного порогового уровня необходимо формировать заявку на закупку данного вида товара у оптовых поставщиков (формируется автоматически базой данных, необходимо предусмотреть информацию о том, чей заказ привел к формированию заявки). Эти заявки подлежат реализации, о чем должна быть сделана соответствующая запись (запись в числе прочего, должна содержать информацию о том, кто произвел закупку).
В виду ограниченности складского пространства было принято решение, что все закупки продукции должны производится таким образом, чтобы максимальное количество присутствующей на складе продукции было равно:
- 50 бутылок родниковой воды;
- 30 бутылок серебряной воды;
- 20 бутылок дистиллированной воды;
- 40 водяных помповых насосов;
- 15 диспенсеров для воды;
- 10 кулеров для воды.
Необходимо предусмотреть случаи, когда производится попытка продать товаров больше, чем имеется на складе. Сотруднику, осуществляющему продажу необходимо сообщить, что его заказ не может быть удовлетворен, поскольку на складе отсутствует необходимое количество продукции (база данных должна отслеживать и пресекать такие попытки автоматически). Недопустимы попытки продажи отрицательного или нулевого количества товаров.
Организация "Росинка" является молодой и поэтому в ней работают всего трое сотрудников:
- Иванов Петр Сидорович (оклад 3000 руб.)
- Петров Сидор Иванович (оклад 2600 руб.)
- Сидоров Иван Петрович (оклад 2200 руб.).
Заработок сотрудников является коммерческой тайной (поэтому сотрудник не должен видеть информацию о продажах других сотрудников, но должен иметь возможность просматривать информацию о своих продажах, количестве товара на складе, а также заявки на оптовую закупку дефицитного товара), и складывается из оклада и 5% премии с продаж. Каждый квартал сотрудникам начисляется премия в размере %% с заработной платы выплаченной за квартал (зарплаты и премии должны начисляться автоматически; эти функции должны быть реализованы с помощью сохраненных процедур).
Отчет компании должен содержать информацию о том, кто и на какую сумму продал за отчетный период, какой доход он при этом получил. Общий доход компании за отчетный период, расходы компании на заработную плату в отчетный период. Отчеты должны формироваться с помощью процедур с параметрами, указывающими начало и конец отчетного периода.[/FIXED]

в общем начал реализовывать, выделив 6 сущностей: поставщики, продукция, сотрудники, заказы, покупатели, заявки. В итоге создал 6 таблиц:
1) Поставщики  [code=plaintext]CREATE TABLE [dbo].[Suppliers] (
	[IDSupl] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameSupl] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surName] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[patronymic] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[Produce] (
	[IDProd] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameProd] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[container] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[minLevel] [int] NULL ,
	[maxLevel] [int] NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[Employies] (
	[IDEmploy] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surnameEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[patronymicEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[salary] [money] NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[Orders] (
	[IDOrder] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[IDEmploy] [int] NULL ,
	[IDProd] [int] NULL ,
	[countProd] [int] NOT NULL ,
	[IDCust] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[Customers] (
	[IDCust] [int] NOT NULL ,
	[nameCust] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surnameCust] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[patronymicCustq] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[Claims] (
	[IDClaim] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[IDOrder] [int] NOT NULL 
) ON [PRIMARY]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ROSINKA')
	DROP DATABASE [ROSINKA]
GO

CREATE DATABASE [ROSINKA]  ON (NAME = N'ROSINKA_Data', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL\data\ROSINKA_Data.MDF' , SIZE =  1 , FILEGROWTH =  10 %) LOG ON (NAME = N'ROSINKA_Log', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL\data\ROSINKA_Log.LDF' , SIZE =  1 , FILEGROWTH =  10 %)
 COLLATE Cyrillic_General_CI_AS
GO

exec sp_dboption N'ROSINKA', N'autoclose', N'false'
GO

exec sp_dboption N'ROSINKA', N'bulkcopy', N'false'
GO

exec sp_dboption N'ROSINKA', N'trunc. log', N'false'
GO

exec sp_dboption N'ROSINKA', N'torn page detection', N'true'
GO

exec sp_dboption N'ROSINKA', N'read only', N'false'
GO

exec sp_dboption N'ROSINKA', N'dbo use', N'false'
GO

exec sp_dboption N'ROSINKA', N'single', N'false'
GO

exec sp_dboption N'ROSINKA', N'autoshrink', N'false'
GO

exec sp_dboption N'ROSINKA', N'ANSI null default', N'false'
GO

exec sp_dboption N'ROSINKA', N'recursive triggers', N'false'
GO

exec sp_dboption N'ROSINKA', N'ANSI nulls', N'false'
GO

exec sp_dboption N'ROSINKA', N'concat null yields null', N'false'
GO

exec sp_dboption N'ROSINKA', N'cursor close on commit', N'false'
GO

exec sp_dboption N'ROSINKA', N'default to local cursor', N'false'
GO

exec sp_dboption N'ROSINKA', N'quoted identifier', N'false'
GO

exec sp_dboption N'ROSINKA', N'ANSI warnings', N'false'
GO

exec sp_dboption N'ROSINKA', N'auto create statistics', N'true'
GO

exec sp_dboption N'ROSINKA', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power( 2 ,  24 ) =  8 ) and (@@microsoftversion & 0xffff >=  724 ) )
	exec sp_dboption N'ROSINKA', N'db chaining', N'false'
GO

use [ROSINKA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Customers]') and OBJECTPROPERTY(id, N'IsForeignKey') =  1 )
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Customers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Employies]') and OBJECTPROPERTY(id, N'IsForeignKey') =  1 )
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Employies
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Orders_Produce]') and OBJECTPROPERTY(id, N'IsForeignKey') =  1 )
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT FK_Orders_Produce
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Claims]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Claims]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Customers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employies]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Employies]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Orders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Produce]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Produce]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[Suppliers]
GO

exec sp_addsrvrolemember N'BUILTIN\Администраторы', sysadmin
GO

if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess =  1 )
	EXEC sp_grantdbaccess N'guest'
GO

CREATE TABLE [dbo].[Claims] (
	[IDClaim] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[IDOrder] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
	[IDCust] [int] NOT NULL ,
	[nameCust] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surnameCust] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[patronymicCustq] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employies] (
	[IDEmploy] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surnameEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[patronymicEmploy] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[salary] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
	[IDOrder] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[IDEmploy] [int] NULL ,
	[IDProd] [int] NULL ,
	[countProd] [int] NOT NULL ,
	[IDCust] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Produce] (
	[IDProd] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameProd] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[container] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[minLevel] [int] NULL ,
	[maxLevel] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Suppliers] (
	[IDSupl] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[nameSupl] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[surName] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[patronymic] [nvarchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Claims] WITH NOCHECK ADD 
	CONSTRAINT [PK_Claims] PRIMARY KEY  CLUSTERED 
	(
		[IDClaim]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED 
	(
		[IDCust]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employies] WITH NOCHECK ADD 
	CONSTRAINT [PK_Employies] PRIMARY KEY  CLUSTERED 
	(
		[IDEmploy]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD 
	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
	(
		[IDOrder]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Produce] WITH NOCHECK ADD 
	CONSTRAINT [PK_Produce] PRIMARY KEY  CLUSTERED 
	(
		[IDProd]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 
	(
		[IDSupl]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Orders] ADD 
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[IDCust]
	) REFERENCES [dbo].[Customers] (
		[IDCust]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Orders_Employies] FOREIGN KEY 
	(
		[IDEmploy]
	) REFERENCES [dbo].[Employies] (
		[IDEmploy]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_Orders_Produce] FOREIGN KEY 
	(
		[IDProd]
	) REFERENCES [dbo].[Produce] (
		[IDProd]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO