k7Mgjese

1-- ############################## BEGIN INSERTS ##############################
2
3-- -----------------------------
4-- Data for person table
5-- -----------------------------
6
7START TRANSACTION;
8
9INSERT INTO person
10(per_id, per_ssn, per_fname, per_lname, per_street, per_city, per_state, per_zip, per_email, per_dob, per_type, per_notes)
11VALUES
12(NULL, NULL, 'Steve', 'Rogers', '437 Southern Dnve', 'Rochester', 'NY', 324402222, 'srogers@comcast.net', '1923-10-03', 'c', NULL),
13(NULL, NULL, 'Bruce', 'Wayne', '1007 Mountain Drive', 'Gotham', 'NY', 003208440, 'bwayne@knology.net', '1968-03-20', 'c', NULL),
14(NULL, NULL, 'Peter', 'Parker', '20 Ingram Street', 'New York', 'NY', 102862341, 'pparker@msn.com', '1988-09-12', 'c', NULL),
15(NULL, NULL, 'Jane', 'Thompson', '13563 Ocean View Drive', 'Seattle', 'WA', 032084409, 'jthompson@gmailcom', '1978-05-08', 'c', NULL),
16(NULL, NULL, 'Debra', 'Steele', '543 Oak Ln', 'Milwaukee', 'WI', 286234178, 'dsteele@venzon.net', '1994-07-19', 'c', NULL),
17(NULL, NULL, 'Tony', 'Stark', '332 Palm Avenue', 'Malibu', 'CA', 902638332, 'tstark@yahoo.com', '1972-05-04' , 'a', NULL),
18(NULL, NULL, 'Hank', 'Pym', '2355 Brown Street', 'Cleveland', 'OH', 022348890, 'hpym@aol.com', '1980-08-28', 'a', NULL),
19(NULL, NULL, 'Bob', 'Best', '4902 Avendale Avenue', 'Scottsdale', 'AZ', 872638332, 'bbest@yahoo.com', '1992-02-10', 'a', NULL),
20(NULL, NULL, 'Sandra', 'Dole', '87912 Lawrence Ave', 'Atlanta', 'GA', 002348890, 'soole@gma1l.com', '1990-01-26', 'a', NULL),
21(NULL, NULL, 'Ben', 'Avery', '6432 Thunderbird Ln', 'Sioux Falls', 'SD', 562638332, 'bavery@hotma1l.com', '1983-12-24', 'a', NULL),
22(NULL, NULL, 'Arthur', 'Curry', '3304 Euclid Avenue', 'Miami', 'FL', 000219932, 'acurry@gma1l.com', '1975-12-15', 'j', NULL),
23(NULL, NULL, 'Diana', 'Price', '944 Green Street', 'Las Vegas', 'NV', 332048823, 'dpnce@symaptico.com', '1980-08-22', 'j', NULL),
24(NULL, NULL, 'Adam', 'Jurns', '98435 Valencia Dr.', 'Gulf Shores' , 'AL', 870219932, 'ajurns@gmx.com', '1995-01-31', 'j', NULL),
25(NULL, NULL, 'Judy', 'Sleen', '56343 Rover Ct.', 'Billngs', 'MT', 672048823, 'jsleen@symaptico.com', '1910-03-22', 'j', NULL),
26(NULL, NULL, 'Bill', 'Neiderheim', '43567 Netherland Blvd', 'South Bend', 'IN', 320219932, 'beiderheim@comcast.net', '1982-03-13', 'j', NULL);
27
28COMMIT;
29
30
31-- -----------------------------
32-- Data for phone table
33-- -----------------------------
34
35START TRANSACTION;
36INSERT INTO phone 
37(phn_id, per_id, phn_num, phn_type, phn_notes)
38VALUES
39(NULL, 1, 8032288827, 'c', NULL),
40(NULL, 2, 2052338293, 'h', NULL),
41(NULL, 4, 1034325598, 'w', 'has two office nubmers'),
42(NULL, 5, 6402338494, 'f', NULL),
43(NULL, 6, 5508326842, 'c', 'fax number not currently working'),
44(NULL, 7, 8202052203, 'h', 'prefers home calls'),
45(NULL, 8, 4008338294, 'w', NULL),
46(NULL, 9, 6546513254, 'f', 'work fax number'),
47(NULL, 10, 8748613214, 'h', 'prefers cell phone calls'),
48(NULL, 11, 4546484212, 'h', NULL),
49(NULL, 12, 1816335486, 'w', 'best number to reach'),
50(NULL, 13, 4894652164, 'w', 'call during lunch'),
51(NULL, 14, 3815698744, 'c', 'prefers cell phone calls'),
52(NULL, 15, 2666855948, 'f', 'use for faxing legal documents');
53
54COMMIT;
55
56
57-- -----------------------------
58-- Data for client table
59-- -----------------------------
60START TRANSACTION;
61
62INSERT INTO client
63(per_id, cli_notes)
64VALUES
65(1, NULL),
66(2, NULL),
67(3, NULL),
68(4, NULL),
69(5, NULL);
70
71COMMIT;
72
73
74-- -----------------------------
75-- Data for attorney table
76-- -----------------------------
77START TRANSACTION;
78
79INSERT INTO attorney
80(per_id, aty_start_date, aty_end_date, aty_hourly_rate, aty_years_in_practice, aty_notes)
81VALUES
82(6, '2006-06-12', NULL, 85, 5, NULL),
83(7, '2003-08-20', NULL, 130,28, NULL),
84(8, '2009-12-12', NULL, 70, 17, NULL),
85(9, '2008-06-08', NULL, 78, 13, NULL),
86(10, '2011-09-12', NULL, 60, 24, NULL);
87
88COMMIT;
89
90
91-- -----------------------------
92-- Data for bar table
93-- -----------------------------
94START TRANSACTION;
95
96INSERT INTO bar
97(bar_id, per_id, bar_name, bar_notes)
98VALUES
99(NULL, 6, 'Florida Bar', NULL),
100(NULL, 7, 'Alabama Bar', NULL),
101(NULL, 8, 'Georgia Bar', NULL),
102(NULL, 9, 'Michigan Bar', NULL),
103(NULL, 10, 'South Carolina Bar', NULL),
104(NULL, 6, 'Montana Bar', NULL),
105(NULL, 7, 'Arizona Bar', NULL),
106(NULL, 8, 'Nevada Bar', NULL),
107(NULL, 9, 'New York Bar', NULL),
108(NULL, 10, 'New York Bar', NULL),
109(NULL, 6, 'Mississippi Bar', NULL),
110(NULL, 7, 'California Bar', NULL),
111(NULL, 8, 'Illinois Bar', NULL),
112(NULL, 9, 'Indiana Bar', NULL),
113(NULL, 10, 'Illinois Bar', NULL),
114(NULL, 6, 'Tallahassee Bar', NULL),
115(NULL, 7, 'Ocala Bar', NULL),
116(NULL, 8, 'Bay County Bar', NULL),
117(NULL, 9, 'Cincinatti Bar', NULL);
118
119COMMIT;
120
121
122-- -----------------------------
123-- Data for speciality table
124-- -----------------------------
125START TRANSACTION;
126
127INSERT INTO specialty
128(spc_id, per_id, spc_type, spc_notes)
129VALUES
130(NULL, 6, 'business', NULL),
131(NULL, 7, 'traffic', NULL),
132(NULL, 8, 'bankruptyc', NULL),
133(NULL, 9, 'insurance', NULL),
134(NULL, 10, 'judicial', NULL),
135(NULL, 6, 'environmental', NULL),
136(NULL, 7, 'criminal', NULL),
137(NULL, 8, 'real estate', NULL),
138(NULL, 9, 'malpractice', NULL);
139
140COMMIT;
141
142
143-- -----------------------------
144-- Data for court table
145-- -----------------------------
146START TRANSACTION;
147
148INSERT INTO court
149(crt_id, crt_name, crt_street, crt_city, crt_state, crt_zip, crt_phone, crt_email, crt_url, crt_notes)
150VALUES
151(NULL, 'leon county circuit court', '301 south monroe street', 'tallahassee', 'fl', 323035292, 8506065504, 'lccc@us.fl.gov', 'http://www.leoncountycircuitcourt.gov/', NULL),
152(NULL, 'leoun county traffic court', '1921 thomasville road', 'tallahssee', 'fl', 323035292, 8505774100, 'lctc@us.fl.gov', 'http://www.leoncountytrafficcourt.gov/', NULL),
153(NULL, 'florida supreme court', '500 south duval street', 'tallahassee', 'fl', 323035292, 8504880125, 'fsc@us.fl.gov', 'http://www.floridasupremecourt.org/', NULL),
154(NULL, 'orange county courthouse', '424 north orange avenue', 'orlando', 'fl', 328012248,  4078362000, 'occ@us.fsl.gov', 'http://www.ninthcircuit.org/', NULL),
155(NULL, 'fifth district court of appeal', '300 south beach street', 'daytona beach', 'fl', 321158763, 3862258600, '5dca@us.fl.gov', 'http://5dcca.org', NULL);
156
157COMMIT;
158
159
160-- -----------------------------
161-- Data for judge table
162-- -----------------------------
163START TRANSACTION;
164
165INSERT INTO judge
166(per_id, crt_id, jud_salary, jud_years_in_practice, jud_notes)
167VALUES
168(11, 5, 150000, 10, NULL),
169(12, 4, 185000, 3, NULL),
170(13, 4, 135000, 2, NULL),
171(14, 3, 170000, 6, NULL),
172(15, 1, 120000, 1, NULL);
173
174COMMIT;
175
176
177-- -----------------------------
178-- Data for judge_hist table
179-- -----------------------------
180START TRANSACTION;
181
182INSERT INTO judge_hist
183(jhs_id, per_id, jhs_crt_id, jhs_date, jhs_type, jhs_salary, jhs_notes)
184VALUES
185(NULL, 11, 3, '2009-01-16', 'i', 130000, NULL),
186(NULL, 12, 2, '2010-05-27', 'i', 140000, NULL),
187(NULL, 13, 5, '2000-01-02', 'i', 115000, NULL),
188(NULL, 13, 4, '2005-07-05', 'i', 135000, NULL),
189(NULL, 14, 4, '2008-12-09', 'i', 155000, NULL),
190(NULL, 14, 1, '2011-03-17', 'i', 120000, 'freshman justice'),
191(NULL, 11, 5, '2010-07-05', 'i', 150000, 'assigned to another court'),
192(NULL, 12, 4, '2012-10-08', 'i', 165000, 'became chief justice'),
193(NULL, 14, 3, '2009-04-19', 'i', 170000, 'reassigned to court based upon local area population growth');
194
195COMMIT;
196
197
198-- -----------------------------
199-- Data for case table
200-- -----------------------------
201START TRANSACTION;
202
203INSERT INTO `case`
204(cse_id, per_id, cse_type, cse_description, cse_start_date, cse_end_date, cse_notes)
205VALUES
206(NULL, 13, 'civil', 'client says that his log is being used without his consent to promote a rival business', '2010-09-09', NULL, 'copyright infringement'),
207(NULL, 12, 'criminal', 'client is charged with assaulting her husband during an argument', '2009-11-18', '2010-12-23', 'assault'),
208(NULL, 14, 'civil', 'client broke an ankle while shopping at a local grocery store. no wet floor sign was posted although the floor had just been mopped', '2008-05-06', '2008-07-23', 'slip and fall'),
209(NULL, 11, 'criminal', 'client was charged with stealing several televisions from his former place of employment. client has a solid alibi', '2011-05-20', NULL, 'grand theft'),
210(NULL, 13, 'criminal', 'client charged with posession of 10 grams of cocaine, allegedly found in his glove box by state police', '2011-06-05', NULL, 'posession of narcotics'),
211(NULL, 14, 'civil', 'client alleges newspaper printed false information about his personal activities while he ran a large laundry business in a small nearby town.', '2007-01-19', '2007-05-20', 'defamation'),
212(NULL, 12, 'criminal', 'client charged with the murder of his co-worker over a lovers fued. client has no alibi', '2010-03-20', NULL, 'murder'),
213(NULL, 15, 'civil', 'client made the horrible mistake of selecting a degree other than IT and had to declare bankruptcy.', '2012-01-26', '2013-02-28', 'bankruptcy');
214
215COMMIT;
216
217
218-- -----------------------------
219-- Data for assignment table
220-- -----------------------------
221START TRANSACTION;
222
223INSERT INTO assignment
224(asn_id, per_cid, per_aid, cse_id, asn_notes)
225VALUES
226(NULL, 1, 6, 7, NULL),
227(NULL, 2, 6, 6, NULL),
228(NULL, 3, 7, 2, NULL),
229(NULL, 4, 8, 2, NULL),
230(NULL, 5, 9, 5, NULL),
231(NULL, 1, 10, 1, NULL),
232(NULL, 2, 6, 3, NULL),
233(NULL, 3, 7, 8, NULL),
234(NULL, 4, 8, 8, NULL),
235(NULL, 5, 9, 8, NULL),
236(NULL, 4, 10, 4, NULL);
237
238COMMIT;
239
240
241-- -----------------------------
242-- Procedure for hashing SSN
243-- -----------------------------
244DROP PROCEDURE IF EXISTS CreatePersonSSN;
245DELIMITER $$
246CREATE PROCEDURE CreatePersonSSN()
247BEGIN
248    DECLARE x, y INT;
249    SET x = 1;
250    select count(*) into y from person;
251
252    WHILE x <= y DO
253        UPDATE person
254        SET per_ssn=(SELECT unhex(sha2(FLOOR(000000001 + (RAND() * 1000000000)), 512)))
255        WHERE per_id=x;
256    
257    SET x = x + 1;
258
259    END WHILE;
260
261END$$
262DELIMITER ;
263CALL CreatePersonSSN();
264
265SHOW WARNINGS;
266
267SELECT 'show populated per_ssn fields after calling stored proc' as '';
268SELECT per_id, LENGTH(per_ssn) FROM person ORDER BY per_id;
269DO SLEEP(7);
270
271
272-- ############################## BEGIN REPORTS ##############################