Разработка базы данных "ОТДЕЛ КАДРОВ"

МИНИСТЕРСТВО ОБРАЗОВАНИЯ  И НАУКИ РФ

Государственное образовательное  учреждение

высшего профессионального  образования

«Курский государственный  университет»

Факультет информатики и  вычислительной техники

Кафедра программного обеспечения  и администрирования 
информационных систем

 

Контрольная работа

по дисциплине

"БАЗЫ ДАННЫХ И СУБД"

 

Разработка  базы данных "ОТДЕЛ КАДРОВ"

 

 

 

 

Студент группы______________    _______________  (________________)

/номер группы/   /подпись/           / Ф.И.О./

 

Преподаватель: профессор кафедры ПОиАИС ________________Бабкин Е.А.

/подпись/  

Оценка _______________

Дата _______________

 

Курск 2012

 

1. Контрольное задание

Разработать базу данных «Отдел кадров».

2. Описание предметной  области.

В базе данных Отдел кадров должна храниться и обрабатываться информация о сотрудниках, работающих на преприятии. Данная база данных позволяет просмотр и изменение данных обо всех сотрудниках.

Инспектор отдела кадров ведет  учет анкетных данных сотрудников, отделов  предприятия. Он регистрирует и дополняет  происходящие изменения для каждого  сотрудника: переход в другой отдел, изменение профессии, получение премий, получение образования, состав родственников.

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

Имена информационных объектов предметной области и их краткие характеристики:

Код сотрудника – целое уникальное число, присваивается программой автоматически.

Фамилия – фамилия сотрудника (например: Петров).

Имя Отчество – имя и отчество сотрудника (например: Иван Петрович).

Дата рождения – дата рождения сотрудника.

Семейное положение – семейное положение (женат, не женат, замужем, не замужем)

Телефон – сотовый номер сотрудника (например: 9517459934, если имеется).

E-mail – контактный E-mail (например: nata__86@rambler.ru, если имеется).

Военнообязанный – воинская обязанность сотрудника.

Код – код образования, целое уникальное число, присваиваемое автоматически.

Название учебного заведения – название учебного заведения, которое окончил сотрудник (например: КГУ).

Дата окончания – дата окончания учебного заведения (пример: 12.07.1989).

Специальность– специальность, полученная в учебном заведении (пример: инженер)

Квалификация  – квалификация (пример: специалист).

Код отдела – целое уникальное число, присваиваемое автоматически.

Отдел – название отдела (пример: бухгалтерия).

Код приказа – целое уникальное число, присваивемое программой автоматически.

Дата – дата составления приказа.

Название приказа – название приказа (например: приказ об увольнении)

Оклад – ежемесячная денежная сумма, получаемая сотрудником за выполняемую работу (например: 18 000,00 р.).

Дата поступления – дата поступления сотрудника на работу (пример: 23.05.2000).

Должность – должность, занимаемая сотрудником на данном предприятии (например: швея).

Код родственника – целое уникальное число, присваиваемое программой автоматически.

Фамилия – фамилия родственника.

Имя – имя родственника.

Отчество – отчество родственника.

Степень родства – принадлежность родственника к сотруднику (например: дочь).

 

3. Разработка инфологической  модели

 

Первый шаг - выделение сущностей и связей между ними.

Выделим следующие сущности: СОТРУДНИКИ, ОТДЕЛЫ, РОДСТВЕННИКИ, ОБРАЗОВАНИЕ, ПРИКАЗЫ.

Выделим связи между сущностями.

Сущности  СОТРУДНИКИ И ОБРАЗОВАНИЕ  связаны бинарной связью ПОЛУЧИЛ.

Сущности СОТРУДНИКИ И РОДСТВЕННИКИ связаны бинарной связью СТЕПЕНЬ РОДСТВА.

Сущности СОТРУДНИКИ, ОТДЕЛЫ И ПРИКАЗЫ связаны тернарной связью ПОСТУПЛЕНИЕ.

Атрибуты  сущностей и связей.

Атрибуты сущности СОТРУДНИКИ: Код сотрудника, фамилия, имя отчество, дата рождения, телефон(сот), E-mail, семейное положение, воинская обязанность.

Атрибуты сущности ПРИКАЗЫ: Код приказа, дата, название.

Атрибуты сущности РОДСТВЕННИКИ: код родственника, фамилия, имя, отчество.

Атрибуты сущности ОТДЕЛЫ: код отдела, отдел.

Атрибуты сущности ОБРАЗОВАНИЕ: код, название уч. заведения, дата окончания, специальность, квалификация.

Атрибуты связи ПОСТУПЛЕНИЕ: оклад, дата поступления, должность.

Атрибуты связи СТЕПЕНЬ РОДСТВА: степень родства.

У связи ПОЛУЧИЛ нет атрибутов.

 

Второй шаг - построение диаграммы ER-типа с учетом всех сущностей и связей между ними.

Связь ПОЛУЧИЛ является бинарной связью типа М:1, так как один сотрудник может получить несколько образований. Сущность СОТРУДНИКИ имеет обязательный класс принадлежности, так как сотрудник не может быть принят на работу без образования. Сущность ОБРАЗОВАНИЕ имеет обязательный класс принадлежности, так как образование не может быть никем не получено.

Связь СТЕПЕНЬ РОДСТВА является связью типа М:М поскольку много сотрудников может иметь много родственников. Сущности СОТРУДНИКИ имеют необязательный класс принадлежности, так как не у каждого сотрудника есть родственники . Сущность РОДСТВЕННИКИ имеет обязательный класс принадлежности, так как каждый родственник относится к определенному сотруднику.

Связь ПОСТУПЛЕНИЕ является связью типа М:М поскольку много сотрудников работают во многих отделах и на них составляются многочисленные приказы. Сущности СОТРУДНИКИ и ОТДЕЛЫ имеют обязательный класс принадлежности, так как сотрудники не могут не работать ни в одном отделе. Сущности СОТРУДНИКИ и ПРИКАЗЫ имеют обязательный класс принадлежность, так как на каждого сотрудника имеется хотя бы один приказ о зачислении на работу. Сущности ОТДЕЛЫ и ПРИКАЗЫ имеют обязательный класс принадлежности, так как при зачислении в отдел пишется приказ.

 

4. Преобразование  инфологической модели в реляционную  модель данных.

Так как степень связи СТЕПЕНЬ РОДСТВА между сущностями РОДСТВЕННИКИ и СОТРУДНИКИ М:М, то (правило 2) независимо от класса принадлежности сущностей формируются три отношения. Два отношения соответствуют связываемым сущностям (СОТРУДНИКИ и РОДСТВЕННИКИ) и их ключи являются первичными ключами этих отношений. Третье (СТЕПЕНЬ РОДСТВА) является отношением связи между первыми двумя, а его ключ Код сотрудника – Код родственника объединяет ключевые атрибуты связываемых отношений. В отношении СТЕПЕНЬ РОДСТВА составной ключ будет состоять из атрибутов код сотрудника и код родственника.

 _1/О М/О

 

Связь ПОЛУЧИЛ является бинарной (число связываемых сущностей 2), степень связи - 1:М, то (правило 6) достаточно формирования 2-х отношений, ключ сущности СОТРУДНИКИ добавляется к сущности ОБРАЗОВАНИЕ.

                                   ^{1/Н                       M/О} 

 

 

 

Так как степень связи ПОСТУПЛЕНИЕ между сущностями СОТРУДНИКИ, ПРИКАЗЫ и ОТДЕЛЫ М:М, то (правило 2) независимо от класса принадлежности сущностей формируются четыре отношения. Три отношения соответствуют связываемым сущностям (СОТРУДНИКИ, ПРИКАЗЫ и ОТДЕЛЫ) и их ключи являются первичными ключами этих отношений. Четвертое (ПОСТУПЛЕНИЕ) является отношением связи между первыми тремя, а его ключ объединяет ключевые атрибуты связываемых отношений. В отношении ПОСТУПЛЕНИЕ  составной ключ будет состоять из атрибутов код сотрудника, код приказа и код отдела.

 

Рассмотрим предварительную  схему данных (рис. 1).

Рис. 1 – предварительная схема данных

 

5. Нормализация  реляционной модели данных.

Сотрудники  БКНФ:

 

Родственники БКНФ:

 

 

 

 

 

 

 

Приказы БКНФ:

 

 

 

 

 

 

 

Образование БКНФ:

 

 

 

 

 

 

 

 

 

 

 

 

Отделы БКНФ:

 

6. Схема данных (реализация разработанной модели в СУБД Microsoft

Access)

Схема данных в БКНФ.

Рис. 2 – нормализованная схема данных

 

7. Таблицы.

1.Таблица сотрудники.

2. Таблица образование.

3. Таблица отделы.

 

4. Таблица приказы.

 

 

5. Таблица родственники.

6. Таблица поступление.

7. Таблица степень родства.

 

 

8. Формы.

1. Форма заставка

   

 

 

2. Форма зарплата.

3. Форма образование.

 

4. Форма отделы.

5. Форма поиск сотрудников.

 

6. Форма поступление на работу.

 

7. Форма приказы.

8. Форма  сотрудники.

 

 

9. Форма профессии.

10. Форма родственники.

 

SELECT [Фамилия]

FROM Сотрудники

UNION SELECT [Фамилия]

FROM Родственники;

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

SELECT Сотрудники.Фамилия, Отделы.Отдел

FROM Сотрудники, Отделы;

Запрос 8. Получить все возможные сочетания сотрудников и должностей – все возможные варианты занимаемых должностей. Вывести фамилии, имена и отчества сотрудников и должности.

SELECT Сотрудники.Фамилия, Сотрудники.[Имя Отчество], Поступление.Должность

FROM Сотрудники, Поступление;

Запрос 9. Получить сотрудников и названия отделов, в которых они работают. Вывести фамилии, имена и отчества сотрудников и названия отделов.

SELECT Сотрудники.Фамилия, Сотрудники.[Имя Отчество], Отделы.Отдел

FROM Сотрудники INNER JOIN (Отделы INNER JOIN Поступление ON Отделы.[Код отдела] = Поступление.[Код отдела]) ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника];

Запрос 10. Получить всех сотрудников, в том числе и тех, которые не занимают никакой должности. Вывести фамилии и имена сотрудников и название должности.

SELECT Поступление.Должность, Сотрудники.[Имя Отчество], Сотрудники.Фамилия

FROM Сотрудники RIGHT JOIN Поступление  ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника];

Запрос 11. Получить фамилии сотрудников, даже тех, на которых не писали приказы. Вывести фамилии и имена сотрудников и название приказа.

SELECT Сотрудники.Фамилия, приказы.[Название приказа]

FROM приказы LEFT JOIN (Сотрудники RIGHT JOIN Поступление ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника]) ON приказы.[Код приказа] = Поступление.[Код приказа];

Запрос 12. Получить фамилии, имена и отчества сотрудников, причем имена атрибутов переименовать.

SELECT Сотрудники.Фамилия AS [Фамилия сотрудника], Сотрудники.[Имя Отчество]

FROM Сотрудники;

Запрос 13. Для каждого  сотрудника определить количество проработанных на сегодняшнюю дату дней.

SELECT Сотрудники.Фамилия, Сотрудники.[Имя Отчество], DateDiff("d",[Дата поступления],Now()) AS [Кол дней]

FROM Сотрудники INNER JOIN Поступление  ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника];

Запрос 14. Пусть  требуется вывести название отдела и количество сотрудников, работающих в нем.

SELECT Отделы.Отдел, Count(Сотрудники.[Код сотрудника]) AS [Кол сотр]

FROM Сотрудники INNER JOIN (Отделы INNER JOIN Поступление ON Отделы.[Код отдела] = Поступление.[Код отдела]) ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника]

GROUP BY Отделы.Отдел;

Запрос 15. Пусть  требуется вывести названия отделов, в которых работает меньше 10 человек.

SELECT Отделы.Отдел

FROM Сотрудники INNER JOIN (Отделы INNER JOIN Поступление ON Отделы.[Код отдела] = Поступление.[Код отдела]) ON Сотрудники.[Код сотрудника] = Поступление.[Код сотрудника]

GROUP BY Отделы.Отдел

HAVING (((Count(Сотрудники.[Код сотрудника]))<10));

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

SELECT Count(Сотрудники.[Код сотрудника]) AS [Кол Петровых]

FROM Сотрудники

WHERE (((Сотрудники.Фамилия)="Петрова"));

Запрос 17. Создать  таблицу Новички  с полями код новичка, фамилия, имя отчество, дата рождения, профессия, дата поступления. Внести в список сотрудников тех новичков, которые проработали больше 30 дней.

INSERT INTO Сотрудники ( Фамилия, [Имя Отчество], [Дата рождения] )

SELECT Новички.Фамилия, Новички.[Имя Отчество], Новички.[Дата рождения]

FROM Новички

WHERE (((DateDiff("d",[Дата поступления],Now()))>"30"));

Запрос 18. В таблицу Новички добавить одну запись с параметрами: номер 23, фамилия Соколовская, имя Галина, отчество Григорьевна, дата рождения 12.10.1985, дата поступления 16.05.2012.

INSERT INTO Новички

VALUES (23, "Соколовская", "Галина Григорьевна", #12/10/1985#, #16/05/2012#);

Запрос 19. Повысить зарплату на 1000 руб. сотрудникам с окладом 8000 руб.

UPDATE Поступление SET Поступление.Оклад = [Поступление].[Оклад]+1000

WHERE (((Поступление.Оклад)=8000));

Запрос 20. Удалить  из таблицы сотрудники всех сотрудников с датой рождения больше 1.01.1985.

DELETE Сотрудники.[Дата рождения]

FROM Сотрудники

WHERE (((Сотрудники.[Дата рождения])>#1/1/1985#));

Запрос 21а.  Удалить  все записи из таблицы Сотрудники.

DELETE *

FROM Сотрудники;