Creation_Script.sql

-- ===============================================
-- 1. CRIAÇÃO DE TABELAS (DDL)
-- ===============================================
CREATE TABLE library.tb01_client (
    id_client SERIAL PRIMARY KEY,
    CPF CHAR(11) UNIQUE NOT NULL,
    client_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE library.tb02_author (
    id_author SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    CPF CHAR(11) UNIQUE NOT NULL,
    birth VARCHAR(10) NOT NULL,
    death VARCHAR(10) DEFAULT '00-00-0000'
);

CREATE TABLE library.tb03_publisher (
    id_publisher SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL,
    CNPJ CHAR(14) UNIQUE NOT NULL,
    phone VARCHAR(10) UNIQUE NOT NULL,
    locale VARCHAR(100) NOT NULL
);

CREATE TABLE library.tb04_book (
    id_book SERIAL PRIMARY KEY,
    isbn VARCHAR(50) UNIQUE NOT NULL,
    book_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    pages INT NOT NULL,
    category VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    id_author INT NOT NULL,
    id_publisher INT NOT NULL,
    CONSTRAINT fk_book_author FOREIGN KEY (id_author) 
        REFERENCES library.tb02_author(id_author),
    CONSTRAINT fk_book_publisher FOREIGN KEY (id_publisher) 
        REFERENCES library.tb03_publisher(id_publisher)
);

CREATE TABLE library.tb05_order_status (
    id_order_status SERIAL PRIMARY KEY,
    status VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE library.tb06_order (
    id_order SERIAL PRIMARY KEY,
    number_order VARCHAR(11) UNIQUE NOT NULL,
    order_data DATE NOT NULL DEFAULT CURRENT_DATE,
    id_client INT NOT NULL,
    id_order_status INT NOT NULL,
    CONSTRAINT fk_client_order FOREIGN KEY (id_client) 
        REFERENCES library.tb01_client(id_client) ON DELETE RESTRICT,
    CONSTRAINT fk_order_status FOREIGN KEY (id_order_status) 
        REFERENCES library.tb05_order_status(id_order_status) ON DELETE RESTRICT
);

CREATE TABLE library.tb07_ordered_item (
    id_ordered_item SERIAL PRIMARY KEY,
    id_order INT NOT NULL,
    id_book INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    CONSTRAINT uk_ordered_item UNIQUE (id_order, id_book),
    CONSTRAINT fk_itemchosen FOREIGN KEY (id_order) 
        REFERENCES library.tb06_order(id_order) ON DELETE RESTRICT,
    CONSTRAINT fk_bookchosen FOREIGN KEY (id_book) 
        REFERENCES library.tb04_book(id_book) ON DELETE RESTRICT
);

-- ===============================================
-- 2. INSERÇÃO DE TODOS OS DADOS INICIAIS (DML)
-- ===============================================
INSERT INTO library.tb01_client (CPF, client_name, email, created_at) VALUES
('49764510008', 'Ana Carolina da Silva', 'ana.carolina@email.com','2025-10-15 09:30:00'),
('34521098711', 'Bruno Mendes Oliveira', 'bruno.m.oli@email.com', '2025-10-15 10:15:20'),
('70240582106', 'Carlos Eduardo Ferreira', 'carlos.edu@email.com',  '2025-10-16 14:00:10'),
('29035617899', 'Daniela Almeida Santos', 'daniela.a.s@email.com',  '2025-10-17 11:45:00'),
('05008776886', 'Elaine Cristina Rocha', 'elaine.rocha@email.com',  '2025-10-18 16:20:30'),
('90904460100', 'Felipe Gomes Souza', 'felipe.souza@email.com',  '2025-10-19 08:00:50'),
('43813879100', 'Gabriela Costa Lima', 'gabi.lima@email.com',  '2025-10-20 13:10:45'),
('87648547104', 'Hugo Xavier de Melo', 'hugo.xmelo@email.com',  '2025-10-21 17:55:12'),
('74383051120', 'Isabela Neves Pereira', 'isa.neves@email.com',  '2025-10-22 09:40:05'),
('04960639105', 'João Vitor Martins', 'joao.vitor@email.com',  '2025-10-23 12:05:33');

INSERT INTO library.tb01_client (CPF, client_name, email, created_at) VALUES
('11223344556', 'Laura Ribeiro Costa', 'laura.rcosta@email.com', '2025-10-24 15:30:15'),
('66778899001', 'Marcos Vinicius Santos', 'marcos.vini@email.com', '2025-10-25 09:00:00'),
('33445566778', 'Natália Correia Lima', 'natalia.lima@email.com', '2025-10-26 18:40:55'),
('00112233445', 'Otávio Pereira Guedes', 'otavio.pg@email.com', '2025-10-27 10:25:30'),
('55667788990', 'Patrícia Oliveira Rocha', 'patricia.or@email.com', '2025-10-28 14:15:20'),
('22334455667', 'Ricardo Gomes Alves', 'ricardo.g.alves@email.com', '2025-10-29 07:50:10'),
('77889900112', 'Sofia Fernandes Brito', 'sofia.fb@email.com', '2025-10-30 11:05:00'),
('44556677889', 'Thiago Henrique Lemos', 'thiago.h.lemos@email.com', '2025-10-31 16:20:40'),
('99001122334', 'Valéria Souza Campos', 'valeria.sc@email.com', '2025-11-01 19:10:25'),
('10120230340', 'Wallace Barbosa Pires', 'wallace.bp@email.com', '2025-11-02 13:00:50');

INSERT INTO library.tb02_author(author_name, CPF, birth, death) VALUES 
('Machado de Assis', '11111111111', '21-06-1839', '29-09-1908'),
('Clarice Lispector', '22222222222', '10-12-1920', '09-12-1977'),
('Jorge Amado', '33333333333', '10-08-1912', '06-08-2001'),
('Cecília Meireles', '44444444444', '07-11-1901', '09-11-1964'),
('Paulo Coelho', '55555555555', '24-08-1947', DEFAULT),
('José Saramago', '66666666666', '16-11-1922', '18-06-2010'),
('Gabriel García Márquez', '77777777777', '06-03-1927', '17-04-2014'),
('J.K. Rowling', '88888888888', '31-07-1965', DEFAULT), 
('Agatha Christie', '99999999999', '15-09-1890', '12-01-1976'),
('Erico Verissimo', '00000000000', '17-12-1905', '28-11-1975');

INSERT INTO library.tb02_author (author_name, CPF, birth, death) VALUES
('Fernando Pessoa', '11111111112', '1888-06-18', '1935-11-30'), 
('Clarice Lispector (2)', '22222222223', '1920-12-10', '1977-12-09'), 
('Mary Shelley', '33333333334', '1797-08-30', '1851-02-01'), 
('George Orwell', '44444444445', '1903-06-25', '1950-01-21'), 
('Jane Austen', '55555555556', '1775-12-16', '1817-07-18'),
('Stephen King', '66778899000', '1947-09-02', DEFAULT),
('Neil Gaiman', '77889900111', '1960-11-01', DEFAULT), 
('Liu Cixin', '88990011222', '1963-06-30', DEFAULT), 
('Homer', '99001122333', '1905-12-03', DEFAULT), 
('Virginia Woolf', '00112233445', '1882-01-25', '1941-03-28'); 

INSERT INTO library.tb03_publisher(publisher_name, CNPJ, phone, locale) VALUES 
('Editora Record', '00000000000001', '2198765432', 'Rio de Janeiro, RJ'),
('Companhia das Letras', '00000000000002', '1123456789', 'São Paulo, SP'),
('Editora Rocco', '00000000000003', '2134567890', 'Rio de Janeiro, RJ'),
('Sextante', '00000000000004', '2145678901', 'Rio de Janeiro, RJ'),
('Intrínseca', '00000000000005', '1156789012', 'São Paulo, SP'),
('Martins Fontes', '00000000000006', '1167890123', 'São Paulo, SP'),
('Nova Fronteira', '00000000000007', '2178901234', 'Rio de Janeiro, RJ'),
('Globo Livros', '00000000000008', '5189012345', 'Porto Alegre, RS'),
('HarperCollins Brasil', '00000000000009', '1190123456', 'São Paulo, SP'),
('Editora Aleph', '00000000000010', '1101234567', 'São Paulo, SP');

INSERT INTO library.tb03_publisher (publisher_name, CNPJ, phone, locale) VALUES 
('Editora Viseu', '00000000000011', '1134567890', 'São Paulo, SP'),
('Editora DarkSide', '00000000000012', '5145678901', 'Porto Alegre, RS'),
('Edições 70', '00000000000013', '2156789012', 'Rio de Janeiro, RJ'),
('Zahar', '00000000000014', '2167890123', 'Rio de Janeiro, RJ'),
('L&PM Editores', '00000000000015', '5178901234', 'Porto Alegre, RS'),
('Rocco Jovens Leitores', '00000000000016', '2189012345', 'Rio de Janeiro, RJ'),
('Editora 34', '00000000000017', '1190123459', 'São Paulo, SP'),
('Suma', '00000000000018', '1101234569', 'São Paulo, SP'), 
('Penguin Companhia', '00000000000019', '1112345678', 'São Paulo, SP'),
('Aleph Edições', '00000000000020', '1123456799', 'São Paulo, SP');

INSERT INTO library.tb04_book (isbn, book_name, price, pages, category, quantity, id_author, id_publisher) VALUES 
('9788501010001', 'Dom Casmurro', 45.50, 288, 'Romance Clássico', 25, 1, 1),
('9788535904833', 'A Hora da Estrela', 39.90, 104, 'Ficção Moderna', 18, 2, 2),
('9788532512061', 'Gabriela, Cravo e Canela', 55.00, 416, 'Romance Regional', 30, 3, 3),
('9788575424578', 'O Alquimista', 35.99, 208, 'Ficção Filosófica', 50, 5, 4),
('9788532529061', 'Harry Potter e a Pedra Filosofal', 69.90, 224, 'Fantasia', 40, 8, 5),
('9788533604928', 'E Não Sobrou Nenhum', 49.90, 280, 'Mistério/Policial', 22, 9, 6),
('9788525046271', 'O Tempo e o Vento: O Continente', 79.90, 640, 'Romance Histórico', 15, 10, 8),
('9788535902129', 'Ensaio Sobre a Cegueira', 52.00, 312, 'Distopia', 28, 6, 2),
('9788501072979', 'Cem Anos de Solidão', 65.50, 432, 'Realismo Mágico', 35, 7, 1),
('9788520937803', 'Ou isto ou aquilo', 30.00, 120, 'Poesia Infantil', 10, 4, 7);

INSERT INTO library.tb04_book (isbn, book_name, price, pages, category, quantity, id_author, id_publisher) VALUES 
('9788599419137', 'Frankenstein', 85.00, 360, 'Terror Gótico', 12, 13, 12),
('9788535914849', '1984', 42.90, 320, 'Ficção Distópica', 38, 14, 14),
('9788582850785', 'Orgulho e Preconceito', 39.90, 416, 'Romance Inglês', 25, 15, 19),
('9788551000670', 'It: A Coisa', 89.90, 1104, 'Terror', 15, 16, 18),
('9788579800627', 'Sandman: Prelúdios e Noturnos', 75.00, 240, 'Graphic Novel', 18, 17, 16),
('9788579805905', 'O Problema dos Três Corpos', 59.90, 304, 'Ficção Científica', 20, 18, 20),
('9788573265745', 'O Livro do Desassossego', 68.00, 520, 'Literatura Portuguesa', 10, 11, 17),
('9788535930603', 'Mrs. Dalloway', 40.00, 208, 'Ficção Moderna', 17, 20, 19),
('9788525413349', 'Odisseia', 45.00, 480, 'Épico Clássico', 28, 19, 15),
('9788535900590', 'Laços de Família', 35.00, 160, 'Contos', 23, 12, 2);

INSERT INTO library.tb05_order_status (status) VALUES 
('Emprestado'),
('Devolvido'),
('Atrasado'),
('Reservado'),
('Cancelado');

INSERT INTO library.tb06_order (number_order, order_data, id_client, id_order_status) VALUES 
('EMP00000001', '2025-11-01', 1, 1),
('EMP00000002', '2025-10-20', 3, 2),
('EMP00000003', '2025-11-15', 5, 1),
('EMP00000004', '2025-10-05', 9, 3),
('EMP00000005', '2025-11-20', 2, 4),
('EMP00000006', '2025-11-08', 4, 1),
('EMP00000007', '2025-09-10', 6, 2),
('EMP00000008', '2025-11-23', 8, 4),
('EMP00000009', '2025-11-10', 10, 5),
('EMP00000010', '2025-11-12', 7, 1);

INSERT INTO library.tb06_order (number_order, order_data, id_client, id_order_status) VALUES 
('EMP00000011', '2025-11-24', 11, 1),
('EMP00000012', '2025-11-24', 13, 4),
('EMP00000013', '2025-11-22', 15, 1),
('EMP00000014', '2025-11-21', 17, 2),
('EMP00000015', '2025-11-20', 19, 3),
('EMP00000016', '2025-11-18', 12, 1),
('EMP00000017', '2025-11-17', 14, 4),
('EMP00000018', '2025-11-16', 16, 5),
('EMP00000019', '2025-11-14', 18, 1),
('EMP00000020', '2025-11-13', 20, 2);

INSERT INTO library.tb07_ordered_item (id_order, id_book, quantity, price) VALUES 
(1, 1, 1, 45.50),
(2, 3, 1, 55.00),
(3, 5, 1, 69.90),
(4, 9, 1, 65.50),
(5, 2, 1, 39.90),
(6, 6, 1, 49.90),
(7, 8, 1, 52.00),
(8, 4, 1, 35.99),
(9, 10, 1, 30.00),
(10, 7, 1, 79.90);

INSERT INTO library.tb07_ordered_item (id_order, id_book, quantity, price) VALUES 
(11, 11, 1, 85.00),
(12, 12, 1, 42.90),
(13, 13, 1, 39.90),
(14, 14, 1, 89.90),
(15, 15, 1, 75.00),
(16, 16, 1, 59.90),
(17, 17, 1, 68.00),
(18, 18, 1, 40.00),
(19, 19, 1, 45.00),
(20, 20, 1, 35.00);