База данных софтверной фирмы

Размещено на http://www.allbest.ru/

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

РОССИЙСКИЙ ЭКОНОМИЧЕСКИЙ УНИВЕРСИТЕТ ИМ Г.В. ПЛЕХАНОВА

ТВОРЧЕСКАЯ РАБОТА

НА ТЕМУ: База данных софтверной фирмы

Выполнил:

Студентка 2 курса ИЦЭиИТ

группы 291Д-05ПИ/17

Кещян Софья Георгиевна



ОПИСАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ

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

1. База данных должна содержать таблицы со следующими сведениями о клиентах, оставляющих заказ:

- Фамилия, Имя клиента

- Личная почта для связи

- Контактный телефон

2. База данных должна содержать таблицы содержащие следующие сведения о проектах:

-Короткое название проекта

- Полное официальное название проекта

- Дата начала работы над проектом

- Дата окончания работы над проектом

- Направленность проекта

- Данные заказчика проекта

- Данные о сотрудниках, прикрепленных к проекту

3. База данных должна содержать в себе таблицы следующие сведения о штатных сотрудниках компании:

- Фамилия, Имя сотрудника

- Личная почта сотрудника

- Телефон для связи с сотрудником

- Перечень навыков, которыми обладает сотрудник

- Оклад сотрудника



КОНЦЕПТУАЛЬНАЯ БАЗА ДАННЫХ

РЕЛЯЦИОННАЯ БАЗА ДАННЫХ



SQL-СКРИПТЫ ДЛЯ СОЗДАНИЯ ТАБЛИЦ

Сначала надо создать базу данных. Для этого нужно кликнуть правой кнопкой мыши по папке «базы данных» и выбрать создать новую базу данных, назовем ее «DB_SoftFirm». Дальше представлен SQL-скрипт для создания таблиц в базе данных. useDB_SoftFirm;/* для того, чтобы таблицы создавались именно в БД SoftFirm*/ Создание таблицы CLIENTS, где будут храниться данные о заказчиках.

CREATETABLE [CLIENTS](

[ID_client] [int] NOTNULL,

[FIO_client] [nvarchar](150)NOTNULL,

CONSTRAINT [PK_CLIENTS] PRIMARYKEY (ID_client)

);

Создание таблицы DEVRLOPERS, где будут храниться данные о разработчиках.

CREATETABLE [DEVELOPERS](

[ID_developer] [int] NOTNULL,

[FIO_developer] [nvarchar](150)NOTNULL,

CONSTRAINT [PK_DEVELOPERS] PRIMARYKEY (ID_developer)

);

Создание таблицы EMAIL, где будут храниться mail адреса заказчиков и разработчиков.

CREATETABLE [EMAIL](

[ID_mail] [int] NOTNULL,

[mail] [nvarchar](150)NOTNULL,

[ID_client] [int],

[ID_developer] [int],

CONSTRAINT [PK_EMAIL] PRIMARYKEY (ID_mail),

CONSTRAINT [FK_EMAIL_CLIENTS] FOREIGNKEY([ID_client])/*внешнийключ, связьстаблицейCLIENTS*/

REFERENCES [CLIENTS]([ID_client])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_EMAIL_DEVELOPERS] FOREIGNKEY([ID_developer])/*внешнийключ, связьстаблицейDEVELOPERS*/

REFERENCES [DEVELOPERS]([ID_developer])

ONUPDATECASCADE

ONDELETECASCADE

);

Создание таблицы TEL, где будут храниться номера телефонов заказчиков и разработчиков.

CREATETABLE [TEL](

[ID_tel] [int] NOTNULL,

[numder_tel] [nvarchar](20)NOTNULL,

[ID_client] [int],

[ID_developer] [int],

CONSTRAINT [PK_TEL] PRIMARYKEY (ID_tel),

CONSTRAINT [FK_TEL_CLIENTS] FOREIGNKEY([ID_client])/*внешнийключ, связьстаблицейCLIENTS*/

REFERENCES [CLIENTS]([ID_client])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_TEL_DEVELOPERS] FOREIGNKEY([ID_developer])/*внешнийключ, связьстаблицейDEVELOPERS*/

REFERENCES [DEVELOPERS]([ID_developer])

ONUPDATECASCADE

ONDELETECASCADE

);

Создание таблицы PROGECT, где будет храниться общая информация о проекте.

CREATETABLE [PROGECT](

[ID_progect] [int] NOTNULL,

[shot_name_prog] [nvarchar](150)NOTNULL,

[ID_client] [int] NOTNULL,

[ID_developer] [int] NOTNULL,

CONSTRAINT [PK_PROGECT] PRIMARYKEY (ID_progect),

CONSTRAINT [FK_PROGECT_CLIENTS] FOREIGNKEY([ID_client])/*внешнийключ, связьстаблицейCLIENTS*/

REFERENCES [CLIENTS]([ID_client])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_PROGECT_DEVELOPERS] FOREIGNKEY([ID_developer])/*внешнийключ, связьстаблицейDEVELOPERS*/

REFERENCES [DEVELOPERS]([ID_developer])

ONUPDATECASCADE

ONDELETECASCADE

);



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

CREATETABLE [DEV_ROLE](

[ID_dev_role] [int] NOTNULL,

[role] [nvarchar](50)NOTNULL,

[ID_progect] [int] NOTNULL,

[ID_developer] [int] NOTNULL,

CONSTRAINT [PK_DEV_ROLE] PRIMARYKEY (ID_dev_role),

CONSTRAINT [FK_DEV_ROLE_PROGECT] FOREIGNKEY([ID_progect])/*внешнийключ, связьстаблицейPROGECT*/

REFERENCES [PROGECT]([ID_progect])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_DEV_ROLE_DEVELOPERS] FOREIGNKEY([ID_developer])/*внешнийключ, связьстаблицейDEVELOPERS*/

REFERENCES [DEVELOPERS]([ID_developer])

ONUPDATENOACTION

ONDELETENOACTION

);

Создание таблицы SKILLS, в которой будет храниться информация о умениях разработчиков.

CREATETABLE [SKILLS](

[ID_skill] [int] NOTNULL,

[name_skill] [nvarchar](150)NOTNULL,

CONSTRAINT [PK_SKILLS] PRIMARYKEY (ID_skill));



Создание таблицы DEVELOPERS_PREFERENCES, где будет храниться информация о работе разработчика.

CREATETABLE [DEVELOPER_PREFERENCES](

[ID_dev_pref] [int] NOTNULL,

[ID_developer] [int] NOTNULL,

[ID_skill] [int] NOTNULL,

[price_work] [money] NOTNULL,

CONSTRAINT [PK_DEVELOPER_PREFERENCES] PRIMARYKEY (ID_dev_pref),

CONSTRAINT [FK_DEVELOPER_PREFERENCES_SKILLS] FOREIGNKEY([ID_skill])/*внешнийключ, связьстаблицейSKILLS*/

REFERENCES [SKILLS]([ID_skill])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_DEVELOPER_PREFERENCES_DEVELOPERS] FOREIGNKEY([ID_developer])/*внешнийключ, связьстаблицейDEVELOPERS*/

REFERENCES [DEVELOPERS]([ID_developer])

ONUPDATECASCADE

ONDELETECASCADE

);

Создание таблицы TYPE_PROGECT, где будет храниться информация о типе проекта.

CREATETABLE [TYPE_PROGECT](

[ID_type_prog] [int] NOTNULL,

[type] [nvarchar](50)NOTNULL,

CONSTRAINT [PK_TYPE_PROGECT] PRIMARYKEY (ID_type_prog)

);

Создание таблицы INF_PROGECT, где будет храниться полная информация о проекте.

CREATETABLE [INF_PROGECT](

[ID_inf_prog] [int] NOTNULL,

[ID_progect] [int] NOTNULL,

[start_date] [date] NOTNULL,

[finish_date] [date] NOTNULL,

[full_name_prog] [nvarchar](150)NOTNULL,

[ID_prog_type] [int] NOTNULL,

CONSTRAINT [PK_INF_PROGECT] PRIMARYKEY (ID_inf_prog),

CONSTRAINT [FK_INF_PROGECT_PROGECT] FOREIGNKEY([ID_progect])/*внешнийключ, связьстаблицейPROGECT*/

REFERENCES [PROGECT]([ID_progect])

ONUPDATECASCADE

ONDELETECASCADE,

CONSTRAINT [FK_INF_PROGECT_TYPE_PROGECT] FOREIGNKEY([ID_prog_type])/*внешнийключ, связьстаблицейTYPE_PROGECT*/

REFERENCES [TYPE_PROGECT]([ID_type_prog])

ONUPDATECASCADE

ONDELETECASCADE

);



SQL-СКРИПТЫ ДЛЯ ЗАПОЛНЕНИЕ ТАБЛИЦЫ

Заполнение таблицы CLIENTS

INSERT [CLIENTS]([ID_client], [FIO_client])VALUES (1,N'ИвановИванИванович')

INSERT [CLIENTS]([ID_client], [FIO_client])VALUES (2,N'СидоровМихаилЮрьевич')

INSERT [CLIENTS]([ID_client], [FIO_client])VALUES (3,N'КозловКириллЛеонидович')

Результат

Заполнение таблицы DEVELOPERS

INSERT [DEVELOPERS]([ID_developer], [FIO_developer])VALUES (1,N'ЛосевЛеонидДанилович')

INSERT [DEVELOPERS]([ID_developer], [FIO_developer])VALUES (2,N'КаповДенисИмрадович')



Результат

Заполнение таблицы EMAIL

INSERT [EMAIL]([ID_mail], [ID_client], [mail])VALUES (1, 1,N'ivanovII@mail.ru')

INSERT [EMAIL]([ID_mail], [ID_client], [mail])VALUES (2, 2,N'sidorovMU@mail.ru')

INSERT [EMAIL]([ID_mail], [ID_client], [mail])VALUES (3, 3,N'kozlovKL@mail.ru')

INSERT [EMAIL]([ID_mail], [ID_developer], [mail])VALUES (4, 1,N'losevLD@mail.ru')

INSERT [EMAIL]([ID_mail], [ID_developer], [mail])VALUES (5, 2,N'kapovDI@mail.ru')

Результат



Заполнение таблицы TEL

INSERT [TEL]([ID_tel], [ID_client], [numder_tel])VALUES (1, 1,N'89034567389')

INSERT [TEL]([ID_tel], [ID_client], [numder_tel])VALUES (2, 2,N'89178549023')

INSERT [TEL]([ID_tel], [ID_client], [numder_tel])VALUES (3, 3,N'89854732345')

INSERT [TEL]([ID_tel], [ID_developer], [numder_tel])VALUES (4, 1,N'89264538910')

INSERT [TEL]([ID_tel], [ID_developer], [numder_tel])VALUES (5, 2,N'89873920876')

Результат



Заполнение таблицы DEVELOPER_PREFERENCES

INSERT [DEVELOPER_PREFERENCES]([ID_dev_pref], [ID_developer], [ID_skill], [price_work])VALUES (1, 1, 1,N'5000')

INSERT [DEVELOPER_PREFERENCES]([ID_dev_pref], [ID_developer], [ID_skill], [price_work])VALUES (2, 2, 2,N'6000')

Результат



Заполнение таблицы SKILLS

INSERT [SKILLS]([ID_skill], [name_skill])VALUES (1,N'Знание sql')

INSERT [SKILLS]([ID_skill], [name_skill])VALUES (2,N'Знание C++')

INSERT [SKILLS]([ID_skill], [name_skill])VALUES (3,N'Знание C#')

Результат

Заполнение таблицы PROGECT

INSERT [PROGECT]([ID_progect], [shot_name_prog], [ID_client], [ID_developer])VALUES (1,N'БДО', 1, 1)

INSERT [PROGECT]([ID_progect], [shot_name_prog], [ID_client], [ID_developer])VALUES (2,N'Д2', 2, 2)

INSERT [PROGECT]([ID_progect], [shot_name_prog], [ID_client], [ID_developer])VALUES (3,N'ПО', 3, 2)

Результат



Заполнение таблицы TYPE_PROGECT

INSERT [TYPE_PROGECT]([ID_type_prog], [type])VALUES (1,N'игра')

INSERT [TYPE_PROGECT]([ID_type_prog], [type])VALUES (2,N'программа')

INSERT [TYPE_PROGECT]([ID_type_prog], [type])VALUES (3,N'БД')

INSERT [TYPE_PROGECT]([ID_type_prog], [type])VALUES (4,N'инф. система')

Результат



Заполнение таблицы INF_PROGECT

INSERT [INF_PROGECT]([ID_inf_prog], [ID_progect], [start_date], [finish_date], [full_name_prog], [ID_prog_type])VALUES (1, 1,N'2018.01.02',N'2018.03.15',N'Базаданныхорифлэйм', 1)

INSERT [INF_PROGECT]([ID_inf_prog], [ID_progect], [start_date], [finish_date], [full_name_prog], [ID_prog_type])VALUES (2, 2,N'2018.11.03',N'2019.03.15',N'Дока 2', 2)

INSERT [INF_PROGECT]([ID_inf_prog], [ID_progect], [start_date], [finish_date], [full_name_prog], [ID_prog_type])VALUES (3, 3,N'2018.01.12',N'2018.03.30',N'ПрограммноеОбеспечение', 3)

Результат

Заполнение таблицы DEV_ROLE

INSERT [DEV_ROLE]([ID_dev_role], [role], [ID_developer], [ID_progect])VALUES (1,N'ГЛАВНЫЙПРОГРАММИСТ', 2, 2)

INSERT [DEV_ROLE]([ID_dev_role], [role], [ID_developer], [ID_progect])VALUES (2,N'РАЗРАБОТЧИКПО', 2, 3)

INSERT [DEV_ROLE]([ID_dev_role], [role], [ID_developer], [ID_progect])VALUES (3,N'РАЗРАБОТЧИК', 1, 1)

Результат

ЗАПРОСЫ К БАЗЕ ДАННЫХ

ЗАПРОСЫ С INNERJOIN

1) Выбрать проект, его заказчика, разработчика и полное название:

SELECT PROGECT.ID_progect,

PROGECT.shot_name_prog 'КОРОТКОЕИМЯПРОЕКТА',

INF_PROGECT.full_name_prog 'ПОЛНОЕИМЯПРОЕКТА',

CLIENTS.FIO_client 'КЛИЕНТ',

DEVELOPERS.FIO_developer 'РАЗРАБОТЧИК'

FROM PROGECT INNERJOIN CLIENTS ON PROGECT.ID_client = CLIENTS.ID_client

INNERJOIN DEVELOPERS ON PROGECT.ID_developer = DEVELOPERS.ID_developer

INNERJOIN INF_PROGECT ON PROGECT.ID_progect = INF_PROGECT.ID_inf_prog;

Результат

2) Выбрать разработчика и его скилл

SELECT DEVELOPERS.ID_developer,

DEVELOPERS.FIO_developer 'РАЗРАБОТЧИК',

DEVELOPER_PREFERENCES.ID_skill,

SKILLS.name_skill 'СКИЛЛ'

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

INNERJOIN SKILLS ON DEVELOPERS.ID_developer = SKILLS.ID_skill;

Результат



3) Выбрать проект, дату его начала и тип проекта

SELECT PROGECT.ID_progect,

PROGECT.shot_name_prog 'КОРОТКОЕИМЯПРОЕКТА',

INF_PROGECT.full_name_prog'ПОЛНОЕИМЯПРОЕКТА',

INF_PROGECT.start_date'ДАТАНАЧАЛА',

TYPE_PROGECT.type'ТИППРОЕТА'

FROM PROGECT INNERJOIN INF_PROGECT ON PROGECT.ID_progect = INF_PROGECT.ID_progect

INNERJOIN TYPE_PROGECT ON PROGECT.ID_progect = TYPE_PROGECT.ID_type_prog;

Результат

ЗАПРОСЫ С HAVING

1) Выдать ФИО разработчика, стоимость работы которого больше 1500 рублей.

SELECT DEVELOPERS.FIO_developer,SUM(DEVELOPER_PREFERENCES.price_work)AS mon

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

GROUPBY FIO_developer

HAVINgSUM(DEVELOPER_PREFERENCES.price_work)> 1500;



Результат

2) Выдать ФИО разработчика, стоимость работы которого равно 1500 рублей.

SELECT DEVELOPERS.FIO_developer,SUM(DEVELOPER_PREFERENCES.price_work)AS mon

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

GROUPBY FIO_developer

HAVINgSUM(DEVELOPER_PREFERENCES.price_work)=5000;

Результат

3) Выбрать ФИО разработчиков, чья работа стоит от 1000 до 6000 рублей (не включительно)

SELECT DEVELOPERS.FIO_developer,SUM(DEVELOPER_PREFERENCES.price_work)AS mon

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

GROUPBY FIO_developer

HAVINgSUM(DEVELOPER_PREFERENCES.price_work)> 1000 ANDSUM(DEVELOPER_PREFERENCES.price_work)> 5000;

Результат

ЗАПРОСЫ С ПОДЗАПРОСАМИ

1) Выдать разработчикови их ID.

SELECT DEVELOPERS.ID_developer, DEVELOPERS.FIO_developer

FROM DEVELOPERS

WHERE FIO_developer IN(SELECT FIO_developer

FROM DEVELOPERS);

Результат

2) Выбрать разработчиков, их ID и цену работы.

SELECT DEVELOPERS.ID_developer, DEVELOPERS.FIO_developer, DEVELOPER_PREFERENCES.price_work

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

WHERE price_work IN(SELECT DEVELOPER_PREFERENCES.price_work

FROM DEVELOPER_PREFERENCES );

Результат

3) Выдать ФИО разработчиков, их ID и полное название проекта, над которым они работают.

SELECT DEVELOPERS.ID_developer, DEVELOPERS.FIO_developer, DEVELOPER_PREFERENCES.price_work

FROM DEVELOPERS INNERJOIN DEVELOPER_PREFERENCES ON DEVELOPERS.ID_developer = DEVELOPER_PREFERENCES.ID_dev_pref

WHERE price_work IN(SELECT DEVELOPER_PREFERENCES.price_work

FROM DEVELOPER_PREFERENCES );Результат

Результат

Размещено на Allbest.ru