NEW TABLES

tables.sql

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;