START TRANSACTION;

CREATE DATABASE  IF NOT EXISTS `VideoRental_db`;
USE `VideoRental_db`;

DROP TABLE IF EXISTS `detailrental`;
DROP TABLE IF EXISTS `rental`;
DROP TABLE IF EXISTS `video`;
DROP TABLE IF EXISTS `movie`;
DROP TABLE IF EXISTS `price`;
DROP TABLE IF EXISTS `membership`;

CREATE TABLE `membership` (
  `MEM_NUM` decimal(8,0) NOT NULL,
  `MEM_FNAME` varchar(30) NOT NULL,
  `MEM_LNAME` varchar(30) NOT NULL,
  `MEM_STREET` varchar(120) DEFAULT NULL,
  `MEM_CITY` varchar(50) DEFAULT NULL,
  `MEM_STATE` char(2) DEFAULT NULL,
  `MEM_ZIP` char(5) DEFAULT NULL,
  `MEM_BALANCE` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`MEM_NUM`)
);

CREATE TABLE `price` (
  `PRICE_CODE` decimal(2,0) NOT NULL,
  `PRICE_DESCRIPTION` varchar(20) NOT NULL,
  `PRICE_RENTFEE` decimal(5,2) DEFAULT NULL,
  `PRICE_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`PRICE_CODE`)
);

CREATE TABLE `movie` (
  `MOVIE_NUM` decimal(8,0) NOT NULL,
  `MOVIE_TITLE` varchar(75) NOT NULL,
  `MOVIE_YEAR` decimal(4,0) DEFAULT NULL,
  `MOVIE_COST` decimal(5,2) DEFAULT NULL,
  `MOVIE_GENRE` varchar(50) DEFAULT NULL,
  `PRICE_CODE` decimal(2,0) DEFAULT NULL,
  PRIMARY KEY (`MOVIE_NUM`),
  KEY `PRICE_CODE` (`PRICE_CODE`),
  CONSTRAINT `movie_ibfk_1` FOREIGN KEY (`PRICE_CODE`) REFERENCES `price` (`PRICE_CODE`)
);

CREATE TABLE `video` (
  `VID_NUM` decimal(8,0) NOT NULL,
  `VID_INDATE` date DEFAULT NULL,
  `MOVIE_NUM` decimal(8,0) DEFAULT NULL,
  PRIMARY KEY (`VID_NUM`),
  KEY `MOVIE_NUM` (`MOVIE_NUM`),
  CONSTRAINT `video_ibfk_1` FOREIGN KEY (`MOVIE_NUM`) REFERENCES `movie` (`MOVIE_NUM`)
);

CREATE TABLE `rental` (
  `RENT_NUM` decimal(8,0) NOT NULL,
  `RENT_DATE` date DEFAULT NULL,
  `MEM_NUM` decimal(8,0) DEFAULT NULL,
  PRIMARY KEY (`RENT_NUM`),
  KEY `MEM_NUM` (`MEM_NUM`),
  CONSTRAINT `rental_ibfk_1` FOREIGN KEY (`MEM_NUM`) REFERENCES `membership` (`MEM_NUM`)
);

CREATE TABLE `detailrental` (
  `RENT_NUM` decimal(8,0) NOT NULL,
  `VID_NUM` decimal(8,0) NOT NULL,
  `DETAIL_FEE` decimal(5,2) DEFAULT NULL,
  `DETAIL_DUEDATE` date DEFAULT NULL,
  `DETAIL_RETURNDATE` date DEFAULT NULL,
  `DETAIL_DAILYLATEFEE` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`RENT_NUM`,`VID_NUM`),
  KEY `VID_NUM` (`VID_NUM`),
  CONSTRAINT `detailrental_ibfk_1` FOREIGN KEY (`RENT_NUM`) REFERENCES `rental` (`RENT_NUM`),
  CONSTRAINT `detailrental_ibfk_2` FOREIGN KEY (`VID_NUM`) REFERENCES `video` (`VID_NUM`)
);

START TRANSACTION;

INSERT INTO `membership` VALUES (102,'TAMI','DAWSON','2632 TAKLI CIRCLE','NORENE','TN','37136',11.00),(103,'CURT','KNIGHT','4025 CORNELL COURT','FLATGAP','KY','41219',6.00),(104,'JAMAL','MELENDEZ','788 EAST 145TH AVENUE','QUEBECK','TN','38579',0.00),(105,'IVA','MCCLAIN','6045 MUSKET BALL CIRCLE','SUMMIT','KY','42783',15.00),(106,'MIRANDA','PARKS','4469 MAXWELL PLACE','GERMANTOWN','TN','38183',0.00),(107,'ROSARIO','ELLIOTT','7578 DANNER AVENUE','COLUMBIA','TN','38402',5.00),(108,'MATTIE','GUY','4390 EVERGREEN STREET','LILY','KY','40740',0.00),(109,'CLINT','OCHOA','1711 ELM STREET','GREENEVILLE','TN','37745',10.00),(110,'LEWIS','ROSALES','4524 SOUTHWIND CIRCLE','COUNCE','TN','38326',0.00),(111,'STACY','MANN','2789 EAST COOK AVENUE','MURFREESBORO','TN','37132',8.00),(112,'LUIS','TRUJILLO','7267 MELVIN AVENUE','HEISKELL','TN','37754',3.00),(113,'MINNIE','GONZALES','6430 VASILI DRIVE','WILLISTON','TN','38076',0.00);
INSERT INTO `price` VALUES (1,'Standard',3.00,1.00),(2,'New Release',4.50,3.00),(3,'Discount',2.50,1.00),(4,'Weekly Special',2.00,0.50);
INSERT INTO `movie` VALUES (1234,'The Cesar Family Christmas',2014,39.95,'FAMILY',2),(1235,'Smokey Mountain Wildlife',2011,59.95,'ACTION',3),(1236,'Richard Goodhope',2015,59.95,'DRAMA',2),(1237,'Beatnik Fever',2014,29.95,'COMEDY',2),(1238,'Constant Companion',2015,89.95,'DRAMA',NULL),(1239,'Where Hope Dies',2005,25.49,'DRAMA',3),(1245,'Time to Burn',2015,45.49,'ACTION',3),(1246,'What He Doesn\'t Know',2013,58.29,'COMEDY',1);
INSERT INTO `video` VALUES (34341,'2014-01-22',1235),(34342,'2014-01-22',1235),(34366,'2016-03-02',1236),(34367,'2016-03-02',1236),(34368,'2016-03-02',1236),(34369,'2016-03-02',1236),(44392,'2015-10-21',1237),(44397,'2015-10-21',1237),(54321,'2015-06-18',1234),(54324,'2015-06-18',1234),(54325,'2015-06-18',1234),(59237,'2016-02-14',1237),(61353,'2013-01-28',1245),(61354,'2013-01-28',1245),(61367,'2015-07-30',1246),(61369,'2015-07-30',1246),(61388,'2014-01-25',1239);
INSERT INTO `rental` VALUES (1001,'2016-03-01',103),(1002,'2016-03-01',105),(1003,'2016-03-02',102),(1004,'2016-03-02',110),(1005,'2016-03-02',111),(1006,'2016-03-02',107),(1007,'2016-03-02',104),(1008,'2016-03-03',105),(1009,'2016-03-03',111);
INSERT INTO `detailrental` VALUES (1001,34342,2.00,'2016-03-04','2016-03-02',NULL),(1001,34366,3.50,'2016-03-04','2016-03-02',3.00),(1001,61353,2.00,'2016-03-04','2016-03-03',1.00),(1002,59237,3.50,'2016-03-04','2016-03-04',3.00),(1003,54325,3.50,'2016-03-04','2016-03-09',3.00),(1003,61369,2.00,'2016-03-06','2016-03-09',1.00),(1003,61388,0.00,'2016-03-06','2016-03-09',1.00),(1004,34341,2.00,'2016-03-07','2016-03-07',1.00),(1004,34367,3.50,'2016-03-05','2016-03-07',3.00),(1004,44392,3.50,'2016-03-05','2016-03-07',3.00),(1005,34342,2.00,'2016-03-07','2016-03-05',1.00),(1005,44397,3.50,'2016-03-05','2016-03-05',3.00),(1006,34366,3.50,'2016-03-05','2016-03-04',3.00),(1006,61367,2.00,'2016-03-07',NULL,1.00),(1007,34368,3.50,'2016-03-05',NULL,3.00),(1008,34369,3.50,'2016-03-05','2016-03-05',3.00),(1009,54324,3.50,'2016-03-05',NULL,3.00);

COMMIT;