Логическое проектирование и реализация реляционных баз данных. Основы Transact-SQL (Пример выполнения лабораторной работы)

Логическое проектирование и реализация реляционных баз данных. Основы Transact-SQL

Пример выполнения лабораторной работы

1. Построение реляционной схемы БД

По всем правилам отображения концептуальной схемы (см. пример лабораторной работы №1) на логическую, получается схема отношений, показанная на рис. 1.

На схеме имеются таблицы, соответствующие сущностям: passengers, documents, stations, seats, coaches, trains. Каким сущностям они соответствуют, понятно из их названий. Первичные ключи таблиц – уникальные идентификаторы id_passenger, id_document, id_station, id_seat, id_coach, id_train соответственно.

Связи отображаются следующим образом.

1. Связь «проезжает» между поездами и станциями осуществляется с помощью таблицы trains_stations. В ней наличествуют два атрибута (внешних ключа): id_train и id_station.

2. Для связи вагона с поездом служит внешний ключ id_train в таблице coaches. 

3. Для связи места с вагоном служит внешний ключ id_coach в таблице seats.

4. Связь «занимает» между пассажирами и местами осуществляется с помощью таблицы passengers_seats, имеющая атрибуты id_passenger и id_seat.

5. Сущности "Пассажир" и "Документ" отобразятся на одну таблицу, т. к. связь 1:1.

 

Рисунок 1 – Реляционная схема БД

2. Реализация БД

Ниже представлен скрипт создания таблиц и ограничений целостности с помощью CREATE TABLE. Ограничения PRIMARY KEY и FOREIGN KEY заданы на табличном уровне, а все прочие – на уровне столбцов.

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

--Поезда

create table trains(

          id_train int identity,

          train_name varchar(80),

          train_type varchar(20),

          train_number int,

          constraint pk_train primary key (id_train))

--Станции

create table stations(

          id_station int identity,

          st_name varchar(80) unique,

          constraint pk_station primary key (id_station))

--Связующая таблица "Поезда-Станции"

create table trains_stations(

          id_train int,

          id_station int,

          type_route varchar(20),

          time_date_arrive datetime,

          time_date_depart datetime,

          constraint fk_ts_train foreign key (id_train) references trains(id_train),

          constraint fk_ts_station foreign key (id_station) references stations(id_station))

--Вагоны

create table coaches(

          id_coach int identity,

          coach_number int,

          coach_type varchar(20),

          id_train int,

          constraint pk_coach primary key (id_coach),

          constraint fk_coach_train foreign key (id_train) references trains(id_train))

--Места

create table seats(

          id_seat int identity,

          seat_number int,

          seat_type varchar(2),

          id_coach int,

          constraint pk_seat primary key (id_seat),

          constraint fk_seat_coach foreign key (id_coach) references coaches(id_coach))

--Пассажиры с своими документами

create table passengers(

          id_passenger int identity,

          pas_name varchar(20),

          pas_initials varchar(2),

          doc_type text,

          doc_serial varchar(80),

          constraint pk_passenger primary key (id_passenger))

--Связующая таблица "Пассажиры-Места"

create table passengers_seats(

          id_passenger int,

          id_seat int,

          price smallmoney,

          time_date_in datetime,

          time_date_out datetime,

          constraint fk_ps_passenger foreign key (id_passenger) references passengers(id_passenger),

          constraint fk_ps_seat foreign key (id_seat) references seats(id_seat))

Результат:

Command(s) completed successfully.

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

3. Заполнение таблиц и просмотр их содержимого

1. Вставка строк в таблицу trains.

--Поезда(название, тип, номер)

insert into trains values('Смена', 'фирменный', 25)

insert into trains values('Смена', 'фирменный', 26)

insert into trains values('Николаевский экспресс', 'фирменный', 5)

insert into trains values('Николаевский экспресс', 'фирменный', 6)

insert into trains values('Красная стрела', 'фирменный', 1)

insert into trains values('Красная стрела', 'фирменный', 2)

insert into trains values('Аврора', 'фирменный', 159)

insert into trains values('Аврора', 'фирменный', 160)

Результат:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

и т. д. для каждой вставленной строки

2. Просмотр содержимого таблицы trains:

select * from trains

Результат: