Ирина Эланс

Автор который поможет с любыми образовательными и учебными заданиями

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/подпись/

Оценка _______________

Дата _______________

Курск 2012

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

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

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

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

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

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

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

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

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

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

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

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

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

E-mail – контактный E-mail (например: [email protected], если имеется).

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

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

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

Дата окончания – дата окончания учебного заведения (пример: 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 [Кол сотр]

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

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

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

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 Сотрудники;

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

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

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

FROM Новички;

Запрос 22. Найти сотрудника по профессии.

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

WHERE (((Поступление.Должность)=[введите профессию]));

Запрос 23. Получить фамилии сотрудников, работающих в отделе администрация, их оклад и должность.

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

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

HAVING (((Отделы.Отдел)="Администрация"));

Запрос 24. Получить список сотрудников необходимого отдела.

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

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

HAVING (((Отделы.Отдел)=[Введите отдел]));

Запрос 25. Добавить нового сотрудника в таблицу Сотрудники, указав его фамилию, имя отчество, дату рождения, семейное положение, телефон, E-mail, воинскую обязанность.

INSERT INTO Сотрудники ( Фамилия, [Имя Отчество], [Дата рождения], [Семейное положение], [Телефон(сот)], [E-mail], Военнообязанный )

SELECT [Введите Фамилию] AS Выражение1, [Введите имя] AS Выражение2, [Введите дату рождения] AS Выражение3, [Введите семейное положение] AS Выражение4, [Введите телефон] AS Выражение5, [Введите E-mail] AS Выражение6, [Воинская обязанность] AS Выражение7;

Запрос 26. Вывести список сотрудников с должностью швея.

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

WHERE (((Поступление.Должность)="швея"));

Запрос 27. Вывести список сотрудников с зарплатой меньше 15000 руб.

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

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

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

Запрос 28. Подсчитать зарплату для каждого отдела.

SELECT Отделы.Отдел, Sum(Поступление.Оклад) AS Зарплата

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

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

Запрос 29. Создать таблицу СОТРУДНИКИ.

CREATE TABLE Сотрудники

(

[код сотрудника] counter,

[фамилия] text,

[имя отчество] text,

[дата рождения] datetime,

[семейное положение] text,

[телефон (сот)] integer,

[E-mail] text,

[военнообязанный] text,

CONSTRAINT [индекс1] PRIMARY KEY ([код сотрудника])

);

Запрос 30. Создать таблицу ПРИКАЗЫ.

CREATE TABLE Приказы

(

[код приказа] counter,

[дата] datetime,

[название] text,

CONSTRAINT [индекс2] PRIMARY KEY ([код приказа])

);

Запрос 31. Создать таблицу ОБРАЗОВАНИЕ.

CREATE TABLE Образование

(

[код] counter,

[код сотрудника] integer,

[назв уч завед] text,

[дата окончания] datetime,

[специальность] text,

[квалификация] text,

CONSTRAINT [индекс3] PRIMARY KEY ([код])

);

Запрос 32. Создать таблицу ОТДЕЛЫ.

CREATE TABLE Отделы

(

[код отдела] counter,

[отдел] text,

CONSTRAINT [индекс4] PRIMARY KEY ([код отдела])

);

Запрос 33. Создать таблицу ПОСТУПЛЕНИЕ.

CREATE TABLE Поступление

(

[код сотрудника] integer,

[код отдела] integer,

[код приказа] integer,

[оклад] integer,

[дата поступления] date,

[должность] text,

CONSTRAINT [индекс5] PRIMARY KEY ([код сотрудника], [код отдела], [код приказа])

);

Запрос 34. Создать таблицу РОДСТВЕННИКИ.

CREATE TABLE Родственники

(

[код родственника] counter,

[фамилия] text,

[имя] text,

[отчество] text,

CONSTRAINT [индекс6] PRIMARY KEY ([код родственника])

);

Запрос 35. Создать таблицу СТЕПЕНЬ РОДСТВА.

CREATE TABLE Степеньродства

(

[код сотрудника] integer,

[код родственника] integer,

[степень родства] text,

CONSTRAINT [Индекс7] PRIMARY KEY ([код сотрудника], [код родственника])

);

Запрос 36. Создать связь СОТРУДНИКИ – ОБРАЗОВАНИЕ.

ALTER TABLE Образование

ADD CONSTRAINT Связь1

FOREIGN KEY ([код сотрудника])

REFERENCES Сотрудники

Запрос 37. Создать связь СОТРУДНИКИ – ПОСТУПЛЕНИЕ.

ALTER TABLE Поступление

ADD CONSTRAINT Связь2

FOREIGN KEY ([код сотрудника])

REFERENCES Сотрудники

Запрос 38. Создать связь ОТДЕЛЫ – ПОСТУПЛЕНИЕ.

ALTER TABLE Поступление

ADD CONSTRAINT Связь3

FOREIGN KEY ([код отдела])

REFERENCES Отделы

Запрос 39. Создать связь ПРИКАЗЫ – ПОСТУПЛЕНИЕ.

ALTER TABLE Поступление

ADD CONSTRAINT Связь4

FOREIGN KEY ([код приказа])

REFERENCES Приказы

Запрос 40. Создать связь СОТРУДНИКИ – СТЕПЕНЬ РОДСТВА.

ALTER TABLE Степеньродства

ADD CONSTRAINT Связь5

FOREIGN KEY ([код сотрудника])

REFERENCES Сотрудники

Запрос 41. Создать связь РОДСТВЕННИКИ – СТЕПЕНЬ РОДСТВА.

ALTER TABLE Степеньродства

ADD CONSTRAINT Связь6

FOREIGN KEY ([код родственника])

REFERENCES Родственники

Запрос 42. Удаление всех таблиц.

DROP TABLE Поступление, Приказы, Отделы, Образование, Степень родства, Родственники, Сотрудники

9. Отчеты.

1. Отчет печать оценки на основе запроса 22.

2. Отчет список сотрудников на основе запроса 23.

3. Отчет список экспонатов на основе запроса 26.

4. Отчет список экспонатов автора на основе 28.

Литература

Бабкин Е.А. Базы данных: Практикум. Часть 1. СУБД Microsoft Access. – Курск: КГУ, 2006. – 83 с.
Базы данных: Учебник для высших учебных заведений / Под ред. проф. Хомоненко А. Д. Авторы: Хомоненко А. Д., Цыганков В.М., Мальцев М.Г. -СПб.: КОРОНА принт, 2000.