CREATE DATABASE `ASSIGNMENT` DEFAULT CHARACTER SET latin1 COLLATE latin1_turkish_ci;
USE `ASSIGNMENT`;

CREATE TABLE `CUSTOMER` (
  `Customer_ID` int(11) NOT NULL,
  `Customer_Name` varchar(50) NOT NULL
);

CREATE TABLE `PRODUCT` (
  `Product_ID` int(11) NOT NULL,
  `Product_Name` varchar(50) NOT NULL,
  `Category` varchar(50) NOT NULL,
  `Price` double DEFAULT '0'
);

CREATE TABLE `SALE` (
  `Sale_ID` int(11) NOT NULL,
  `Product_ID` int(11) NOT NULL,
  `Customer_ID` int(11) NOT NULL,
  `Salesman_ID` int(11) NOT NULL,
  `Sale_Date` date NOT NULL,
  `Commission_Ratio` double DEFAULT '0'
);


CREATE TABLE `SALESMAN` (
  `Salesman_ID` int(11) NOT NULL,
  `Salesman_Name` varchar(50) NOT NULL,
  `Hire_Date` date NOT NULL,
  `Salary` double DEFAULT '0'
);


ALTER TABLE `CUSTOMER`
  ADD PRIMARY KEY (`Customer_ID`);

ALTER TABLE `PRODUCT`
  ADD PRIMARY KEY (`Product_ID`);

ALTER TABLE `SALE`
  ADD PRIMARY KEY (`Sale_ID`),
  ADD KEY `Product_ID` (`Product_ID`),
  ADD KEY `Customer_ID` (`Customer_ID`),
  ADD KEY `Salesman_ID` (`Salesman_ID`);

ALTER TABLE `SALESMAN`
  ADD PRIMARY KEY (`Salesman_ID`);

ALTER TABLE `SALE`
  ADD CONSTRAINT `sale_ibfk_1` FOREIGN KEY (`Product_ID`) REFERENCES `PRODUCT` (`Product_ID`),
  ADD CONSTRAINT `sale_ibfk_2` FOREIGN KEY (`Customer_ID`) REFERENCES `CUSTOMER` (`Customer_ID`),
  ADD CONSTRAINT `sale_ibfk_3` FOREIGN KEY (`Salesman_ID`) REFERENCES `SALESMAN` (`Salesman_ID`);