mirror of
https://github.com/artagaz/bd-semestovaya.git
synced 2026-06-19 02:01:30 +07:00
andrei
63 lines
3.2 KiB
SQL
63 lines
3.2 KiB
SQL
-- 01_create_tables.sql
|
|
-- Информационная система магазина автозапчастей
|
|
|
|
DROP TABLE purchase_orders PURGE;
|
|
DROP TABLE products PURGE;
|
|
DROP TABLE suppliers PURGE;
|
|
|
|
-- 1 Поставщики (suppliers)
|
|
CREATE TABLE suppliers (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR2(255) NOT NULL,
|
|
category VARCHAR2(100),
|
|
contact_info VARCHAR2(255),
|
|
has_guarantee CHAR(1) CHECK (has_guarantee IN ('Y', 'N')),
|
|
contract_id VARCHAR2(50)
|
|
);
|
|
|
|
-- 2 Товары (products)
|
|
CREATE TABLE products (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name VARCHAR2(255) NOT NULL,
|
|
article VARCHAR2(100) UNIQUE NOT NULL
|
|
);
|
|
|
|
-- 3 Заказы поставщикам (purchase_orders)
|
|
CREATE TABLE purchase_orders (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
supplier_id NUMBER NOT NULL,
|
|
order_date DATE NOT NULL,
|
|
CONSTRAINT fk_po_suppliers FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
|
|
);
|
|
|
|
-- Данные ----------------------------------------------------------------------------------------------------
|
|
INSERT INTO suppliers (id, name, category, contact_info, has_guarantee, contract_id) VALUES
|
|
(1, 'Bosch Russia', 'Proizvoditel', 'info@bosch.ru', 'Y', 'CT-BOSCH-01');
|
|
INSERT INTO suppliers (id, name, category, contact_info, has_guarantee, contract_id) VALUES
|
|
(2, 'Mann-Filter Rus', 'Proizvoditel', 'support@mann-filter.ru', 'Y', 'CT-MANN-02');
|
|
INSERT INTO suppliers (id, name, category, contact_info, has_guarantee, contract_id) VALUES
|
|
(3, 'Kontrakt Avto', 'Diler', '+7(495)123-45-67', 'Y', 'CT-KAVTO-03');
|
|
INSERT INTO suppliers (id, name, category, contact_info, has_guarantee, contract_id) VALUES
|
|
(4, 'Masterskaya Petrova', 'Nebolshoe proizvodstvo', 'petrov_garage@mail.ru', 'N', NULL);
|
|
INSERT INTO suppliers (id, name, category, contact_info, has_guarantee, contract_id) VALUES
|
|
(5, 'Zapchasti s Rynka', 'Melkiy magazin', 'rynochniy@yandex.ru', 'N', NULL);
|
|
|
|
INSERT INTO products (id, name, article) VALUES (1, 'Tormoznye kolodki perednie', 'BOSCH-1987936037');
|
|
INSERT INTO products (id, name, article) VALUES (2, 'Maslyanyy filtr', 'MANN-W6018');
|
|
INSERT INTO products (id, name, article) VALUES (3, 'Vozdushnyy filtr', 'BOSCH-1987432234');
|
|
INSERT INTO products (id, name, article) VALUES (4, 'Svecha zazhiganiya', 'DENSO-IU27');
|
|
INSERT INTO products (id, name, article) VALUES (5, 'Remen GRM', 'GATES-T420');
|
|
|
|
INSERT INTO purchase_orders (id, supplier_id, order_date) VALUES (1, 1, DATE '2025-12-01');
|
|
INSERT INTO purchase_orders (id, supplier_id, order_date) VALUES (2, 2, DATE '2025-12-02');
|
|
INSERT INTO purchase_orders (id, supplier_id, order_date) VALUES (3, 3, DATE '2025-12-03');
|
|
INSERT INTO purchase_orders (id, supplier_id, order_date) VALUES (4, 1, DATE '2025-12-05');
|
|
INSERT INTO purchase_orders (id, supplier_id, order_date) VALUES (5, 4, DATE '2025-12-10');
|
|
|
|
-- Сдвигаем последовательности IDENTITY, чтобы не конфликтовали с ручными ID
|
|
ALTER TABLE suppliers MODIFY id GENERATED BY DEFAULT AS IDENTITY START WITH 6;
|
|
ALTER TABLE products MODIFY id GENERATED BY DEFAULT AS IDENTITY START WITH 6;
|
|
ALTER TABLE purchase_orders MODIFY id GENERATED BY DEFAULT AS IDENTITY START WITH 6;
|
|
|
|
COMMIT;
|