· 6 years ago · Jul 04, 2019, 01:14 PM
1CREATE DATABASE IF NOT EXISTS `myflixdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
2USE `myflixdb`;
3-- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86)
4--
5-- Host: localhost Database: myflixdb
6-- ------------------------------------------------------
7-- Server version 5.5.25a
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!40101 SET NAMES utf8 */;
13/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14/*!40103 SET TIME_ZONE='+00:00' */;
15/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20--
21-- Table structure for table `movies`
22--
23
24DROP TABLE IF EXISTS `movies`;
25/*!40101 SET @saved_cs_client = @@character_set_client */;
26/*!40101 SET character_set_client = utf8 */;
27CREATE TABLE `movies` (
28 `movie_id` int(11) NOT NULL AUTO_INCREMENT,
29 `title` varchar(300) DEFAULT NULL,
30 `director` varchar(150) DEFAULT NULL,
31 `year_released` year(4) DEFAULT NULL,
32 `category_id` int(11) DEFAULT NULL,
33 PRIMARY KEY (`movie_id`),
34 KEY `fk_Movies_Categories1` (`category_id`),
35 KEY `title_index` (`title`),
36 KEY `qw` (`title`),
37 CONSTRAINT `fk_Movies_Categories1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
38) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
39/*!40101 SET character_set_client = @saved_cs_client */;
40
41--
42-- Dumping data for table `movies`
43--
44
45LOCK TABLES `movies` WRITE;
46/*!40000 ALTER TABLE `movies` DISABLE KEYS */;
47INSERT INTO `movies` VALUES (1,'Pirates of the Caribean 4',' Rob Marshall',2011,1),(2,'Forgetting Sarah Marshal','Nicholas Stoller',2008,2),(3,'X-Men',NULL,2008,NULL),(4,'Code Name Black','Edgar Jimz',2010,NULL),(5,'Daddy\'s Little Girls',NULL,2007,8),(6,'Angels and Demons',NULL,2007,6),(7,'Davinci Code',NULL,2007,6),(9,'Honey mooners','John Schultz',2005,8),(16,'67% Guilty',NULL,2012,NULL);
48/*!40000 ALTER TABLE `movies` ENABLE KEYS */;
49UNLOCK TABLES;
50
51--
52-- Table structure for table `payments`
53--
54
55DROP TABLE IF EXISTS `payments`;
56/*!40101 SET @saved_cs_client = @@character_set_client */;
57/*!40101 SET character_set_client = utf8 */;
58CREATE TABLE `payments` (
59 `payment_id` int(11) NOT NULL AUTO_INCREMENT,
60 `membership_number` int(11) DEFAULT NULL,
61 `payment_date` date DEFAULT NULL,
62 `description` varchar(75) DEFAULT NULL,
63 `amount_paid` float DEFAULT NULL,
64 `external_reference_number` int(11) DEFAULT NULL,
65 PRIMARY KEY (`payment_id`),
66 KEY `fk_Payments_Members1` (`membership_number`),
67 CONSTRAINT `fk_Payments_Members1` FOREIGN KEY (`membership_number`) REFERENCES `members` (`membership_number`) ON DELETE NO ACTION ON UPDATE NO ACTION
68) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
69/*!40101 SET character_set_client = @saved_cs_client */;
70
71--
72-- Dumping data for table `payments`
73--
74
75LOCK TABLES `payments` WRITE;
76/*!40000 ALTER TABLE `payments` DISABLE KEYS */;
77INSERT INTO `payments` VALUES (1,1,'2012-07-23','Movie rental payment',2500,11),(2,1,'2012-07-25','Movie rental payment',2000,12),(3,3,'2012-07-30','Movie rental payment',6000,NULL);
78/*!40000 ALTER TABLE `payments` ENABLE KEYS */;
79UNLOCK TABLES;
80
81--
82-- Table structure for table `members`
83--
84
85DROP TABLE IF EXISTS `members`;
86/*!40101 SET @saved_cs_client = @@character_set_client */;
87/*!40101 SET character_set_client = utf8 */;
88CREATE TABLE `members` (
89 `membership_number` int(11) NOT NULL AUTO_INCREMENT,
90 `full_names` varchar(350) NOT NULL,
91 `gender` varchar(6) DEFAULT NULL,
92 `date_of_birth` date DEFAULT NULL,
93 `physical_address` varchar(255) DEFAULT NULL,
94 `postal_address` varchar(255) DEFAULT NULL,
95 `contact_number` varchar(75) DEFAULT NULL,
96 `email` varchar(255) DEFAULT NULL,
97 PRIMARY KEY (`membership_number`)
98) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
99/*!40101 SET character_set_client = @saved_cs_client */;
100
101--
102-- Dumping data for table `members`
103--
104
105LOCK TABLES `members` WRITE;
106/*!40000 ALTER TABLE `members` DISABLE KEYS */;
107INSERT INTO `members` VALUES (1,'Janet Jones','Female','1980-07-21','First Street Plot No 4','Private Bag','0759 253 542','janetjones@yagoo.cm'),(2,'Janet Smith Jones','Female','1980-06-23','Melrose 123',NULL,NULL,'jj@fstreet.com'),(3,'Robert Phil','Male','1989-07-12','3rd Street 34',NULL,'12345','rm@tstreet.com'),(4,'Gloria Williams','Female','1984-02-14','2nd Street 23',NULL,NULL,NULL);
108/*!40000 ALTER TABLE `members` ENABLE KEYS */;
109UNLOCK TABLES;
110
111--
112-- Temporary table structure for view `accounts_v_members`
113--
114
115DROP TABLE IF EXISTS `accounts_v_members`;
116/*!50001 DROP VIEW IF EXISTS `accounts_v_members`*/;
117SET @saved_cs_client = @@character_set_client;
118SET character_set_client = utf8;
119/*!50001 CREATE TABLE `accounts_v_members` (
120 `membership_number` int(11),
121 `full_names` varchar(350),
122 `gender` varchar(6)
123) ENGINE=MyISAM */;
124SET character_set_client = @saved_cs_client;
125
126--
127-- Temporary table structure for view `general_v_movie_rentals`
128--
129
130DROP TABLE IF EXISTS `general_v_movie_rentals`;
131/*!50001 DROP VIEW IF EXISTS `general_v_movie_rentals`*/;
132SET @saved_cs_client = @@character_set_client;
133SET character_set_client = utf8;
134/*!50001 CREATE TABLE `general_v_movie_rentals` (
135 `membership_number` int(11),
136 `full_names` varchar(350),
137 `title` varchar(300),
138 `transaction_date` date,
139 `return_date` date
140) ENGINE=MyISAM */;
141SET character_set_client = @saved_cs_client;
142
143--
144-- Table structure for table `categories`
145--
146
147DROP TABLE IF EXISTS `categories`;
148/*!40101 SET @saved_cs_client = @@character_set_client */;
149/*!40101 SET character_set_client = utf8 */;
150CREATE TABLE `categories` (
151 `category_id` int(11) NOT NULL AUTO_INCREMENT,
152 `category_name` varchar(150) DEFAULT NULL,
153 `remarks` varchar(500) DEFAULT NULL,
154 PRIMARY KEY (`category_id`)
155) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
156/*!40101 SET character_set_client = @saved_cs_client */;
157
158--
159-- Dumping data for table `categories`
160--
161
162LOCK TABLES `categories` WRITE;
163/*!40000 ALTER TABLE `categories` DISABLE KEYS */;
164INSERT INTO `categories` VALUES (1,'Comedy','Movies with humour'),(2,'Romantic','Love stories'),(3,'Epic','Story acient movies'),(4,'Horror',NULL),(5,'Science Fiction',NULL),(6,'Thriller',NULL),(7,'Action',NULL),(8,'Romantic Comedy',NULL);
165/*!40000 ALTER TABLE `categories` ENABLE KEYS */;
166UNLOCK TABLES;
167
168--
169-- Table structure for table `movierentals`
170--
171
172DROP TABLE IF EXISTS `movierentals`;
173/*!40101 SET @saved_cs_client = @@character_set_client */;
174/*!40101 SET character_set_client = utf8 */;
175CREATE TABLE `movierentals` (
176 `reference_number` int(11) NOT NULL AUTO_INCREMENT,
177 `transaction_date` date DEFAULT NULL,
178 `return_date` date DEFAULT NULL,
179 `membership_number` int(11) DEFAULT NULL,
180 `movie_id` int(11) DEFAULT NULL,
181 `movie_returned` bit(1) DEFAULT b'0',
182 PRIMARY KEY (`reference_number`),
183 KEY `fk_MovieRentals_Members1` (`membership_number`),
184 KEY `fk_MovieRentals_Movies1` (`movie_id`),
185 CONSTRAINT `fk_MovieRentals_Members1` FOREIGN KEY (`membership_number`) REFERENCES `members` (`membership_number`) ON DELETE NO ACTION ON UPDATE NO ACTION,
186 CONSTRAINT `fk_MovieRentals_Movies1` FOREIGN KEY (`movie_id`) REFERENCES `movies` (`movie_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
187) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
188/*!40101 SET character_set_client = @saved_cs_client */;
189
190--
191-- Dumping data for table `movierentals`
192--
193
194LOCK TABLES `movierentals` WRITE;
195/*!40000 ALTER TABLE `movierentals` DISABLE KEYS */;
196INSERT INTO `movierentals` VALUES (11,'2012-06-20',NULL,1,1,'\0'),(12,'2012-06-22','2012-06-25',1,2,'\0'),(13,'2012-06-22','2012-06-25',3,2,'\0'),(14,'2012-06-21','2012-06-24',2,2,'\0'),(15,'2012-06-23',NULL,3,3,'\0');
197/*!40000 ALTER TABLE `movierentals` ENABLE KEYS */;
198UNLOCK TABLES;
199
200--
201-- Final view structure for view `accounts_v_members`
202--
203
204/*!50001 DROP TABLE IF EXISTS `accounts_v_members`*/;
205/*!50001 DROP VIEW IF EXISTS `accounts_v_members`*/;
206/*!50001 SET @saved_cs_client = @@character_set_client */;
207/*!50001 SET @saved_cs_results = @@character_set_results */;
208/*!50001 SET @saved_col_connection = @@collation_connection */;
209/*!50001 SET character_set_client = utf8 */;
210/*!50001 SET character_set_results = utf8 */;
211/*!50001 SET collation_connection = utf8_general_ci */;
212/*!50001 CREATE ALGORITHM=UNDEFINED */
213/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
214/*!50001 VIEW `accounts_v_members` AS select `members`.`membership_number` AS `membership_number`,`members`.`full_names` AS `full_names`,`members`.`gender` AS `gender` from `members` */;
215/*!50001 SET character_set_client = @saved_cs_client */;
216/*!50001 SET character_set_results = @saved_cs_results */;
217/*!50001 SET collation_connection = @saved_col_connection */;
218
219--
220-- Final view structure for view `general_v_movie_rentals`
221--
222
223/*!50001 DROP TABLE IF EXISTS `general_v_movie_rentals`*/;
224/*!50001 DROP VIEW IF EXISTS `general_v_movie_rentals`*/;
225/*!50001 SET @saved_cs_client = @@character_set_client */;
226/*!50001 SET @saved_cs_results = @@character_set_results */;
227/*!50001 SET @saved_col_connection = @@collation_connection */;
228/*!50001 SET character_set_client = utf8 */;
229/*!50001 SET character_set_results = utf8 */;
230/*!50001 SET collation_connection = utf8_general_ci */;
231/*!50001 CREATE ALGORITHM=UNDEFINED */
232/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
233/*!50001 VIEW `general_v_movie_rentals` AS select `mb`.`membership_number` AS `membership_number`,`mb`.`full_names` AS `full_names`,`mo`.`title` AS `title`,`mr`.`transaction_date` AS `transaction_date`,`mr`.`return_date` AS `return_date` from ((`movierentals` `mr` join `members` `mb` on((`mr`.`membership_number` = `mb`.`membership_number`))) join `movies` `mo` on((`mr`.`movie_id` = `mo`.`movie_id`))) */;
234/*!50001 SET character_set_client = @saved_cs_client */;
235/*!50001 SET character_set_results = @saved_cs_results */;
236/*!50001 SET collation_connection = @saved_col_connection */;
237/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
238
239/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
240/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
241/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
242/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
243/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
244/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
245/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
246
247-- Dump completed on 2012-08-07 18:37:36