Проектирование и реализация структуры базы данных в СУБД MS SQL server 2008 R2

 

 

 

Описание индексирования таблиц

Имя таблицы	Кластерные индексы	Некластерные индексы
Users	Person_id	login
Users_info	Person_id	First_name, surname
Comments	Person_id	Comment
Recycler	Person_id	Product Name, Total
Suplier	Person_id	Product Name, Time delivery
TV	-	[Screen Resolution], Diagonal
Camers	-	MegaPixel, [Focal length], [Video quality]
Tablets	-	OS, Battery, RAM
PC	-	Processor, Videomemory, RAM
Telephones	-	[Screen Resolution],OS, Product name
Notebooks	-	Processor, Videomemory, RAM

 

 

 

Все индексы отсортированы по Sort Order: Ascending.

 

   Диаграмма структуры БД

 

Список контрольных тестов для проверки корректности данных в таблицах.

Созданные записи

Users

User_info

 

 

 

 

 

 

 

 

 

Suplier

 

Comments

 

 

 

 

 

 

 

Camers

 

 

 

 

 

 

Notebooks

PC

Telephones

 

 

Tablets

 

 

 

 

 

 

 

 

 

TV

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Тесты:

1.Пытаемся  создать пользователя с существующим  логином 

INSERT INTO [Internet-market].[dbo].[users]

            ([Person_id],

            [login]

           ,[password])

          

     VALUES

           ('1','tiger','123')

GO

Ошибка:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_users'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (1).

2. Пытаемся создать информацию о несуществующем пользователе.

 

INSERT INTO [Internet-market].[dbo].[users info]

           ([Person_id]

           ,[First_name]

           ,[Surname]

           ,[Pol]

           ,[Age]

           ,[Country]

           ,[City]

           ,[Phone]

           ,[E-mail]

           ,[Single/Marry])

     VALUES

           ('34','dt','bren','M','45','China','VDFD','767678','spider@gmail.com','Single')

GO

Ошибка: Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_users info_users1". The conflict occurred in database "Internet-market", table "dbo.users", column 'Person_id'.

3. Пытаемся продублировать информацию о пользователе

INSERT INTO [Internet-market].[dbo].[users info]

           ([Person_id]

           ,[First_name]

           ,[Surname]

           ,[Pol]

           ,[Age]

           ,[Country]

           ,[City]

           ,[Phone]

           ,[E-mail]

           ,[Single/Marry])

     VALUES

           ('5','Max','Balik','M','18','Russia','Rostov','89114561235','jjjddf@mail.ru','Single')

GO

 

 

Ошибка:

 

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_users info'. Cannot insert duplicate key in object 'dbo.users info'. The duplicate key value is (5).

4. Пытаемся создать товар с некорректными значениями полей

INSERT INTO [Internet-market].[dbo].[camers]

           ([Photo_id]

           ,[Photo_name]

           ,[Video_name]

           ,[MegaPixel]

           ,[Focal length]

           ,[Video quality]

           ,[Price]

           ,[Discount]

           ,[Bargain price])

     VALUES

           ('43','panasonic456','sony55546.90','56.008834','67---345mm','9000*456575/677','45667889809087-6789','-10000','rub:87878675')

GO

Ошибка

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_camers_users info". The conflict occurred in database "Internet-market", table "dbo.users info", column 'Person_id'.

5. Создадим комментарий несуществующего пользователя

INSERT INTO [Internet-market].[dbo].[comments]

           ([Comment_id]

           ,[Person_id]

           ,[Name_user]

           ,[Time]

           ,[Comment])

     VALUES

           ('4','890','Dima','2014.10.11','This comments');

GO

Ошибка:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_comments_users info". The conflict occurred in database "Internet-market", table "dbo.users info", column 'Person_id'.

 

 

 

 

Полный скрипт создания БД

USE [master]

GO

 

/****** Object:  Database [Internet-market]    Script Date: 09/29/2014 16:54:15 ******/

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Internet-market')

DROP DATABASE [Internet-market]

GO

 

USE [master]

GO

 

/****** Object:  Database [Internet-market]    Script Date: 09/29/2014 16:54:15 ******/

CREATE DATABASE [Internet-market] ON  PRIMARY

( NAME = N'Internet-market', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Internet-market.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'Internet-market_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Internet-market_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

 

ALTER DATABASE [Internet-market] SET COMPATIBILITY_LEVEL = 100

GO

 

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Internet-market].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

 

ALTER DATABASE [Internet-market] SET ANSI_NULL_DEFAULT OFF

GO

 

ALTER DATABASE [Internet-market] SET ANSI_NULLS OFF

GO

 

ALTER DATABASE [Internet-market] SET ANSI_PADDING OFF

GO

 

ALTER DATABASE [Internet-market] SET ANSI_WARNINGS OFF

GO

 

ALTER DATABASE [Internet-market] SET ARITHABORT OFF

GO

 

ALTER DATABASE [Internet-market] SET AUTO_CLOSE ON

GO

 

ALTER DATABASE [Internet-market] SET AUTO_CREATE_STATISTICS ON

GO

 

ALTER DATABASE [Internet-market] SET AUTO_SHRINK OFF

GO

 

ALTER DATABASE [Internet-market] SET AUTO_UPDATE_STATISTICS ON

GO

 

ALTER DATABASE [Internet-market] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

 

ALTER DATABASE [Internet-market] SET CURSOR_DEFAULT  GLOBAL

GO

 

ALTER DATABASE [Internet-market] SET CONCAT_NULL_YIELDS_NULL OFF

GO

 

ALTER DATABASE [Internet-market] SET NUMERIC_ROUNDABORT OFF

GO

 

ALTER DATABASE [Internet-market] SET QUOTED_IDENTIFIER OFF

GO

 

ALTER DATABASE [Internet-market] SET RECURSIVE_TRIGGERS OFF

GO

 

ALTER DATABASE [Internet-market] SET  DISABLE_BROKER

GO

 

ALTER DATABASE [Internet-market] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

 

ALTER DATABASE [Internet-market] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

 

ALTER DATABASE [Internet-market] SET TRUSTWORTHY OFF

GO

 

ALTER DATABASE [Internet-market] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

 

ALTER DATABASE [Internet-market] SET PARAMETERIZATION SIMPLE

GO

 

ALTER DATABASE [Internet-market] SET READ_COMMITTED_SNAPSHOT OFF

GO

 

ALTER DATABASE [Internet-market] SET HONOR_BROKER_PRIORITY OFF

GO

 

ALTER DATABASE [Internet-market] SET  READ_WRITE

GO

 

ALTER DATABASE [Internet-market] SET RECOVERY SIMPLE

GO

 

ALTER DATABASE [Internet-market] SET  MULTI_USER

GO

 

ALTER DATABASE [Internet-market] SET PAGE_VERIFY CHECKSUM 

GO

 

ALTER DATABASE [Internet-market] SET DB_CHAINING OFF

GO

 

 

 

 

 

Скрипты создания таблиц, индексов и ограничений

 

 

 

Users

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[users]    Script Date: 09/29/2014 16:55:07 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))

DROP TABLE [dbo].[users]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[users]    Script Date: 09/29/2014 16:55:07 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[users](

[Person_id] [int] NOT NULL,

[login] [nvarchar](50) NOT NULL,

[password] [nvarchar](50) NOT NULL,

[is_suplier] [bit] NULL,

 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED

(

[Person_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

Users_info

USE [Internet-market]

GO

 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_users info_users1]') AND parent_object_id = OBJECT_ID(N'[dbo].[users info]'))

ALTER TABLE [dbo].[users info] DROP CONSTRAINT [FK_users info_users1]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[users info]    Script Date: 09/29/2014 16:55:56 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users info]') AND type in (N'U'))

DROP TABLE [dbo].[users info]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[users info]    Script Date: 09/29/2014 16:55:57 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[users info](

[Person_id] [int] NOT NULL,

[First_name] [nvarchar](70) NOT NULL,

[Surname] [nvarchar](100) NOT NULL,

[Pol] [nvarchar](50) NOT NULL,

[Age] [nvarchar](50) NOT NULL,

[Country] [nvarchar](100) NOT NULL,

[City] [nvarchar](100) NOT NULL,

[Phone] [nvarchar](50) NOT NULL,

[E-mail] [nvarchar](100) NOT NULL,

[Single/Marry] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_users info] PRIMARY KEY CLUSTERED

(

[Person_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[users info]  WITH CHECK ADD  CONSTRAINT [FK_users info_users1] FOREIGN KEY([Person_id])

REFERENCES [dbo].[users] ([Person_id])

ON DELETE CASCADE

GO

 

ALTER TABLE [dbo].[users info] CHECK CONSTRAINT [FK_users info_users1]

GO

 

 

Suplier

USE [Internet-market]

GO

 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_suplier_users info]') AND parent_object_id = OBJECT_ID(N'[dbo].[suplier]'))

ALTER TABLE [dbo].[suplier] DROP CONSTRAINT [FK_suplier_users info]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[suplier]    Script Date: 09/29/2014 17:00:48 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[suplier]') AND type in (N'U'))

DROP TABLE [dbo].[suplier]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[suplier]    Script Date: 09/29/2014 17:00:48 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[suplier](

[Person_id] [int] NOT NULL,

[Product_name] [nvarchar](100) NOT NULL,

[Quantity] [int] NOT NULL,

[Price] [nvarchar](50) NOT NULL,

[Time delevery] [date] NOT NULL,

[Place of delevery] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_suplier_1] PRIMARY KEY CLUSTERED

(

[Person_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[suplier]  WITH CHECK ADD  CONSTRAINT [FK_suplier_users info] FOREIGN KEY([Person_id])

REFERENCES [dbo].[users info] ([Person_id])

ON DELETE CASCADE

GO

 

ALTER TABLE [dbo].[suplier] CHECK CONSTRAINT [FK_suplier_users info]

GO

 

 

Recycler

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[Recycler]    Script Date: 09/29/2014 17:01:17 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Recycler]') AND type in (N'U'))

DROP TABLE [dbo].[Recycler]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[Recycler]    Script Date: 09/29/2014 17:01:17 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Recycler](

[Person_id] [int] NOT NULL,

[Product_name] [nvarchar](100) NOT NULL,

[Quantity] [nvarchar](50) NOT NULL,

[Price] [nvarchar](50) NOT NULL,

[Discount] [nvarchar](50) NULL,

[Bargain price] [nvarchar](50) NULL,

[Total] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_Recycler] PRIMARY KEY CLUSTERED

(

[Product_name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

Comments

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[comments]    Script Date: 09/29/2014 17:01:46 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[comments]') AND type in (N'U'))

DROP TABLE [dbo].[comments]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[comments]    Script Date: 09/29/2014 17:01:46 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[comments](

[Comment_id] [int] NOT NULL,

[Person_id] [int] NOT NULL,

[Product_name] [nvarchar](100) NOT NULL,

[Time] [date] NOT NULL,

[Comment] [nvarchar](300) NOT NULL,

 CONSTRAINT [PK_comments_1] PRIMARY KEY CLUSTERED

(

[Product_name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

Camers

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[camers]    Script Date: 09/29/2014 17:02:12 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[camers]') AND type in (N'U'))

DROP TABLE [dbo].[camers]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[camers]    Script Date: 09/29/2014 17:02:12 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[camers](

[Photo_id] [int] NOT NULL,

[Photo_name] [nvarchar](100) NULL,

[Video_name] [nvarchar](100) NULL,

[MegaPixel] [nvarchar](50) NOT NULL,

[Focal length] [nvarchar](50) NOT NULL,

[Video quality] [nvarchar](50) NOT NULL,

[Price] [nvarchar](50) NULL,

[Discount] [nvarchar](50) NULL,

[Bargain price] [nvarchar](50) NULL

) ON [PRIMARY]

 

GO

 

Notebooks

USE [Internet-market]

GO

 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_notebooks_users info]') AND parent_object_id = OBJECT_ID(N'[dbo].[notebooks]'))

ALTER TABLE [dbo].[notebooks] DROP CONSTRAINT [FK_notebooks_users info]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[notebooks]    Script Date: 09/29/2014 17:02:32 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[notebooks]') AND type in (N'U'))

DROP TABLE [dbo].[notebooks]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[notebooks]    Script Date: 09/29/2014 17:02:32 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[notebooks](

[note_id] [int] NOT NULL,

[Product_name] [nvarchar](100) NOT NULL,

[OS] [nvarchar](50) NOT NULL,

[Battery] [nvarchar](50) NULL,

[RAM] [nvarchar](50) NOT NULL,

[Videomemory] [nvarchar](50) NOT NULL,

[Processor] [nvarchar](50) NOT NULL,

[HDD] [nvarchar](50) NOT NULL,

[Price] [nvarchar](50) NOT NULL,

[Discount] [nvarchar](50) NULL,

[Bargain price] [nvarchar](50) NULL

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[notebooks]  WITH CHECK ADD  CONSTRAINT [FK_notebooks_users info] FOREIGN KEY([note_id])

REFERENCES [dbo].[users info] ([Person_id])

ON DELETE CASCADE

GO

 

ALTER TABLE [dbo].[notebooks] CHECK CONSTRAINT [FK_notebooks_users info]

GO

 

PC

USE [Internet-market]

GO

 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_pc_users info]') AND parent_object_id = OBJECT_ID(N'[dbo].[pc]'))

ALTER TABLE [dbo].[pc] DROP CONSTRAINT [FK_pc_users info]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[pc]    Script Date: 09/29/2014 17:03:10 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pc]') AND type in (N'U'))

DROP TABLE [dbo].[pc]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[pc]    Script Date: 09/29/2014 17:03:11 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[pc](

[pc_id] [int] NOT NULL,

[Product_name] [nvarchar](100) NOT NULL,

[OS] [nvarchar](50) NOT NULL,

[RAM] [nvarchar](50) NOT NULL,

[Videomemory] [nvarchar](50) NOT NULL,

[Processor] [nvarchar](70) NOT NULL,

[HDD] [nvarchar](50) NOT NULL,

[Price] [nvarchar](50) NOT NULL,

[Discount] [nvarchar](50) NULL,

[Bargain price] [nvarchar](50) NULL

) ON [PRIMARY]

 

GO

 

ALTER TABLE [dbo].[pc]  WITH CHECK ADD  CONSTRAINT [FK_pc_users info] FOREIGN KEY([pc_id])

REFERENCES [dbo].[users info] ([Person_id])

GO

 

ALTER TABLE [dbo].[pc] CHECK CONSTRAINT [FK_pc_users info]

GO

 

Tablets

USE [Internet-market]

GO

 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tablets_users info]') AND parent_object_id = OBJECT_ID(N'[dbo].[tablets]'))

ALTER TABLE [dbo].[tablets] DROP CONSTRAINT [FK_tablets_users info]

GO

 

USE [Internet-market]

GO

 

/****** Object:  Table [dbo].[tablets]    Script Date: 09/29/2014 17:03:43 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tablets]') AND type in (N'U'))

DROP TABLE [dbo].[tablets]