Системы управления базами данных access 2003

Применения ключей шифрования SQL Server и базы данных

SQL Server поддерживает два основных варианта применения ключей: главный ключ службы (SMK) создается на экземпляре SQL Server и для этого экземпляра, а главный ключ базы данных (DMK) используется для базы данных.

Главный ключ службы

Главный ключ службы является корнем иерархии шифрования SQL Server. Главный ключ службы автоматически создается при первом запуске экземпляра SQL Server и используется для шифрования пароля связанного сервера, учетных данных или главного ключа базы данных в каждой базе данных. Главный ключ службы шифруется с помощью ключа локального компьютера и API-интерфейса защиты данных Windows. Этот API-интерфейс использует ключ, получаемый из учетных данных Windows, которые соответствуют учетной записи службы для SQL Server и учетным данным компьютера. Главный ключ службы может быть расшифрован лишь той учетной записью службы, под которой он был создан, или участником, имеющим доступ к учетным данным компьютера.

Главный ключ службы может быть открыт только учетной записью службы Windows, под которой он был создан, либо участником, имеющим доступ к имени и паролю учетной записи службы.

SQL Server использует для защиты главного ключа службы и главного ключа базы данных алгоритм шифрования AES. AES — это новый алгоритм шифрования, отличный от алгоритма 3DES, используемого в более ранних версиях. После обновления экземпляра компонента Компонент Database Engine до SQL Server необходимо заново сформировать главный ключ службы и главный ключ базы данных, чтобы обновить главные ключи до алгоритма AES. Дополнительные сведения о повторном создании главного ключа базы данных см. в статьях ALTER SERVICE MASTER KEY (Transact-SQL) и ALTER MASTER KEY (Transact-SQL).

Главный ключ базы данных

Главный ключ базы данных — это симметричный ключ, который применяется для защиты закрытых ключей сертификатов и асимметричный ключей, которые есть в базе данных. Он также может использоваться для шифрования данных, но из-за ограниченной длины не может применяться на практике для шифрования данных так же широко, как симметричный ключ. Чтобы разрешить автоматическое шифрование главного ключа базы данных, копия этого ключа зашифровывается с помощью главного ключа службы. Ключ хранится как в базе данных, где используется ключ, так и в системной базе данных master .

Копия, которая хранится в базе данных master , автоматически обновляется при каждом изменении главного ключа. Но это поведение по умолчанию может быть изменено с помощью параметра DROP ENCRYPTION BY SERVICE MASTER KEY инструкции ALTER MASTER KEY . Главный ключ базы данных, который не зашифрован с помощью главного ключа службы, следует открывать с помощью инструкции OPEN MASTER KEY и пароля.

Отношение один к одному

Пока что мы рассмотрели классическую связь, когда одной строке основной таблицы данных соответствует одна строка из связанной таблицы. Такая связь называется один ко многим. Но существуют и другие связи, и сейчас мы рассмотрим еще одну – один к одному, когда одна запись основной таблице связана с одной записью другой. Чтобы это реализовать, достаточно связать первичные ключи обеих таблиц. Так как первичные ключи не могут повторяться, то в обеих таблицах связанными могут быть только одна строка.

Следующий пример создает две таблицы, у которых создана связь между первичными ключами:

 
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

Внешний ключ нужен только у одной из таблиц. Так как связь идет один к одному, то не имеет значения, в какой таблице создать его.

Связи между отношениями

Итак, первичный ключ в базе данных – это один или несколько столбцов таблицы, позволяющий однозначно идентифицировать строку этого отношения. Для чего же он нужен?

Вернемся к первому примеру с отношением «Студенты». В базе данных, кроме этого отношения, хранится и другая информация, например, успеваемость каждого учащегося. Чтобы не повторять всю информацию, что уже содержится в БД, пользуются ключом, ссылаясь на нужную запись. Это выглядит так.

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

1 Анализ предметной области

Зачастую, кинотеатр состоит из нескольких залов разной конфигурации, а посетителю предоставляется возможность выбора билета, для этого ему отображается текущее состояние зала. Выбранные места посетитель сообщает кассиру, который вводит их в систему и места помечаются как «проданные». Это «основной» сценарий использования информационной системы, однако надо учесть следующее:

1. репертуар и расписание проката кинотеатра должен кто-то вносить в систему — соответствующую роль назовем «Менеджер»;
2. посетитель и кассир должны иметь возможность просматривать расписание, при этом интересно расписание, начиная с некоторого момента времени (например, текущего времени). Составлять оно может по-разному:
   1. расписание показа всех фильмов, упорядоченное по времени;
   2. расписание прокатов в отдельных залах кинотеатра;
   3. расписание проката определенного фильма.

Из этого описания понятны основные функции системы, изображенные на рисунке с помощью нотации диаграммы прецедентов UML. На диаграмме не отображена роль администратора базы данных, так как администратор обычно взаимодействует с системой не через интерфейс, а через выполнение SQL-запросов.

Несмотря на то, что мы не будет разрабатывать интерфейс информационной системы и текстовые описания прецедентов, дальше нас будут интересовать данные, необходимые для выполнения того или иного прецедента, а для этого надо выделить и описать сущности. Иначе, невозможно определить «какие данные должен вводить менеджер при добавлении фильма». Основные сущности, данные которых потребуются во время работы, показаны на рисунке, при этом используется нотация диаграммы классов UML. Каждый прямоугольник соответствует одной сущности, внутри записаны поля и типы данных.

Каждая сущность, кроме hall_row содержит поле id, которое идентифицирует объект. У сущности hall_row поле id не нужно, так как в одном и том же зале кинотеатра (id_hall) не могут повторяться номера рядов (number).

Когда пользователь выберет зал и прокат — система должна отобразить заполненность зала, при этом надо отобразить конфигурацию зала с пометкой занятых и свободных мест. Под конфигурацией зала тут имеется ввиду, что разные залы имеют разный размер, а ряды зала могут иметь различное количество мест. Поэтому в базе данных зал (hall) составляется из рядов (hall_row), одним из параметров которых является вместимость (capacity).

Что такое первичный ключ

Столбец первичного ключа в таблице помогает идентифицировать каждую строку или запись в таблице. Содержит уникальные значения. Столбец первичного ключа не может иметь значения Null. Таблица может иметь один первичный ключ. В таблице Student, student_id является первичным ключом. В таблице Patient_Details, Patient_id является первичным ключом. Для первичного ключа необязательно иметь одно поле. Это также может быть комбинация нескольких полей. Когда первичный ключ состоит из нескольких полей, он называется составным ключом. Например, первичный ключ таблицы Student может быть комбинацией student_id и name.

Проектирование базы данных

   Основой любой реляционной БД являются
таблицы. Разработка таблиц яв­ляется одним из наиболее сложных этапов в
проектировании БД. Грамотно спро­ектированные таблицы являются основой для
создания работоспособной и эффек­тивной БД.

   Понятие таблицы в Access полностью соответствует аналогичному
понятию реляционной модели данных. Любая таблица реляционной БД состоит из строк
(называемых также записями) и столбцов (называемых
также полями).

  Строки таблицы содержат сведения об
однотипных объектах — документах, людях, предметах. На пересечении столбца и
строки находится конкретное значе­ние, характеризующее объект.

   Можно сформулировать ряд основных
требований, которым должны удов­летворять таблицы.

    1. Информация в таблице не должна
дублироваться, т.е. в таблице не должно существовать двух записей с полностью
совпадающим набором значений ее по­лей.

    2. На пересечении любого столбца и
любой строки должно находиться одно

значение.

    3.  Не рекомендуется включать в
таблицу данные, которые являются резуль­татом вычислений.

    4.  Значения данных в одном и том же
столбце должны принадлежать к од­ному и тому же типу, доступному для
использования в данной СУБД.

    5. Каждое поле должно иметь уникальное
имя.

    6. Каждая таблица должна иметь
первичный ключ.

    7. Таблицы БД должны быть связаны
через внешние ключи.

  Каждая таблица должна содержать поле
(или набор из нескольких полей), значения в котором однозначно идентифицируют
каждую запись в таблице. Такое поле (или набор полей) называется ключевым полем
таблицы или первичным ключом. Первичный ключ любой таблицы обязан
содержать уникальные непус­тые значения для каждой записи. Если
для таблицы обозначены ключевые поля, то Access предотвращает дублирование или ввод пустых значений в ключевое по­ле.

   В Access можно выделить три типа ключевых полей: простой ключ, состав­ной
ключ и поле счетчика.

   Если поле содержит уникальные значения,
такие как коды или инвентарные номера, то это поле можно определить как простой
первичный ключ. Если в этом поле появятся повторяющиеся или пустые
значения, Access выведет сообщение об ошибке.

   В случаях, когда невозможно
гарантировать уникальность значений ни од­ного из полей, можно создать ключ,
состоящий из нескольких полей — составной первичный ключ. Для
составного ключа существенным может оказаться порядок образующих ключ полей. Не
рекомендуется определять ключ по полям Имена и Фамилии, поскольку нельзя исключить
повторения этой пары значений для раз­ных людей.

   Составной ключ необходим для таблицы,
используемой для связывания двух таблиц в отношении «многие — ко — многим»
Обычно такой ключ состоит из ключевых полей связываемых таблиц.

   Если для какой-либо таблицы не удалось
определить простой первичный ключ или найти подходящий набор полей для
составного ключа, можно добавить в таблицу поле счетчика и
сделать его ключевым. При создании каждой новой за­писи Access генерирует уникальный номер записи,
называемый счетчиком. Ука­зание такого поля в качестве ключевого
является наиболее простым способом соз­дания ключевых полей.

   Если до сохранения созданной таблицы
ключевые поля не были определены, то при сохранении будет выдано предложение о
создании системой ключевого по­ля. При ответе Да будет создано ключевое
поле счетчика.

   Сила реляционных баз данных, таких как
БД Microsoft Access, заключается в том, что они могут быстро найти и связать данные
из разных таблиц при помощи запросов, форм и отчетов. Таблицы реляционных БД
связываются через одинако­вые значения одноименных полей, содержащихся в
связываемых таблицах. Такие поля называются внешним ключом для
этих таблиц. Все таблицы БД Access должны
быть связаны с помощью внешних ключей.

Нормализация таблиц при проектировании БД

При проектировании структуры
новой БД определяют сущности (объекты,
явления) предметной области, которые должны
найти свое отражение в базе данных. В
конечном итоге анализ предметной области
должен привести к созданию эскиза БД.
Сначала желательно изобразить сущности
и связи между ними. Как правило, каждой
сущности в БД соответствует таблица.
Затем — в эскизе второго порядка — для каждой
таблицы БД приводится список атрибутов
— полей записи.

На этом этапе процесс
проектирования структур БД является
процессом творческим, неоднозначным, с
другой стороны, узловые его моменты могут
быть формализованы.

Одной из таких формализаций
является требование, согласно которому
реляционная база данных должна быть
нормализована. Процесснормализации имеет своей целью
устранение избыточности данных и
заключается в приведении к третьей
нормальной форме (3НФ).

Первая нормальная форма (1НФ)
требует, чтобы каждое поле таблицы БД:

·было неделимым;

·не содержало повторяющихся групп.

Неделимость поля означает, что
значение поля не должно делиться на более
мелкие значения. Например, если в поле «Подразделение»
содержится название факультета и название
кафедры, требование неделимости не
соблюдается и необходимо из данного поля
выделить или название факультета, или
кафедры в отдельное поле.

Повторяющимися являются поля,
содержащие одинаковые по смыслу значения.
Например, если требуется получить
статистику сдачи экзаменов по предметам,
можно создать поля для хранения данных об
оценке по каждому предмету. Однако в этом
случае мы имеем дело с повторяющимися
группами.

Вторая
нормальная форма (2НФ) требует, чтобы все
поля таблицы зависели от первичного ключа,
то есть, чтобы первичный ключ однозначно
определял запись и не был избыточен. Те поля,
которые зависят только от части первичного
ключа, должны быть выделены в составе
отдельных таблиц.

Третья
нормальная форма (ЗНФ) требует, чтобы
значение любого поля таблицы, не входящего
в первичный ключ, не зависело от значения
другого поля, не входящего в первичный ключ.

Пример логической модели базы
данных «Сессия»

Представление в базе данных

Рассмотрим пример таблицы в базе данных с обоими видами ключей:

Ниже приведен SQL-скрипт, описывающий таблицу, хранящую информацию о социальных историях.

      IF
      NOT
      EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'U'))
BEGINCREATETABLE .(
    IDENTITY(1,1) NOTNULL,
   (50) NOTNULL,
   (50) NOTNULL,
   (400) NOTNULL,
 CONSTRAINT  PRIMARYKEYCLUSTERED 
(
   ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON 
) ON 
END

В данном примере у нас суррогатный ключ Id является первичным ключом, а поля Title и Url входят в дополнительный ключ. Уникальность обеспечивается следующим ограничением (constraint):

      IF
      NOT
      EXISTS (
  SELECT * 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID(N'.')
AND name = N'IX_NK_TitleUrl'
)
  CREATEUNIQUENONCLUSTEREDINDEX  ON . 
  (
     ASC,
     ASC
  )  WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
           SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, 
           ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
    ON  

Простой и составной первичный ключ

Primary key может быть простым и составным. Если уникальность записи определяется значением только в одном поле, как описано выше, мы имеем дело с простым ключом. Составной ключ – это первичный ключ базы данных, состоящий из двух и более полей. Рассмотрим следующее отношение клиентов банка.

Паспорта людей могут содержать одни и те же серии либо номера, но паспортов с одним и тем же сочетанием серии и номера не существует. Таким образом, поля «Серия паспорта» и «Номер паспорта» станут составным ключом указанного отношения, однозначно идентифицируя человека.

SQL CREATE TABLE с PRIMARY KEY CONSTRAINT для большего количества столбцов

В следующем разделе мы обсудим использование SQL PRIMARY KEY CONSTRAINT вместе с оператором CREATE TABLE для двух или более столбцов.

Пример:

В следующем примере создается таблица. Таблица должна содержать ПЕРВИЧНЫЙ КЛЮЧ с комбинацией двух столбцов ‘cust_code’ и ‘cust_city’. Вот имя поля и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход:

Денормализация

Денормализация — это умышленное изменение структуры базы, нарушающее правила нормальных форм. Обычно это делается с целью улучшения производительности базы данных.
Теоретически, надо всегда стремиться к полностью нормализованной базе, однако на практике полная нормализация базы почти всегда означает падение производительности. Чрезмерная нормализация базы данных может привести к тому, что при каждом извлечении данных придется обращаться к нескольким таблицам. Обычно в запросе должны участвовать четыре таблицы или менее.
Стандартными приемами денормализации являются: объединение нескольких таблиц в одну, сохранение одинаковых атрибутов в нескольких таблицах, а также хранение в таблице сводных или вычисляемых данных.

5
10
Голоса

Рейтинг статьи

Теория

Первичный ключ таблицы БД может быть естественным или суррогатным.

Естественный ключ – это вид первичного ключа, который состоит из информационных полей таблицы (то есть полей, содержащих полезную информацию об описываемых объектах).

Суррогатные ключи
 — это искусственно созданные технические ключевые поля, не несущие информации об объектах.

Естественные ключи можно использовать для идентификации объектов-сущностей, даже если они не были сохранены в БД, так как, фактически, они являются частью данных самого объекта. На практике, однако, использование естественных ключей наталкивается на определённые сложности:

• Низкая эффективность — Естественный ключ может быть велик по размеру (особенно когда он составной), и его использование окажется технически неэффективным (ведь во всех таблицах, связанных с данной, понадобится создать поле того же размера, чтобы хранить ссылки).
• Необходимость каскадных изменений — При изменении значения поля, входящего в естественный ключ, оказывается необходимым изменить значение поля не только в данной таблице, но и во всех таблицах, связанных с данной, в противном случае все ссылки на данную запись окажутся некорректными. В сложных базах данных таких связанных таблиц может быть очень много, и всегда остаётся опасность упустить из виду какую-то из них. При добавлении новых связанных таблиц приходится добавлять согласующие изменения во все места программ, где правится исходная таблица.
• Несоответствие реальности — Уникальность естественного первичного ключа в реальных БД не всегда соблюдается. Допустим, например, что первичный ключ в таблице — данные личного документа. В такую таблицу окажется невозможным внести человека, о документах которого нет информации в момент добавления записи, а на практике такая необходимость может возникнуть.
• Повторяемость — При использовании естественного ключа, содержание может повторяться (так как могут повторяться поля, из которых состоит ключ), что недопустимо в первичном ключе.

Эти проблемы служат причиной того, что на практике используются суррогатные ключи, которые нельзя использовать для сравнения на эквивалентность объектов, не сохраненных в БД.

Зачастую, кроме суррогатного ключа, в таблице БД вводятся дополнительные уникальные ключи, которые, по сути, претендуют на роль первичного ключа, но не являются таковыми по одной или нескольким из перечисленных выше причин. Далее мы будем называть такие ключи дополнительными ключами.

На основании дополнительных ключей можно автоматически генерировать код проверки объектов на эквивалентность.

Хорошая практика для первичных ключей в таблицах

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

Пример:

Предположим, мы собираемся создать таблицу с именем ‘agent1’. Он содержит столбцы и типы данных, которые показаны ниже. Для каждой строки таблицы ‘agent1’ требуется идентифицировать каждого агента с помощью уникального кода, поскольку имена двух или более агентов в городе страны могут совпадать.

Так что не стоит создавать PRIMARY KEY для ‘agent_name’. ‘Agent_code’ может быть единственным и исключительным выбором для PRIMARY KEY для этой таблицы.

При создании таблицы вы можете включить первичный ключ, используя ограничение первичного ключа на уровне столбца или ограничение на уровне таблицы. Вот два примера на упомянутой таблице:

Ограничение первичного ключа на уровне столбца:

Код SQL:

Ограничение первичного ключа на уровне таблицы:

Код SQL:

SQL PRIMARY KEY в CREATE TABLE

Следующий SQL создает первичный ключ в столбце «ID» при создании таблицы «Persons»:

MySQL:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL PRIMARY KEY,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int
);

Чтобы разрешить именование ограничения PRIMARY KEY и определить ограничение PRIMARY KEY для нескольких столбцов, используйте следующий синтаксис SQL:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Примечание: В приведенном выше примере существует только один первичный ключ (PK_Person).
Однако значение первичного ключа состоит из TWO COLUMNS (ID + LastName).

Определение сущностей

На этом этапе вам необходимо определить сущности, из которых будет состоять база данных.

Сущность — это объект в базе данных, в котором хранятся данные. Сущность может представлять собой нечто вещественное (дом, человек, предмет, место) или абстрактное (банковская операция, отдел компании, маршрут автобуса). В физической модели сущность называется таблицей.

Сущности состоят из атрибутов (столбцов таблицы) и записей (строк в таблице).

Обычно базы данных состоят из нескольких основных сущностей, связанных с большим количеством подчиненных сущностей. Основные сущности называются независимыми: они не зависят ни от какой-либо другой сущности. Подчиненные сущности называются зависимыми: для того чтобы существовала одна из них, должна существовать связанная с ней основная таблица.
На диаграммах сущности обычно представляются в виде прямоугольников. Имя сущности указывается внутри прямоугольника:

Любая таблица имеет следующие характеристики:

• в ней нет одинаковых строк;
• все столбцы (атрибуты) в таблице должны иметь разные имена;
• элементы в пределах одной колонки имеют одинаковый тип (строка, число, дата);
• порядок следования строк в таблице может быть произвольным.

На этом этапе вам необходимо выявить все категории информации (сущности), которые будут храниться в базе данных.

ОСНОВНОЙ КЛЮЧ

SQL PRIMARY KEY — это столбец в таблице, который должен содержать уникальное значение, которое можно использовать для уникальной идентификации каждой строки таблицы.

Тем не менее, SQL поддерживает первичные ключи напрямую с ограничением PRIMARY KEY.

Функционально это то же самое, что и ограничение UNIQUE, за исключением того, что для данной таблицы можно определить только один PRIMARY KEY. PRIMARY KEY не допускает значения NULL.

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

SQL PRIMARY KEY может состоять из одного или нескольких полей в таблице, и когда это происходит, они называются составным ключом.

Первичные ключи могут быть указаны во время CREATING TABLE или во время изменения структуры существующей таблицы с помощью инструкции ALTER TABLE.

Это ограничение является комбинацией ограничения NOT NULL и ограничения UNIQUE. Это ограничение гарантирует, что конкретный столбец или комбинация из двух или более столбцов для таблицы имеют уникальную идентификацию, которая помогает легче и быстрее найти конкретную запись в таблице.

Синтаксис:

 CREATE TABLE <имя_таблицы>
column1 data_type  NOT NULL PRIMARY KEY,
column2 data_type ,
...);

Параметры: