MySQL BD Restaurante

Diagramação e codificação

Problemática

O restaurante do Papai Noel, localizado no coração do Polo Norte, enfrenta desafios para gerenciar o fluxo mágico de clientes durante a temporada de Natal. Com elfos atendentes, renas como clientes e itens exclusivos do cardápio inspirados no espírito natalino, o restaurante precisa de uma base de dados eficiente para organizar suas operações. Os desafios incluem:

Gerenciar clientes, com registro da atividade no restaurante e identificação de quem atua como atendente.
Gerenciar categorias de itens mágicos, como "Bebidas Quentes do Pólo" (ex.: chocolate quente com canela encantada), "Doces de Natal" (ex.: biscoitos de gengibre animados) e "Pratos Festivos" (ex.: peru assado com glacê estrelado).
Gerenciar comandas das mesas, desde abertura até encerramento, registrando pedidos personalizados, como bebidas e sobremesas com mensagens mágicas.
Associar cada pedido ao atendente, permitindo identificar quem realizou o atendimento para premiar os melhores elfos no final da temporada natalina.

Um banco de dados relacional robusto e encantado será a base para organizar todas essas informações e garantir que o restaurante do Papai Noel continue a oferecer uma experiência mágica e impecável durante o Natal.

Pré-requisitos

XAMPP;
Códigos MySQL via phpMyAdmin.

Modelo ER

Entidades e atributos:

Cliente: cpf (PK), nome, ativo, atendente.
Categoria: codigo (PK), descricao.
Item: codigo (PK), descricao, valor, ativo, qtdEstoque, codCategoria (FK -> Categoria.codigo).
Comanda: codigo (PK), mesa, estado, dtHrAbertura, dtHrEncerramento, valorTotal, cpfCliente (FK -> Cliente.cpf), cpfAtendente (FK -> Cliente.cpf).
Comanda_Item: codComanda (FK -> Comanda.codigo), codItem (FK -> Item.codigo), qtdItem, obs.

Relacionamentos e multiplicidades:

Cliente ↔ Comanda: 1 cliente pode ter várias comandas (1:N); 1 comanda pertence a exatamente 1 cliente (N:1).
Categoria ↔ Item: 1 categoria pode conter vários itens (1:N); 1 item pertence a exatamente 1 categoria (N:1).
Comanda ↔ Comanda_Item: 1 comanda pode ter vários itens associados (1:N); 1 item pode estar associado a várias comandas (N:M, intermediado pela tabela Comanda_Item).
Item ↔ Comanda_Item: 1 item pode aparecer em várias comandas (1:N); 1 entrada de comanda pode referenciar exatamente 1 item (N:1).
Cliente (como Atendente) ↔ Comanda: 1 atendente pode estar associado a várias comandas (1:N); 1 comanda tem exatamente 1 atendente responsável (N:1).

Queries

Queries DDL (Data Definition Language):

CREATE TABLE cliente;
CREATE TABLE categoria;
CREATE TABLE item;
CREATE TABLE comanda;
CREATE TABLE comanda_item.

Queries DML (Data Manipulation Language):
- INSERT INTO cliente (cpf, nome);
- INSERT INTO cliente (cpf, nome, atendente);
- INSERT INTO categoria (descricao);
- INSERT INTO item (descricao, valor, qtdEstoque, codCategoria);
- INSERT INTO comanda (mesa, cpfCliente, cpfAtendente);
- INSERT INTO comanda_item (codComanda, codItem, qtdItem);
- INSERT INTO comanda_item (codComanda, codItem, qtdItem, obs).

Códigos


CREATE DATABASE restaurante;
USE restaurante;

CREATE TABLE cliente (
    cpf VARCHAR(11) PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    ativo BOOLEAN NOT NULL DEFAULT 1,
    atendente BOOLEAN NOT NULL DEFAULT 0
);

CREATE TABLE categoria (
    codigo INTEGER AUTO_INCREMENT PRIMARY KEY,
    descricao VARCHAR(50) NOT NULL
);

CREATE TABLE item (
    codigo INTEGER AUTO_INCREMENT PRIMARY KEY,
    descricao VARCHAR(50) NOT NULL,
    valor FLOAT NOT NULL DEFAULT 0,
    ativo BOOLEAN NOT NULL DEFAULT 1,
    qtdEstoque FLOAT NOT NULL DEFAULT 0,
    codCategoria INTEGER NOT NULL,
    FOREIGN KEY(codCategoria) REFERENCES categoria(codigo)
);

CREATE TABLE comanda (
    codigo INTEGER AUTO_INCREMENT PRIMARY KEY,
    mesa INTEGER NOT NULL,
    estado VARCHAR(20) NOT NULL DEFAULT 'aberta',
    dtHrAbertura DATETIME NOT NULL DEFAULT NOW(),
    dtHrEncerramento DATETIME,
    valorTotal FLOAT NOT NULL DEFAULT 0,
    cpfCliente VARCHAR(11) NOT NULL,
    cpfAtendente VARCHAR(11) NOT NULL,
    FOREIGN KEY(cpfCliente) REFERENCES cliente(cpf),
    FOREIGN KEY(cpfAtendente) REFERENCES cliente(cpf)
);

CREATE TABLE comanda_item (
    codComanda INTEGER NOT NULL,
    codItem INTEGER NOT NULL,
    qtdItem FLOAT NOT NULL,
    obs VARCHAR(100),
    FOREIGN KEY(codComanda) REFERENCES comanda(codigo),
    FOREIGN KEY(codItem) REFERENCES item(codigo)
);

# Inserção de dados de clientes:
INSERT INTO cliente (cpf, nome) VALUES
('12345678901', 'Rodolfo, o Renegado'),
('23456789012', 'Maria, a Estrela Guia'),
('34567890123', 'Jingle Bells'),
('45678901234', 'Clara, a Mãe Natalina');

# Inserção de dados de atendentes:
INSERT INTO cliente (cpf, nome, atendente) VALUES
('56789012345', 'Filó, o Mordomo', 1),
('67890123456', 'Lúcia, a Mamãe Noel', 1);

# Inserção de categorias:
INSERT INTO categoria (descricao) VALUES
('Ceia dos Elfos'),
('Delícias do Trenó'),
('Sabores do Pólo Norte');

# Inserção de itens:
INSERT INTO item (descricao, valor, qtdEstoque, codCategoria) VALUES
('Pernil do Papai Noel', 49.90, 100, 1),
('Rabanada Encantada', 19.90, 150, 2),
('Sopa de Neve Mágica', 29.90, 80, 1),
('Torta do Treno', 24.90, 120, 2),
('Capuccino do Pólo Norte', 9.90, 200.4, 3);

# Inserção de comandas:
INSERT INTO comanda (mesa, cpfCliente, cpfAtendente) VALUES
(1, '12345678901', '56789012345'),
(2, '23456789012', '67890123456');

# Inserção de itens em comandas:
INSERT INTO comanda_item (codComanda, codItem, qtdItem) VALUES (2, 3, 1);

INSERT INTO comanda_item (codComanda, codItem, qtdItem, obs) VALUES
(1, 2, 4, 'Rabanadas com bengalas doces'),
(2, 4, 2, 'Torta de chocolate e frutas vermelhas');

# Ver descrição, código e categoria:
SELECT i.*, c.descricao FROM item as i, categoria as c WHERE i.codCategoria = c.codigo;

# Ver CPFs com seus nomes:
SELECT 
    c.mesa, 
    ca.cpf AS cpf_atendente, 
    ca.nome AS nome_atendente, 
    cl.cpf AS cpf_cliente, 
    cl.nome AS nome_cliente
FROM 
    comanda c, cliente ca, cliente cl
WHERE 
    c.cpfAtendente = ca.cpf
    AND c.cpfCliente = cl.cpf;

# Ver itens de comandas:
SELECT ci.codComanda, ci.codItem, i.descricao, ci.qtdItem, ci.obs FROM comanda_item as ci, item as i WHERE ci.codItem = i.codigo;

# Ver comandas abertas:
SELECT
    c.codigo AS comanda_codigo,
    c.mesa,
    c.estado,
    c.dtHrAbertura,
    c.dtHrEncerramento,
    c.valorTotal,
    c.cpfCliente AS cpf_cliente,
    cl.nome AS nome_cliente,
    c.cpfAtendente AS cpf_atendente,
    a.nome AS nome_atendente,
    ci.codItem AS item_codigo,
    i.descricao AS item_nome,
    ci.qtdItem,
    ci.obs
FROM comanda c
JOIN cliente cl ON c.cpfCliente = cl.cpf
JOIN cliente a ON c.cpfAtendente = a.cpf
JOIN comanda_item ci ON c.codigo = ci.codComanda
JOIN item i ON ci.codItem = i.codigo
WHERE c.estado="aberta"
ORDER BY codComanda;

# Fechar comanda aberta:
UPDATE comanda SET estado="fechada", dtHrEncerramento=NOW() WHERE codigo=1;

# Atualizar valor total da comanda fechada (valor total é a soma do valor total dos seus itens (qtdItem * valor)):
UPDATE comanda c
JOIN (
    SELECT 
        ci.codComanda,
        SUM(ci.qtdItem * i.valor) AS total
    FROM 
        comanda_item ci
    JOIN 
        item i ON ci.codItem = i.codigo
    GROUP BY 
        ci.codComanda
) AS subquery ON c.codigo = subquery.codComanda
SET 
    c.valorTotal = subquery.total
WHERE 
    c.codigo = 1;

# Fechar comanda 2:
UPDATE comanda SET estado="fechada", dtHrEncerramento=NOW() WHERE codigo=2;

UPDATE comanda c
JOIN (
    SELECT 
        ci.codComanda,
        SUM(ci.qtdItem * i.valor) AS total
    FROM 
        comanda_item ci
    JOIN 
        item i ON ci.codItem = i.codigo
    GROUP BY 
        ci.codComanda
) AS subquery ON c.codigo = subquery.codComanda
SET 
    c.valorTotal = subquery.total
WHERE 
    c.codigo = 2;

Exportar BD

Exportar base de dados via phpMyAdmin

Selecionar banco de dados;
Aba "Exportar";
Selecionar "Rápido" ou "Personalizado";
Selecionar formato SQL ("Executar");
Baixe o arquivo.