Система учета данных о пациентах в больнице

Содержание контрольной работы

1. Разработайте структуру таблицы для варианта, указанного преподавателем или методистом. Варианты заданий приведены ниже в разделе 3. Таблица должна содержать не менее 10 полей разного типа (символьного, числового, даты и др.).

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

№ п/п	Имя	Тип	Длинна	Точность	Смысл
1.	ID	INT			Номер по штату
2.	POST	CHAR			Должность по штату
3.	RANK	CHAR			Воинское звание
4.	FAM	CHAR			Фамилия
5.	NAME	CHAR			Имя
6.	OTCH	CHAR			Отчество
7.	DATAR	DATE			Дата рождения
8.	GODPR	INT			Год призыва
9.	LN	CHAR			Личный номер
10.	OKLAD	DECIMAL(9,2)			Оклад

-- Запускаем сервер mysql

mysql -uroot -ppassword

-- Удалить базу данных если она существует, для «чистоты эксперимента»

DROP DATABASE voenchast;

-- Создаем базу данных voenchast с кодировкой utf8

CREATE DATABASE voenchast COLLATE utf8_general_ci;

-- Указываем интерпретатору, что работать будем с базой данных voenchast

USE voenchast;

-- Создаем таблицу и указываем ее структуру

CREATE TABLE upravlenie

(

id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Индекс',

post VARCHAR(31) NOT NULL DEFAULT '' COMMENT 'Должность по штату',

zvan VARCHAR(17) NOT NULL DEFAULT '' COMMENT 'Воинское звание',

fam VARCHAR(15) NOT NULL DEFAULT '' COMMENT 'Фамилия',

name VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'Имя',

otch VARCHAR(15) NOT NULL DEFAULT '' COMMENT 'Отчество',

datar DATE NOT NULL DEFAULT '1000-01-01' COMMENT 'Дата рождения',

godpr INT(4) NOT NULL DEFAULT 1000 COMMENT 'Год призыва',

nomer VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Личный номер',

oklad DECIMAL(9,2) NOT NULL DEFAULT 0 COMMENT 'Оклад',

PRIMARY KEY (id)

) COMMENT 'Структура таблицы базы данных воинской части';

-- Выводим структуру таблицы

DESCRIBE upravlenie;

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

-- Добавляем в таблицу upravlenie новые записи

INSERT INTO upravlenie (post, zvan, fam, name, otch, datar, godpr, nomer, oklad)

VALUES ('Командир батальона','подполковник',’Иванов’,’Геннадий’,’Борисович’,’1972-05-12’,1990,’А-875432’,76120.54),

('Заместитель командира батальоа','майор',’Петров’,’Иван’,’Викторович’,’1975-09-23’,1996,’А-574962’,68500.10),

('Начальник штаба','майор',’Сидоров’,’Анатолий’,’Иванович’,’1976-12-10’,1996,’И-798778’,68500.10),

('Командир роты','капитан',’Сахаров’,’Иван’,’Андреевич’,’1980-02-15’,2001,’И-848787’,65050.25),

('Командир взвода','лейтенант',’Шкляев’,’Юрий’,’Викторович’,’1985-07-27’,2006,’Е-256954’,52000.80);

-- Выводим таблицу upravlenie

SELECT * FROM upravlenie;

4. Приведите ниже три варианта выборок из исходной таблицы (с указанием всех полей таблицы), упорядочив записи в выборках по одному из полей:

а) символьного типа,

б) числового типа или

в) поля типа даты.

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

-- Выводим таблицу upravlenie отсортированную по полую Фамилия

SELECT * FROM upravlenie ORDER BY fam;

-- Выводим таблицу upravlenie отсортированную по полую Оклад

SELECT * FROM upravlenie ORDER BY oklad;

-- Выводим таблицу upravlenie отсортированную по полую Год призыва в порядке убывания

SELECT * FROM upravlenie ORDER BY datar DESC;

5. Дополните исходную таблицу БД еще пятью записями в произвольном порядке. Назовем эту таблицу полной таблицей.

-- Добавляем в таблицу upravlenie новые записи

INSERT INTO upravlenie (post, zvan, fam, name, otch, datar, godpr, nomer, oklad)

VALUES ('Командир взвода','старший лейтенант',’Иванов’,’Дмитрий’,’Иванович’,’1984-02-21’,2004,’У-586545’,52000.80),

('Заместитель командира роты','старший лейтенат',’Дмитриев’,’Игорь’,’Петрович’,’1981-09-14’,2001,’Э-889874’,63800.20),

('Командир роты','майор',’Смолов’,’Алексей’,’Анатольевич’,’1977-04-18’,1997,’С-873547’,66050.10),

('Командир взвода','лейтенант',’Белявцев’,’Михаил’,’Юрьевич’,’1986-08-08’,2007,’Н-654591’,53000.80),

('Командир взвода','старшина',’Кравченко’,’Юрий’,’Николаевич’,’1984-08-14’,2005,’А-213456’,48056.75);

-- Выводим таблицу upravlenie

SELECT * FROM upravlenie;

6. Сформулируйте на языке SQL команду, обеспечивающую выбор записей по одному условию из полной таблицы (по единственному условию в одном поле). Текст команды и результат выборки приведите в виде отдельной таблицы со всеми полями.

-- Выводим записи таблицы upravlenie с фильтром по званию «майор»

SELECT * FROM upravlenie WHERE zvan=’майор’;

7. Повторите выборку по пункту 6 еще три раза, составляя условие отбора записей по полям различного типа. Приведите три таблицы с результатами (выборками) после указания условия отбора и соответствующей SQL-команды.

-- Выводим записи таблицы upravlenie с фильтром по должности «командир взвода»

SELECT * FROM upravlenie WHERE post=’командир взвода’;

-- Выводим записи таблицы upravlenie с фильтром по году призыва «1996»

SELECT * FROM upravlenie WHERE godpr=1996;

-- Выводим записи таблицы upravlenie с фильтром по званиям «майор» и «подполковник»

SELECT * FROM upravlenie WHERE zvan=’майор’ OR zvan=’подполковник’;

8. Сформулируйте словами на русском языке (а затем приведите соответствующие команды на языке SQL) условие выбора записей из полной таблицы:

- по двум условиям для одного поля;

- по двум условиям для двух полей (т.е. по одному условию для каждого из двух полей);

- по любым трем условиям.

Запросы, в которых отсутствуют выбранные записи, недопустимы.

Результат каждой выборки приведите в виде отдельной таблицы со всеми полями после выполнения условия выборки и после команды запроса.

-- Выводим записи таблицы upravlenie с фильтром по званию «майор» и году призыва 1996

SELECT * FROM upravlenie WHERE zvan=’майор’ AND godpr=1996;

-- Выводим записи таблицы upravlenie с фильтром по званиям «лейтенант», «старший лейтенант» и «капитан», а также 2006 году призыва и окладом больше 50000 и меньше 70000

SELECT * FROM upravlenie WHERE zvan IN (’лейтенант’,’старший лейтенант’,’капитан’) AND godpr=2006 AND (oklad>50000 AND oklad<70000);

9. Выполните декомпозицию плоской таблицы на отдельные проекции (таблицы), указав поля для обеспечения связи этих таблиц друг с другом.

В качестве ключа для связи между отдельными таблицами выбираем поле «id».

Список военнослужащих

-- Создаем таблицу военнослужащих из таблицы upravlenie, с помощью запроса

CREATE TABLE spvoen SELECT id, fam, zvan FROM upravlenie;

-- Отобразить созданную таблицу

SELECT * FROM spvoen;

Список должностей

-- Создаем таблицу должностей из таблицы upravlenie, с помощью запроса

CREATE TABLE sppost SELECT id, post FROM upravlenie;

-- Отобразить созданную таблицу

SELECT * FROM sppost;

Список окладов

-- Создаем таблицу окладов из таблицы upravlenie, с помощью запроса

CREATE TABLE spoklad SELECT id, oklad FROM upravlenie;

-- Отобразить созданную таблицу

SELECT * FROM spoklad;

Задание 10.

Команда SHOW TABLES

Реферат

Формат команды:

SHOW TABLES [{FROM | IN} db_name] [LIKE 'pattern']

Команда SHOW TABLES выводит список не временных таблицы базы данных. Аналог команды «mysqlshow db_name».

Результат работы – вывод наименований таблиц базы данных в один столбец таблицы имен.

Пример:

SHOW TABLES;

Если у вас нет никаких привилегий для таблицы, то таблица не будет отображаться.

В сроке команды SHOW TABLES можно задать два дополнительных параметра.

Первый параметр FROM или IN с указанием имени базы данных, что даст серверу MySQL указание вывести список таблиц указанной базы данных.

Например, выведим список таблиц базы данных с именем mysql:

SHOW TABLES FROM mysql;

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

Второй параметр LIKE, с его помощью можно задать фильтр для названий выводимых таблиц.

Например, выведем список таблиц базы данных mysql начинающих с «help»:

SHOW TABLES FROM mysql LIKE 'help%';

В данном случае знак % означает любое количество любых символов.

11. На языке запросов SQL составить:

‑ команды создания базы данных и таблиц (объектом создания является плоская таблица, данная в задании как таблица для декомпозиции, и проекции, полученные при декомпозиции);

‑ запрос, обеспечивающий выборку не менее 4-5 полей из таблиц, полученных после декомпозиции, с заданием собственных имен колонкам вывода. Обязательно использовать критерий отбора данных, обеспечивающий вывод не менее трех записей и сортировку по любому выбранному полю. Перед текстом запроса, выполненного на языке SQL, должно быть текстовое описание запроса на русском языке.

Система учета данных о пациентах в больнице

-- Запускаем сервер mysql

mysql -uroot -ppassword

-- Удалить базу данных если она существует, для «чистоты эксперимента»

DROP DATABASE hospital;

-- Создание базы данных hospital

CREATE DATABASE IF NOT EXISTS hospital;

-- Выбор текущей базы данных hospital

USE hospital;

-- Создание таблицы patient(пациент)

CREATE TABLE IF NOT EXISTS patient

(

tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,

fam CHAR(20) NOT NULL,

name CHAR(10) NOT NULL,

otch CHAR(20) NOT NULL,

PRIMARY KEY (tab_n)

);

-- Добавление записей в таблицу patient

INSERT INTO patient (fam, name, otch) VALUES

(’Иванов’,’Иван’,’Иванович’),

(’Павлов’,’Игорь’,’Иванович’),

(’Петров’,’Петр’,’Петрович’),

(’Гусев’,’Илья’,’Игоревич’),

(’Мотов’,’Павел’,’Петрович’),

(’Иванов’,’Сергей’,’Станиславович’);

-- Создание таблицы state (состояние)

CREATE TABLE IF NOT EXISTS state

(

tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,

INDEX par_ind (tab_n),

FOREIGN KEY (tab_n) REFERENCES patient(tab_n) ON DELETE CASCADE,

tm DECIMAL(3,1) NOT NULL DEFAULT 0,

dg CHAR(15) NOT NULL,

dat DATE NOT NULL DEFAULT '1000-01-01',

PRIMARY KEY (tab_n)

);

-- Добавление записей в таблицу patient

INSERT INTO state (tm, dg, dat) VALUES

(36.8,’Астма’,’1999-12-12’),

(38.5,’Бронхит’,’2000-01-03’),

(36.6,’Астма’,’2000-01-04’),

(37.8,’Бронхит’,’2000-02-18’),

(36.9,’Астма’,’2000-06-06’),

(36.5,’Астма’,’2000-06-06’);

-- Создание таблицы doctor (лечащий врач)

CREATE TABLE IF NOT EXISTS doctor

(

tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,

INDEX par_ind (tab_n),

FOREIGN KEY (tab_n) REFERENCES patient(tab_n) ON DELETE CASCADE,

vrach CHAR(20) NOT NULL

);

-- Добавление записей в таблицу doctor

INSERT INTO doctor (vrach) VALUES

(’Комов А.С.’),

(’Ладов Л.Л.’),

(’Комов А.С.’),

(’Ладов Л.Л.’),

(’Комов А.С.’),

(’Комов А.С.’);

Текстовое описание запроса:

SELECT

patient.tab_n AS ‘ТАБ’,

fam AS ‘ФАМИЛИЯ’,

dat AS ‘ДАТА ПОСТУПЛЕНИЯ’,

tm AS ‘ТЕМПЕРАТУРА’,

dg as ‘ДИАГНОЗ’,

doctor.vrach AS ‘ЛЕЧАЩИЙ ВРАЧ’

FROM patient, state, doctor

WHERE patient.tab_n=state.tab_n and patient.tab_n=doctor.tab_n

ORDER BY patient.tab_n ASC;

Примечание:

FROM patient, state, doctor – используемые таблицы

WHERE patient.tab_n=state.tab_n and patient.tab_n=doctor.tab_n – условия вывода

ORDER BY patient.tab_n ASC – сортировка по полю patient.tab_n