Задание 1. Разработка модели предметной области
Разработать формальную модель предметной области для небольшой строительной фирмы, которая занимается ремонтом помещений. При выполнении проектов в фирме используются детали, закупаемые у поставщиков.
Модель и описание предметной области

Рисунок 1 – Модель строительной фирмы, занимающейся ремонтом помещений и закупающей детали у поставщиков
Деталь имеет следующие атрибуты: уникальный идентификатор, название, цена, цвет, вес. Поставщик имеет следующие атрибуты: уникальный идентификатор, название, город, адрес, рейтинг.
Поставка детали для проекта имеет атрибут количество деталей, ID проекта, детали и поставщика.

Проект имеет следующие атрибуты: уникальный идентификатор, название, город, адрес, бюджет.
Деталь может поставляться различными поставщиками для различных проектов. Например, могут одновременно существовать следующие поставки: поставка детали «Гвоздь 50 мм» от поставщика «Стройкомплект» для проекта «Ремонт квартиры» и поставка детали «Гвоздь 50 мм» от поставщика «РемСнабСбыт» для проекта «Ремонт садового домика».
Поставщик может поставлять различные детали для различных проектов. Например, могут одновременно существовать следующие поставки: поставка детали «Гвоздь 50 мм» от поставщика «Стройкомплект» для проекта «Ремонт квартиры» и поставка детали «Вагонка 5 м» от поставщика «Стройкомплект» для проекта «Ремонт садового домика».
В проекте могут использоваться различные детали, поставленные различными поставщиками. Например, могут одновременно существовать следующие поставки: поставка детали «Гвоздь 50 мм» от поставщика «Стройкомплект» для проекта «Ремонт квартиры» и поставка детали «Саморез 50 мм» от поставщика «РемСнабСбыт» для проекта «Ремонт квартиры».
Задание 2-3. Разработка схемы базы данных и ограничений целостности
1) Разработка схемы базы данных

Разработать схему базы данных в виде ER-диаграммы (допустима любая нотация). Убедиться, что разработанная диаграмма адекватно и полно отражает требования ограничений целостности атрибутов и связи между сущностями.

Создать рабочую базу данных и настроить права доступа к ней. Разработать команды SQL для создания реляционных таблиц базы данных. Каждый запрос должен предваряться комментарием с указанием создаваемой таблицы. Каждое

---

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


Рисунок 1 – ER диаграмма, отражающая связи между таблицами и ограничения целостности атрибутов
2) Разработка ограничений целостности данных

1. 
   CREATE DATABASE test
   
2. 
   WITH
   
3. 
   OWNER = postgres
   
4. 
   ENCODING = 'UTF8'
   
5. 
   LC_COLLATE = 'Russian_Russia.1251'
   
6. 
   LC_CTYPE = 'Russian_Russia.1251'
   
7. 
   TABLESPACE = pg_default
   
8. 
   CONNECTION LIMIT = -1
   
9. 
   IS_TEMPLATE = FALSE;
   
10. 
     
    
11. 
    -- таблица детали
    
12. 
    CREATE TABLE "Detail"
    
13. 
    (
    
14. 
    ID Serial PRIMARY KEY, -- иддетали
    
15. 
    Name VARCHAR(128) NOT NULL, -- название, не пустое
    
16. 
    "Price" INTEGER CHECK("Price" > 0), -- цена, только положительная
    
17. 
    "Weight" INTEGER CHECK("Weight" > 0), -- вес в граммах, только положительный
    
18. 
    "Color" VARCHAR(128), -- цвет детали для сравнения со списком определенных цветов
    
19. 
    "High cost" VARCHAR(128)
    
20. 
    );
    
21. 
     
    
22. 
    ALTER TABLE "Detail"
    
23. 
    ADD CHECK ("Color" IN ('White', 'Red', 'Black', 'Grey', 'Green', 'Orange'));
    
24. 
     
    
25. 
    -- таблица поставщика
    
26. 
    CREATE TABLE "Contractor"
    
27. 
    (
    
28. 
    ID Serial PRIMARY KEY, -- идпоставщика
    
29. 
    Name VARCHAR(128) NOT NULL, -- название, не пустое
    
30. 
    "City" VARCHAR(128) NOT NULL, -- город, непустой
    
31. 
    "Address" VARCHAR(128) DEFAULT 'Неизвестен', -- адрес, не пустой, по умолчанию неизвестен
    
32. 
    "Rating" INTEGER CHECK("Rating" > 0 AND "Rating" <= 10), -- рейтингот 1 до 10
    
33. 
    UNIQUE ("City", Name), -- уникальность названия поставщиков в одном городе
    
34. 
    "Reliability" VARCHAR(128)
    
35. 
    );
    
36. 
     
    
37. 
    -- таблица проекта
    
38. 
    CREATE TABLE "Project"
    
39. 
    (
    
40. 
    ID Serial PRIMARY KEY, -- идпроекта
    
41. 
    Name VARCHAR(128) NOT NULL, -- название, не пустое
    
42. 
    "City" VARCHAR(128) NOT NULL, -- город, непустой
    
43. 
    "Address" VARCHAR(128) NOT NULL, -- адрес, непустой
    
44. 
    "Budget" INTEGER CHECK("Budget" > 0) -- бюджет, только положительный
    
45. 
    );
    
46. 
     
    
47. 
    -- таблица доставки
    
48. 
    CREATE TABLE "Delivery"
    
49. 
    (
    
50. 
    ID Serial PRIMARY KEY, -- идпоставки
    
51. 
    "Detail_ID" INTEGER, -- ид детали
    
52. 
    "Contractor_ID" INTEGER, -- ид поставщика
    
53. 
    "Project_ID" INTEGER, -- ид проекта
    
54. 
    "Amount" INTEGER CHECK ("Amount" > 0), -- количество деталей, только положительное
    
55. 
    FOREIGN KEY ("Detail_ID") REFERENCES "Detail"(ID) -- связи
    
56. 
    ON DELETE CASCADE ON UPDATE CASCADE,
    
57. 
    FOREIGN KEY ("Contractor_ID") REFERENCES "Contractor"(ID)
    
58. 
    ON DELETE CASCADE ON UPDATE CASCADE,
    
59. 
    FOREIGN KEY ("Project_ID") REFERENCES "Project"(ID)
    
60. 
    ON DELETE CASCADE ON UPDATE CASCADE
    
61. 
    );
    

---

Были разработаны ограничения целостности атрибутов и ограничения ссылочной целостности данных согласно описанию приведенному ниже:

Название поставщика должно быть уникальным в рамках города. Например, не могут одновременно существовать следующие поставщики: «Стройкомплект» в Челябинске по адресу ул. Электродная, 7 и «Стройкомплект» в Челябинске по адресу пр. Ленина, 3. Название, город и адрес поставщика не могут быть пустыми. По умолчанию адрес поставщика должен иметь значение «неизвестен». Рейтинг поставщика должен находиться в диапазоне от 1 до 10. Название и цена детали не могут быть пустыми. Вес и цена детали должны быть положительными. Цвет детали должен принимать значение из фиксированного списка значений (н-р: белый, черный, красный, синий, серый, зеленый, желтый, оранжевый). Название, город и адрес проекта не могут быть пустыми. Бюджет проекта должен быть положительным. Количество деталей в поставке должно быть положительным.

Удаление (изменение) детали, поставщика или проекта должно инициировать каскадное удаление (изменение) соответствующих поставок.
Задание 4-5. Представления
1. Заполнить часть ранее созданных таблиц базы данных, используя команду SQL: insert into … values …

Остальные таблицы базы данных заполнить, выполнив импорт тестовых данных из файла с расширением csv. Этот файл можно создать, например в MS Excel. В случае, если имена сущностей и/или атрибутов в файлах отличаются от использованных, внести необходимые изменения в файлы перед выполнением импорта.

1. 
   INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
   
2. 
   VALUES ('Detail 1', 125, 15, 'Black');
   
3. 
   INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
   
4. 
   VALUES ('Detail 2', 300, 250, 'White');
   
5. 
   INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
   
6. 
   VALUES ('Detail 3', 400, 1000, 'Black');
   
7. 
   INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
   
8. 
   VALUES ('Detail 4', 25, 5, 'Grey');
   
9. 
   INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
   
10. 
    VALUES ('Detail 5', 50, 2, 'Grey');
    
11. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
12. 
    VALUES ('Detail 6', 500, 1000, 'Orange');
    
13. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
14. 
    VALUES ('Detail 7', 5500, 1000, 'White');
    
15. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
16. 
    VALUES ('Detail 8', 200, 4700, 'Black');
    
17. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
18. 
    VALUES ('Detail 9', 1500, 400, 'Black');
    
19. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
20. 
    VALUES ('Detail 10', 3000, 2500, 'Black');
    
21. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
22. 
    VALUES ('Detail 11', 300, 200, 'Red');
    
23. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
24. 
    VALUES ('Detail 12', 550, 900, 'Black');
    
25. 
    INSERT INTO "Detail" (Name, "Price", "Weight", "Color")
    
26. 
    VALUES ('Detail 13', 90, 25, 'Grey');
    
27. 
     
    
28. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
29. 
    VALUES('Company 1', 'Chelyabinsk', 'Street A, 23', 9);
    
30. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
31. 
    VALUES('Company 2', 'Moscow', 'Street B, 5', 6);
    
32. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
33. 
    VALUES('Company 3', 'Zlatoust', 'Street C, 1', 7);
    
34. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
35. 
    VALUES('Company 4', 'Chelyabinsk', 'Street D, 1', 4);
    
36. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
37. 
    VALUES('Company 5', 'Vladivostok', 'Street E, 10', 2);
    
38. 
    INSERT INTO "Contractor" (Name, "City", "Address", "Rating")
    
39. 
    VALUES('Company 6', 'Ryazan', 'Street F, 9', 8);
    
40. 
     
    
41. 
    COPY "Project" FROM 'D:\SQL\Project.csv' DELIMITER ',' CSV HEADER;
    
42. 
    COPY "Delivery" FROM 'D:\SQL\Delivery.csv' DELIMITER ',' CSV HEADER;
    

---

Триггеры для автоматического заполнения атрибутов «Дороговизна» и «Надежность»:

1) триггер для заполнения «Дороговизны» при добавлении новой записи в таблицу деталей:

1. 
   CREATE OR REPLACE FUNCTION TriggerCheckPrice() RETURNS TRIGGER AS $$
   
2. 
   BEGIN
   
3. 
   IF TG_OP = 'INSERT' THEN
   
4. 
   UPDATE "Detail"
   
5. 
   SET "High cost" =
   
6. 
   CASE
   
7. 
   WHEN "Price" >= 1000
   
8. 
   THEN 'Дорогая'
   
9. 
   ELSE 'Дешёвая'
   
10. 
    END;
    
11. 
    RETURN NEW;
    
12. 
    END IF;
    
13. 
    END;
    
14. 
    $$ LANGUAGE plpgsql;
    
15. 
     
    
16. 
    CREATE TRIGGER CheckPrice
    
17. 
    AFTER INSERT ON "Detail" FOR EACH ROW EXECUTE
    
18. 
    PROCEDURE TriggerCheckPrice();
    

2) триггер для заполнения «Надежности» при добавлении новой записи в таблицу поставщиков:

1. 
   CREATE OR REPLACE FUNCTION TriggerCheckRating() RETURNS TRIGGER AS $$
   
2. 
   BEGIN
   
3. 
   IF TG_OP = 'INSERT' THEN
   
4. 
   UPDATE "Contractor"
   
5. 
   SET "Reliability" =
   
6. 
   CASE
   
7. 
   WHEN "Rating" >= 6
   
8. 
   THEN 'Надёжный'
   
9. 
   ELSE 'Ненадёжный'
   
10. 
    END;
    
11. 
    RETURN NEW;
    
12. 
    END IF;
    
13. 
    END;
    
14. 
    $$ LANGUAGE plpgsql;
    
15. 
     
    
16. 
    CREATE TRIGGER CheckRating
    
17. 
    AFTER INSERT ON "Contractor" FOR EACH ROW EXECUTE
    
18. 
    PROCEDURE TriggerCheckRating();
    

Рисунок 1 – Таблица деталей


Рисунок 2 – Таблица поставщиков


Рисунок 3 – Таблица проектов


Рисунок 4 – Таблица поставок
2. Разработка представлений экономиста и директора.

2.1. Представления экономиста: экономист имеет право осуществлять выборку, добавление, обновление и удаление данных о поставщиках и поставках, а также выборку данных о деталях и проектах. При выборке данных Поставщик для Экономиста должен быть представлен следующими атрибутами: название, город, адрес, рейтинг, признак «ненадежный» или «надежный» (символьная строка). Данные должны быть отсортированы следующим образом: по городу, названию, убыванию рейтинга. Деталь для Экономиста должна быть представлена следующими атрибутами: название, цена в тыс.руб., цвет, вес в килограммах, признак «дешевая» или «дорогая» (символьная строка). Данные должны быть отсортированы следующим образом: по убыванию цены, названию, цвету, возрастанию веса. При выборке данных Проект для Экономиста должен быть представлен следующими атрибутами: название, город, адрес, бюджет. Данные должны быть отсортированы следующим образом: по городу, названию, убыванию бюджета. При выборке данных Поставка для Экономиста должна быть представлена следующими атрибутами: название детали, город детали, цвет детали, признак детали «дешевая» или «дорогая» (символьная строка), название поставщика, город поставщика, признак поставщика «ненадежный» или «надежный» (символьная строка), количество деталей, вес поставки в килограммах, стоимость поставки в тыс.руб. Данные должны быть отсортированы следующим образом: названию детали, названию поставщика, по убыванию стоимости, по убыванию веса.

---

2.1.1. Поставщики:

1. 
   CREATE VIEW "EconomistContractor" AS
   
2. 
   SELECT name, "City", "Address", "Rating", "Reliability" FROM "Contractor"
   
3. 
   ORDER BY "City", Name, "Rating" DESC;
   
4. 
    
   
5. 
   SELECT * FROM "EconomistContractor";
   

Рисунок 5 – Представление «Экономист-поставщики»
2.1.2. Детали:

1. 
   CREATE VIEW "EconomistDetail" AS
   
2. 
   SELECT
   
3. 
   Name, ROUND("Price"/1000.0, 3) AS "Price, K RUB", "Color", ROUND("Weight"/1000.0, 3) AS "Weight, Kg", "High cost"
   
4. 
   FROM "Detail"
   
5. 
   ORDER BY "Price, K RUB" DESC, Name, "Color", "Weight, Kg";
   
6. 
    
   
7. 
   SELECT * FROM "EconomistDetail";
   
8. 
    
   

Рисунок 6 – Представление «Экономист-детали»
2.1.3. Проекты:

1. 
   CREATE VIEW "EconomistProject" AS
   
2. 
   SELECT Name, "City", "Address", "Budget" FROM "Project"
   
3. 
   ORDER BY "City", Name, "Budget" DESC;
   
4. 
    
   
5. 
   SELECT * FROM "EconomistProject";
   
6. 
    
   

Рисунок 7 – Представление «Экономист-проекты»

2.1.4. Поставки:

1. 
   CREATE VIEW "EconomistDelivery" AS
   
2. 
   SELECT
   
3. 
   "Detail".Name AS "NameDetail",
   
4. 
   "Detail"."Color" AS "ColorDetail",
   
5. 
   "Detail"."High cost" AS "HighcostDetail",
   
6. 
   "Contractor".Name AS "NameContractor",
   
7. 
   "Contractor"."City" AS "CityContractor",
   
8. 
   "Contractor"."Reliability" AS "ReliabilityContractor",
   
9. 
   "Delivery"."Amount" AS "AmountDelivery",
   
10. 
    ROUND("Delivery"."Amount" * "Weight"/1000.0,3) AS "WeightDelivery",
    
11. 
    ROUND("Delivery"."Amount" * "Price"/1000.0, 3) AS "PriceDelivery"
    
12. 
    FROM "Detail", "Contractor", "Delivery", "Project"
    
13. 
    WHERE "Detail_ID" = "Detail".id AND "Contractor_ID" = "Contractor".id
    
14. 
    AND "Project_ID" = "Project".id
    
15. 
    ORDER BY "NameDetail", "NameContractor", "PriceDelivery" DESC, "WeightDelivery" DESC;
    
16. 
     
    
17. 
    SELECT * FROM "EconomistDelivery";
    

Рисунок 8 – Представление «Экономист-поставки»
2.2. Представления директора: директор имеет право осуществлять выборку, добавление, обновление и удаление данных о поставщиках, деталях и проектах, а также выборку и удаление данных о поставках. При выборке данных Поставщик для Директора должен быть представлен следующими атрибутами: название, город, признак «ненадежный» или «надежный» (символьная строка). Данные должны быть отсортированы следующим образом: по признаку, городу, названию. При выборке данных Деталь для Директора должна быть представлена следующими атрибутами: название, цена в тыс.руб., цвет, признак «дешевая» или «дорогая» (символьная строка). Данные должны быть отсортированы следующим образом: по убыванию цены, названию. При выборке данных Проект для Директора должен быть представлен следующими атрибутами: название, город, бюджет. Данные должны быть отсортированы следующим образом: по убыванию бюджета, городу, названию. При выборке данных Поставка для Директора должна быть представлена следующими атрибутами: название детали, город детали, цвет детали, признак детали «дешевая» или «дорогая» (символьная строка), название поставщика, город поставщика, признак поставщика «ненадежный» или «надежный» (символьная строка), количество деталей, вес поставки в килограммах, стоимость поставки в тыс.руб. Данные должны быть отсортированы следующим образом: по признаку детали, городу детали, признаку поставщика, городу поставщика, названию детали, названию поставщика, по убыванию стоимости, по убыванию веса.

---

Смотрите также файлы

• Выделите грамматические основы, расставьте недостающие знаки препинания. Сделайте разбор предложений 19, 20.docx

• Техническое задание на проведение исследования запросов жителей Екатеринбурга на недвижимость.docx

• Кузеванова Наталья Вениаминовна гр бж 1931z психологопедагогическая безопасность образовательных систем.docx

• Проблемы формирования мотивации здорового образа жизни в Российской Федерации.pdf

• Сценарий Гимн молодежи Кубани.docx