5. Физическая модель

Физическая модель данных представлена реляционными таблицами, в которых в виде кортежей реляционных отношений хранится информация.

Создание таблицы сведений о сотрудниках:

Create Table Sotrudnik

(TrDogNumb NUMBER,

TabNumb NUMBER PRIMARY KEY NOT NULL,

FIO VARCHAR2(250) UNIQUE,

FOREIGN KEY (TrDogNumb) REFERENCES dogovor,

Pasport VARCHAR2(250) UNIQUE,

Pol CHAR(15) CHECK (Pol IN ('мужской','женский')) )

Создание таблицы сведений о трудовых договорах:

CREATE Table dogovor

(

TrDogNumb NUMBER PRIMARY KEY NOT NULL,

INNrabotodat NUMBER,

Dolgnost VARCHAR2(250) UNIQUE,

DataDogov DATE,

NachaloDeist DATE,

OkonchDeist DATE,

Oklad NUMBER,

Otdel VARCHAR2(250),

IsputSrok INT,

SrokWork INT,

FOREIGN KEY (INNrabotodat) REFERENCES Rabotodat

);

Создание таблицы сведений об увольнении:

Create Table Prinyatie

(TabNumb NUMBER,

FOREIGN KEY (TabNumb) REFERENCES Sotrudnik,

PricasNumb NUMBER PRIMARY KEY NOT NULL,

DataPrikasa DATE

)

Создание таблицы сведений о повышении квалификации:

Create Table PovushKvalif

(TabNumb NUMBER,

FOREIGN KEY (TabNumb) REFERENCES Sotrudnik,

NachaloObuchenia date,

OkonchObuchenia DATE,

VidPovushKvalifikaci VARCHAR2(250),

NaimenovanieObrazUchrezhd VARCHAR2(250),

Osnovanie VARCHAR2(250),

FOREIGN KEY (Osnovanie) REFERENCES OBRAZOVANIE,

DocumentPovush VARCHAR2(250) UNIQUE

)

Создание таблицы сведений о работодателе:

Create Table Rabotodat2Laba

(INNrabotodat NUMBER PRIMARY KEY NOT NULL UNIQUE,

NameFirm VARCHAR2(250),

Adres VARCHAR2(250),

Telefon VARCHAR2(250),

FIOrukovod VARCHAR2(250))

Создание таблицы сведений о принятии на работу:

Create Table Prinyatie

(TabNumb NUMBER,

FOREIGN KEY (TabNumb) REFERENCES Sotrudnik,

PricasNumb NUMBER PRIMARY KEY NOT NULL,

DataPrikasa DATE

)

Создание таблицы сведений о перемещении в должности

Create Table Peremeshenie

(TabNumb NUMBER,

FOREIGN KEY (TabNumb) REFERENCES Sotrudnik,

PrikasNumberPerem NUMBER PRIMARY KEY NOT NULL,

DataPrikasaPeremesh DATE,

Dolgnost varchar2(250) NOT NULL,

FOREIGN KEY (Dolgnost) REFERENCES dogovor,

NovoeMesto varchar2(250) NOT NULL

)

Создание таблицы об образовании:

Create Table Obrazovanie

(TabNumb NUMBER,

FOREIGN KEY (TabNumb) REFERENCES Sotrudnik,

KnowForengLang VARCHAR2(250),

NameVUZ VARCHAR2(250),

Kvalifikacia VARCHAR2(250),

Professia VARCHAR2(250),

DocAboutStudy VARCHAR2(250) UNIQUE,

StagWorkObshi integer,

StagWorkNepreruv integer

)

Создание таблицы о воинском учете:

CREATE Table VoinskUchet

(

TabNumb NUMBER UNIQUE,

VoinskoeZvanie VARCHAR2(250),

KodovoeOboznVUS NUMBER,

KategoriaZapasa VARCHAR2(250),

KategoriaGodnosti CHAR(15) CHECK (KategoriaGodnosti IN ('A',

'B', 'C')),

NaimenKommissPoZhitelstv VARCHAR2(250),

FOREIGN KEY (TabNumb) REFERENCES sotrudnik,

VoinskiiUchet CHAR(15) CHECK (VoinskiiUchet IN ('общий',

'специальный') ),

OtmetkaSnyatiaUcheta CHAR(15) CHECK (OtmetkaSnyatiaUcheta IN ('снят с воинского учета',

'не снят с воинского учета') )

);

6. Проектирование и реализация информационной системы

6.1 Описание средств, использованных при реализации

1 Создание базы данных

Для создания базы данных, сначала создаются таблицы с помощью средств, предлагаемых СУБД Oracle 10g :

• Создание таблицы помощью графического инструментального средства конфигурирования Oracle Database Configuration Assistant (DBCA);

• Создание таблицы шаблонов баз данных (database templates);

• Создание таблицы путём ввода данных.

При создании таблиц был использован 1-й вариант, как наиболее удобный из всех предлагаемых. Сначала создаётся каркас таблицы, отмечаются ключевые поля, устанавливаются типы данных для каждого поля, а уже потом таблицы заполняются данными.

6.2 Тексты SQL-запросов и результаты их выполнения

Ниже приведены все SQL-запросы, использованные в процессе создания базы данных.

Запрос 1. Вывести фамилию сотрудника по номеру паспорта

select FIO, Pasport

FROM SOTRUDNIK

WHERE Pasport='2007 890678'

FIO	PASPORT
Карпов Олег Геннадьевич	2007 890678

Запрос 2. Вывести сотрудников, у которых начало действия договора расположено между двумя заданными датами

SELECT *

FROM "DOGOVOR"

WHERE NACHALODEIST>='21/03/2001' AND NACHALODEIST<='31/12/2004'

Запрос 3. Вывести сотрудников, оклад которых меньше либо равен 9000 рублей. Результат представлен на рисунке 2.

select SOTRUDNIK.FIO as ФИО, DOGOVOR.DOLGNOST as Должность, DOGOVOR.OKLAD as Оклад

FROM SOTRUDNIK, DOGOVOR

WHERE OKLAD<=9000 AND DOGOVOR.TRDOGNUMB = SOTRUDNIK.TRDOGNUMB

Запрос 4. Поиск окончивших срок действия договоров.

select SOTRUDNIK.TABNUMB, DOGOVOR.TRDOGNUMB, DOGOVOR.DOLGNOST, DOGOVOR.OKONCHDEIST

from DOGOVOR, SOTRUDNIK

WHERE sysdate >= DOGOVOR.OKONCHDEIST AND DOGOVOR.TRDOGNUMB = SOTRUDNIK.TRDOGNUMB

Запрос 5. Ищем запланированные повышения квалификации

select ts.FIO as ФИО, td.DOLGNOST as Должность, tp.nachaloobuchenia as

Начало_обучения,

tp.okonchobuchenia as Окончание_обучения,

tp.NAIMENOVANIEOBRAZUCHREZHD as Образовательное_учреждение

FROM SOTRUDNIK ts, DOGOVOR td, POVUSHKVALIF tp

WHERE sysdate<tp.nachaloobuchenia AND td.TRDOGNUMB = ts.TRDOGNUMB AND ts.TABNUMB=tp.tabnumb

Запрос 6. Поиск изученных сотрудником иностранных языков по номеру договора сотрудника

select s.FIO as ФИО, s.Pasport as Паспорт, o.knowforenglang

FROM SOTRUDNIK s, DOGOVOR d, OBRAZOVANIE o

WHERE s.trdognumb='10000' and s.trdognumb=d.trdognumb and s.tabnumb=o.tabnumb

Запрос 7. Поиск бухгалтеров и программистов с окладом от 10000 до 50000

SELECT s.FIO AS ФИО, d.DOLGNOST AS Должность, d.OKLAD AS Оклад

FROM dogovor d, sotrudnik s

WHERE (d.dolgnost='Программист' OR d.dolgnost='Бухгалтер') AND d.trdognumb=s.trdognumb

AND d.oklad BETWEEN 10000 AND 50000

Запрос 8. Поиск бухгалтеров или программистов с окладом от 10000 до 50000

SELECT s.FIO AS ФИО, d.DOLGNOST AS Должность, d.OKLAD AS Оклад

FROM dogovor d, sotrudnik s

WHERE d.dolgnost='Программист' OR d.dolgnost='Бухгалтер' AND d.trdognumb=s.trdognumb

AND d.oklad BETWEEN 10000 AND 50000

Запрос 9. Поиск сотрудников мужского пола в возрасте до 28 лет

select *

from sotrudnik

where months_between(sysdate, SOTRUDNIK.DATA_ROZHD)/12<=28 AND pol='мужской'

Запрос 10. Найдем всех слесарей и токарей

SELECT s.fio, d.dolgnost

FROM sotrudnik s, dogovor d

WHERE d.dolgnost IN ('Слесарь', 'Токарь') AND s.trdognumb=d.trdognumb

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

SELECT s.fio, d.dolgnost, u.osnovanie

FROM sotrudnik s, dogovor d, uvolnenie u

WHERE u.osnovanie NOT IN ('истечение срока действия договора', 'по собственному желанию')

AND s.trdognumb=d.trdognumb AND u.tabnumb=s.tabnumb

Запрос 12. Вывести всех заместителей какой-либо должности

SELECT s.fio, d.dolgnost

FROM sotrudnik s, dogovor d

WHERE d.dolgnost LIKE 'Заместитель %'

AND s.trdognumb=d.trdognumb

Запрос 13. Вывести сотрудников мужского пола в возрасте до 28 лет и отсортировать по дате рождения

SELECT *

FROM sotrudnik

WHERE months_between(sysdate, data_rozhd)/12<28 AND pol='мужской'

ORDER BY data_rozhd

Запрос 14. Вывести средний оклад по всем работникам

SELECT AVG (oklad) AS Средний_оклад

FROM dogovor d

Запрос 15. Вывести количество сотрудников с окладом больше 30000

SELECT COUNT (*)

FROM dogovor d, sotrudnik s

WHERE 30000<d.oklad AND d.trdognumb=s.trdognumb

Запрос 16. Вывести список сотрудников, повышение квалификации которых пройдет между 12.01.2011 и 20.05.2011

select ts.FIO as ФИО, td.DOLGNOST as Должность, tp.nachaloobuchenia as Начало_обучения,

tp.okonchobuchenia as Окончание_обучения,

tp.NAIMENOVANIEOBRAZUCHREZHD as Образовательное_учреждение

FROM SOTRUDNIK ts, DOGOVOR td, POVUSHKVALIF tp

WHERE tp.nachaloobuchenia BETWEEN '12.01.2011' AND '20.05.2011'

AND td.TRDOGNUMB = ts.TRDOGNUMB AND ts.TABNUMB=tp.tabnumb

AND tp.okonchobuchenia BETWEEN '12.01.2011' AND '20.05.2011'

Запрос 17. Найти сотрудников, оклад которых как минимум в два раза меньше среднего оклада по предприятию

SELECT s.fio, d.oklad, d.dolgnost

FROM sotrudnik s, dogovor d

WHERE d.oklad*2<(SELECT AVG (oklad)

FROM dogovor)

AND s.trdognumb=d.trdognumb

Запрос 18. Выведем по отделам суммарные оклады, которые составляют менее 100000 рублей

Запрос 19. Выведем сотрудников, подлежащих призыву

Запрос 20. Вывести сотрудников пенсионного возраста

Запрос 21. Вывести всех сотрудников женского пола, работающих механиками в любом цехе.

Запрос 22. Найти сотрудников, оклад которых равен минимальному окладу по отделам

Запрос 23. Получить наименование отдела сотрудников, которым установлен минимальный оклад

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

SELECT d.OTDEL, S.FIO, D.OTDEL, S.TABNUMB, D.DOLGNOST

FROM DOGOVOR D INNER JOIN SOTRUDNIK S ON S.TRDOGNUMB=D.TRDOGNUMB

WHERE OTDEL IN (SELECT OTDEL

FROM DOGOVOR WHERE DATADOGOV='01.12.2009')

AND D.DOLGNOST IN (SELECT DOLGNOST

FROM DOGOVOR

WHERE DATADOGOV='01.12.2009')

AND DATADOGOV<>'01.12.2009'

Запрос 25. Найти отделы, где есть сотрудники возрастом до 23 лет.

Запрос 26. Найти отделы, на которых оклад сотрудника больше оклада всех сотрудников отдела ИТ

Запрос 27. Вывести сотрудников, у которых должность и отдел одновременно совпадают с должностью и отделом сотрудников, оклад которых больше 50000.

Запрос 28. Ввод новой информации о сотруднике

Запрос 29. Изменить оклад сотрудника под номером трудового договора 10000 на оклад сотрудника под номером 10001

update dogovor set (oklad)=

(select oklad

from dogovor

where trdognumb=10000)

where trdognumb=10001;

Запрос 30. Вывести попарно ФИО сотрудников, родившихся в один день