Нормализация 3НФ.
Нормализация 1НФ. Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов (атомарность значений). Для получения 1-ой нормальной формы необходимо разбить столбцы Пассажир, Откуда/Куда, Вылет – на 2 столбца, Самолёт – на 5 столбцов:
Все столбцы содержат сейчас только атомарные значения. 1НФ достигнута. Нормализация 2НФ. Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый не ключевой атрибут неприводимо зависит от ее потенциального ключа. Как видно из таблицы, имя и фамилия пассажира и номер рейса зависят от номера билета; серийный номер самолёта, дата и время вылета, а также время в пути и пункты назначения и отправления зависят от номера рейса; модель самолёта, количество пассажирских мест, количество пилотов и принадлежность компаниям зависят от серийного номера самолёта. Для получения 2-ой нормальной формы разобьем таблицу (отношение) на 3 отдельных отношения:
Нормализация 3НФ. Из определения 3-ой нормальной формы: отношение находится в третьей нормальной форме если оно уже 2НФ и любой её не ключевой атрибут функционально зависит только от первичного ключа. Отношение оказалось в 3НФ. Нормализация выполнена. Таблица 1: USE Misha_aeroport CREATE TABLE Planes ( PlaneNumber int PRIMARY KEY, Model varchar(50) NOT NULL, Company varchar(50) NOT NULL, PilotsN int NOT NULL, PassengersN int NOT NULL ) Описание столбцов: PlaneSerial – серийный номер самолёта, тип число, первичный ключ; Model – модель самолёта, тип строка (до 50 символов); Company – компания-владелец самолёта, тип строка (до 50 символов); PilotsN – количество пилотов, тип число; PassengersN – количество пассажирских мест, тип число.
Таблица 2: USE Misha_aeroport CREATE TABLE Flights ( FlightNumber int PRIMARY KEY, FlightFrom varchar(50) NOT NULL, FlightTo varchar(50) NOT NULL, PlaneSerial int NOT NULL FOREIGN KEY REFERENCES Planes, DateFlight date NOT NULL, TimeFlight time NOT NULL, TravelTime int NOT NULL ) Описание столбцов: FlightNumber – номер рейса, тип число, первичный ключ; FlightFrom, FlightTo – пункт отправления и пункт назначения, тип строка (до 50 символов). В дальнейшем создадим ограничение CHECK, препятствующее добавлению рейсов, в которых пункт отправления и пункт назначения совпадают; PlaneSerial – серийный номер самолёта, тип число, внешний ключ; DateFlight – дата вылета, тип дата; TimeFlight – время вылета, тип время; TravelTime – время в пути (в часах), тип число.
Таблица 3: USE Misha_Aeroport CREATE TABLE Tickets ( TicketNumber int NOT NULL PRIMARY KEY, PassengerName varchar(50) NOT NULL, PassengerSurname varchar(50) NOT NULL, FlightNumber int NOT NULL FOREIGN KEY REFERENCES Flights )
Описание столбцов: TicketNumber – номер билета, тип число, первичный ключ; PassengerName, PassengerSurname – имя и фамилия пассажира, тип строка (до 50 символов); FlightNumber – номер рейса, тип число, внешний ключ. Проверим, что пункт вылета и назначения не совпадают (ограничение CHECK) USE Misha_Aeroport ALTER TABLE Flights ADD CONSTRAINT from_to CHECK ( FlightFrom <> FlightTo ) 3. Нарисовать схему Базы данных.
4. Выбрать СУБД. Создать физическую модель.
5. Заполнить таблицы (10 записей в основной таблице).
6. Придумать три запроса для вывода данных из двух-трех таблиц. 1) Вывести пункт назначения для пассажиров с именем Михаил.
2) Выведем Фамилии пассажиров и дату их вылета с помощью RIGHT OUTER JOIN.
Выведем Фамилии пассажиров и дату их вылета с помощью LEFT OUTER JOIN.
3) Выведем Ил-96, летящий в Милан.
7.Создать хотя бы одно представление для вывода данных из двух таблиц USE Misha_Aeroport GO CREATE VIEW Представление AS SELECT FlightFrom + ' – ' + FlightTo as [Рейс], FlightNumber as [#], DateFlight as [Дата], model as [Модель] FROM Flights inner join Planes ON Flights.PlaneSerial = Planes.PlaneSerial GO
8. Создадим процедуру, которая по заданному номеру рейса возвращает количество доступных билетов. USE Misha_Aeroport GO CREATE PROCEDURE if_flight @tFlightNumber int AS SELECT (SELECT PassengersN FROM Planes WHERE PlaneSerial=(SELECT PlaneSerial FROM Flights WHERE FlightNumber=@tFlightNumber)) - (SELECT COUNT(*) FROM Tickets WHERE FlightNumber=@tFlightNumber) AS [Доступно билетов] GO
Создадим триггер, препятствующий удалению данных из таблицы Flights. USE Misha_Aeroport GO CREATE TRIGGER DisableFlightDelete ON Flights INSTEAD OF DELETE AS DECLARE @FlightDestination varchar(50) SELECT @FlightDestination = FlightTo FROM deleted RAISERROR (Вы хотите удалить %s. Отменено.', 10, 1, @FlightDestination); ROLLBACK TRANSACTION GO
9. В дополнении к уже созданной БД выполнить пункт, связанный с использованием конструкцией PIVOT. Для этого нужно будет добавить несколько столбцов (если они отсутствуют), заполнить таблицу нужной информацией и написать запрос с применением конструкции PIVOT. Посчитаем количество самолетов разных моделей.
|
