Question

A database used for a toy store that keeps track of the inventory and purchase orders from the store. All clients will have their membership ID card to shop. This database will help the store to keep track of their orders. It will also help the store to know how many products are available in the inventory. It is necessary to have a membership to purchase a product. The customer can also bring coupons to get discount.

Key attributes:
Salesman: salesmanID, name, lastName, telephone, sex, birthdate, direction, email,           salaryPerHour, hourWork.
Customer: customerID, name, lastName, direction, telephone.
Coupon: Coupon_Code, discountedPrice.
Order: OrderID, SalesmanID, MembershipD., Orderdate.
ORDER AND PRODUCT WILL HAVE A MIX TABLE: OrderID, ProductID.
Product: ProductID, name, price, brand, description, quantity
Membership: MembershipID, CsitomerID. OrderNo
Store: StoreIDdirection, telephone, email, owner
NOTE: direction is a summarized form of street name, city, and zip code.

Create a simple SQL database

Use Cases:
Check product quantity
Salesman works at a store
Store stores products
Once the salesman can check the productID, he can check the quantity.
List all orders done by a specific customer
Salesman can view customer information using their unique membership ID card.
Each order will have membership ID as a foreign key, which helps the salesman pull out all the orders related to a specific membership.
Check who sold a specific product
A product will be part of an order
Each order will have a foreign key representing the person who placed the order and the salesman who served the customer in a specific order.
Which day a specific product was sold
Salesman checks orders and their date.
Each order will have products.
Once the salesman know the order containing a specific product, he can pull out the date the order was made.

Answer 1

    CREATE DATABASE

    first you need to create database for your toy store by using following command:

            CREATE DATABASE TOYSTORE;

       2. CREATE TABLES

    Second you need to create table to store all information about part of your organization in the database.
  
        Salesman table:

            CREATE TABLE SALESMAN(salesmanID INT NOT NULL,name VARCHAR(20) NOT NULL,

            lastName VARCHAR(20) NOT NULL, telephone INT NOT NULL, sex VARCHAR(10) NOT

           NULL, birthdate VARCHAR(20) NOT NULL, direction VARCHAR(10) NOT NULL, email

           VARCHAR(20) NOT NULL, salaryPerHour INT NOT NULL, hourWork INT NOT NULL,

           PRIMARY KEY (salesmanID));

        Customer table:

            CREATE TABLE CUSTOMER(customerID INT NOT NULL,name VARCHAR(20) NOT NULL,

            lastName VARCHAR(20) NOT NULL, direction VARCHAR(10) NOT NULL, telephone INT

           NOT NULL,

           PRIMARY KEY (customerID));

        Coupon table:

           CREATE TABLE COUPON(Coupon_code VARCHAR(20) NOT NULL,

            discountedPrice DECIMAL NOT NULL,

           PRIMARY KEY (Coupon_code));

        Order table:

            CREATE TABLE ORDER(OrderID INT NOT NULL, salesmanID INT NOT NULL,

            membershipID INT NOT NULL, productID INT NOT NULL, Orderdate VARCHAR(20) NOT NULL,

           PRIMARY KEY (OrderID),
         
           FOREIGN KEY (productID) REFERENCES Persons(productID)

           FOREIGN KEY (salesmanID) REFERENCES Persons(salesmanID)

           FOREIGN KEY (membershipID) REFERENCES Persons(membershipID));

        Product table:

           CREATE TABLE PRODUCT(productID INT NOT NULL,OrderID INT NOT NULL, name VARCHAR(20) NOT NULL,

            price DECIMAL NOT NULL, brand VARCHAR(20) NOT NULL, description VARCHAR(50) NOT NULL,
          
           quantity INT NOT NULL,
          
           FOREIGN KEY (OrderID) REFERENCES Persons(OrderID))

           PRIMARY KEY (productID));
         
       Membership table:

           CREATE TABLE MEMBERSHIP(membershipID INT NOT NULL, customerID INT NOT NULL,

           OrderNo VARCHAR(20) NOT NULL,

           PRIMARY KEY (membershipID),

           FOREIGN KEY (customerID) REFERENCES Persons(customerID));
         
      
       Store table:

           CREATE TABLE STORE(storeID INT NOT NULL,direction VARCHAR(20) NOT NULL,

            telephone INT NOT NULL, email VARCHAR(20) NOT NULL, owner VARCHAR(50) NOT NULL,
          

           PRIMARY KEY (storeID));
         
         
       Direction table:

           CREATE TABLE DIRECTION(directionID INT NOT NULL,name VARCHAR(20) NOT NULL,

            city VARCHAR(20) NOT NULL, zipcode INT NOT NULL,
          
           PRIMARY KEY (directionID));
         
         
       USECASES SOLUTION:
      
       1) CHECK PRODUCT QUANTITY:
       SELECT SUM(quantity) as "Total Quantity"
       FROM PRODUCT
      
      
       2)SALESMAN WORKS AT A STORE:
          SELECT COUNT(*) FROM SALESMAN
      
       3) STORE STORES PRODUCTS:
          SELECT COUNT(*) FROM PRODUCT
      
       4) Once the salesman can check the productID, he can check the quantity:
          SELECT productID, sum(quantity) as total_quantity
          FROM PRODUCT
      
       5)List all orders done by a specific customer:
          SELECT * FROM CUSTOMER a, ORDER b
          WHERE a.customerID=b.OrderID
          AND b.customerID='02'
      
       6)Salesman can view customer information using their unique membership ID card.
          SELECT * FROM MEMBERSHIP a, CUSTOMER b
          WHERE a.membershipID=b.customerID
          AND a.membershipID='02'
      
       7)Each order will have membership ID as a foreign key, which helps the salesman pull out all the orders related to a specific membership:
          SELECT * FROM ORDER a, MEMBERSHIP b
          WHERE a.OrderID=b.membershipID
          AND a.membershipID='02'
      
       8) Check who sold a specific product:
          SELECT * FROM ORDERS WHERE ID IN
         (SELECT OrderID FROM ORDER
          WHERE productID = 2
          GROUP BY OrderID
          HAVING COUNT(OrderID) = 1
         )
      
       9) A product will be part of an order:
       SELECT * FROM PRODUCT a, ORDER b
          WHERE a.productID=b.OrderID
      
       10) Each order will have a foreign key representing the person who placed the order and the salesman who served the customer in a specific order:
          SELECT o.OrderID, o.membershipID, o.salesmanID
          FROM ORDER o
          WHERE o.membershipID=o.salesmanID
      
       11)Which day a specific product was sold
          SELECT * FROM PRODUCT a, ORDER b
          WHERE a.productID=b.OrderID
          AND b.Orderdate='dd/mm/yyyy'
      
       12) Salesman checks orders and their date:
          SELECT OrderID, Orderdate FROM ORDER
      
       13)Each order will have products:
          SELECT * FROM ORDER a, PRODUCT b
          WHERE a.OrderID=b.productID
      
       14) Once the salesman know the order containing a specific product, he can pull out the date the order was made.
          SELECT o.Orderdate, o.productID, o.OrderID
          FROM ORDER o
          WHERE o.OrderID=o.productID