-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le : mer. 19 nov. 2025 à 19:13
-- Version du serveur : 8.0.31
-- Version de PHP : 8.3.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de données : `perso_nsi-books`
--

-- --------------------------------------------------------

--
-- Structure de la table `authors`
--

DROP TABLE IF EXISTS `authors`;
CREATE TABLE IF NOT EXISTS `authors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lastname` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `firstname` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `country` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `authors`
--

INSERT INTO `authors` (`id`, `lastname`, `firstname`, `country`) VALUES
(1, 'Orwell', 'Georges', 'Grande Bretagne'),
(2, 'Salinger', 'J.D.', 'États-Unis'),
(3, 'Lee', 'Harper', 'États-Unis'),
(4, 'Golding', 'William', 'Grande Bretagne'),
(5, 'Green', 'John', 'États-Unis'),
(6, 'Austen', 'Jane', 'Grande Bretagne'),
(7, 'Huxley', 'Aldous', 'Grande Bretagne'),
(8, 'Fitzgerald', 'F. Scott', 'États-Unis'),
(9, 'Wilde', 'Oscar', 'Irlande'),
(10, 'Haddon', 'Mark', 'Grande Bretagne'),
(11, 'Steinbeck', 'John', 'États-Unis'),
(12, 'Simmons', 'Dan', 'États-Unis'),
(13, 'Tolkien', 'J.R.R.', 'Grande Bretagne');

-- --------------------------------------------------------

--
-- Structure de la table `books`
--

DROP TABLE IF EXISTS `books`;
CREATE TABLE IF NOT EXISTS `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `date` int DEFAULT NULL,
  `id_author` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_author` (`id_author`)
) ;

--
-- Déchargement des données de la table `books`
--

INSERT INTO `books` (`id`, `title`, `date`, `id_author`) VALUES
(1, 'Animal Farm', 1945, 1),
(2, 'The Catcher in the Rye', 1951, 2),
(3, 'To Kill a Mockingbird', 1960, 3),
(4, 'Lord of the Flies', 1954, 4),
(5, 'The Fault in Our Stars', 2012, 5),
(6, 'Pride and Prejudice', 1813, 6),
(7, 'Brave New World', 1932, 7),
(8, 'The Great Gatsby', 1925, 8),
(9, 'The Picture of Dorian Gray', 1890, 9),
(10, 'The Curious Incident of the Dog in the Night-Time', 2003, 10),
(11, 'Of Mice and Men', 1937, 11),
(12, 'Hyperion', 1989, 12),
(13, 'The Lord of the Rings: The Fellowship of the Ring', 1954, 13),
(14, 'The Lord of the Rings: The Two Towers', 1954, 13),
(15, 'The Lord of the Rings: The Return of the King', 1955, 13);

-- --------------------------------------------------------

--
-- Structure de la table `loans`
--

DROP TABLE IF EXISTS `loans`;
CREATE TABLE IF NOT EXISTS `loans` (
  `id_book` int NOT NULL,
  `id_user` int NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id_book`,`id_user`),
  KEY `id_user` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `loans`
--

INSERT INTO `loans` (`id_book`, `id_user`, `date`) VALUES
(1, 1, '2024-09-19'),
(2, 2, '2024-09-26'),
(3, 3, '2024-10-03'),
(4, 4, '2024-10-22'),
(5, 5, '2024-11-04'),
(6, 6, '2024-11-15'),
(7, 7, '2024-12-05'),
(8, 8, '2025-01-08'),
(9, 5, '2025-01-13'),
(10, 7, '2025-02-17'),
(11, 9, '2025-02-26');

-- --------------------------------------------------------

--
-- Structure de la table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `class` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `card` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `users`
--

INSERT INTO `users` (`id`, `name`, `class`, `card`) VALUES
(1, 'Lucas', '1e 2', 'E073'),
(2, 'Thomas', '1e 2', 'E102'),
(3, 'Julie', '1e 1', 'E084'),
(4, 'Maxime', '1e 1', 'E092'),
(5, 'Nicolas', '1e 2', 'E125'),
(6, 'Sophie', '2nde 1', 'E145'),
(7, 'Emma', '1e 1', 'E137'),
(8, 'Camille', '2nde 1', 'E118'),
(9, 'Chloé', '1e 1', 'E109');

--
-- Contraintes pour les tables déchargées
--

--
-- Contraintes pour la table `books`
--
ALTER TABLE `books`
  ADD CONSTRAINT `books_ibfk_1` FOREIGN KEY (`id_author`) REFERENCES `authors` (`id`);

--
-- Contraintes pour la table `loans`
--
ALTER TABLE `loans`
  ADD CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`id_book`) REFERENCES `books` (`id`),
  ADD CONSTRAINT `loans_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
