Проектирование и разработка базы данных информационной системы " Магазин электроники и бытовой техники"
Определение ограничений целостности
жүктеу/скачать 0,69 Mb.
|
Магазин электроники и бытовой техники
- Бұл бет үшін навигация:
- 4Физическое проектирование и реализация базы данных в MS SQL Server
Определение ограничений целостностиИспользование несколько проверочных ограничений. Для таблицы saleitem : [quantity]>(0) Для таблицы wares [orderprice]>(0) Для таблицы discount [percent]>=(0) [percent]<=(20) Для таблицы period [day]>(0) Для таблицы orderitem [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. Достарыңызбен бөлісу:
|