---

Вот всё то же самое, но мелким шрифтом и в одну строку (без переносов, компактно):

---

ER-диаграмма: WRITER(passport_no PK, last_name, first_name, middle_name, address, phone) 1:1 CONTRACT(contract_id PK, writer_passport FK UNIQUE, sign_date, term_years, restaurant_flag, restaurant_date) ; WRITER 1 : M BOOK_WRITER (book_code FK, writer_passport FK) M : 1 BOOK(book_code PK, title, publisher, release_date, cost_price, sale_price, royalty_total) ; BOOK 1 : M ORDERS(order_id PK, book_code FK, customer_id FK, order_date, execute_date, quantity) M : 1 CUSTOMER(customer_id PK, name, address, phone, contact_person). Связи: писатель-контракт 1:1, писатель-книга M:N (через book_writer), книга-заказ 1:N, заказчик-заказ 1:N.

PostgreSQL DDL (всё в строчку): CREATE TABLE writer (passport_no VARCHAR(20) PRIMARY KEY, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, middle_name VARCHAR(50), address TEXT NOT NULL, phone VARCHAR(20) NOT NULL); CREATE TABLE contract (contract_id SERIAL PRIMARY KEY, writer_passport VARCHAR(20) NOT NULL UNIQUE, sign_date DATE NOT NULL DEFAULT CURRENT_DATE, term_years INTEGER NOT NULL CHECK (term_years > 0), restaurant_flag BOOLEAN NOT NULL DEFAULT FALSE, restaurant_date DATE, FOREIGN KEY (writer_passport) REFERENCES writer(passport_no) ON DELETE CASCADE); CREATE TABLE book (book_code VARCHAR(20) PRIMARY KEY, title VARCHAR(200) NOT NULL, publisher VARCHAR(100) NOT NULL, release_date DATE NOT NULL, cost_price DECIMAL(10,2) NOT NULL CHECK (cost_price >= 0), sale_price DECIMAL(10,2) NOT NULL CHECK (sale_price >= 0), royalty_total DECIMAL(10,2) NOT NULL CHECK (royalty_total >= 0)); CREATE TABLE book_writer (book_code VARCHAR(20) NOT NULL, writer_passport VARCHAR(20) NOT NULL, PRIMARY KEY (book_code, writer_passport), FOREIGN KEY (book_code) REFERENCES book(book_code) ON DELETE CASCADE, FOREIGN KEY (writer_passport) REFERENCES writer(passport_no) ON DELETE CASCADE); CREATE TABLE customer (customer_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, address TEXT NOT NULL, phone VARCHAR(20) NOT NULL, contact_person VARCHAR(100) NOT NULL); CREATE TABLE orders (order_id SERIAL PRIMARY KEY, book_code VARCHAR(20) NOT NULL, customer_id INTEGER NOT NULL, order_date DATE NOT NULL DEFAULT CURRENT_DATE, execute_date DATE, quantity INTEGER NOT NULL CHECK (quantity > 0), FOREIGN KEY (book_code) REFERENCES book(book_code) ON DELETE RESTRICT, FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE); CREATE INDEX idx_orders_customer ON orders(customer_id); CREATE INDEX idx_orders_book ON orders(book_code); CREATE INDEX idx_book_writer_writer ON book_writer(writer_passport);

Приложение (Python, psycopg2) одной строкой: import psycopg2; from getpass import getpass; DB_CONFIG = {"dbname": "publishing_center", "user": "postgres", "password": "your_password", "host": "localhost", "port": 5432}; def get_customer_orders(customer_id): conn = psycopg2.connect(DB_CONFIG); cur = conn.cursor(); cur.execute("SELECT o.order_id, o.order_date, o.execute_date, o.quantity, b.book_code, b.title, b.publisher, b.release_date, b.sale_price, c.name FROM orders o JOIN book b ON o.book_code = b.book_code JOIN customer c ON o.customer_id = c.customer_id WHERE o.customer_id = %s ORDER BY o.order_date DESC;", (customer_id,)); rows = cur.fetchall(); cur.close(); conn.close(); return rows; def authenticate_customer(phone_or_name): conn = psycopg2.connect(DB_CONFIG); cur = conn.cursor(); cur.execute("SELECT customer_id, name, phone FROM customer WHERE phone = %s OR name ILIKE %s;", (phone_or_name, f"%{phone_or_name}%")); customer = cur.fetchone(); cur.close(); conn.close(); return customer; def main(): print("=== Авторизация ==="); identifier = input("Введите имя или телефон: ").strip(); customer = authenticate_customer(identifier); if not customer: print("Не найден"); return; print(f"Добро пожаловать, {customer[1]}!"); orders = get_customer_orders(customer[0]); if not orders: print("Нет заказов"); return; print("=== Ваши заказы ==="); for order in orders: print(f"Заказ №{order[0]}, книга: {order[5]}, кол-во: {order[3]}, цена: {order[8]:.2f}, дата заказа: {order[1]}, выполнено: {order[2] or 'нет'}"); if name == "main": main()

Тестовые данные (одной строкой): INSERT INTO writer VALUES ('1234-567890','Иванов','Иван','Иванович','Москва, ул. Ленина 1','+79001234567'),('2345-678901','Петров','Пётр','Петрович','Москва, ул. Пушкина 2','+79007654321'); INSERT INTO contract (writer_passport, sign_date, term_years, restaurant_flag) VALUES ('1234-567890','2025-01-01',3,FALSE),('2345-678901','2025-02-01',2,TRUE); INSERT INTO book VALUES ('B001','Война и мир','Типография №1','2025-03-01',500.00,1200.00,300.00),('B002','Анна Каренина','Типография №2','2025-04-01',400.00,1100.00,250.00); INSERT INTO book_writer VALUES ('B001','1234-567890'),('B001','2345-678901'),('B002','1234-567890'); INSERT INTO customer (name, address, phone, contact_person) VALUES ('Книжный мир','Москва, Тверская 15','+74951234567','Сидорова А.А.'),('Библиотека №1','СПб, Невский 10','+78121234567','Кузнецов Б.Б.'); INSERT INTO orders (book_code, customer_id, order_date, execute_date, quantity) VALUES ('B001',1,'2025-03-10','2025-03-20',100),('B001',2,'2025-03-12',NULL,50),('B002',1,'2025-04-05','2025-04-15',200);

Дополнительные запросы (в строчку): SELECT w.last_name, w.first_name, SUM(b.royalty_total / (SELECT COUNT(*) FROM book_writer bw2 WHERE bw2.book_code = bw.book_code)) AS total_royalty FROM writer w JOIN book_writer bw ON w.passport_no = bw.writer_passport JOIN book b ON bw.book_code = b.book_code GROUP BY w.passport_no; SELECT w.last_name, c.sign_date, c.term_years, COUNT(DISTINCT bw.book_code) AS books_written FROM writer w JOIN contract c ON w.passport_no = c.writer_passport LEFT JOIN book_writer bw ON w.passport_no = bw.writer_passport GROUP BY w.passport_no, c.sign_date, c.term_years; SELECT b.title, SUM(o.quantity * (b.sale_price - b.cost_price)) AS profit FROM orders o JOIN book b ON o.book_code = b.book_code GROUP BY b.book_code;