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

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

Были определены следующие ограничения целостности:

· book_lending: внешний ключ library_card_id ссылается на library_card (library_card_id) ON UPDATE CASCADE ON DELETE NO ACTION

· book_lending: внешний ключ edition_id ссылается на edition (edition_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_genre_id ссылается на genre (genre_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_format_id ссылается на edition_format (format_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_pubhouse_id ссылается на publishing_house (pubhouse_id) ON UPDATE CASCADE ON DELETE NO ACTION

· book_lending: [lending_date]<[delivery_date_fact] OR [delivery_date_fact]=NULL

· book_lending: [lending_date]<[delivery_date_plan] OR [delivery_date_plan]=NULL

· edition_format: [format_penalty]>=(0)

· library_card: [reader_pass_n] like '[0-9][0-9][0-9][0-9][0-9][0-9]'

· library_card: [reader_pass_s] like '[0-9][0-9][0-9][0-9]'

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

Приступая к физическому проектированию, прежде всего, необходимо проанализировать и хорошо усвоить информацию об отношениях, собранную на этапе построения логической модели базы данных. Эта информация содержаться в словаре данных и в определениях отношений, записанных на языке DBDL.

Ниже представлены описания структуры базы данных для целевой СУБД:

book_lending:

CREATE TABLE [dbo].[book_lending](

[book_lend_id] [int] IDENTITY(1,1) NOT NULL,

[library_card_id] [int] NOT NULL,

[edition_id] [int] NOT NULL,

[lending_date] [datetime] NOT NULL,

[delivery_date_fact] [datetime] NULL,

[delivery_date_plan] [datetime] NULL,

[penalty] [money] NULL,

CONSTRAINT [PK_book_lending] PRIMARY KEY CLUSTERED

(

[book_lend_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

edition:

CREATE TABLE [dbo].[edition](

[edition_id] [int] IDENTITY(1,1) NOT NULL,

[edition_name] [varchar](50) NOT NULL,

[edition_author] [varchar](50) NULL,

[edition_genre_id] [int] NOT NULL,

[edition_format_id] [int] NOT NULL,

[edition_pubhouse_id] [int] NOT NULL,

[edition_year] [date] NOT NULL,

[edition_count] [int] NOT NULL,

[edition_biblfeatures] [varchar](100) NULL,

[edition_number] [int] NOT NULL,

CONSTRAINT [PK_edition] PRIMARY KEY CLUSTERED

(

[edition_id] ASC

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

CONSTRAINT [IX_edition_number] UNIQUE NONCLUSTERED

(

[edition_number] 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

library_card:

CREATE TABLE [dbo].[library_card](

[library_card_id] [int] IDENTITY(1,1) NOT NULL,

[library_card_number] [int] NOT NULL,

[reader_surname] [varchar](30) NOT NULL,

[reader_first_name] [varchar](20) NOT NULL,

[reader_middle] [varchar](20) NOT NULL,

[reader_address] [varchar](30) NULL,

[reader_phone] [char](12) NULL,

[reader_pass_s] [char](4) NOT NULL,

[reader_pass_n] [char](6) NOT NULL,

CONSTRAINT [PK_Library_card] PRIMARY KEY CLUSTERED

(

[library_card_id] ASC

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

CONSTRAINT [IX_Library_card] UNIQUE NONCLUSTERED

(

[library_card_number] ASC

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

CONSTRAINT [IX_library_card_1] UNIQUE NONCLUSTERED

(

[reader_phone] ASC

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

CONSTRAINT [IX_library_card_2] UNIQUE NONCLUSTERED

(

[reader_pass_s] ASC,

[reader_pass_n] 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

publishing_house:

CREATE TABLE [dbo].[publishing_house](

[pubhouse_id] [int] IDENTITY(1,1) NOT NULL,

[pubhouse_name] [varchar](50) NOT NULL,

CONSTRAINT [PK_publishing_house] PRIMARY KEY CLUSTERED

(

[pubhouse_id] ASC

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

CONSTRAINT [IX_publishing_house] UNIQUE NONCLUSTERED

(

[pubhouse_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

edition_format:

REATE TABLE [dbo].[edition_format](

[format_id] [int] IDENTITY(1,1) NOT NULL,

[format_name] [varchar](20) NOT NULL,

[format_penalty] [money] NOT NULL,

CONSTRAINT [PK_edition_format] PRIMARY KEY CLUSTERED

(

[format_id] ASC

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

CONSTRAINT [IX_edition_format] UNIQUE NONCLUSTERED

(

[format_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

genre:

CREATE TABLE [dbo].[genre](

[genre_id] [int] IDENTITY(1,1) NOT NULL,

[genre_name] [varchar](20) NOT NULL,

CONSTRAINT [PK_Genre] PRIMARY KEY CLUSTERED

(

[genre_id] ASC

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

CONSTRAINT [IX_genre] UNIQUE NONCLUSTERED

(

[genre_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

Дата добавления: 2015-05-09