1. Creation of the database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3cec2518-ac22-4a4e-91a1-bfab69a12949",
   "metadata": {},
   "source": [
    "# 1. Bookshop Near Me"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e11e1d91-e7be-4e41-b923-caa146a2ab20",
   "metadata": {},
   "source": [
    "Bookshop Near Me is a global book publishing company that publishes and distributes a wide range of books. The company has a diverse portfolio of authors and genres, making it a prominent player in the publishing industry. To streamline their operations, enhance customer experience, and manage their extensive catalogue of books, they have decided to develop a database management system tailored to their needs. Let's start by understanding the business requirements and identifying the key entities and relationship:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a88da17-480d-4dbd-a8cc-883e95bbdb2b",
   "metadata": {},
   "source": [
    "## Business requirements"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "576f3789-d561-4467-b034-63af53088967",
   "metadata": {},
   "source": [
    "**Book catalog:** The database should efficiently manage information about books, including their titles, authors, publication dates, ISBNs, genres, avarage rating, available stock quantities and price. One book can be written by more than just one author. For data integrity, recovery and historical reasons, it is advised that whenever a book is deleted, it is not actually deleted, but archived in a special table for books that are not actively in sale (soft delete). New book insertion in the database should be a controled process, as the results will be seen on their webpage, so there is not place for mistakes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d142dceb-8db3-4da9-9da1-555be3f935bb",
   "metadata": {},
   "source": [
    "**Author information:** It's essential to maintain details about authors, such as their names, birthdates, and nationalities. One author may write multiple books. The same as for books, also authors are better to be soft deleted for easier data recovery and compliancee reasons. In case an author is supposed to be deleted, this process needs to be also controlled and only authors, that have no books assigned to them, or their book stock is 0, can be deleted."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca153cbe-85e1-4441-a620-948328734800",
   "metadata": {},
   "source": [
    "**Customer management:** The system should store customer information as well as user information. As these entities goes hand by hand, their creation process and deletion process is connected - whenever a user is created also a customer is created and the same goes for deletion. It is neccessary to know users email, password for registration, the last login and registration date must be automatically generated, for the customer entity, their name, surname, phone, address and country is needed to known. Customers membership status, that is also represented in customers table, is a autogenerated field, whcih status depends on a differentent entity in the database - their shopping ability. Membership statuses must be strongly controlled, as the only allowed values are \"Standard\", \"Premium\" and \"VIP\". For data analysis purposes, deleted customers must be saved (soft deleted) in a specified table."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92e34456-9e79-4b15-8be7-b2afa1629712",
   "metadata": {},
   "source": [
    "**Order and payment processing:** The database should facilitate order creation, tracking, and processing. This includes customer orders, order dates, statuses (e.g., \"Pending,\" \"Shipped,\" \"Delivered,\" \"Cancelled\"), and payment information (payment dates, methods, amounts, and payment statuses). For order to be done, it`s status must be \"Delivered\" and payment must be assigned. For easier worklfow, whenever a customer places an order for specifiedd customer, it has to appear in specified view for pending orders, where later order items are assigned to this specific order, after order items are assigned, and invoice is created, that represents unique order id, date in which order has been made and the total amount. From there a payment is made, one customer can only make one whole payment, Bookshop Near Me is not allowing to pay in parts, and the only available payment methods are Credit Card, App Wallet or PayPal. When a payment for specified order is created, it can obtain these statuses : \"Completed\", \"Failed\", \"Pending\" or \"Refunded\". When a payment is Completed, it means that everything went smooth and the order status appears as Shipped - on its way customer. If the payment is Failed or Pending, also order is withhold - in status of Pending, bcause, for obvious reasons, it cannot be shipped if the payment is not recieved. In case a customer is not satisfied with his goods, or the book has been damaged on the way till his doorstep, Bookshop Near Me allows for their customers to return their books, in this case order status would appear Cancelled and payment status must appear as Refunded. In case the specified customer is deleted from database, its corresponding payments and orders are archived for analysis reasons. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab088019-1ef9-46e8-880e-492c407e1fde",
   "metadata": {},
   "source": [
    "**Order items:** Represents the item, count and subtotal for each order. As one order can be constructed from different kind of books in different amounts, it is important for data integrity and inventory reasons to represent each item for each order. Order items can only be assigned to orders that yet dont have payment assigned to them and whenever the items are assigned for the order, an invoice is created, as the payment is expected. Also whenever a corresponding customer for the order is deleted, it's order items are also archived for analysis purposes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6935fa96-571b-4ecb-a015-78ae05880d0c",
   "metadata": {},
   "source": [
    "**Reviews:** Customers should be able to leave reviews for books. The database should capture review details, such as customer IDs, book IDs, ratings, comments, and review dates. It is important that clients can only give ratings from 1 to 5. And the average rating for each book is guarded in the Book table for a more comfortable way to see how popular and liked is each book, in this way Bookshop Near Me can easily see which books are more liked by their customers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6547a779-4344-478c-ac28-aedc3701e7d5",
   "metadata": {},
   "source": [
    "**Loyalty points:** Maintain information about customer loyalty points, including points earned and the date they were earned. Loyalty points are obtained for each order that has status as Delivered. For each 10 EUR customer receives 1 loyalty point, if the order is 20 EUR, customer receives 2 loyalty points and so on. If a client has not purchased anything - no loyalty points earned, its membership status is undefined. Whenever an order has been completed on a total value greater or equal 10 EUR, the customer receives its first loyalty point(s) and enters the first membership status “Standard”. Loyalty points are a great mechanism to engage with customers and nourish their will to buy more. “Premium” membership status starts from 100 loyalty points, its benefits include - 5% discount on all orders and early access to promotions and sales. “VIP” membership status starts from 500 loyalty points, its benefits include - 10% discount on all orders, exclusive access to limited-edition items, priority customer support."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f7adc21-5309-4771-ab54-27bfa1aef36c",
   "metadata": {},
   "source": [
    "**Database interaction:**  The system should allow for interaction with external tools like Jupyter Notebook, which can be used for data analysis and visualization."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13afa8de-fb91-4972-91d9-0b5fd570bb3d",
   "metadata": {},
   "source": [
    "**Data security:** Implement robust security measures to protect customer data and ensure data privacy.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f92ec82-cd34-433c-bc6d-c4d9c999af7c",
   "metadata": {},
   "source": [
    "## EER schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "279a63fc-702f-47f0-993f-a5905507b125",
   "metadata": {},
   "source": [
    "![EER Diagram](images/conceptualReal.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16aa5e95-8c96-4ea4-9af8-dd87ca79af89",
   "metadata": {},
   "source": [
    "## Database structure code"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afd22981-34c8-493e-9ac7-237d82b55bc4",
   "metadata": {},
   "source": [
    "The database schema for Bookshop Near Me consists of the following tables:\n",
    "\n",
    "<ul>\n",
    "   <li>1. Authors:\n",
    "        <ul>\n",
    "   <li><u>author_id</u> - a primary key to uniquely identify each author</li>\n",
    "   <li>author_name</li>\n",
    "   <li>birth_date</li>\n",
    "   <li>nationality </li>  \n",
    "    </ul>\n",
    "</li>\n",
    "    <br>\n",
    "    <li>2. Books:\n",
    "        <ul>\n",
    "    <li><u>book_id</u> - primary key to uniquely identify each book</li>\n",
    "  <li>title</li>\n",
    "  <li>publication_date</li>\n",
    "   <li>ISBN - a natural key, but managing and querying data with different ISBN formats can be complex and error prone </li>\n",
    "   <li>stock_quantity</li>\n",
    "   <li>avarage_rating</li>\n",
    "    <li>price</li>\n",
    "    </ul>\n",
    "</li>\n",
    "    <br>\n",
    "<li>3. Authorsbooks:\n",
    "    <ul>\n",
    "   <li><u> authors_id</u> - these two will work as a compound key as a result of a M:M relationship</li>\n",
    "   <li> <u>book_id</u></li>\n",
    "    </ul>\n",
    "</li>\n",
    "<br>\n",
    "<li>4. Genres:\n",
    "    <ul>\n",
    "   <li><u> genre_id</u></li>\n",
    "  <li> genre_name</li>\n",
    "  </ul>\n",
    "</li>\n",
    "<br>\n",
    "\n",
    "<li>5. Genresbooks:\n",
    "    <ul>\n",
    "   <li><u> book_id</u> - also these two function as a compound key and are also assigned as foreign keys for data integrity</li>\n",
    "  <li><u>genre_id</u></li>\n",
    "    </ul>\n",
    "</li>\n",
    "<br>\n",
    "\n",
    "<li>6. Customers:\n",
    "    <ul>\n",
    "   <li><u> customer_id</u></li>\n",
    "   <li> first_name</li>\n",
    "    <li>last_name</li>\n",
    "   <li>email</li>\n",
    "   <li> password</li>\n",
    "   <li> phone</li>\n",
    "   <li> address</li>\n",
    "   <li> membership_status</li>\n",
    "   <li>registration_date</li>\n",
    "   <li>last_login</li>\n",
    "  </ul>\n",
    "</li>\n",
    "<br>\n",
    "<li>7. Reviews:\n",
    "    <ul>\n",
    "   <li><u> review_id</u></li>\n",
    "  <li> customer_id</li>\n",
    "   <li> book_id</li>\n",
    "   <li>rating</li>\n",
    "  <li> comments</li>\n",
    "   <li> review_date</li>\n",
    "  </ul>\n",
    "</li>\n",
    "<br>\n",
    "\n",
    "<li>8. Loyaltypoints:\n",
    "    <ul>\n",
    "   <li> <u>loyalty_id</u></li>\n",
    "   <li> customer_id</li>\n",
    "   <li>points_earned</li>\n",
    "   <li>transaction_date</li>\n",
    "   </ul>\n",
    "</li>\n",
    "<br>\n",
    "<li>9. Orders:\n",
    "    <ul>\n",
    "    <li><u>order_id</u></li>\n",
    "    <li>customer_id</li>\n",
    "    <li> order_date</li>\n",
    "    <li> status</li>\n",
    "  </ul>\n",
    "</li>\n",
    "<br>\n",
    "<li>10. Orderitems:\n",
    "    <ul>\n",
    "    <li><u> order_item_id</u></li>\n",
    "    <li>order_item</li>\n",
    "    <li> book_id</li>\n",
    "    <li>quantity</li>\n",
    "    <li>subtotal</li>\n",
    "    </ul>\n",
    "   </li>\n",
    "   <br>\n",
    "<li>11. Payments:\n",
    "    <ul>\n",
    "    <li><u>payment_id</u></li>\n",
    "    <li> order_id</li>\n",
    "   <li> payment_date</li>\n",
    "   <li>payment_method</li>\n",
    "    <li> amount</li>\n",
    "   <li> status</li>\n",
    "    </ul>\n",
    "</li>\n",
    "<br>\n",
    "<li>12. Users:\n",
    "    <ul>\n",
    "    <li><u>user_id</u></li>\n",
    "    <li> customer_id</li>\n",
    "   <li> email</li>\n",
    "   <li>password</li>\n",
    "    <li> salting</li>\n",
    "   <li> last login</li>\n",
    "    <li> registration date</li>\n",
    "    </ul>\n",
    "</li>\n",
    "<br>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0d8855b-dcfd-4929-9b39-446aa6602083",
   "metadata": {},
   "source": [
    "## SQL code defining the database structure:  \n",
    "Please note that this code is not executable in this notebook.  \n",
    "It is provided for reference only.\n",
    "\n",
    "\n",
    "\n",
    "```sql\n",
    "-- creating authors table\n",
    "CREATE TABLE `authors` (\n",
    "  `authors_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `author_name` varchar(255) NOT NULL,\n",
    "  `birth_date` date NOT NULL,\n",
    "  `nationality` varchar(255) NOT NULL,\n",
    "  PRIMARY KEY (`authors_id`)\n",
    "); \n",
    "\n",
    "\n",
    "-- creating books table\n",
    "CREATE TABLE `books` (\n",
    "  `book_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `title` varchar(255) NOT NULL,\n",
    "  `publication_date` date NOT NULL,\n",
    "  `ISBN` varchar(13) NOT NULL,\n",
    "  `stock_quantity` int NOT NULL,\n",
    "  `avarage_rating` decimal(3,1) DEFAULT NULL,\n",
    "  `price` decimal(10,2) DEFAULT NULL,\n",
    "  PRIMARY KEY (`book_id`)\n",
    ");\n",
    "\n",
    "\n",
    "-- creating table authorsbooks\n",
    "REATE TABLE `authorsbooks` (\n",
    "  `authors_id` int NOT NULL,\n",
    "  `book_id` int NOT NULL,\n",
    "  PRIMARY KEY (`authors_id`,`book_id`),\n",
    "  KEY `book_id` (`book_id`),\n",
    "  CONSTRAINT `authorsbooks_ibfk_1` FOREIGN KEY (`authors_id`) REFERENCES `authors` (`authors_id`),\n",
    "  CONSTRAINT `authorsbooks_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`)\n",
    ");\n",
    "\n",
    "\n",
    "-- creating genres table\n",
    "CREATE TABLE `genres` (\n",
    "  `genre_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `genre_name` varchar(50) NOT NULL,\n",
    "  PRIMARY KEY (`genre_id`)\n",
    ");\n",
    "\n",
    "\n",
    "-- creating genresbooks table\n",
    "CREATE TABLE `genresbooks` (\n",
    "  `book_id` int NOT NULL,\n",
    "  `genre_id` int NOT NULL,\n",
    "  PRIMARY KEY (`book_id`,`genre_id`),\n",
    "  KEY `genre_id` (`genre_id`),\n",
    "  CONSTRAINT `genresbooks_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),\n",
    "  CONSTRAINT `genresbooks_ibfk_2` FOREIGN KEY (`genre_id`) REFERENCES `genres` (`genre_id`)\n",
    ");\n",
    "\n",
    "\n",
    "-- creating customers table\n",
    "CREATE TABLE `customers` (\n",
    "  `customer_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `first_name` varchar(100) NOT NULL,\n",
    "  `last_name` varchar(100) NOT NULL,\n",
    "  `phone` varchar(20) NOT NULL,\n",
    "  `address` text NOT NULL,\n",
    "  `membership_status` varchar(20) NOT NULL,\n",
    "  `country` varchar(40) NOT NULL,\n",
    "  PRIMARY KEY (`customer_id`)\n",
    ");\n",
    "\n",
    "\n",
    " -- creating reviews table\n",
    "CREATE TABLE `reviews` (\n",
    "  `review_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `customer_id` int NOT NULL,\n",
    "  `book_id` int NOT NULL,\n",
    "  `rating` int NOT NULL,\n",
    "  `comments` text,\n",
    "  `review_date` date NOT NULL,\n",
    "  PRIMARY KEY (`review_id`),\n",
    "  KEY `reviews_ibfk_1` (`customer_id`),\n",
    "  KEY `reviews_ibfk_2` (`book_id`),\n",
    "  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),\n",
    "  CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),\n",
    "  CONSTRAINT `checkrating` CHECK (((`rating` >= 1) and (`rating` <= 10)))\n",
    "); \n",
    "\n",
    "\n",
    "-- creating loyalitypoints table\n",
    "CREATE TABLE `loyaltypoints` (\n",
    "  `loyalty_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `customer_id` int NOT NULL,\n",
    "  `points_earned` int NOT NULL,\n",
    "  `transaction_date` date NOT NULL,\n",
    "  PRIMARY KEY (`loyalty_id`),\n",
    "  KEY `loyaltypoints_ibfk_1` (`customer_id`),\n",
    "  CONSTRAINT `loyaltypoints_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)\n",
    ");\n",
    "\n",
    "\n",
    " -- creating table orders\n",
    "CREATE TABLE `orders` (\n",
    "  `order_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `customer_id` int NOT NULL,\n",
    "  `order_date` date NOT NULL,\n",
    "  `status` varchar(20) NOT NULL,\n",
    "  PRIMARY KEY (`order_id`),\n",
    "  KEY `orders_ibfk_1` (`customer_id`),\n",
    "  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)\n",
    ");\n",
    "\n",
    "\n",
    "-- creating table orderitems\n",
    "CREATE TABLE `orderitems` (\n",
    "  `order_item_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `order_id` int NOT NULL,\n",
    "  `book_id` int NOT NULL,\n",
    "  `quantity` int NOT NULL,\n",
    "  `subtotal` decimal(10,2) NOT NULL,\n",
    "  PRIMARY KEY (`order_item_id`),\n",
    "  KEY `orderitems_ibfk_1` (`book_id`),\n",
    "  KEY `orderitems_ibfk_2` (`order_id`),\n",
    "  CONSTRAINT `orderitems_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),\n",
    "  CONSTRAINT `orderitems_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`)\n",
    "); \n",
    "\n",
    "\n",
    "-- creating table payments\n",
    "CREATE TABLE `payments` (\n",
    "  `payment_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `order_id` int NOT NULL,\n",
    "  `payment_date` date NOT NULL,\n",
    "  `payment_method` varchar(50) NOT NULL DEFAULT 'PayPal, Credit Card, App Wallet',\n",
    "  `amount` decimal(10,2) NOT NULL,\n",
    "  `status` varchar(20) NOT NULL,\n",
    "  PRIMARY KEY (`payment_id`),\n",
    "  KEY `payments_ibfk_1` (`order_id`),\n",
    "  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),\n",
    "  CONSTRAINT `validstatus` CHECK ((`status` in (_utf8mb4'Pending',_utf8mb4'Completed',_utf8mb4'Failed',_utf8mb4'Refunded')))\n",
    "); \n",
    "\n",
    "-- creating table users\n",
    "CREATE TABLE `users` (\n",
    "  `user_id` int NOT NULL AUTO_INCREMENT,\n",
    "  `email` varchar(100) NOT NULL,\n",
    "  `password` varchar(255) DEFAULT NULL,\n",
    "  `salting` varbinary(16) DEFAULT NULL,\n",
    "  `last_login` date DEFAULT NULL,\n",
    "  `registration_date` date DEFAULT NULL,\n",
    "  `customer_id` int NOT NULL,\n",
    "  PRIMARY KEY (`user_id`),\n",
    "  KEY `customer_id` (`customer_id`),\n",
    "  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)\n",
    ");\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95704e2a-e4f9-475e-8d53-5a545d7cdf54",
   "metadata": {},
   "source": [
    "## EER Diagram:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a166bcc9-4050-4c87-ba4a-568018783ad7",
   "metadata": {},
   "source": [
    "![EER Diagram](images/realdealschema.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca987efd-dd98-4eb6-9023-6a3e0c660157",
   "metadata": {},
   "source": [
    "## Views and referencial tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52b07547-b771-4fee-8ec9-1cddae69b2ac",
   "metadata": {},
   "source": [
    "![views_and_reference_photo](images/views_and_reference.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}