Введение
Целью данного индивидуального задания является разработка базы данных, предназначенной для хранения и обработки данных какой-либо одной небольшой автостостоянки (на 50 мест). В базе данных необходимо хранить и обрабатывать следующую информацию:
1. Сотрудники авто парковки. В базе данных должна храниться контактная, а также любая другая информация о каждом работнике авто стоянки, от сторожа до директора:
· ФИО сотрудника
· Адрес
· Телефон (домашний, мобильный)
· Дата рождения
· Дата приема на работу
· Должность
· Оклад
· Дата увольнения (если сотрудник увольняется, информация о нем из базы данных не удаляется)
2. Парковка. Информация о парковочных местах.
· Номер парковочного места
· Количество парковочных мест
3. Операции. Сюда входит информация о со скольки и до скольки парковочное место было занято.
· Парковочный номер
· Время приезда на парковку
· Время уезда с парковки
· Стоимость парковочного места (и днем и ночью парковочное место стоит одинаково)
Концептуальная модель
Определение сущностей
Любой фрагмент предметной области может быть представлен как множество сущностей, между которыми существует некоторое множество связей. Дадим определения:
Сущность (entity) - это объект, который может быть идентифицирован неким способом, отличающим его от других объектов. Примеры: конкретный человек, предприятие, событие и т.д.
Набор сущностей (entity set) - множество сущностей одного типа (обладающих одинаковыми свойствами). Примеры: все люди, предприятия, праздники и т.д. Наборы сущностей не обязательно должны быть непересекающимися. Например, сущность, принадлежащая к набору МУЖЧИНЫ, также принадлежит набору ЛЮДИ.
Сущность, фактически, представляет из себя множество атрибутов, которые описывают свойства всех членов данного набора сущностей.
На первом этапе проектирования БД мы определяем сущности для корректного использования их значений при определении типов связей между ними.
1. Парковочное место – сущность, представляющая из себя территорию авто стоянки, которое может быть занятым или свободным. Атрибуты:
· ID (уникальный идентификатор парковочного места)
· Price (цена)
2. Сотрудник – сущность, описывающая каждого сотрудника предприятия.
· ID (уникальный идентификатор сотрудника)
· Firstname (имя)
· Lastname (фамилия)
· Address (адрес)
· Phone (домашний телефон)
· Cellphone (мобильный телефон)
· Birthday (дата рождения)
· Hiredate (дата приема на работу)
· Post (занимаемая должность)
· Salary (текущая заработная плана)
· Fired (поле указывающее на то, если сотрудник был уволен)
· Firedate (дата увольнения сотрудника)
3. Операция – сущность описывающая учет парковочных мест.
· ID (уникальный идентификатор операции)
· Place_id (ID парковочного места, с которым эта операция связана)
· Time_in (время приезда на парковку)
· Time_out (время уезда на парковку)
· Price (тут указывается цена, за час парковки в данный момент)
Определение типов связей между сущностями
На втором этапе проектирования БД мы предварительно строим связи между сущностями, которые в дальнейшем будут использоваться при построении физической модели.
| Сущность | Связь | Сущность | Тип связи |
| Сотрудник | Дежурит | На стоянке | М:1 |
| Операции | Совершаются над | Стоянками | 1:1 |
Определение атрибутов
На этом этапе проектирования БД мы определяем атрибуты для наших сущностей, которые будут описывать будущую физическую модель.
| Сотрудники | Операции | Парковочное место |
| Id | Id | Id |
| Firstname | Place_id | Price |
| Lastname | Time_in | ---------------------------------- |
| Address | Time_out | ---------------------------------- |
| Phone | Price | ---------------------------------- |
| Cellphone | ---------------------------------- | ---------------------------------- |
| Hiredate | ---------------------------------- | ---------------------------------- |
| Birthday | ---------------------------------- | ---------------------------------- |
| Post | ---------------------------------- | ---------------------------------- |
| Salary | ---------------------------------- | ---------------------------------- |
| Fired | ---------------------------------- | ---------------------------------- |
| Firedate | ---------------------------------- | ---------------------------------- |
Определение доменов
В данной части проекта мы запишем все сущности, какие к ним атрибуты относятся, диапазон допустимых значений для каждого поля и тип данных для всех атрибутов. Атрибуты БД не просто определяют структуру базы – они еще определяют групповые свойства данных, записываемых в ячейки, принадлежащие каждому из атрибутов. Сущности БД, как правило, допускают работу с гораздо большим количеством разных типов данных.
| Сущность | Атрибут | Диапазон допустимых значений | Тип данных |
| 1. Сотрудники | Id | Число от 1 до 1 млн. | Int |
| Firstname | До 30 символов | Varchar(30) | |
| Lastname | До 30 символов | Varchar(30) | |
| Address | До 60 символов | Varchar(60) | |
| Phone | До 30 символов | Varchar(30) | |
| Cellphone | До 30 символов | Varchar(30) | |
| Hiredate | Полный формат даты | Datetime | |
| Birthday | Полный формат даты | Datetime | |
| Post | До 20 символов | Varchar(20) | |
| Salary | Полный денежный формат даны | Money | |
| Fired | До 5 символов ('TRUE' или 'FALSE') | Varchar(5) | |
| Firedate | Полный формат даты | Datetime | |
| 2. Операции | Id | Число от 1 до 1 млн. | Int |
| Place_id | Число от 1 до 1 млн. | Int | |
| Time_in | Полный формат даты | Datetime | |
| Time_out | Полный формат даты | Datetime | |
| 3. Парковочные места | Id | Число от 1 до 1 млн. | Int |
| Price | Число от 1 до 1 млн. | Money |
Определение первичных и потенциальных ключей
После того, как мы выполнили определение доменов, мы определяем для каждой сущности первичный и потенциальный ключи. Это говорит о том, что поле, имеющее такой ключ, будет уникальным, и его значения не будут повторяться. Обычно поля с такими ключами и являются связующими на этапе определения физической модели.
| Сущность | Первичный ключ | Потенциальный ключ |
| Сотрудники | Id | Cellphone |
| Phone | ||
| Address | ||
| Парковочное место | Id | Id |
| Операции | Id | ------------------------------------- |
Диаграмма
Диаграмма является предзавершающим этапом в проектировании БД и определяет цепь событий осуществляемых пользователем.
Логическая модель
Логическая модель является завершающим этапом при проектировании БД. Здесь мы определяем структуру связей всех сущностей друг с другом. Удачная разработка логической модели данных обеспечивает простоту построения физической модели.
 | |
 |
Физическая модель
Схема связей
Создание таблиц
· Employees (Сотрудники) – в данной таблице будет собрана информация о всех сотрудниках парковки, работающих сейчас, или работающих раньше, но уволенных. Помимо имени и фамилии, необходима также контактная информация (адрес и телефон), а также информация о занимаемой должности и заработной плате в магазине.
CREATE TABLE [Employees] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[fname] [varchar] (20) COLLATE Cyrillic_General_CI_AS NULL,
[lname] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[address] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[phone] [int] COLLATE Cyrillic_General_CI_AS NULL,
[cellphone] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[hiredate] [datetime] NULL,
[birthday] [datetime] NULL,
[post] [varchar] (20) COLLATE Cyrillic_General_CI_AS NULL,
[salary] [money] NULL,
[fired] [varchar] (5) COLLATE Cyrillic_General_CI_AS NOT NULL CONSTRAINT [DF__Employees__fired__6B24EA82] DEFAULT ('FALSE'),
[firedate] [datetime] NULL CONSTRAINT [DF__Employees__fired__6C190EBB] DEFAULT (null),
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
· Parking_Place (парковочное место) – В этой таблице собрана информация о парковочных местах.
CREATE TABLE [Products] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[price] [money]NULL
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
· Operations (Операции) – В данной таблице сохраняется информация об операция проводимых на парковке. Причем присутствуют данные как и о настоящем состоянии парковки, так и история. Также указывается дата и время операции для построения отчетов о прибыли.
CREATE TABLE [Operations] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[place_id] [int] NULL,
[time_in] [datetime] NULL,
[time_out] [datetime] NULL,
[price] [money] NULL,
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY],
FOREIGN KEY
(
[place_id]
) REFERENCES [Parking_Place] (
[id]
)
) ON [PRIMARY]
GO
Создание функций
Для построения некоторых запросов, которые понадобятся в процедурах, очень пригодятся некоторые функции, например, такие как преобразование полного формата даты «DATETIME», в более понятный и удобочитаемый для человека формат «ДД.ММ.ГГГГ», и наоборот. Также понадобятся функции, которые помогут быстро узнать, свободно ли парковочное место, кто был ответственен за парковку в указанное время.
· DatetimeToDate – функция преобразующая дату из вида
«0000-00-00 00:00:00.000 » в формат «ДД.ММ.ГГГГ ». В качестве аргумента принимает параметр типа «DATETIME », и возвращает строку длиной 10 символов (VARCHAR(10)).
CREATE FUNCTION DatetimeToDate (
@datetime datetime
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @date VARCHAR(20)
SET @date = CONVERT(VARCHAR(2), DAY(@datetime)) + '.' +
CONVERT(VARCHAR(2), MONTH(@datetime)) + '.' +
CONVERT(VARCHAR(4), YEAR(@datetime))
RETURN (@date)
END
GO
· IsFree – функция, принимающая в качестве аргумента число типа INT, и возвращающее 1, если парковочное место с таким идентификационным номером свободно, и 0, если нет.
CREATE FUNCTION IsFree(@id int) RETURNS INT
AS
BEGIN
DECLARE @result int
IF (EXISTS (SELECT * FROM Operations WHERE id = @id AND time_out LIKE ’’)) SET @result = 1
ELSE SET @result = 0
RETURN @result
END
GO
Создание процедур
· hireEmployee – процедура предназначена для приема на работу сотрудника. Принимает в качестве параметров 8 аргументов, необходимых нам для приема на работу, такие как имя, контактные и личные данные и информация о занимаемой должности и заработной планет. Также процедура, проверяет, нет ли в базе данных уже сотрудника с указанными именем и фамилией на случай ошибки оператора или случайной отправки двух или более одинаковых запросов на сервер. Если такой сотрудник находится, выводится сообщение оповещающее об этом.
CREATE PROCEDURE hireEmployee
@firstname varchar(30),
@lastname varchar(30),
@address varchar(30),
@phone varchar(30),
@cellphone varchar(30),
@birthday datetime,
@post varchar(20),
@salary money
AS
IF EXISTS (SELECT * FROM Employees WHERE firstname = @firstname AND lastname = @lastname)
PRINT 'В базе данных уже существует сотрудник с заданными именем и фамилией.'
ELSE
INSERT INTO Employees (firstname, lastname, address, phone, cellphone, hiredate, birthday, post, salary)
VALUES (@firstname, @lastname, @address, @phone, @cellphone, getdate(), @birthday, @post, @salary)
GO
· updateEmployee – процедура обновляет какую-либо информацию о сотруднике стоянки. В качестве параметров принимаются: ID сотрудника, чью информацию необходимо изменить, поле, которое должно быть изменено и новое значение, которое должно быть установлено. Если вторым аргументом указывается недопустимое для изменения поле или строка, с названием которого в таблице Employees не существует столбца, процедура выводит сообщение об ошибке.
CREATE PROC updateEmployee
@id int,
@param varchar(255),
@value varchar(255)
AS
IF @param='firstname' UPDATE Employees SET firstname = @value WHERE id=@id
ELSE IF @param='lastname' UPDATE Employees SET lastname = @value WHERE id=@id
ELSE IF @param='address' UPDATE Employees SET address = @value WHERE id=@id
ELSE IF @param='phone' UPDATE Employees SET phone = @value WHERE id=@id
ELSE IF @param='cellphone' UPDATE Employees SET cellphone = @value WHERE id=@id
ELSE IF @param='birthday' UPDATE Employees SET birthday = @value WHERE id=@id
ELSE IF @param='post' UPDATE Employees SET post = @value WHERE id=@id
ELSE IF @param='salary' UPDATE Employees SET salary = CONVERT(money, @value) WHERE id=@id
ELSE PRINT 'Данный параметр не может быть изменен, либо его не существует.'
GO
· fireEmployee – процедура предназначена для увольнения сотрудника с указанным идентификационным номером. При успешном выполнении процедуры, значение поля «fired » у сотрудника стоянки меняется на «TRUE », а в поле «firedate» выставляется текущая дата. Если по ошибке сотрудник, с указанным ID уже был уволен, или такого сотрудника вообще не существует в таблице Employees, об этом выводит сообщение.
CREATE PROCEDURE fireEmployee
@id int
AS
IF EXISTS (SELECT * FROM Employees WHERE id = @id AND fired = 'FALSE')
BEGIN
UPDATE Employees SET fired = 'TRUE', firedate = getdate() WHERE id = @id
PRINT 'Сотрудник уволен.'
END
ELSE IF EXISTS (SELECT * FROM Employees WHERE id = @id AND fired = 'TRUE')
PRINT 'Ранее сотрудник уже был уволен.'
ELSE
PRINT 'Сотрудника с указаными ID не существует.'
GO
· showEmployees – процедура предназначена для вывода информации о всех сотрудниках. При этом может быть указан необязательный параметр @status, который служит для вывода либо только текущих сотрудников магазина «hired », либо только уволенных сотрдуников «fired ». Если данный параметр не указан, абсолютно все сотрудники когда-либо работающие в магазине.
CREATE PROC showEmployees
@status VARCHAR(5) = ‘’
AS
IF (@status = 'hired')
SELECT firstname, lastname, address, phone, cellphone, dbo.DatetimeToDate(hiredate), dbo.DatetimeToDate(birthday), post, salary FROM Employees WHERE fired = 'FALSE'
ELSE IF (@status = 'fired')
SELECT firstname, lastname, address, phone, cellphone, dbo.DatetimeToDate(hiredate), dbo.DatetimeToDate(birthday), post, salary, fired, dbo.DatetimeToDate(firedate) FROM Employees WHERE fired = 'TRUE'
ELSE
SELECT id, firstname, lastname, address, phone, cellphone, dbo.DatetimeToDate(hiredate) AS hiredate, dbo.DatetimeToDate(birthday) AS birthday, post, salary, fired, dbo.DatetimeToDate(firedate) AS firedate FROM Employees
GO
· showFreePlace – процедура выводит информацию о свободных парковочных местах.
CREATE PROC showFreePlace
AS
SELECT * FROM Parking_Place WHERE dbo.isFree(id)
GO
· updateParkingPrice – простая процедура предназначена для изменения цены парковочного места.
CREATE PROC updateParkingPlace
@id int,
@value varchar(50)
AS
BEGIN
DECLARE @price money
SELECT @price = price FROM Parking_Place WHERE id = @id
BEGIN
UPDATE Parking_Place SET price = CONVERT(money, @value)
PRINT 'Цена парковочного места "' + @id + '" была изменена с ' + CONVERT(varchar(10), @price) + ' на ' + CONVERT(varchar(10), CONVERT(money, @value)) + '.'
END
ELSE PRINT 'Произошла ошибка в записи.'
END
GO
· updateParking – когда парковочное место освободится то понадобится процедура при помощи которой можно будет отобразить это в таблице.
CREATE PROC updateParkingPlace
@id int
AS
BEGIN
UPDATE Operations SET firedate = getdate() WHERE id = @id
PRINT 'Данные успешно внесены.'
END
· showDohod – выводится информация о выручке. Так же есть возможность вывести информация за определенный промежуток времени.
CREATE proc showDohod
@begin_date varchar(10) = '',
@end_date varchar(10) = ''
AS
BEGIN
/* Если указаны даты, преобзауем их в DATETIME. */
DECLARE @first_date datetime, @second_date datetime
IF (@begin_date!= '') SET @first_date = dbo.DateToDatetime(@begin_date)
IF (@end_date!= '') SET @second_date = dbo.DateToDatetime(@end_date)
/* Если указан период, за который необходимо вывести информацию. */
IF (@begin_date!= '' AND @end_date!= '')
SELECT sum(price) as 'Сумма '
FROM Operations
WHERE datetime BETWEEN @first_date AND @second_date
/* Если указана только первая дата, выводим информацию с той даты
по сегодняшний день. */
ELSE IF (@begin_date!= '' AND @end_date = '')
SELECT sum(price) as 'Сумма '
FROM Operations
WHERE datetime BETWEEN @first_date AND DATEADD(day, 1, getdate())
END
GO
Создание триггеров
· insertTimeOut – данный триггер прописывается для таблицы «Operations », и предназначен для того, что не возможно было вставить время освобождения парковочного места если время приезда не указано. В противном случае могут возникнуть логические ошибки.
CREATE TRIGGER insertTimeOut ON Operations
FOR INSERT
AS
BEGIN
DECLARE @in
DECLARE @out
SELECT @in = time_in, @out = time_out
FROM inserted
IF (@time_in = '')
PRINT (‘Вы не указали дату приезда.’)
ELSE IF
PRINT (‘Спасибо что воспользовались нашей парковкой.’)
END
GO
Оглавление
Введение. 2
1. Концептуальная модель. 2
1.1 Определение сущностей. 2
1.2 Определение типов связей между сущностями. 3
1.3 Определение атрибутов. 3
1.4 Определение доменов. 4
1.4 Определение первичных и потенциальных ключей. 5
1.5 Диаграмма. 5
2. Логическая модель. 5
3. Физическая модель. 6
3.1 Схема связей. 6
3.2 Создание таблиц. 6
3.3 Создание функций. 8
3.4 Создание процедур. 9
3.5 Создание триггеров. 13