Курсовое_проектир методич указания. Методические указания к курсовому проектированию по курсу "Базы данных" Составитель

• Название: Методические указания к курсовому проектированию по курсу "Базы данных" Составитель
• Дата: 10.01.2023
• Размер: 340.75 Kb.
• Имя файла: Курсовое_проектир методич указания.docx
• Тип: Методические указания; #880276
• страница: 8 из 9

2.5. Реализация проекта базы данных

Мы условились не привязываться к конкретной СУБД и выполнять описание логической схемы БД на SQL-92. Приведём описание схемы БД на DDL.

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

1. 
   Отношение Departs (отделы):
   

create table departs (

d_id varchar(12) primary key,

d_name varchar(100) not null);

2. 
   Отношение Rooms (комнаты):
   

create table rooms (

d_depart varchar(12) references departs(d_id),

r_room numeric(4) not null,

r_phone varchar(20),

unique(r_room, r_phone));

3. 
   Отношение Posts (должности):
   

create table posts (

p_post varchar(30) primary key,

p_salary numeric(8,2) not null check(p_salary>=4500));

4. 
   Отношение Employees (сотрудники):
   

create table employees (

e_id numeric(4) primary key,

e_fname varchar(25) not null,

e_lname varchar(30) not null,

e_born date not null,

e_sex char(1) check(e_sex in ('ж','м')),

e_pasp char(10) not null unique,

e_date date not null,

e_given varchar(50) not null,

e_inn char(12) not null unique,

e_pens char(14) not null unique,

e_depart varchar(12) references departs,

e_post varchar(30) references posts,

e_room numeric(4) not null,

e_phone varchar(20) not null,

e_login varchar(30),

foreign key(e_room,e_phone)

references rooms(r_room,r_phone));

(Если внешний ключ ссылается на первичный ключ отношения, его можно не указывать, как в случае ссылок на Departs и Posts).

5. 
   Отношение Edu (образование):
   

create table edu (

u_id numeric(4) references employees,

u_type varchar(20) not null,

u_spec varchar(40),

u_diplom varchar(15),

u_year number(4) not null,

check(u_spec in ('начальное', 'среднее', 'высшее',
'средне-специальное')));

6. 
   Отношение AdrTel (адреса-телефоны):
   

create table adrtel (

a_id numeric(4) references employees,

a_adr varchar(50),

a_phone varchar(30));

7. 
   Отношение Clients (заказчики):
   

create table clients (

c_id numeric(4) primary key,

c_company varchar(40) not null,

c_adr varchar(50) not null,

c_person varchar(50) not null,

c_phone varchar(30));

8. 
   Отношение Projects (проекты):
   

create table projects (

p_id numeric(6) not null unique,

p_title varchar(100) not null,

p_abbr char(10) primary key,

p_depart varchar(12) references departs,

p_company numeric(4) references clients,

p_chief numeric(4) references employees,

p_begin date not null,

p_end date not null,

p_finish date,

p_cost numeric(10) not null check(p_cost>0),

check (p_end>p_begin),

check (p_finish is null or p_finish>p_begin));

9. 
   Отношение Stages (этапы проектов):
   

create table stages (

s_pro char(10) references projects,

s_num numeric(2) not null,

s_title varchar(200) not null,

s_begin date not null,

s_end date not null,

s_finish date,

s_cost numeric(10) not null,

s_sum numeric(10) not null,

s_form varchar(100) not null,

check (s_cost>0),

check (s_end>s_begin),

check (s_finish is null or s_finish>s_begin));

10. 
    Отношение Job (участие):
    

create table job (

j_pro char(10) references projects,

j_emp numeric(2) references employees,

j_role varchar(20) not null,

j_bonus numeric(2) not null,

check(j_bonus>0),

check (j_role in ('исполнитель', 'консультант')));

2.5.2. Создание представлений (готовых запросов)

Приведём примеры нескольких готовых запросов (представлений):

1. 
   Список всех текущих проектов (sysdate – функция, возвращающая текущую дату, определена в СУБД Oracle; в других системах аналогичная функция может называться по-другому, например, getdate() в Transact-SQL, now() в MS Access, currdate() в MySQL и т.д.):
   

create view curr_projects as

select *

from projects

where p_begin<=sysdate and sysdate<=p_end;

2. 
   Определение суммы по текущим проектам, полученной на текущую дату:
   

create or replace view summ (title, cost, total) as

select p_title, p_cost, sum(s_sum)

from curr_projects, stages

where p_abbr=s_pro

group by p_title, p_cost;

3. 
   Список сотрудников, участвующих в текущих проектах:
   

create view participants (project, name, role) as

select p_abbr, e_fname||' '||e_lname, 'руководитель'

from curr_projects, employees

where p_chief=e_id

union all

select p_abbr, e_fname||' '||e_lname, j_role

from curr_projects, employees, job

where p_abbr=j_pro and e_id=j_emp

order by 1, 3 desc;

4. 
   Список рабочих телефонов сотрудников:
   

create or replace view worktel (name, room, phone) as

select e_fname||' '||e_lname, e_room, e_phone

from employees

order by 1;

5. 
   Форма отчётности и сроки выполнения этапов по текущим проектам:
   

create or replace view reports as

select s_pro, s_num, s_title, s_begin, s_end, s_form

from stages

order by 1, 2;

6. 
   Данные о проектах для руководителя проектов:
   

create or replace view my_projects as

select *

from projects p

where exists (select * from employees e

where e.e_id=p.p_chief and e.e_login=user);

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

7. 
   Данные об этапах проектов для руководителя проектов:
   

create or replace view my_stages as

select s.*

from stages s

where exists (select *

from employees e, projects p

where e.e_id=p.p_chief and e.e_login=user

and s.s_pro=p.p_abbr);

8. 
   Данные об участниках проектов для руководителя проектов:
   

create or replace view my_staff as

select j.*

from job j

where exists (select *

from employees e, projects p

where e.e_id=p.p_chief and e.e_login=user

and j.j_pro=p.p_abbr);

9. 
   Данные о других участниках проекта:
   

create or replace view