ࡱ> { bjbjzz (ttzzz4hDZtF\rrrY=Y=Y=sssssss$uxsz>><Y=>>>>srrs,H,H,H>>p^rzrs,H>>s,H,H)oX,Nrr{;>p(qss0tpTy?"TyPrrTyzrY=m=,H{= =Y=Y=Y=ssBzY=Y=Y=t>>>>>>>>TyY=Y=Y=Y=Y=Y=Y=Y=Y=t : aName _____________________________ Lecture time ________________ CSE1111 Homework #5 (30 points) Joins People  Violations  Types  SHAPE \* MERGEFORMAT  The tables above represent an insurance companys database of traffic violations for the year of 2003. The People table lists the names of all the insured customers and their personal data including the address, the license number and category (PC-personal car, T-truck, M-motorcycle), and the state in which the license was issued. The Types table lists the descriptions of each violation type, the points, and the penalty associated with each. The Violations table is a record of all traffic violations that have taken place. This table includes the drivers license number of the offender, the date the violation happened, the violation type, and the state where the violation occurred. (1 point) Database Relationships: set up the relationships of this database. Using the boxes below, fill in the primary key (if any) of each table and draw relationship lines between tables. Label each relationship with the name of the foreign key(s) just above the line you have drawn.    (1 Point) What is Referential Data Integrity? Based only on the data shown, has Referential Data been violated for any of the relationships? Explain your answer. Base questions 3-8 on the database provided at the beginning of this homework. Please note when writing query grids, to receive full credit you must use correct Access syntax. The query should look identical to how it looks when the computer runs it. Be sure to include Like, , and # symbols as needed. Do not use unnecessary tables, fields or criteria in your query. (1 Point) List the drivers license number, the first and last name, and the violation type associated with each traffic violation. Sort the results by the license number in increasing order. Tables Used ______________________________________ Join on ________________ FieldTableTotalSortShow CriteriaOR (3 Points) List all speeding 1 violations that happened between 1/1/2003 and 5/1/2003. Your list should include the license number, the date on which the violation took place, and the state in which the violations occurred. Sort the list alphabetically by the license number then by the violation date most recent first. Tables Used ____________________________________ Join on _____________________ FieldTableTotalSortShow Criteria OR (5 Points) List the license number, the state the license was issued, the state in which the violation happened, the violation type and the fine associated with the violation of all violations that meet either of the following criteria: DUI violations that happened in the state of NY. Speeding violations for persons whose license was issued in the state of OH (Note: your criteria should cover all types of speeding violations). Tables Used _______________________________ Join on ____ ______________________ FieldTableTotalSortShow CriteriaOROR (4 Points) Write a query to display the number of violations in each vehicle category. Tables Used ____________________________ Join on _____________________ FieldTableTotalSortShow CriteriaOROR (5 Points) The legislature has approved a surcharge of 20% of the fine value for fines over $100. Create a list of violations with fines that were originally over $100. Include the following information: the license number, the violation type, the original fine amount and the new fine amount including the extra 20% surcharge. Name the new fine amount field Fine with surcharge so that you can use it in the answer of the next question. Tables Used ________________________ Join on ___________________ FieldTableTotalSortShow CriteriaOROR (10 Points) Using the results of query 7, summarize the list of violations over $100 by license number. Your list should include the license number, the last name of the violator, the total amount owed (including surcharge), the average ticket amount (including surcharge) and the total number of tickets received over $100. Tables Used __________________________ Join on ____________________ FieldTableTotalSortShow CriteriaOROR      PAGE 1 Type Description Points Fine DUI having the alcohol level >.001 1 $400.00 parking getting a parking ticket 0 $10.00 right of way not giving priority to people at a crossing 2 $50.00 red light passing through a red light 3 $100.00 speeding 1 driving with 20mph over the legal speed 1 $50.00 speeding 2 driving with 30mph over the legal speed 2 $150.00 Table name: Violations Primary Key Table name: People Primary Key Table name: Type Primary Key CGIWXZ\dnvwyz ! {   s | } d 䱡}v hv5CJ hCJ hqCJ hvCJh: h)05CJjh)0Ujh)0UmHnHtHujh)0U hzh)0 hEh)0jhEh)0U h%c h)0jh%c h)0Uh)0 hWCJ hJpgCJ h)0CJ h)0CJ-BCnovxyz{ o c d gdfI $ & Fa$gdfI $ & F a$gd)0gd)0$a$d e f g h 8 : ; n w ڶvlel^UIhGOhGO56CJhGO56CJ hGO5CJ h)05CJhh)05CJ *hiCJaJhxDh)0CJaJ h)0CJ h3CJ h'CJ hgCJjhfICJUmHnHuhfIjhfIUmHnHuhLfzhfI56CJaJh3CJaJhLfzhfICJaJ(jhLfzhfICJUaJmHnHu hfICJ 7 8 9 : ; &$d%d&d'dNOPQgdGO$a$gd)0gd)0h`hgd1h^hgd)0 & FgdggdggdfIsuAVXfpĺĺĺĺĺĺĺĺĺĺĺġh'CJaJ *hhi5 *h)05 h0CJ h|CJ h'CJ *hh)05 h)05 hiCJhE4bh)0CJ h\^CJhhuCJaJmHnHu huCJ h3CJ h)0CJ7tu$d$Ifa$gd)0 d$Ifgd)0gd)0^gd)0 $ & Fa$gdg TH999H$d$Ifa$gd)0 d$Ifgd)0kd{$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kdc$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kdK$$Iflrj U@+"04 la   TH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laWXTKKK@K $ & Fa$gdg^gd)0kd3$$Iflrj U@+"04 laX$d$Ifa$gd)0 d$Ifgd)0gd)0TH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTK????K $$Ifa$gd)0 $Ifgd)0kdw$$Iflrj U@+"04 laTH999H$d$Ifa$gd)0 d$Ifgd)0kd$$Iflrj U@+"04 laTKK@55 $ & F a$gdq $ & Fa$gdg^gd)0kd_$$Iflrj U@+"04 la+67=})+7D 78C¾yynh)05mHnHu *hL`h)05 h)05 h)0>*CJ hi5>*h4Kh)05>* h)05>* hi>*CJh4Kh)0>*CJ h)0CJh)0hjhqCJaJh0CJaJhhTCJaJhqCJaJh)0CJaJhjh)0CJaJh|CJaJ+  d$Ifgd)0gd)0 0^`0gd)0  D^gd)0  ?3333 d$Ifgd)0kd$$Iflֈ  pX ^&wwww0&4 la3kdg$$Iflֈ  pX ^&wwww0&4 la d$Ifgd)0 !"3kd$$Iflֈ  pX ^&wwww0&4 la d$Ifgd)0"'()*+, d$Ifgd)0,-3456?3333 d$Ifgd)0kd $$Iflֈ  pX ^&wwww0&4 la6789BC3kd#!$$Iflֈ  pX ^&wwww0&4 la d$Ifgd)0CDEFGH3kd!$$Iflֈ  pX ^&wwww0&4 la d$Ifgd)0CFLOZ\^_efhi*,UVXYZc!578؞ЖzrhJh)05 hJh)0h%5CJaJhkothkot5CJaJhkotCJaJhsh)0CJaJjh)05UmHnHu *hsh)05hi hiCJh)0h)0CJaJh|CJaJhJYh)0CJaJ h)0CJ h)05 *hL`h)05,HKLMNOP d$Ifgd)0PQTUVW?3333 d$Ifgd)0kdK"$$Iflֈ  pX ^&wwww0&4 laWXYZ\3*^gd)0kd"$$Iflֈ  pX ^&wwww0&4 la d$Ifgd)0\  d$Ifgd)0gd)0gd)0 $ & Fa$gdg  THHHHH d$Ifgd)0kds#$$Iflrj U@+"04 laTHHHHH d$Ifgd)0kd#$$Iflrj U@+"04 la %&'()THHHHH d$Ifgd)0kd[$$$Iflrj U@+"04 la)*23456THHHHH d$Ifgd)0kd$$$Iflrj U@+"04 la67@ABCDTHHHHH d$Ifgd)0kdC%$$Iflrj U@+"04 laDEHIJKLTHHHHH d$Ifgd)0kd%$$Iflrj U@+"04 laLMPQRSTTHHHHH d$Ifgd)0kd+&$$Iflrj U@+"04 laTUVWXTOOOD;^gd)0 $ & Fa$gdggd)0kd&$$Iflrj U@+"04 la8JRVY_bcjnuyCDEIz{.56>AGMSY_ejpv|̼̼򔊔 *h7h)05hih)05hJh)05h CJaJh%CJaJh: CJaJh)0CJaJhrCJaJhvh)0CJaJh5he#h)05 *h h)05 h)05 h)0CJhih)06VY_`abc $If^gde# d$Ifgd)0gd)0^gd)0cdjklmnRFFFFF d$Ifgd)0kd'$$Iflr H$<<<< 04 lanouvwxyRFFFFF d$Ifgd)0kd'$$Iflr H$<<<< 04 layzRFFFFF d$Ifgd)0kd($$Iflr H$<<<< 04 laRFFFFF d$Ifgd)0kd($$Iflr H$<<<< 04 laRFFFFF d$Ifgd)0kd+)$$Iflr H$<<<< 04 laRFFFFF d$Ifgd)0kd)$$Iflr H$<<<< 04 laRFFFFF d$Ifgd)0kd7*$$Iflr H$<<<< 04 laRIII>6$a$gd)0 $ & Fa$gdg^gd)0kd*$$Iflr H$<<<< 04 la>AGHIJKL $Ifgd)0 d$Ifgd)0gd)0^gd)0gd)0 LMSTUVWA55555 d$Ifgd)0kdC+$$Iflֈj UA; %0d&4 laWXY_`ab5kd+$$Iflֈj UA; %0d&4 la d$Ifgd)0bcdejkl5kd,$$Iflֈj UA; %0d&4 la d$Ifgd)0lmnopvw5kd-$$Iflֈj UA; %0d&4 la d$Ifgd)0wxyz{|5kd-$$Iflֈj UA; %0d&4 la d$Ifgd)05kdY.$$Iflֈj UA; %0d&4 la d$Ifgd)0 d$Ifgd)0A55555 d$Ifgd)0kd.$$Iflֈj UA; %0d&4 la   !9;<>DFRTƻƫߝ띏h\B*OJQJ^Jphh)0B*OJQJ^Jphh80J5>*CJmHnHuh)00J5>*CJjh)00J5>*CJUh)05>*CJ\hp]"jhp]"Uh)0h7h)05hih)05850..gd)0kd/$$Iflֈj UA; %0d&4 la d$Ifgd)0   !::;=>EFST  3568?AMWXtz{hp]" hfI56 hg56hfIh)0h)0B*OJQJ^Jph"  4578@AMXYefght{| &d P gdfIgdfIgd)0gdfI(/ =!"#*$% 8 0/ =!"#$%0 h1 0:p)0/ =!"#$% Dd 0  # A2 T\]rAb,^ D`!V T\]rAb,n GxXMlvȵIzؒ+TD(:TJ D+DdlA' XݓA=ri]TJº'"d4F'ofgV%Kxٙy4(M-BfOH)2%"gM%r`s>i)3%ʫ .2Cϒ,5ZQ2T*Eeٳgzzzƍ4;;KSSS4>>NKį]gto9}쟴 9| ]<F>\| 553ՠ| xWw &(F">\M<p@-xW>\-@E_\.@q|9)pG| \<+c8XPpQTfr(@p@<# .Mr| 2xeO=# .\h,E=/<ݼy޽Kz =z􈶶 z%z^~M◸H?\e7P ޮӴnqJ̲Rjzrcv5 im:a%Qۃpbj T|f3ie{T쫅ס1@N ]~ػi;j^R3SbH5_,+n:#GU/-._+j%:UL wQ9WܛPa .gPvF]b0֗W yF9i)}xZ kJYyD= ntN|PXtsjNG1_ۆJyѭw%Sk:9;2|17UUxclA!{R.|⺵0F7:߭[FϨ IR:%':  z7<7nc)vF}\G{u0o+\eǜAʵ*ʲyJSמ-~^wkrj.+rlM6!=Gs{fc]/1 %ϭMT:)V},g֪Xn$;L^%{٬=-5m;}3Kpra9ahj'Vx '9gY}2uL$YBvK$Y蜏vrތq/UeSw}P~#v>7`' $;.ZGy攞~ ևM>oAG `}FOuB_= %=8uF3ģo.әӺxleF8BceU6q{y( KjWl)35e^yl}J_|d"B2OQnMT_!N(CH)}Z$)!zSy[:ڕyڵ!!$w!c!$G!OӮ/Cj!$4v/!Muϗ71/ v%RqްcF,Kz*Dz/ ~2R1߰7rb˵0 k-^hw:߭tӦC,Lud0]Nu۟2uae  vf,u!^U aҩȽ[|? Dd uP  # AC"2A ΒK J`! ΒKܤGL+x՘OhTG3q71ښfU%$Lf\, C^kNti͗RHJ|_qP VMp2.fggiY)NS6VڱcuttPWWuww޽{ixxȑ#trL;}[:k'zdpz]B F h`. \XXuƇX \` U@,pa h`0! H0&XB"XB0q_I4H(ֻ(GeQ:v(-p8Wj \0DG=p$QZq*d1Yo` *5z \X hcA=p\\YbR ,p}8P40`:<}q Ⱦ8۴Ν;GW\ׯ͛7޽{t]z!=zӧsz%Wm<=\8GtxgǮtʻJy":ҿQ{P~_eZ+ʏwӳ0/LwpJ S!Y4>rj~d/hʾfkN?N:@ wL||v}6^:?25^5uhТͷw.;i?{#ڷpNN!侂|ƙ |%QH}ѯe2b*W*gٮ fJl+jsD|o!@~RBӷoڕ l8Rkt*3>:|8:ZNϋ4PCZwn+z:@oվV^Wm!4ȥ'r {/<=o>9 7o3ѨoEvX_Tdk("z)|ܚ|l0nWI$bcf/x7-=2Bpo\GqqϜ@QZ8Jɯûq޵GK {wQ.?wj^߻һylɷoM|¯?[|vY"v˲_%߲:V8cQK;m6_^Kkյ4ZӴ6_Cյ2+ʄ̗"\c) ,å$ v#muKId G /.%^3i^imgVJk:2Ltf'%cX8lykbum^TkgL2mJڔ\]ډD;QG;L{?ޯRݸ]=%qCbI|q~&lճHL+x?hSAhuPJ vmHPO6bǂZVJC E(tpp.d) .:9wm$Cw-DzwgI-^5~o_Q1ՙ`N?_t,ESn -PLdd]xP櫳3eIHS|mꌞIhM{g@ 'û'yelh`4b+a]0jOôslP6>Z7C=YQ\:6pT{":Zd"1VԈ4Mu;w;k3 $"{$g=eY~ l%b<;%KoĥX vUxkMد&K D/ZuL008ei*=_۸}3} һw;vwTGSyq]Cx;*մw$gaڡwށ{gHwsH=(БFQ{PewVG9/OT$ww$DL]4$S#9I20ddlSϚ9WnXY:a^]<,s凰^]L,sef\HGZƵ3I *Ir6\mNA΍:d-RUw,os=YsTUO|rj5~YrDd\D  3 @@"?r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554$$If!vh#vw#v#v:V l0&5w554r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054r$$If!vh#v:V l054$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v<#v :V l05<5 4$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554$$If!vh#v#v#v#v:V l0d&55554b 2 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~PJ_HamH nHsH tH8`8 Normal_HmH sH tH >@>  Heading 1$@& 5>*CJ$8@8  Heading 2$@&CJ@@@  Heading 3$&d @&56DA`D Default Paragraph FontVi@V  Table Normal :V 44 la (k (No List 2B@2 Body TextCJ4@4 Header  !4 @4 Footer  !.)@!. Page Numberj3j i i Table Grid7:V0H@BH , Balloon TextCJOJQJ^JaJB'`QB R7uComment ReferenceCJaJ4@b4 R7u Comment Text@j@ab@ R7uComment Subject5\PK![Content_Types].xmlN0EH-J@%ǎǢ|ș$زULTB l,3;rØJB+$G]7O٭V$ !)O^rC$y@/yH*񄴽)޵߻UDb`}"qۋJחX^)I`nEp)liV[]1M<OP6r=zgbIguSebORD۫qu gZo~ٺlAplxpT0+[}`jzAV2Fi@qv֬5\|ʜ̭NleXdsjcs7f W+Ն7`g ȘJj|h(KD- dXiJ؇(x$( :;˹! I_TS 1?E??ZBΪmU/?~xY'y5g&΋/ɋ>GMGeD3Vq%'#q$8K)fw9:ĵ x}rxwr:\TZaG*y8IjbRc|XŻǿI u3KGnD1NIBs RuK>V.EL+M2#'fi ~V vl{u8zH *:(W☕ ~JTe\O*tHGHY}KNP*ݾ˦TѼ9/#A7qZ$*c?qUnwN%Oi4 =3N)cbJ uV4(Tn 7_?m-ٛ{UBwznʜ"Z xJZp; {/<P;,)''KQk5qpN8KGbe Sd̛\17 pa>SR! 3K4'+rzQ TTIIvt]Kc⫲K#v5+|D~O@%\w_nN[L9KqgVhn R!y+Un;*&/HrT >>\ t=.Tġ S; Z~!P9giCڧ!# B,;X=ۻ,I2UWV9$lk=Aj;{AP79|s*Y;̠[MCۿhf]o{oY=1kyVV5E8Vk+֜\80X4D)!!?*|fv u"xA@T_q64)kڬuV7 t '%;i9s9x,ڎ-45xd8?ǘd/Y|t &LILJ`& -Gt/PK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 0_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!0C)theme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] !&FIR[ux/2:For{!&FIR[ux/2:For{ z*d '''''''''''''''*d C8#,9LP X ",6CHPW\ )6DLTcnyLWblw: !"$%&'()*+-./012345678:;<=>?@ABCDEFGHIJKMNOQR_!#*!T8J@Jf(    3  "0?`  c $X99?N  3  ~  6h% ~  6x a ~  6 ~  6 ~  60  ~  6  ~  6l  ~  6X + ~  6 0  ~  6 `   ~  6 l  ~  6   ~  6 0/  ~  6 ~  6l ~  6 ~  60 ~  6  ~  6l ~  6X+ ~  60 ~  6S ~  6l ~  6 ~  60 ~  6S ~  6l ~  6X+ HB  #  N  3  HB  # _` N  3 _s HB  # OP N  3 Ob HB  #  N  3  HB  #  N  3  NB  3  N  3  NB  3 _ `N  3 _ sNB  3 O PN  3 O bNB  3  N  3  NB  3  N  3  HB  # N  3 HB  # N  3  NB  3 N  3 NB  3 N  3 NB  3 N  3 NB  3 N  3 NB  3 N  3 NB  3 N  3 P 6 # ?#" ?P 7 # ?#" ?n  # S"??  V  # "? V  # "? V  # "? B S  ?e* + t) tA! t @! tt69+t78y+tT\;*urn:schemas-microsoft-com:office:smarttagschsdate 12003DayFalse IsLunarDate IsROCDateMonthYearddcg!(FKTW 333333333333Cmv{uX    D H L Q [     X _djoGMSY_e   !9;<>DFR 568?MYt|GIWXZ\ddd8:f p                     - - 7 7 D D E E L L N N ^ _ e f             , , Y Z !577JR__``aabbjjkkll.5GGHHIIJJKKSSTTUUVVWW__``aabbccppww||A8$ޝTu:A` 48x&W?@ABCDEFGHIJKLMNOPQRSUVWXYZ[\]^_`abcdefghijklnopqrstuvwxyz{|}~Root Entry F`;Data T301TablemyWordDocumentSummaryInformation(DocumentSummaryInformation8CompObjr  F Microsoft Word 97-2003 Document MSWordDocWord.Document.89q