hf0RbfMY

1/*
2
3SQLyog Community v8.4
4MySQL - 5.1.46-community : Database - mobilefirst_training
5
6*********************************************************************
7
8*/
9
10
11
12/*!40101 SET NAMES utf8 */;
13
14
15
16/*!40101 SET SQL_MODE=''*/;
17
18
19
20/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
21
22/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
23
24/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
25
26/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
27
28CREATE DATABASE /*!32312 IF NOT EXISTS*/`mobilefirst_training` /*!40100 DEFAULT CHARACTER SET latin1 */;
29
30
31
32USE `mobilefirst_training`;
33
34
35
36/*Table structure for table `accounts` */
37
38
39
40DROP TABLE IF EXISTS `accounts`;
41
42
43
44CREATE TABLE `accounts` (
45  `accountId` varchar(15) NOT NULL,
46  `userId` varchar(100) NOT NULL DEFAULT '',
47  `accountType` varchar(50) NOT NULL,
48  PRIMARY KEY (`accountId`),
49  KEY `FK_accounts_users` (`userId`),
50  CONSTRAINT `FK_accounts_users` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`) ON DELETE CASCADE
51) ENGINE=InnoDB DEFAULT CHARSET=utf8;
52
53
54
55/*Data for the table `accounts` */
56
57
58
59insert  into `accounts`(`accountId`,`userId`,`accountType`) values ('12345','bjones','Checking'),('54321','bjones','Savings');
60
61
62
63/*Table structure for table `accounttransactions` */
64
65
66
67DROP TABLE IF EXISTS `accounttransactions`;
68
69
70
71CREATE TABLE `accounttransactions` (
72  `transactionId` char(12) NOT NULL DEFAULT '',
73  `fromAccount` varchar(15) DEFAULT '',
74  `toAccount` varchar(15) DEFAULT NULL,
75  `transactionType` varchar(100) NOT NULL,
76  `transactionDate` datetime NOT NULL,
77  `transactionAmount` decimal(10,2) NOT NULL,
78  PRIMARY KEY (`transactionId`),
79  KEY `FK_accounttransactions_from` (`fromAccount`),
80  KEY `FK_accounttransactions_to` (`toAccount`),
81  CONSTRAINT `FK_accounttransactions_from` FOREIGN KEY (`fromAccount`) REFERENCES `accounts` (`accountId`) ON DELETE CASCADE,
82  CONSTRAINT `FK_accounttransactions_to` FOREIGN KEY (`toAccount`) REFERENCES `accounts` (`accountId`) ON DELETE CASCADE
83) ENGINE=InnoDB DEFAULT CHARSET=utf8;
84
85
86
87/*Data for the table `accounttransactions` */
88
89
90
91insert  into `accounttransactions`(`transactionId`,`fromAccount`,`toAccount`,`transactionType`,`transactionDate`,`transactionAmount`) values ('W06091500863','12345','54321','Funds Transfer','2009-03-11 11:08:39','180.00'),('W214122/5332','12345','54321','Funds Transfer','2009-03-04 10:35:24','150.00'),('W214122/5333',NULL,'54321','Deposit','2009-03-06 11:08:39','7500.00'),('W214122/5334',NULL,'12345','Deposit','2009-03-01 11:09:39','9050.00'),('W214122/5337','12345',NULL,'ATM Withdrawal','2009-03-07 11:09:39','130.00'),('W273218/4101',NULL,'54321','Accrued Interest','2009-03-01 11:09:39','10.08'),('W274158/5551','12345',NULL,'Check Withdrawal','2009-03-03 11:09:39','150.00');
92
93
94
95/*Table structure for table `users` */
96
97
98
99DROP TABLE IF EXISTS `users`;
100
101
102
103CREATE TABLE `users` (
104  `userId` varchar(100) NOT NULL,
105  `firstName` varchar(100) NOT NULL,
106  `lastName` varchar(100) NOT NULL,
107  `password` varchar(100) NOT NULL,
108  PRIMARY KEY (`userId`)
109) ENGINE=InnoDB DEFAULT CHARSET=utf8;
110
111
112
113/*Data for the table `users` */
114
115
116
117insert  into `users`(`userId`,`firstName`,`lastName`,`password`) values ('bjones','Brad','Jones','bjones');
118
119
120
121/*Table structure for table `weather` */
122
123
124
125DROP TABLE IF EXISTS `weather`;
126
127
128
129CREATE TABLE `weather` (
130  `city` text,
131  `identifier` text,
132  `summary` text
133) ENGINE=InnoDB DEFAULT CHARSET=latin1;
134
135
136
137/*Data for the table `weather` */
138
139
140
141insert  into `weather`(`city`,`identifier`,`summary`) values ('New York','2459115','New York City, which is geographically the largest city in the state and most populous in the United States, is known for its history as a gateway for immigration to the United States and its status as a financial, cultural, transportation, and manufacturing center. According to the U.S. Department of Commerce, it is also a destination of choice for many foreign visitors. Both state and city were named for the 17th century Duke of York, James Stuart, future James II and VII of England and Scotland.'),('Sydney','1105779','Sydney is the largest and most populous city in Australia and the state capital of New South Wales. Sydney is located on Australia\'s south-east coast of the Tasman Sea. With an approximate population of 4.5 million in the Sydney metropolitan area the city is the largest municipality in Oceania.[5]  Inhabitants of Sydney are called Sydneysiders, comprising of a cosmopolitan and international  population of people from many places around the world.'),('San Francisco','2487956','The City and County of San Francisco is the fourth most populous city in California and the 12th most populous city in the United States, with a 2008 estimated population of 808,977.[9]  The only consolidated city-county in California,[11]  it encompasses a land area of 46.7 square miles (121 km2)[12]  on the northern end of the San Francisco Peninsula, making it the second-most densely populated large city (greater than 200,000 population) in the United States.[13]  San Francisco is anchor to the 13th-largest  metropolitan area in the country, containing 4.3 million, and is the financial, cultural, and transportation center of the larger San Francisco Bay Area, a region of 7.4 million people.[14]  For the 2009-2010 season, the San'),('Christchurch','2348327','Christchurch is the largest city  in the South Island of New Zealand, and the country\'s second-largest urban area. It lies one third of the way down the South Island\'s east coast, just north of Banks Peninsula which itself, since 2006, lies within the formal limits of Christchurch.'),('Bankok','1208341','Bangkok is the capital, largest urban area and primary city of Thailand. Known in Thai as Krung Thep Mahanakhon , meaning \'city of angeles\' for short, it was a small trading post at the mouth of the Chao Phraya River during the Ayutthaya Kingdom. It came to the forefront of Siam when it was given the status as the capital city in 1768 after the burning of Ayutthaya. However, the current Rattanakosin Kingdom did not begin until 1782 when the capital was moved across the river by Rama I after the death of King Taksin. The Rattanakosin capital is now more formally called Phra Nakhon (Thai: ??????), pertaining to the ancient boundaries in the metropolis\' core and the name Bangkok now incorporates the urban build-up since the 18th century which has its own public administration and governor.');
142
143
144
145/* Procedure structure for procedure `getAccountTransactions` */
146
147
148
149/*!50003 DROP PROCEDURE IF EXISTS  `getAccountTransactions` */;
150
151
152
153DELIMITER $$
154
155
156
157/*!50003 CREATE DEFINER=`mobilefirst`@`localhost` PROCEDURE `getAccountTransactions`(in accountId varchar(15))
158BEGIN
159SELECT transactionId, fromAccount, toAccount, transactionDate, transactionAmount, transactionType
160FROM accounttransactions
161WHERE accounttransactions.fromAccount = accountId OR accounttransactions.toAccount = accountId
162ORDER BY transactionDate DESC
163LIMIT 20;
164END */$$
165
166DELIMITER ;
167
168
169
170/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
171
172/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
173
174/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
175
176/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;