Концептуальная модель базы данных

 

Реляционная модель базы данных

Заказчик (код заказчика, название, номер лицензии, ИНН, телефон, адрес)

Сотрудник (код сотрудника, ФИО, должность)

Должность (код должности, наименование должности)

Строительная фирма (ИНН, номер лицензии, название, адрес)

Услуга (код услуги, название услуги, описание, стоимость)

Название услуги (код названия, наименование)

Акт о приеме-сдаче (номер акта, дата составления, продолжительность, конечная стоимость)

Сведения о состоянии объекта (код сведений, инвентарный номер, номер паспорта, заводской номер, объект ОС, восстановительная стоимость, срок эксплуатации)

Объект ОС (код объекта, наименование объекта)

Срок эксплуатации (код срока, наименование срока)

Сведения о затратах (код затрат, объект ОС, вид работы, затраты на демонтаж, стоимость ремонта по договору, стоимость модернизации по договору, фактическая стоимость ремонта, фактическая стоимость модернизации, фактические затраты по транспортировке оборудования, примечание)

 

 

Модель данных

На рис.2. показана модель данных.

 

 

Рис.2. Модель данных

 

Создание таблиц

use master;

go

if exists(select * from sys.databases where name='build2')

begin

drop database build2;

end

go

create database build2;

go

use build2;

go

 

 

create table zakazchik(kod_zakazchika int primary key, name varchar(100),nomer_licens int, INN int, phone varchar(15), adres varchar(50));

 

create table job (kod_job int primary key, job_name varchar(30));

 

create table worker (kod_worker int primary key, worker_name varchar (50), job int references job (kod_job));

create table build_org (INN int primary key, nomer_licens int, name varchar (20),phone varchar(15), adres varchar(100));

 

create table service_name (service_name_kod int primary key, service_name_name varchar(50));

 

create table service (service_kod int primary key, servce_name int references service_name(service_name_kod), service_info varchar(5000), service_price varchar(10));

 

create table os_object(os_object_kod int primary key, os_object_name varchar(50));

 

create table info_os_object (info_kod int primary key, inventory_number varchar(15), passport_number varchar(15), produce_number varchar(15), os_object_name int references os_object(os_object_kod), fact_usage int);

 

create table money_lost (kod_zatrat int primary key, os_object int references os_object(os_object_kod),work_type int references service(service_kod), demolit_price varchar(10), repair_act_price varchar(10), mod_act_price varchar(10), fact_rep_price varchar(10), fact_mod_price varchar(10), fact_transp_price varchar(10), adittion varchar(5000));

 

create table act_PS(act_number int primary key,ispolnit int references build_org(INN),response_person int references worker(kod_worker),zakazchik int references zakazchik(kod_zakazchika), sign_date date, finish_date date, end_price int references money_lost(kod_zatrat),result int references info_os_object(info_kod));

 

 

Структура таблиц и данные

Покажем структуру таблиц в базе данных build2 (рис.3).

 

Рис. 3. Структура таблиц

 

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

Должности:

insert into dbo.job values

(001,'генеральный директор'),

(002, 'заместитель директора'),

(003,'главный архитектор'),

(004,'архитектор'),

(005,'прораб'),

(006,'главный бухгалтер'),

(007,'бухгалтер');

 

 

Обьекты ОС:

insert into os_object values

(101,'цех'),

(102,'склад'),

(103,'лаборатория'),

(104,'гараж'),

(105,'примыкающее сооружение'),

(106,'туннель'),

(107,'автодорога'),

(108,'электросеть'),

(109,'теплосеть'),

(110,'цеховое оборудование');

 

 

Название услуг:

insert into dbo.service_name values

(201,'ремонт'),

(202,'модернизация'),

(203,'замена'),

(204,'реконструкция'),

(205,'демонтаж'),

(206,'обсуживание'),

(207,'строительство'),

(208,'дизан'),

(209,'проэктирование'),

(210,'продажа');

 

Описание услуг:

insert into dbo.service values

(1001,201,'перекрытий','50000'),

(1002,201,'внутренняя отделка','30000'),

(1003,201,'наружняя отделка','60000'),

(1004,202,'перекрытий','51000'),

(1005,202,'несущих конструкций','85000'),

(1006,203,'электропроводки','22000'),

(1007,203,'сантехники','12000'),

(1008,204,'памятника архитектуры','120000'),

(1009,205,'здания','600000'),

(1010,205,'подсобных помещений','200000'),

(1011,206,'построенных обьектов','20000'),

(1012,206,'теплосетей','25000'),

(1013,207,'бассейнн','400000'),

(1014,207,'автодорога','1000000'),

(1015,208,'зданий','50000'),

(1016,209,'парковок','55000'),

(1017,209,'телекоммуникационных сетей','110000');

 

 

 

Описание Обьектов ОС:

insert into dbo.info_os_object values

(2001,'987986214','32654984','32165498479',101,5),

(2002,'679833568','13134616','13576757365',101,58),

(2003,'342311752','3223126373','32156613',102,37),

(2004,'247245245','1345136547','243568456',102,101),

(2005,'835624634','13477623525','3684635',103,76),

(2006,'315646560','354343543','498749876',103,3),

(2007,'13624789','23456234','5745867',104,43),

(2008,'858763987','956948076','3945689',104,25),

(2009,'898450234','68583835625','23452646',105,6),

(2010,'3452456','674674','23434753',105,1),

(2011,'42562457','425756853','3245624',106,141),

(2012,'425624623','547456834','2133412345',106,59),

(2013,'3422534','56760068','567850',107,6),

(2014,'2456245','2467563','23246',107,31),

(2015,'52624526','24573567','3426245',108,5),

(2016,'57358635','24562467','78464568',108,17),

(2017,'42564357536','245735673','246245624',109,76),

(2018,'487689','688765446','9879871598',109,46),

(2019,'65465','87981','21498',110,9),

(2020,'65465','65465','289496',110,15);

 

 

 

Информация о заказчиках:

insert into dbo.zakazchik values

(3001,'ЦПКиО','675437','345643','2549431','центр 7'),

(3002,'Сибавто','531343','685673','2311534','ватная 37'),

(3003,'ТЦ АУРА','5638224','7845675','3543265','военная 6'),

(3004,'ФГБУ Новосибирская МВЛ','67356','246356824','2236742','серебренниковская 5'),

(3005,'ЗАО ЗапСибВет','4356734','425624','57245','фабричная 2'),

(3006,'ИП Андреев','84583','243527','34635767','прямая 297');

 

 

 

Сотрудники:

insert into dbo.worker values

(4001,'Иванов Иван иванович',1),

(4002,'петров петр петрович',2),

(4003,'поликарпов поликарп поликарпович',3),

(4004,'анатольев анатолий анатольевич',4),

(4005,'андреев андрей андреевич',4),

(4006,'константинов константин константиновч',5),

(4007,'борисов борис борисович',6),

(4008,'максимов максим максимович',7),

(4009,'васильев василий васильевич',7);

 

 

Данные о сторителной фирме:

insert into dbo.build_org values

(86579987,8546534,'Строим Нормально','2556677','марс 8');

 

Данные о затратах:

insert into dbo.money_lost values

(5001,101,1001,'-','50000','-','48000','-','10000','замена металлоконтрукций перекрытия крыши'),

(5002,101,1003,'10000','60000','-','32000','-','12000','отделка фассада цеха'),

(5003,103,1002,'5000','30000','-','27000','-','5000','стены и потолок'),

(5004,106,1006,'-','22000','-','20000','-','-','замена освещения'),

(5005,107,1014,'-','1000000','-','1200000','-','300000','строительство дорои с асфальтовым покрытие');

 

 

 

Акт Приема-сдачи:

insert into dbo.act_PS values

(6001,86579987,4003,3002,'2011.03.12','2011.04.03',5001,2003),

(6002,86579987,4003,3003,'2009.08.23','2012.01.09',5002,2010),

(6003,86579987,4004,3005,'2011.07.18','2011.08.17',5003,2009),

(6004,86579987,4006,3006,'2010.01.14','2010.06.11',5005,2011);

 

--ЗАПРОСЫ

--1)вывести фио сотрудников долность которых - бухгалтер

select worker_name

From dbo.worker

where job='7';

--2)вывести информацию о строительной фирме

select *

from dbo.build_org;

--3)вывести код сотрудника c фио петров петр петрович

select kod_worker

From dbo.worker

where worker_name='петров петр петрович'

--4)вывести фио сотрудника код которого - 4007

select worker_name

From dbo.worker

where kod_worker='4007'

--5)вывести объекты ОС инвентарный номер которых начинается с "8"

select *

from dbo.info_os_object

where inventory_number like '8%'

--6)вывести объекты ОС названия которых соответствуют коду "106"

select *

from dbo.info_os_object

where os_object_name='106'

--7)вывести обьекты ОС срок использования которых = "5"

select *

from dbo.info_os_object

where fact_usage= '5'

--8)вывести обьекты ОС срок использования которых > "50"

select *

from dbo.info_os_object

where fact_usage>'50'

--9) вывести обьекты ОС срок использования которых < "10"

select *

from dbo.info_os_object

where fact_usage< '10'

--10) вывести имя о заказчике которы йнаходится на улице "фабричная"

Select name

From dbo.zakazchik

where adres like 'фабричная%'

--ВСТРОЕНЫЕ ФУНКЦИИ

--1)вывести количество составленных актов

select COUNT (act_number) as [количество актов]

from dbo.act_ps

--2)кличество объектов ОС

select COUNT (info_kod) as [количество объектов]

from dbo.info_os_object

--3) кличество актов составленных в 2011 году

select COUNT (act_number) as [акты за 2011]

from dbo.act_PS

where sign_date like '2011%'

--4)количество работ стоимостью 50000

select COUNT (service_price) as [услуги 50000]

From dbo.service

where service_price='50000'

--5)количество клиентов фирмы

select COUNT (kod_zakazchika) as [количество клиентов]

From dbo.zakazchik

--Сложные функции (group by & having)

--1.Вывести количество составленных документов на каждую дату подписания актов.

select sign_date, COUNT(act_number)as[Кол-во документов]

from act_PS

group by sign_date

--2.Вывести код должности, в которых более 1 сотрудников.

select job, COUNT(kod_worker)as[Кол-во сотрудников]

From dbo.worker

Group by job

having COUNT(job)>1

--3.Вывести код услуги, стоимость которых больше или равно 50000.

select service_price, COUNT(servce_name)as[Кол-во услуг]

From dbo.service

group by service_price

having COUNT(service_price)>=50000

--4.Вывести коды должностей и количество сотрудников, работающих по этой должности.

select job, COUNT(job)as[Кол-во сотрудников]

From dbo.worker

Group by job

--5.Сколько раз каждый из сотрудников подписывал акт.

select response_person, COUNT(act_number)as[Кол-во актов]

from dbo.act_PS

group by response_person

--6. Сколько ремонтов осуществлено по каждому обьекту ОС.

select result, COUNT(act_number)as[Кол-во ремонтов]

from dbo.act_PS

Group by result

--8. Сколько раз подписывал акт сотрудник с табельным номером 4003.

select response_person, COUNT(act_number)as[Кол-во актов]

from dbo.act_PS

group by response_person

having response_person='4003'

--10. Показать профессию на которой работает 1 сотрудник

SELECT kod_job AS код_должности

FROM dbo.worker,dbo.job

where (select COUNT(dbo.job.kod_job) from dbo.job WHERE dbo.worker.job=dbo.job.kod_job)=1

GROUP BY kod_job

--Хранимые процедуры

--1. Какую должность занимает каждый сотрудник.

go

create procedure doljnost_sotrudnika

as

select dbo.worker.worker_name, dbo.job.job_name

From dbo.worker, dbo.job

where dbo.worker.job=dbo.job.kod_job

go

exec doljnost_sotrudnika

--2. список обьектов отремонтированных в конкретную дату

go

create procedure act @r date

as

select dbo.os_object.os_object_kod, dbo.act_PS.sign_date,dbo.act_PS.finish_date, dbo.act_PS.result

from dbo.os_object,dbo.act_PS

where dbo.act_PS.result=dbo.os_object.os_object_kod and sign_date=@r

go

exec act'2011.03.12'

--3. По табельному номеру определить профессию

GO

CREATE PROCEDURE prof_vibran_sotryd12 @kod int

AS

SELECT kod_worker,worker_name,job_name

FROM dbo.worker, dbo.job

WHERE dbo.worker.job= dbo.job.kod_job and dbo.worker.kod_worker=@kod

go

EXEC prof_vibran_sotryd12 @kod=4004

--4. Возвращает должность по коду сотрудника

Go

Create procedure work

AS

select dbo.worker.kod_worker,dbo.job.kod_job

From dbo.worker,dbo.job

where dbo.worker.kod_worker=dbo.job.kod_job

Go

Exec work

--5. Возвращает код должности и ФИО по табельному номеру

GO

CREATE PROCEDURE koddol @TAB_N int

AS

SELECT dbo.job.kod_job,kod_worker,worker_name

FROM dbo.job,dbo.worker

WHERE dbo.worker.job=dbo.job.kod_job and dbo.worker.kod_worker=@TAB_N

go

EXEC koddol @TAB_N=4006

--6. Возвращает табельный номер и ФИО по коду должности

GO

CREATE PROCEDURE n_fio @job int

AS

SELECT kod_worker,dbo.job.kod_job,worker_name

FROM dbo.worker,dbo.job

WHERE dbo.worker.job=dbo.job.kod_job and dbo.job.kod_job=@job

go

EXEC n_fio @job=4

--7. Возвращает номер акта по коду всех отремонтированных обьектов

Go

Create procedure nact

AS

select dbo.act_PS.act_number,dbo.os_object.os_object_kod

from dbo.act_PS,dbo.os_object

where dbo.act_PS.act_number=dbo.os_object.os_object_kod

Go

Exec nact

--8. Показать данные о акта

GO

CREATE PROCEDURE actsigndate @actn_N int

AS

SELECT act_number,response_person,result

FROM dbo.act_PS,dbo.worker,dbo.os_object

WHERE dbo.act_PS.response_person=dbo.worker.worker_name and dbo.act_PS.result=dbo.os_object.os_object_name and dbo.act_PS.act_number=@actn_N

go

EXEC actsigndate @actn_N=6001

--9. Показать табельный номер и код должности

GO

CREATE PROCEDURE koddponworker @worker_N int

AS

SELECT kod_worker,kod_job

FROM dbo.worker,dbo.job

WHERE dbo.worker.job=dbo.job.kod_job and dbo.worker.kod_worker=@worker_N

EXEC koddponworker @worker_N=4002

--10. Возвращает табельный номер и ФИО по квалификации

GO

CREATE PROCEDURE kvalif_trebyem @kval_tred varchar (50)

AS

SELECT kod_worker,worker_name

FROM dbo.worker,dbo.job

WHERE dbo.worker.job=dbo.job.kod_job and

 dbo.job.job_name=@kval_tred

go

EXEC kvalif_trebyem @kval_tred='прораб'

--Триггеры

--1.При добавлении новой записи выводит сообщение:"В таблицу Должности внесена новая запись!"

Use build2

go

Create trigger dobavlenie

On dbo.job

For insert

as print 'В таблицу Должности внесена новая запись!'

go