CREATE DATABASE [MATXT]
LOG ON
  ( NAME = [MATXT_log],
    FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MATXT_log.ldf',
    SIZE =  1  MB,
    MAXSIZE =  0  MB,
    FILEGROWTH =  10  % )
COLLATE Cyrillic_General_CI_AS
GO

USE [MATXT]
GO

CREATE TABLE [dbo].[ВАРИАНТЫ ОТВЕТОВ] (
  [ID] int IDENTITY( 1 ,  1 ) NOT NULL,
  [IDVoprosa] int NOT NULL,
  [Text] varchar( 2000 ) COLLATE Cyrillic_General_CI_AS NULL,
  [Image] nvarchar(max) COLLATE Cyrillic_General_CI_AS NULL,
  [Yes] bit CONSTRAINT [DF__ВАРИАНТЫ ОТ__Yes__300424B4] DEFAULT ( 0 ) NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'Ключевое поле', 'schema', 'dbo', 'table', 'ВАРИАНТЫ ОТВЕТОВ', 'column', 'ID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'Внешний ключ для Вопроса', 'schema', 'dbo', 'table', 'ВАРИАНТЫ ОТВЕТОВ', 'column', 'IDVoprosa'
GO

EXEC sp_addextendedproperty 'MS_Description', N'Текст варианта ответа', 'schema', 'dbo', 'table', 'ВАРИАНТЫ ОТВЕТОВ', 'column', 'Text'
GO

EXEC sp_addextendedproperty 'MS_Description', N'Картинка', 'schema', 'dbo', 'table', 'ВАРИАНТЫ ОТВЕТОВ', 'column', 'Image'
GO

EXEC sp_addextendedproperty 'MS_Description', N'Правильность', 'schema', 'dbo', 'table', 'ВАРИАНТЫ ОТВЕТОВ', 'column', 'Yes'
GO

CREATE TABLE [dbo].[ВОПРОСЫ] (
  [ID] int IDENTITY( 1 ,  1 ) NOT NULL,
  [IDTheme] int NOT NULL,
  [Text] varchar(max) COLLATE Cyrillic_General_CI_AS NOT NULL,
  [image] nvarchar(max) COLLATE Cyrillic_General_CI_AS NULL
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[ГРУППЫ] (
  [id] int IDENTITY( 1 ,  1 ) NOT NULL,
  [gruppa] varchar( 10 ) COLLATE Cyrillic_General_CI_AS NOT NULL
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[ЗАДАННЫЕ ВОПРОСЫ] (
  [id] int IDENTITY( 1 ,  1 ) NOT NULL,
  [idKontrol_tochka] int NOT NULL,
  [idVoprosa] int NOT NULL,
  [PoryadkovyNomerVoprosaVTeste] int NOT NULL
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[КОНТРОЛЬНЫЕ ТОЧКИ] (
  [ID] int NOT NULL,
  [IDStudenta] int NOT NULL,
  [DataCdachi] int NOT NULL,
  [Ocenka] int NOT NULL
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[ОТВЕТЫ СТУДЕНТОВ] (
  [id] int IDENTITY( 1 ,  1 ) NOT NULL,
  [idZadannogoVoprosa] int NOT NULL,
  [idVariantaOtveta] int NOT NULL
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[СТУДЕНТЫ] (
  [ID] int IDENTITY( 1 ,  1 ) NOT NULL,
  [IDGruppa] int NOT NULL,
  [login] varchar( 12 ) COLLATE Cyrillic_General_CI_AS NULL,
  [pass] varchar( 12 ) COLLATE Cyrillic_General_CI_AS NULL,
  [fam] varchar( 32 ) COLLATE Cyrillic_General_CI_AS NULL,
  [im] varchar( 32 ) COLLATE Cyrillic_General_CI_AS NULL,
  [ot] varchar( 32 ) COLLATE Cyrillic_General_CI_AS NULL
)
ON [PRIMARY]
GO


CREATE TABLE [dbo].[ТЕМЫ] (
  [ID] int IDENTITY( 1 ,  1 ) NOT NULL,
  [Nazvanie] varchar( 100 ) COLLATE Cyrillic_General_CI_AS NOT NULL
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ВАРИАНТЫ ОТВЕТОВ]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ВОПРОСЫ]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ГРУППЫ]
ADD CONSTRAINT [PK_Группы_] 
PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ЗАДАННЫЕ ВОПРОСЫ]
ADD CONSTRAINT [PK_ЗАДАННЫЕ ВОПРОСЫ] 
PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[КОНТРОЛЬНЫЕ ТОЧКИ]
ADD CONSTRAINT [PK__РЕЗУЛЬТАТЫ ТЕСТИ__108B795B] 
PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ОТВЕТЫ СТУДЕНТОВ]
ADD CONSTRAINT [PK_ОТВЕТЫ СТУДЕНТОВ] 
PRIMARY KEY CLUSTERED ([id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[СТУДЕНТЫ]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ТЕМЫ]
ADD CONSTRAINT [PK__ПРЕДМЕТЫ__1BFD2C07] 
PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO


ALTER TABLE [dbo].[ВАРИАНТЫ ОТВЕТОВ]
ADD CONSTRAINT [FK_ВАРИАНТЫ ОТВЕТОВ_ВОПРОСЫ] FOREIGN KEY ([IDVoprosa]) 
  REFERENCES [dbo].[ВОПРОСЫ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ВОПРОСЫ]
ADD CONSTRAINT [ВОПРОСЫ_fk] FOREIGN KEY ([IDTheme]) 
  REFERENCES [dbo].[ТЕМЫ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ЗАДАННЫЕ ВОПРОСЫ]
ADD CONSTRAINT [FK_ЗАДАННЫЕ ВОПРОСЫ_КОНТРОЛЬНЫЕ ТОЧКИ] FOREIGN KEY ([idKontrol_tochka]) 
  REFERENCES [dbo].[КОНТРОЛЬНЫЕ ТОЧКИ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[КОНТРОЛЬНЫЕ ТОЧКИ]
ADD CONSTRAINT [FK_РЕЗУЛЬТАТЫ ТЕСТИРОВАНИЯ_ВАРИАНТЫ ОТВЕТОВ] FOREIGN KEY ([DataCdachi]) 
  REFERENCES [dbo].[ВАРИАНТЫ ОТВЕТОВ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[КОНТРОЛЬНЫЕ ТОЧКИ]
ADD CONSTRAINT [РЕЗУЛЬТАТЫ ТЕСТИРОВАНИЯ_fk] FOREIGN KEY ([IDStudenta]) 
  REFERENCES [dbo].[СТУДЕНТЫ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ОТВЕТЫ СТУДЕНТОВ]
ADD CONSTRAINT [FK_ОТВЕТЫ СТУДЕНТОВ_ВАРИАНТЫ ОТВЕТОВ] FOREIGN KEY ([idVariantaOtveta]) 
  REFERENCES [dbo].[ВАРИАНТЫ ОТВЕТОВ] ([ID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ОТВЕТЫ СТУДЕНТОВ]
ADD CONSTRAINT [FK_ОТВЕТЫ СТУДЕНТОВ_ЗАДАННЫЕ ВОПРОСЫ] FOREIGN KEY ([idVariantaOtveta]) 
  REFERENCES [dbo].[ЗАДАННЫЕ ВОПРОСЫ] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[СТУДЕНТЫ]
ADD CONSTRAINT [FK_СТУДЕНТЫ_ГРУППЫ] FOREIGN KEY ([IDGruppa]) 
  REFERENCES [dbo].[ГРУППЫ] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO