1.3 Физическая структура базы данных

Этап физического проектирования заключается в определении схемы хранения, т.е. физической структуры БД. Схема хранения зависит от той физической структуры, которую поддерживает выбранная СУБД. Физическая структура БД, с одной стороны, должна адекватно отражать логическую структуруБД, а с другой стороны, должна обеспечивать эффективное размещение данных и быстрый доступ к ним. Результаты этого этапа документируются в форме схемы хранения на языке определения данных (DDL, Data Definition Language) выбранной СУБД. Принятые на этом этапе решения оказывают огромное влияние на производительность системы.

Одной из важнейших составляющих проекта базы данных является разработка средств защиты БД. Защита данных имеет два аспекта: защита от сбоев и защита от несанкционированного доступа. Для защиты от сбоев на этапе физического проектирования разрабатывается стратегия резервного копирования.

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

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

Отношения приведены в табл.1-5. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной.

Таблица 1. "Клиенты"

Таблица 2. "Номера"

Таблица 3. "Персонал"

Таблица 4. "Категории номеров"

Таблица 5. "Учет работы"

2. Реализация проекта БД

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

1. Отношение kl (Клиенты)

create table kl (id_k int identity (1,1) not null primary key, f varchar (50) not null, i varchar (50) not null, o varchar (50) not null, dr datetime not null, tel varchar (50) not null, pasport varchar (50) not null);

2. Отношение nomer (Номер)

create table nomer (id_n int identity (1,1) not null primary key, id_kat int not null references kat, cc money not null, km int not null char between 1 and 4);

3. Отношение Personal (Персонал)

create table personal (id_p int identity (1,1) not null primary key, fam varchar (50) not null, im varchar (50) not null, ot varchar (50), dolj varchar (50) not null, zp money not null);

4. Отношение kat (Категории номеров)

create table kat (id_kat int identity (1,1) not null primary key, nazvn varchar (50) not null);

5. Отношение job (Учет работы)

create table job (id_o int identity (1,1) not null primary key, id_n int not null references nomer, id_k int not null references kl, dz datetime not null, dv datetime not null, id_p int not null references personal);

1. Ведение списка постояльцев

SELECT dbo. kl. f AS Фамилия, dbo. kl. i AS Имя, dbo. kl. o AS Отчество, dbo. kl. dr AS [Дата рождения], dbo. job. dz AS [Дата заселения]

FROM dbo. kl INNER JOIN dbo. job ON dbo. kl. id_k = dbo. job. id_k

GROUP BY dbo. kl. f, dbo. kl. i, dbo. kl. o, dbo. kl. dr, dbo. job. dz

HAVING (COUNT (dbo. job. id_k) > 1)

2. Учет забронированных мест

select id_n as [Номер], kat. nazvn as [Категория]

from nomer, kat, job

where nomer. id_n = job. id_n

order by id_n

3. Ведение архива выбывших постояльцев за последний год

select f as [Фамилия], i as [Имя], o as [Отчество], getdate () as [Сегодня], job. dv as [Дата выселения]

from kl, job

where job. dv < getdate () and job. id_k = kl. id_k

order by f

4. Получение списка свободных номеров

select id_n as [Номер], kat. nazvn as [Категория]

from nomer, kat, job

where nomer. id_n! = job. id_n

order by id_n

5. Полученеи списка номеров, освобождающихся сегодня

select nomer. id_n as [Номер], kat. nazvn as [Категория], getdate () as [Сегодня], dateadd (day, 1, current_timestamp) as [Завтра]

from nomer, kat, job

where job. dv = getdate () or job. dv=dateadd (day, 1, current_timestamp) and

nomer. id_kat = kat. id_kat and

job. id_n = nomer. id_n

6. Выдача информации по конкретному номеру

select nomer. id_n as [Номер], kat. nazvn as [Категория], nomer. cc as [Стоимость за сутки], nomer. km as [Количество мест]

from kat, nomer

where kat. id_kat = nomer. id_kat

7. Автоматизация выдачи счетов на оплату

SELECT kl. f AS [Фамилия], kl. i AS [Имя], kl. o AS [Отчество], nomer. id_n AS [Номер], job. kd * nomer. cc AS [Сумма]

FROM kl, nomer, job, kat

WHERE kl. id_k = job. id_k AND nomer. id_kat = kat. id_kat and nomer. id_n = job. id_n

8. Проверка наличия брони по имени клиента

select kl. f as [Фамилия], kl. i as [Имя], kl. o as [Отчество], nomer. id_n as [Номер]

from kl, nomer, job, personal, kat

where kl. id_k = job. id_k and

nomer. id_kat = kat. id_kat and

job. id_p = personal. id_p

Создание процедур

Процедура "Броня"

ALTER PROCEDURE Броня

(

@parameter1 varchar (50) = ''

)

AS

select Фамилия, Имя, Отчество, Номер

from "Наличие брони по имени клиента"

where Фамилия = @parameter1

RETURN

Вызов процедуры

Dim a = InputBox ("Введите фамилию клиента или название организации")

Dim db As New DataClasses1DataContext

Dim b = db. Броня (a)

Броня. DataGridView1. DataSource = b

Броня. Show ()

Процедура "Клиент"

ALTER PROCEDURE Клиент

(

@parameter1 varchar (50) = ''

)

AS

select Фамилия, Имя, Отчество, "Дета рождения", Телефон, Паспорт

from "Информация о клиенте"

where Фамилия = @parameter1

RETURN

Вызов процедуры

Dim a = InputBox ("Введите фамилию клиента")

Dim db As New DataClasses1DataContext

Dim b = db. Клиент (a)

клиент. DataGridView1. DataSource = b

клиент. Show ()

Процедура "Номер"

ALTER PROCEDURE Номер

(

@parameter1 int = ''

)

AS

select Номер, Категория, "Стоимость за сутки", "Количество мест"

from "Информация по конкретному номеру"

where Номер = @parameter1

RETURN

Вызов процедуры

Dim a = InputBox ("Введите значение номера")

Dim db As New DataClasses1DataContext

Dim b = db. Номер (a)

номер. DataGridView1. DataSource = b

номер. Show ()

Процедура "Счет"

ALTER PROCEDURE Счет

(

@parameter1 int = ''

)

AS

select Фамилия, Имя, Отчество, Номер, Сумма

from "Выдача счетов на оплату"

where Номер = @parameter1

RETURN

Вызов процедуры

Dim a = InputBox ("Введите значение номера")

Dim db As New DataClasses1DataContext

Dim b = db. Счет (a)

счет. DataGridView1. DataSource = b

счет. Show ()