Моделирование Схемы реляционной базы данных
Моделирование Схемы реляционной базы данных
Задание:
1 составить физическую концептуальную схему базы данных (т.е. с указанием типов атрибутов каждой сущности). Картинку привести в отчёте со всеми текстовыми пояснениями;
2 написать запросы на языке SQL на создание и удаление всех таблиц концептуальной схемы. Привести текст запросов;
3 написать на языке SQL минимум 2 запроса на выборку данных (SELECT) к проектируемой базе данных. Каждый запрос должен быть минимум к 3-м таблицам (т.е. соединять в запросе эти три таблицы) и должен иметь какой-то смысл – выбирать информацию, которая может реально понадобиться. Привести текст запросов с пояснениями.
В концептуальной схеме должно быть не менее 6 сущностей.
Выполнение:
При помощи программы «ERwin» составим физическую концептуальную схему базы данных «Больница». Реляционная база данных состоит из таблиц, в которых хранятся данные, т.е. реляционная база данных это база данных, информация которой хранится в таблицах. Таблицы состоят из строк (записей) и столбцов.
На этапе моделирования базы данных составляется её концептуальная схема. Она отображает всю структуру базы данных: все её таблицы и связи между ними. Используя программу «ERwin» составим физическую концептуальную схему базы данных «Больница» (рисунок 3.1).
Рисунок 3.1 – Концептуальная схема базы данных
Опишем данную схему. Наша база данных (БД) имеет следующие таблицы (сущности):
doctors (врачи). Она имеет такие атрибуты как: doctors _id (код города) это ключевое поле, тип данных: Integer; doctors_name (название города), тип данных: Varchar;
patients (пациенты). Она имеет такие атрибуты как: patients _id (код улицы) это ключевое поле, тип данных: Integer; patients _name (название улицы), тип данных: Varchar и doctors _id (код города), тип данных: Integer;
disease (заболевания). Она имеет такие атрибуты как: disease _id (код строения) это ключевое поле, тип данных: Integer; doctors _id (номер строения), тип данных: Integer, street_id (код улицы); тип данных: Integer, owner_id (код владельца), тип данных: Integer
drugs (Лекарства). Она имеет такие атрибуты как: object_id (код объекта) это ключевое поле, тип данных: Integer; flat_number (номер помещения), тип данных: Integer; building_id (код строения), тип данных: Integer; floor (этаж), тип данных Integer; price (цена), тип данных: Money; square (площадь), тип данных: Integer; balcony (балкон), тип данных: Boolean; room_amount(количество комнат), тип данных: Integer; stop_id (код остановки), тип данных: Integer.
operations (операции). Она имеет такие атрибуты как: owner_id (код владельца), тип данных: Integer; owner_name (Имя владельца), тип данных: Varchar.
procedure (Процедуры). Она имеет такие атрибуты как: stop_id (код остановки), тип данных: Integer; stop_name (название остановки), тип данных: Varchar.
Cours of medication (курсприемалекарств). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
Coursof procedur (курспроцедур). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
treatment (лечение). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
Между данными сущностями существуют связи. Между сущностями есть связь «неидентифицирующие один ко многим». Это означает, что в одном городе может быть много улиц, в одном строении может быть несколько объектов недвижимости и т. п. Рассмотрим как создаются сущности и связи между ними в программе «ERwin».
Чтобы создать сущность в программе «ERwin» на панели «ERvinTollbox» нажимаем на кнопку с изображением прямоугольника (вторая слева в верхнем ряду кнопок). Кликаем по чистой области окна программы, появляется сущность в виде прямоугольника, разделённого на две части (верхнюю, в ней отображаются ключевые поля сущности; нижнюю, в ней отображаются атрибуты). Нам нужно дать ей имя, для этого в графе с именем кликаем дважды левой кнопкой мыши и даём имя сущности. Чтобы присвоить сущности атрибуты, на панели «ERvinTollbox» жмём на кнопку с изображением стрелки (слева вверху), затем кликаем дважды по интересующей нас сущности, открывается окно «ColumnEditor» (рисунок 3.2).
Рисунок 3.2 - Окно «ColumnEditor»
В открывшемся окне чтобы присвоить сущности атрибут жмём на кнопку «New», вводим название атрибута и жмём «Ок». Далее в маленьком окошке справа «Datatype» выбираем тип данных для атрибута. Если необходимо сделать атрибут ключевым, заходим на вкладку «General» и ставим внизу галку «PrimaryKey». Далее возвращаемся назад и жмём «Ок». Сущность готова.
Для создания связи между сущностями на панели «ERvinTollbox» жмём на кнопку с изображением связи, затем кликаем на одну сущность, потом на другую и связь между ними готова.
Для создания и удаления таблиц можно воспользоваться языком «SQL».
Для создания таблиц на языке «SQL» используется оператор «createtable». После оператора через пробел указывается имя таблиц, далее через пробел в скобках указываются атрибуты таблицы с указанием типа данных и первичных ключей. Представим, как выглядит создание таблиц на «SQL» для нашей БД.
Create table City (
City_idint not null primary key,
City_namevarchar(25), not null)
Create table Object (
Object_idint not null primary key,
Price money not null,
Building_idint not null
Floor int not null,
Flat_numberint not null,
Square int not null,
Balcony boolean not null,
Room_amountint not null,
Stop_idint not null )
Для удаления таблицы на языке «SQL» используется оператор «droptable». После этого оператора через пробел указывается имя таблицы, которую необходимо удалить. Приведём пример, пользуясь таблицами нашей БД.
- DroptableCity
- DroptableObject
Для создания запросов на выборку в языке «SQL» используется оператор «Select». Данный оператор имеет следующий синтаксис: Selectfromwhere. Если необходимо чтобы из результата запроса исключались дубликаты, то после оператора ставится слово «distinct», а если нужны все результаты, то слово «all». Рассмотрим примеры запросов к нашей БД.
Вывести всю информацию о помещениях с ценой менее 500000:
SELECT City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
FROM Transport INNER JOIN ((Transport_stops INNER JOIN ((City INNER JOIN Street ON City.city_id=Street.city_id) INNER JOIN (Owners INNER JOIN (Building INNER JOIN [Object] ON Building.building_id=Object.building_id) ON Owners.owner_id=Building.owner_id) ON Street.street_id=Building.street_id) ON Transport_stops.stop_id=Object.stop_id) INNER JOIN TS ON Transport_stops.stop_id=TS.stop_id) ON Transport.transport_id=TS.transport_id GROUP BY City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
HAVING (((Object.price)<500000));
Вывести всю информацию о помещениях принадлежащих Петрову:
SELECT City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
FROM Transport INNER JOIN ((Transport_stops INNER JOIN ((City INNER JOIN Street ON City.city_id=Street.city_id) INNER JOIN (Owners INNER JOIN (Building INNER JOIN [Object] ON Building.building_id= Object.building_id) ON Owners.owner_id=Building.owner_id) ON Street.street_id=Building.street_id) ON Transport_stops.stop_id = Object.stop_id) INNER JOIN TS ON Transport_stops.stop_id=TS.stop_id) ON Transport.transport_id=TS.transport_id GROUP BY City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
HAVING (((Owners.owner_name)="Петров"));
Задание:
1 составить физическую концептуальную схему базы данных (т.е. с указанием типов атрибутов каждой сущности). Картинку привести в отчёте со всеми текстовыми пояснениями;
2 написать запросы на языке SQL на создание и удаление всех таблиц концептуальной схемы. Привести текст запросов;
3 написать на языке SQL минимум 2 запроса на выборку данных (SELECT) к проектируемой базе данных. Каждый запрос должен быть минимум к 3-м таблицам (т.е. соединять в запросе эти три таблицы) и должен иметь какой-то смысл – выбирать информацию, которая может реально понадобиться. Привести текст запросов с пояснениями.
В концептуальной схеме должно быть не менее 6 сущностей.
Выполнение:
При помощи программы «ERwin» составим физическую концептуальную схему базы данных «Больница». Реляционная база данных состоит из таблиц, в которых хранятся данные, т.е. реляционная база данных это база данных, информация которой хранится в таблицах. Таблицы состоят из строк (записей) и столбцов.
На этапе моделирования базы данных составляется её концептуальная схема. Она отображает всю структуру базы данных: все её таблицы и связи между ними. Используя программу «ERwin» составим физическую концептуальную схему базы данных «Больница» (рисунок 3.1).
Рисунок 3.1 – Концептуальная схема базы данных
Опишем данную схему. Наша база данных (БД) имеет следующие таблицы (сущности):
doctors (врачи). Она имеет такие атрибуты как: doctors _id (код города) это ключевое поле, тип данных: Integer; doctors_name (название города), тип данных: Varchar;
patients (пациенты). Она имеет такие атрибуты как: patients _id (код улицы) это ключевое поле, тип данных: Integer; patients _name (название улицы), тип данных: Varchar и doctors _id (код города), тип данных: Integer;
disease (заболевания). Она имеет такие атрибуты как: disease _id (код строения) это ключевое поле, тип данных: Integer; doctors _id (номер строения), тип данных: Integer, street_id (код улицы); тип данных: Integer, owner_id (код владельца), тип данных: Integer
drugs (Лекарства). Она имеет такие атрибуты как: object_id (код объекта) это ключевое поле, тип данных: Integer; flat_number (номер помещения), тип данных: Integer; building_id (код строения), тип данных: Integer; floor (этаж), тип данных Integer; price (цена), тип данных: Money; square (площадь), тип данных: Integer; balcony (балкон), тип данных: Boolean; room_amount(количество комнат), тип данных: Integer; stop_id (код остановки), тип данных: Integer.
operations (операции). Она имеет такие атрибуты как: owner_id (код владельца), тип данных: Integer; owner_name (Имя владельца), тип данных: Varchar.
procedure (Процедуры). Она имеет такие атрибуты как: stop_id (код остановки), тип данных: Integer; stop_name (название остановки), тип данных: Varchar.
Cours of medication (курсприемалекарств). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
Coursof procedur (курспроцедур). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
treatment (лечение). Онаимееттакиеатрибутыкак: transport_id (кодтранспорта), типданных: Integer; transport_type (типтрпнспорта), типданных: Varchar.
Между данными сущностями существуют связи. Между сущностями есть связь «неидентифицирующие один ко многим». Это означает, что в одном городе может быть много улиц, в одном строении может быть несколько объектов недвижимости и т. п. Рассмотрим как создаются сущности и связи между ними в программе «ERwin».
Чтобы создать сущность в программе «ERwin» на панели «ERvinTollbox» нажимаем на кнопку с изображением прямоугольника (вторая слева в верхнем ряду кнопок). Кликаем по чистой области окна программы, появляется сущность в виде прямоугольника, разделённого на две части (верхнюю, в ней отображаются ключевые поля сущности; нижнюю, в ней отображаются атрибуты). Нам нужно дать ей имя, для этого в графе с именем кликаем дважды левой кнопкой мыши и даём имя сущности. Чтобы присвоить сущности атрибуты, на панели «ERvinTollbox» жмём на кнопку с изображением стрелки (слева вверху), затем кликаем дважды по интересующей нас сущности, открывается окно «ColumnEditor» (рисунок 3.2).
Рисунок 3.2 - Окно «ColumnEditor»
В открывшемся окне чтобы присвоить сущности атрибут жмём на кнопку «New», вводим название атрибута и жмём «Ок». Далее в маленьком окошке справа «Datatype» выбираем тип данных для атрибута. Если необходимо сделать атрибут ключевым, заходим на вкладку «General» и ставим внизу галку «PrimaryKey». Далее возвращаемся назад и жмём «Ок». Сущность готова.
Для создания связи между сущностями на панели «ERvinTollbox» жмём на кнопку с изображением связи, затем кликаем на одну сущность, потом на другую и связь между ними готова.
Для создания и удаления таблиц можно воспользоваться языком «SQL».
Для создания таблиц на языке «SQL» используется оператор «createtable». После оператора через пробел указывается имя таблиц, далее через пробел в скобках указываются атрибуты таблицы с указанием типа данных и первичных ключей. Представим, как выглядит создание таблиц на «SQL» для нашей БД.
Create table City (
City_idint not null primary key,
City_namevarchar(25), not null)
Create table Object (
Object_idint not null primary key,
Price money not null,
Building_idint not null
Floor int not null,
Flat_numberint not null,
Square int not null,
Balcony boolean not null,
Room_amountint not null,
Stop_idint not null )
Для удаления таблицы на языке «SQL» используется оператор «droptable». После этого оператора через пробел указывается имя таблицы, которую необходимо удалить. Приведём пример, пользуясь таблицами нашей БД.
- DroptableCity
- DroptableObject
Для создания запросов на выборку в языке «SQL» используется оператор «Select». Данный оператор имеет следующий синтаксис: Selectfromwhere. Если необходимо чтобы из результата запроса исключались дубликаты, то после оператора ставится слово «distinct», а если нужны все результаты, то слово «all». Рассмотрим примеры запросов к нашей БД.
Вывести всю информацию о помещениях с ценой менее 500000:
SELECT City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
FROM Transport INNER JOIN ((Transport_stops INNER JOIN ((City INNER JOIN Street ON City.city_id=Street.city_id) INNER JOIN (Owners INNER JOIN (Building INNER JOIN [Object] ON Building.building_id=Object.building_id) ON Owners.owner_id=Building.owner_id) ON Street.street_id=Building.street_id) ON Transport_stops.stop_id=Object.stop_id) INNER JOIN TS ON Transport_stops.stop_id=TS.stop_id) ON Transport.transport_id=TS.transport_id GROUP BY City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
HAVING (((Object.price)<500000));
Вывести всю информацию о помещениях принадлежащих Петрову:
SELECT City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
FROM Transport INNER JOIN ((Transport_stops INNER JOIN ((City INNER JOIN Street ON City.city_id=Street.city_id) INNER JOIN (Owners INNER JOIN (Building INNER JOIN [Object] ON Building.building_id= Object.building_id) ON Owners.owner_id=Building.owner_id) ON Street.street_id=Building.street_id) ON Transport_stops.stop_id = Object.stop_id) INNER JOIN TS ON Transport_stops.stop_id=TS.stop_id) ON Transport.transport_id=TS.transport_id GROUP BY City.city_name, Street.srteen_name, Building.building_number, Object.price, Object.flat_number, Object.floor, Object.square, Object.balcony, Object.room_amount, Owners.owner_name, Transport_stops.stop_name, Transport.transport_type
HAVING (((Owners.owner_name)="Петров"));