Определение ограничений целостности

Определение ограничений целостности

Использование несколько проверочных ограничений.

• 
  [quantity]>(0)
  

• 
  [orderprice]>(0)
  

• 
  [percent]>=(0)
  
• 
  [percent]<=(20)
  

• 
  [day]>(0)
  

• 
  [warescount]>(0)
  

Ограничение уникальности:

4Физическое проектирование и реализация базы данных в MS SQL Server

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

Информационная система " Магазин электроники и бытовой техники " была сделана на MS SQL Server.
Создание таблицы discount
CREATE TABLE [dbo].[discount](
[Iddiscount] [int] IDENTITY(1,1) NOT NULL,
[percent] [int] NOT NULL,
CONSTRAINT [PK_discount] PRIMARY KEY CLUSTERED
(
[Iddiscount] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_discount] UNIQUE NONCLUSTERED
(
[percent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Создание таблицы manager

CREATE TABLE [dbo].[manager](

[Idmanager] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[phone] [nvarchar](16) NULL,
CONSTRAINT [PK_manager] PRIMARY KEY CLUSTERED
(
[Idmanager] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_manager] UNIQUE NONCLUSTERED
(
[phone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Создание таблицы order
CREATE TABLE [dbo].[order](
[Idorder] [int] IDENTITY(1,1) NOT NULL,
[Idprovider] [int] NOT NULL,
[orddate] [smalldatetime] NOT NULL CONSTRAINT [DF_order_orddate] DEFAULT (getdate()),
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[Idorder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Создание таблицы orderitem
CREATE TABLE [dbo].[orderitem](
[Idorder] [int] NOT NULL,
[Idwares] [int] NOT NULL,
[warescount] [int] NOT NULL,
CONSTRAINT [PK_orderitem] PRIMARY KEY CLUSTERED
(
[Idorder] ASC,
[Idwares] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Создание таблицы period
CREATE TABLE [dbo].[period](
[Idprovider] [int] NOT NULL,
[Idwares] [int] NOT NULL,
[day] [int] NOT NULL,
CONSTRAINT [PK_period] PRIMARY KEY CLUSTERED
(
[Idprovider] ASC,
[Idwares] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Создание таблицы provider
CREATE TABLE [dbo].[provider](
[Idprovider] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[agentname] [nvarchar](50) NOT NULL,
[phone] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_provider] PRIMARY KEY CLUSTERED
(
[Idprovider] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_provider] UNIQUE NONCLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_provider_1] UNIQUE NONCLUSTERED
(
[phone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Создание таблицы sale

REATE TABLE [dbo].[sale](
[Idsale] [int] IDENTITY(1,1) NOT NULL,
[Idmanager] [int] NULL,
[saledate] [smalldatetime] NOT NULL CONSTRAINT [DF_sale_saledate] DEFAULT (getdate()),
CONSTRAINT [PK_sale] PRIMARY KEY CLUSTERED
(
[Idsale] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Создание таблицы saleitem
CREATE TABLE [dbo].[saleitem](
[Idsale] [int] NOT NULL,
[Idwares] [int] NOT NULL,
[quantity] [int] NOT NULL,
CONSTRAINT [PK_saleitem] PRIMARY KEY CLUSTERED
(
[Idsale] ASC,
[Idwares] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Создание таблицы wares

CREATE TABLE [dbo].[wares](

[Idwares] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[description] [nvarchar](100) NOT NULL,
[orderprice] [int] NOT NULL,
[saleprice] AS ([orderprice]*(1.3)),
[Iddiscount] [int] NOT NULL,
CONSTRAINT [PK_wares] PRIMARY KEY CLUSTERED
(
[Idwares] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_wares] UNIQUE NONCLUSTERED
(
[description] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

жүктеу/скачать 0,69 Mb.

Достарыңызбен бөлісу: