# Projeto Banco de Dados - Escola de Idiomas

## Primeiro passo: Diagrama de Relações e Tabelas


Realizei o diagrama no **draw.io** para melhor visualização de como as tabelas se relacionam. Neste processo, percebi a necessidade da criação de novas tabelas como `aluno_turma` e `items` para evitar relações do tipo "n para n".

### Tabelas Criadas:

- **Alunos**: Armazena as informações dos alunos.
- **Aluno_turma**: Relaciona os alunos com as turmas para permitir comunicação entre essas entidades.
- **Turmas**: Armazena as informações das turmas e associa cada uma a um curso.
- **Cursos**: Armazena as informações dos cursos ofertados.
- **Livros**: Armazena informações dos livros disponíveis.
- **Items**: Tabela intermediária que permite a venda de cursos e livros.
- Se `fk_curso` for `NULL`, trata-se de venda de livro.
- Se `fk_livro` for `NULL`, trata-se de venda de curso.
- **Vendas**: Registra as vendas, ligando-as ao aluno que realizou a compra.

---

## Segundo passo: Convenção das Tabelas e Atributos

Os nomes foram traduzidos para o inglês e padronizados conforme as boas práticas do MySQL.

---

## Terceiro passo: Script MySQL para criação das tabelas

CREATE DATABASE language_school;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cpf VARCHAR(14) UNIQUE NOT NULL
);

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255) NOT NULL,
    workload INT NOT NULL,
    language VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    schedule VARCHAR(50),
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

CREATE TABLE student_class (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    class_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    language VARCHAR(50),
    type VARCHAR(50)
);

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    sale_id INT,
    course_id INT,
    book_id INT,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);


## Quarto Passo: Consultas SQL (Respostas para Vanessa)

---

### 1 Alunos que mais gastam na escola

SELECT s.student_id, st.name, SUM(i.quantity * i.unit_price) AS total_spent
FROM sales s
JOIN students st ON s.student_id = st.id
JOIN items i ON s.id = i.sale_id
GROUP BY s.student_id, st.name
ORDER BY total_spent DESC;

### 2. Idiomas mais vendidos

SELECT c.language, SUM(i.quantity) AS total_sold
FROM items i
JOIN courses c ON i.course_id = c.id
GROUP BY c.language
ORDER BY total_sold DESC;

### 3. Quantidade de alunos por idioma

SELECT c.language, COUNT(DISTINCT sc.student_id) AS total_students
FROM student_class sc
JOIN classes cl ON sc.class_id = cl.id
JOIN courses c ON cl.course_id = c.id
GROUP BY c.language
ORDER BY total_students DESC;

### 4 Faturamento por idioma

SELECT c.language, SUM(i.quantity * i.unit_price) AS total_revenue
FROM items i
JOIN courses c ON i.course_id = c.id
GROUP BY c.language
ORDER BY total_revenue DESC;