mt3bdrPB

1-- phpMyAdmin SQL Dump
2-- version 4.2.11
3-- http://www.phpmyadmin.net
4--
5-- Host: 127.0.0.1
6-- Generation Time: Apr 22, 2015 at 04:45 PM
7-- Server version: 5.6.21
8-- PHP Version: 5.6.3
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8 */;
18
19--
20-- Database: `p&qdatabase`
21--
22
23-- --------------------------------------------------------
24
25--
26-- Table structure for table `accounts`
27--
28
29CREATE TABLE IF NOT EXISTS `accounts` (
30`accountNo` int(7) NOT NULL,
31  `accountType` varchar(6) NOT NULL,
32  `accountHolderFname` varchar(25) NOT NULL,
33  `accountHolderLname` varchar(25) NOT NULL,
34  `accountHolderDob` date NOT NULL,
35  `branchId` int(2) NOT NULL,
36  `phoneNo` varchar(11) NOT NULL,
37  `emailAddress` varchar(30) NOT NULL,
38  `postCode` varchar(8) NOT NULL
39) ENGINE=InnoDB AUTO_INCREMENT=6743529 DEFAULT CHARSET=latin1;
40
41--
42-- Dumping data for table `accounts`
43--
44
45INSERT INTO `accounts` (`accountNo`, `accountType`, `accountHolderFname`, `accountHolderLname`, `accountHolderDob`, `branchId`, `phoneNo`, `emailAddress`, `postCode`) VALUES
46(1011876, 'Credit', 'James', 'Smith', '1982-12-09', 1, '02079989298', 'jsmith@msn.com', 'W2 3JT'),
47(1023942, 'Charge', 'Steve', 'Anderson', '1954-03-11', 2, '02086598532', 'sanderson@yahoo.com', 'W1 5FL'),
48(1029384, 'Charge', 'Steve', 'Clarke', '1982-01-01', 3, '02074526783', 'sclarke@aol.com', 'W2 5JK'),
49(1057436, 'Charge', 'Alan', 'Smith', '1988-02-05', 1, '02075546632', 'asmith@yahoo.com', 'W2 9SS'),
50(1065865, 'Credit', 'Nathan', 'Pettreli', '1974-08-11', 2, '02079876324', 'npettreli@msn.com', 'E1 5DJ'),
51(1075269, 'Credit', 'Adam', 'Monroe', '1635-05-06', 1, '02076394127', 'amonroe@yahoo.co.uk', 'W2 1TR'),
52(1098274, 'Credit', 'Mohindar', 'Suresh', '1969-07-05', 3, '02075289641', 'msuresh@msn.co.uk', 'S8 9DH'),
53(1237846, 'Charge', 'Noah', 'Bennett', '1959-09-12', 3, '02034589726', 'nbennett@yahoo.com', 'N9 9ZX'),
54(1902837, 'Charge', 'Samwise', 'Gamgee', '1985-06-28', 3, '02089652147', 'sgamgee@yahoo.co.uk', 'W1 3DS'),
55(2987399, 'Credit', 'Jack', 'O''Neil', '1993-11-13', 2, '02074569873', 'joneil@outlook.com', 'E2 7NG'),
56(3897269, 'Credit', 'Issac', 'Mendez', '1978-12-12', 3, '02073695241', 'imendez@yahoo.com', 'W2 3JT'),
57(4890189, 'Credit', 'Eden', 'Hazard', '1991-01-07', 1, '02079856214', 'ehazard@chelsea.com', 'W2 4NS'),
58(6743528, 'Credit', 'Jose', 'Mourinho', '1963-01-26', 1, '02070077007', 'special1@chelsea.com', 'W2 3SP');
59
60-- --------------------------------------------------------
61
62--
63-- Table structure for table `assistantlogins`
64--
65
66CREATE TABLE IF NOT EXISTS `assistantlogins` (
67`loginId` int(3) NOT NULL,
68  `salesPNo` varchar(50) NOT NULL,
69  `userName` varchar(11) NOT NULL,
70  `password` varchar(20) NOT NULL,
71  `activatedUserAccount` enum('0','1') NOT NULL DEFAULT '0'
72) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
73
74--
75-- Dumping data for table `assistantlogins`
76--
77
78INSERT INTO `assistantlogins` (`loginId`, `salesPNo`, `userName`, `password`, `activatedUserAccount`) VALUES
79(1, 'SC01', 'ibrown', 'qwert', '1'),
80(2, 'SC02', 'rgreen', 'werty', '1'),
81(3, 'SC03', 'oblack', 'ertyu', '1'),
82(4, 'SC04', 'dblue', 'rtyui', '1'),
83(5, 'SC05', 'ywhite', 'tyuio', '1'),
84(6, 'SC06', 'ggray', 'yuiop', '1'),
85(7, 'SC07', 'jred', 'asdfg', '1'),
86(8, 'SC08', 'dpurple', 'sdfgh', '1'),
87(9, 'SC09', 'mgold', 'dfghj', '1'),
88(10, 'SC10', 'ddarkred', 'fghjk', '1');
89
90-- --------------------------------------------------------
91
92--
93-- Table structure for table `branch`
94--
95
96CREATE TABLE IF NOT EXISTS `branch` (
97  `branchId` int(2) NOT NULL,
98  `branchName` varchar(10) NOT NULL
99) ENGINE=InnoDB DEFAULT CHARSET=latin1;
100
101--
102-- Dumping data for table `branch`
103--
104
105INSERT INTO `branch` (`branchId`, `branchName`) VALUES
106(1, 'Croydon'),
107(2, 'Chelsea'),
108(3, 'Cobham');
109
110-- --------------------------------------------------------
111
112--
113-- Table structure for table `product`
114--
115
116CREATE TABLE IF NOT EXISTS `product` (
117  `productCode` varchar(5) NOT NULL,
118  `productGroup` int(2) NOT NULL,
119  `price` float NOT NULL,
120  `offerPrice` float DEFAULT NULL
121) ENGINE=InnoDB DEFAULT CHARSET=latin1;
122
123--
124-- Dumping data for table `product`
125--
126
127INSERT INTO `product` (`productCode`, `productGroup`, `price`, `offerPrice`) VALUES
128('BA345', 14, 14.99, NULL),
129('BA89', 11, 5.99, NULL),
130('BR47', 9, 5.99, NULL),
131('BR56', 14, 22.99, NULL),
132('BX56', 14, 22.99, NULL),
133('BX8', 11, 5.99, NULL),
134('BX91', 9, 7.5, NULL),
135('BX92', 9, 5.99, 3.99),
136('XD2', 12, 45.99, 39.99);
137
138-- --------------------------------------------------------
139
140--
141-- Table structure for table `sales_assistant`
142--
143
144CREATE TABLE IF NOT EXISTS `sales_assistant` (
145  `salesPNo` varchar(5) NOT NULL,
146  `salesPFname` varchar(15) NOT NULL,
147  `salesPLname` varchar(25) NOT NULL,
148  `branchId` int(2) NOT NULL
149) ENGINE=InnoDB DEFAULT CHARSET=latin1;
150
151--
152-- Dumping data for table `sales_assistant`
153--
154
155INSERT INTO `sales_assistant` (`salesPNo`, `salesPFname`, `salesPLname`, `branchId`) VALUES
156('SC01', 'Isiah', 'Brown', 1),
157('SC02', 'Rob', 'Green', 1),
158('SC03', 'Oliver', 'Black', 1),
159('SC04', 'Damian', 'Blue', 2),
160('SC05', 'Yakob', 'White', 2),
161('SC06', 'Gabriel', 'Gray', 2),
162('SC07', 'Jamie', 'Red', 2),
163('SC08', 'Deep', 'Purple', 3),
164('SC09', 'Michael', 'Gold', 3),
165('SC10', 'Darius', 'Darkred', 3);
166
167-- --------------------------------------------------------
168
169--
170-- Table structure for table `sales_transaction`
171--
172
173CREATE TABLE IF NOT EXISTS `sales_transaction` (
174`transactionNo` int(3) NOT NULL,
175  `salesPNo` varchar(5) NOT NULL,
176  `time` time(4) NOT NULL,
177  `dates` date NOT NULL,
178  `sale` float NOT NULL
179) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
180
181--
182-- Dumping data for table `sales_transaction`
183--
184
185INSERT INTO `sales_transaction` (`transactionNo`, `salesPNo`, `time`, `dates`, `sale`) VALUES
186(1, 'SC01', '16:38:00.0000', '2015-04-15', 29.95),
187(2, 'SC02', '17:03:00.0000', '2015-04-15', 44.97),
188(3, 'SC03', '17:03:00.0000', '2015-04-15', 374.83),
189(4, 'SC01', '17:30:00.0000', '2015-04-15', 459.8),
190(5, 'SC01', '09:30:00.0000', '2015-04-16', 91.48),
191(6, 'SC06', '09:48:00.0000', '2015-04-16', 599.06),
192(7, 'SC09', '15:58:00.0000', '2015-04-16', 3599.1),
193(8, 'SC08', '12:56:00.0000', '2015-04-16', 1333.48),
194(9, 'SC05', '11:34:00.0000', '2015-04-17', 3.99),
195(10, 'SC04', '15:30:00.0000', '2015-04-18', 5678.58);
196
197-- --------------------------------------------------------
198
199--
200-- Table structure for table `transactions_and_accounts`
201--
202
203CREATE TABLE IF NOT EXISTS `transactions_and_accounts` (
204`order` int(3) NOT NULL,
205  `transactionNo` int(2) NOT NULL,
206  `accountNo` int(7) NOT NULL,
207  `productCode` varchar(7) NOT NULL,
208  `quantity` int(2) NOT NULL
209) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
210
211--
212-- Dumping data for table `transactions_and_accounts`
213--
214
215INSERT INTO `transactions_and_accounts` (`order`, `transactionNo`, `accountNo`, `productCode`, `quantity`) VALUES
216(1, 1, 1011876, 'BA89', 5),
217(2, 2, 1023942, 'BA345', 3),
218(3, 3, 1029384, 'BA345', 2),
219(4, 3, 1029384, 'BR56', 15),
220(5, 4, 1057436, 'BX56', 2),
221(6, 5, 1065865, 'XD2', 20),
222(7, 5, 1065865, 'BX56', 5),
223(8, 6, 1075269, 'BX8', 36),
224(9, 6, 1075269, 'BX56', 8),
225(10, 6, 1075269, 'BX92', 50),
226(11, 7, 1098274, 'XD2', 90),
227(12, 8, 2987399, 'BR56', 58),
228(13, 9, 6743528, 'BX92', 1),
229(14, 10, 4890189, 'XD2', 142);
230
231--
232-- Indexes for dumped tables
233--
234
235--
236-- Indexes for table `accounts`
237--
238ALTER TABLE `accounts`
239 ADD PRIMARY KEY (`accountNo`), ADD KEY `branchId` (`branchId`);
240
241--
242-- Indexes for table `assistantlogins`
243--
244ALTER TABLE `assistantlogins`
245 ADD PRIMARY KEY (`loginId`), ADD KEY `salesPNo` (`salesPNo`);
246
247--
248-- Indexes for table `branch`
249--
250ALTER TABLE `branch`
251 ADD PRIMARY KEY (`branchId`);
252
253--
254-- Indexes for table `product`
255--
256ALTER TABLE `product`
257 ADD PRIMARY KEY (`productCode`);
258
259--
260-- Indexes for table `sales_assistant`
261--
262ALTER TABLE `sales_assistant`
263 ADD PRIMARY KEY (`salesPNo`), ADD KEY `branchId` (`branchId`);
264
265--
266-- Indexes for table `sales_transaction`
267--
268ALTER TABLE `sales_transaction`
269 ADD PRIMARY KEY (`transactionNo`), ADD UNIQUE KEY `Transaction no.` (`transactionNo`), ADD KEY `salesPNo` (`salesPNo`);
270
271--
272-- Indexes for table `transactions_and_accounts`
273--
274ALTER TABLE `transactions_and_accounts`
275 ADD PRIMARY KEY (`order`), ADD KEY `transactionNo` (`transactionNo`), ADD KEY `productCode` (`productCode`), ADD KEY `transactions_and_accounts_ibfk_2` (`accountNo`);
276
277--
278-- AUTO_INCREMENT for dumped tables
279--
280
281--
282-- AUTO_INCREMENT for table `accounts`
283--
284ALTER TABLE `accounts`
285MODIFY `accountNo` int(7) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6743529;
286--
287-- AUTO_INCREMENT for table `assistantlogins`
288--
289ALTER TABLE `assistantlogins`
290MODIFY `loginId` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;
291--
292-- AUTO_INCREMENT for table `sales_transaction`
293--
294ALTER TABLE `sales_transaction`
295MODIFY `transactionNo` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;
296--
297-- AUTO_INCREMENT for table `transactions_and_accounts`
298--
299ALTER TABLE `transactions_and_accounts`
300MODIFY `order` int(3) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15;
301--
302-- Constraints for dumped tables
303--
304
305--
306-- Constraints for table `accounts`
307--
308ALTER TABLE `accounts`
309ADD CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`branchId`) REFERENCES `branch` (`branchId`);
310
311--
312-- Constraints for table `assistantlogins`
313--
314ALTER TABLE `assistantlogins`
315ADD CONSTRAINT `assistantlogins_ibfk_1` FOREIGN KEY (`salesPNo`) REFERENCES `sales_assistant` (`salesPNo`);
316
317--
318-- Constraints for table `sales_assistant`
319--
320ALTER TABLE `sales_assistant`
321ADD CONSTRAINT `sales_assistant_ibfk_1` FOREIGN KEY (`branchId`) REFERENCES `branch` (`branchId`);
322
323--
324-- Constraints for table `sales_transaction`
325--
326ALTER TABLE `sales_transaction`
327ADD CONSTRAINT `sales_transaction_ibfk_1` FOREIGN KEY (`salesPNo`) REFERENCES `sales_assistant` (`salesPNo`);
328
329--
330-- Constraints for table `transactions_and_accounts`
331--
332ALTER TABLE `transactions_and_accounts`
333ADD CONSTRAINT `transactions_and_accounts_ibfk_2` FOREIGN KEY (`accountNo`) REFERENCES `accounts` (`accountNo`),
334ADD CONSTRAINT `transactions_and_accounts_ibfk_3` FOREIGN KEY (`transactionNo`) REFERENCES `sales_transaction` (`transactionNo`),
335ADD CONSTRAINT `transactions_and_accounts_ibfk_4` FOREIGN KEY (`productCode`) REFERENCES `product` (`productCode`);
336
337/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
338/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
339/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;