Skip to content

Instantly share code, notes, and snippets.

@gozeloglu

gozeloglu/tables.sql

Last active June 2, 2020 12:29
Show Gist options
  • Save gozeloglu/e42a194be028e9da0f4cdb76dc8b8be8 to your computer and use it in GitHub Desktop.
Save gozeloglu/e42a194be028e9da0f4cdb76dc8b8be8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. gozeloglu revised this gist Jun 2, 2020. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -128,15 +128,15 @@ CREATE TABLE `city_country` (
    `City` varchar(45) NOT NULL,
    `Country` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`City`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `postalcode_city` (
    `City` varchar(45) DEFAULT NULL,
    `PostalCode` varchar(45) NOT NULL,
    PRIMARY KEY (`PostalCode`),
    KEY `postalcode_city_city` (`City`),
    CONSTRAINT `postalcode_city_city` FOREIGN KEY (`City`) REFERENCES `city_country` (`City`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `address` (
    `addressid` int(11) NOT NULL AUTO_INCREMENT,
    @@ -146,7 +146,7 @@ CREATE TABLE `address` (
    PRIMARY KEY (`addressid`),
    KEY `Address_PostalCode` (`PostalCode`),
    CONSTRAINT `Address_PostalCode` FOREIGN KEY (`PostalCode`) REFERENCES `postalcode_city` (`PostalCode`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `orders` (
    `OrderId` int(11) NOT NULL AUTO_INCREMENT,
    @@ -165,7 +165,7 @@ CREATE TABLE `orders` (
    CONSTRAINT `orders_addressid` FOREIGN KEY (`AddressId`) REFERENCES `address` (`addressid`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `orders_couponcode` FOREIGN KEY (`CampaignId`) REFERENCES `campaign` (`CampaignId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `orders_customerid` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `purchaseddetailedinfo` (
    `TrackingNumber` int(11) NOT NULL AUTO_INCREMENT,
    @@ -175,7 +175,7 @@ CREATE TABLE `purchaseddetailedinfo` (
    PRIMARY KEY (`TrackingNumber`),
    KEY `purchaseddetailedinfo_companyid` (`ShippingCompanyId`),
    CONSTRAINT `purchaseddetailedinfo_companyid` FOREIGN KEY (`ShippingCompanyId`) REFERENCES `shippingcompany` (`ShippingCompanyId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `contains` (
    `OrderId` int(11) NOT NULL,
    @@ -190,4 +190,4 @@ CREATE TABLE `contains` (
    CONSTRAINT `contains_OrderId` FOREIGN KEY (`OrderId`) REFERENCES `orders` (`OrderId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `contains_TrackingNumber` FOREIGN KEY (`TrackingNumber`) REFERENCES `purchaseddetailedinfo` (`TrackingNumber`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `contains_bookid` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;
  2. gozeloglu revised this gist Jun 2, 2020. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -12,7 +12,7 @@ CREATE TABLE `book` (
    `Real_Isbn` bigint(13) DEFAULT NULL,
    `InDiscount` int(2) DEFAULT 0,
    PRIMARY KEY (`ISBN`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `customer` (
    `CustomerId` int(11) NOT NULL AUTO_INCREMENT,
    @@ -26,22 +26,22 @@ CREATE TABLE `customer` (
    `RegistrationTime` datetime(6) DEFAULT current_timestamp(6),
    `Status` tinyint(1) DEFAULT 1,
    PRIMARY KEY (`CustomerId`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `shippingcompany` (
    `ShippingCompanyId` int(11) NOT NULL AUTO_INCREMENT,
    `Price` float DEFAULT NULL,
    `CompanyName` varchar(45) DEFAULT 'Null',
    `Website` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`ShippingCompanyId`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `card` (
    `CardNo` char(16) NOT NULL,
    `OwnerName` varchar(45) DEFAULT 'Null',
    `OwnerSurname` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`CardNo`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `campaign` (
    `CampaignId` int(11) NOT NULL AUTO_INCREMENT,
    @@ -51,7 +51,7 @@ CREATE TABLE `campaign` (
    `EndDate` date NOT NULL,
    `ParticipantQuantity` int(11) NOT NULL,
    PRIMARY KEY (`CampaignId`)
    )DEFAULT CHARSET=utf8mb4
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `customeraddress` (
    `CustomerId` int(11) NOT NULL,
  3. gozeloglu created this gist Jun 2, 2020.
    193 changes: 193 additions & 0 deletions tables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,193 @@
    CREATE TABLE `book` (
    `ISBN` int(11) NOT NULL AUTO_INCREMENT,
    `BookName` varchar(45) NOT NULL,
    `Author` varchar(45) NOT NULL,
    `Description` varchar(1024) DEFAULT 'Null',
    `Category` varchar(45) NOT NULL,
    `SubCategory` varchar(45) DEFAULT NULL,
    `InHotlist` tinyint(1) DEFAULT 0,
    `BookImage` varchar(256) NOT NULL,
    `ReleasedTime` timestamp(6) NULL DEFAULT current_timestamp(6),
    `Quantity` int(11) DEFAULT NULL,
    `Real_Isbn` bigint(13) DEFAULT NULL,
    `InDiscount` int(2) DEFAULT 0,
    PRIMARY KEY (`ISBN`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `customer` (
    `CustomerId` int(11) NOT NULL AUTO_INCREMENT,
    `Name` varchar(45) DEFAULT NULL,
    `Surname` varchar(45) DEFAULT NULL,
    `Username` varchar(45) NOT NULL,
    `Password` varchar(256) NOT NULL,
    `DateOfBirth` date DEFAULT NULL,
    `PhoneNumber` varchar(45) DEFAULT NULL,
    `IsAdmin` tinyint(1) DEFAULT 0,
    `RegistrationTime` datetime(6) DEFAULT current_timestamp(6),
    `Status` tinyint(1) DEFAULT 1,
    PRIMARY KEY (`CustomerId`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `shippingcompany` (
    `ShippingCompanyId` int(11) NOT NULL AUTO_INCREMENT,
    `Price` float DEFAULT NULL,
    `CompanyName` varchar(45) DEFAULT 'Null',
    `Website` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`ShippingCompanyId`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `card` (
    `CardNo` char(16) NOT NULL,
    `OwnerName` varchar(45) DEFAULT 'Null',
    `OwnerSurname` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`CardNo`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `campaign` (
    `CampaignId` int(11) NOT NULL AUTO_INCREMENT,
    `CouponCode` varchar(16) DEFAULT NULL,
    `CouponDiscount` int(11) NOT NULL,
    `CampaignName` varchar(128) NOT NULL,
    `EndDate` date NOT NULL,
    `ParticipantQuantity` int(11) NOT NULL,
    PRIMARY KEY (`CampaignId`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `customeraddress` (
    `CustomerId` int(11) NOT NULL,
    `addressid` int(11) NOT NULL,
    PRIMARY KEY (`addressid`),
    KEY `address_cid` (`CustomerId`),
    CONSTRAINT `address_cid` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `card_used_by` (
    `CustomerId` int(11) NOT NULL,
    `CardNo` char(16) NOT NULL,
    PRIMARY KEY (`CustomerId`,`CardNo`),
    KEY `CardUsed_CardNo` (`CardNo`),
    KEY `CardUsed_CustomerId` (`CustomerId`),
    CONSTRAINT `CardUsed_CardNo` FOREIGN KEY (`CardNo`) REFERENCES `card` (`CardNo`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `CardUsed_CustomerId` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `comment` (
    `CommentId` int(11) NOT NULL AUTO_INCREMENT,
    `ISBN` int(11) NOT NULL,
    `CustomerId` int(11) NOT NULL,
    `CommentTime` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
    `CommentText` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`CommentId`),
    KEY `comment_CustomerId` (`CustomerId`),
    KEY `comment_BookId` (`ISBN`),
    CONSTRAINT `comment_BookId` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `comment_CustomerId` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `price` (
    `ISBN` int(11) NOT NULL,
    `Price` float DEFAULT 0,
    `DateTime` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
    PRIMARY KEY (`DateTime`),
    KEY `price_bookid` (`ISBN`),
    CONSTRAINT `price_bookid` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `search` (
    `CustomerId` int(11) NOT NULL,
    `SearchTime` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
    `SearchedWord` varchar(64) DEFAULT NULL,
    `SearchId` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`SearchId`),
    KEY `search_CustomerId` (`CustomerId`),
    CONSTRAINT `search_CustomerId` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `vote` (
    `CustomerId` int(11) NOT NULL,
    `ISBN` int(11) NOT NULL,
    `VoteNumber` int(11) DEFAULT 0,
    PRIMARY KEY (`CustomerId`,`ISBN`),
    KEY `vote_customerid` (`CustomerId`),
    KEY `vote_isbn` (`ISBN`),
    CONSTRAINT `vote_customerid` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `vote_isbn` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `wish_list` (
    `CustomerId` int(11) NOT NULL,
    `ISBN` int(11) NOT NULL,
    PRIMARY KEY (`CustomerId`,`ISBN`),
    KEY `wish_list_customerid` (`CustomerId`),
    KEY `wish_list_isbn` (`ISBN`),
    CONSTRAINT `wish_list_customerid` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `wish_list_isbn` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4;

    CREATE TABLE `city_country` (
    `City` varchar(45) NOT NULL,
    `Country` varchar(45) DEFAULT 'Null',
    PRIMARY KEY (`City`)
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `postalcode_city` (
    `City` varchar(45) DEFAULT NULL,
    `PostalCode` varchar(45) NOT NULL,
    PRIMARY KEY (`PostalCode`),
    KEY `postalcode_city_city` (`City`),
    CONSTRAINT `postalcode_city_city` FOREIGN KEY (`City`) REFERENCES `city_country` (`City`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `address` (
    `addressid` int(11) NOT NULL AUTO_INCREMENT,
    `AddressLine` varchar(45) DEFAULT 'Null',
    `PostalCode` varchar(45) DEFAULT NULL,
    `AddressTitle` varchar(45) NOT NULL,
    PRIMARY KEY (`addressid`),
    KEY `Address_PostalCode` (`PostalCode`),
    CONSTRAINT `Address_PostalCode` FOREIGN KEY (`PostalCode`) REFERENCES `postalcode_city` (`PostalCode`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `orders` (
    `OrderId` int(11) NOT NULL AUTO_INCREMENT,
    `DateTime` date DEFAULT current_timestamp(),
    `CardNo` char(16) NOT NULL,
    `CustomerId` int(11) NOT NULL,
    `AddressId` int(11) NOT NULL,
    `TotalAmount` float NOT NULL,
    `CampaignId` int(11) DEFAULT NULL,
    PRIMARY KEY (`OrderId`),
    KEY `orders_cardno` (`CardNo`),
    KEY `orders_addressid` (`AddressId`),
    KEY `orders_couponcode` (`CampaignId`),
    KEY `orders_customerid` (`CustomerId`),
    CONSTRAINT `orders_cardno` FOREIGN KEY (`CardNo`) REFERENCES `card` (`CardNo`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `orders_addressid` FOREIGN KEY (`AddressId`) REFERENCES `address` (`addressid`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `orders_couponcode` FOREIGN KEY (`CampaignId`) REFERENCES `campaign` (`CampaignId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `orders_customerid` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `purchaseddetailedinfo` (
    `TrackingNumber` int(11) NOT NULL AUTO_INCREMENT,
    `ShippingCompanyId` int(11) NOT NULL,
    `DeliveryTime` date DEFAULT NULL,
    `Status` varchar(45) DEFAULT 'Waiting Confirmation',
    PRIMARY KEY (`TrackingNumber`),
    KEY `purchaseddetailedinfo_companyid` (`ShippingCompanyId`),
    CONSTRAINT `purchaseddetailedinfo_companyid` FOREIGN KEY (`ShippingCompanyId`) REFERENCES `shippingcompany` (`ShippingCompanyId`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4

    CREATE TABLE `contains` (
    `OrderId` int(11) NOT NULL,
    `ISBN` int(11) NOT NULL,
    `TrackingNumber` int(11) NOT NULL,
    `Quantity` int(11) DEFAULT NULL,
    `Status` tinyint(1) DEFAULT 1,
    PRIMARY KEY (`OrderId`,`ISBN`,`TrackingNumber`),
    KEY `contains_OrderId` (`OrderId`),
    KEY `contains_TrackingNumber` (`TrackingNumber`),
    KEY `contains_bookid` (`ISBN`),
    CONSTRAINT `contains_OrderId` FOREIGN KEY (`OrderId`) REFERENCES `orders` (`OrderId`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `contains_TrackingNumber` FOREIGN KEY (`TrackingNumber`) REFERENCES `purchaseddetailedinfo` (`TrackingNumber`) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `contains_bookid` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE NO ACTION
    )DEFAULT CHARSET=utf8mb4