Assignment 02 Solution

C larifications:




N o clarification w illbe posted here after 5pm on O ct 10th.




O ct 7. H ere is
a m arking rubric
for the
Assignm ent 2.
O ct 7. Q 5 should read "...
, i.e. the sum
of their dem erit points w ithin the past tw o years is at least 15"
O ct 4. In Q 10, the text "...
one of those tickets involves..." should be interpreted as "...at least one of those tickets involves..."



O ct 2. Q 2 is asking for people w ho are born to either the sam e father or the sam e m other as M ichaelFox.




O ct 3. In Q 4, M ichaelFox is treated as the father.




Introduction




The goalof this assignm ent is to im prove your skills of w riting declarative queries on a relationaldatabase, in general, and also to im prove your fluency in S Q L (and S Q Lite).




You have been provided w ith the follow ing relationalschem a.




persons(fnam e, lnam e, bdate, bplace, address, phone)




births(regno, fnam e, lnam e, regdate, regplace, gender, f_fnam e, f_lnam e, m _fnam e, m _lnam e)




m arriages (regno, regdate, regplace, p1_fnam e, p1_lnam e, p2_fnam e, p2_lnam e)




vehicles(vin,m ake,m odel,year,color)




registrations(regno, regdate, expiry, plate, vin, fnam e, lnam e)




tickets(tno,regno,fine,violation,vdate)




dem eritN otices(ddate, fnam e, lnam e, points, desc)




The tables are derived from the specification of Assignm ent 1 and the nam es of the tables and colum ns should give the sem antics, except m inor differences w hich are explicit in table definitions, insert statem ents or queries.




C reating the database




U sing the S Q L statem ents provided, create the above tables in S Q Lite3 on Lab m achines w ith som e data. H ere is a sm allinitialdata to get you started.




(90 m arks) Q ueries




W rite dow n the follow ing queries in S Q L and run them in S Q Lite3 over the database created. You w illbe w riting O N E S Q L statem ent for every query (here O ne S Q L statem ent starts w ith a S E LEC T and ends w ith a sem icolon but m ay include m ultiple select statem ents com bined in the form of subqueries and/or using set operations). Your S Q L queries for questions 1-3 cannot use any of aggregation, grouping, or nesting (set operations are ok).W hen the query asks for nam e of a person, it m eans both first nam e and last nam e.




Find the nam es and the phone num bers of persons w ho have had a 1969 C hevrolet C am aro registered under their nam es at som e point.



Find the nam es of people other than M ichaelFox w ho are born to the sam e parent as M ichaelFox.



Find the nam es of persons w ho have the sam e grandfather as M ichaelFox. If X and Y denote the grandfathers of M ichaelFox (from m other and father sides respectively), then w e w ant to find allgrand children of X and Y. The result should exclude M ichaelFox.



W ho is the oldest child of M ichaelFox. In case of ties, return allthose ties.



Find the nam es of persons w ho have accum ulated 15 or m ore dem erit points w ithin the past tw o years, i.e. the sum of their dem erit points w ithin the past tw o years is m ore than 15.H int: C heck out the date and tim e functions in S Q Lite.



W ho is the partner of M ichaelFox. In case of m ultiple m arriages, return the one from the latest m arriage.H int: C heck out the lim itclause for sqlite. You m ay also find subqueries in the from clause useful.



For each color of a car w ith a registration that does not expire at least for another m onth, find the average num ber of tickets issued per registration, the average am ount of fine given, and the m axim um am ount of fine given. Include colors w ith no tickets in the output w ith zero counts (if applicable) or nullvalues.H int: you m ay find outer join useful.



For each year of a car, find the m ost frequent m ake and the m ost frequent car color. In case of ties, list allthose ties.



https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659741
1/3
08/10/2019 C M PU T 291 (Fall2019 LEC A 1 A 2 E A 1 E A 2): Assignm ent 2 S pec







C reate a view called personD etails w ith colum ns fnam e, lnam e, bdate, bplace, carsow ned, and ticketsR cvd. The view includes for each person, fnam e, lnam e, bdate, bplace, the num ber of different cars registered under the person nam e in the past year, and the num ber of different tickets given to those registered cars w ithin the past year. Include people w ho have no cars registered under their nam es or no tickets w ith zero values.



U sing the view created in Q 9, for every person w ho has received at least 3 different tickets w ithin the past year and one of those tickets involves a 'red light'violation (i.e. 'red light'appears in the violation text, e.g. 'red light crossing', 'crossing red light at 114 St and 87 Ave'), list the nam e of the person and the m ake and the m odelof the car for w hich the red violation ticket is given.



(upto 5 bonus m arks for the first 3 people ) Preparing test data




ritten queries should be tested for correctness and bug fixes, very m uch like program s w ritten in any program m ing language. For testing, you need to have enough data in your tables such that allyour queries are m eaningfuland non-trivial(e.g. the returned answ ers are not em pty). You are encouraged to share your data w ith your classm ates or use data prepared by them . To m ake this collaboration happen, there w illbe up to 5 bonus m arks (at the instructor's discretion) to the first 3 people w ho prepare a test data and share it w ith the rest of the class. M ake sure your data is correct and m eets the expectation of the assignm ent. If you are sharing your test data, please post it to the course discussion forum . P ut allyour insert statem ents in a file called a2-data.sql. M ake sure to put dow n your nam e, em ailand a date w hen it is published or revised at the beginning of the file as a com m ent line (e.g. -- D ata prepared by <firstnam e lastnam e, <em ailaddress, and published on <date). If you are using data prepared by som eone else, leave the identification line unchanged.



(10 m arks) Testing and report




Starting from scratch, create your database as




sqlite3 a2.db <a2-tables.sql




and populate your tables using data file a2-data.sql(prepared in the previous step) as




sqlite3 a2.db <a2-data.sql




P ut allyour S Q L queries in a file nam ed a2-queries.sql; Add the follow ing line at the beginning of the file




.echo on




and the follow ing line before each S Q L query (replacing X w ith the query num ber).




--Question X




R un your queries on your data file as




sqlite3 a2.db <a2-queries.sql a2-script.txt




You w illbe subm itting both a2-data.sqland a2-script.txt electronically as described in the instructions for subm issions.




Instructions for Subm issions




e w illm ake use of som e autom ated tools in testing your queries. Thus it is im portant that you follow the follow ing instructions closely.



Your queries w illbe tested under a TA account w ith the provided tables. D o not use any table or colum n nam es other than those provided.



W rite each query in a separate file. Your solution m ust have one SQ L statem ent for each query. In other w ords, you cannot use view s or tem porary tables unless you are explicitly asked to do so. The first query m ust be saved in a file nam ed 1.sql, the second query in a file nam ed 2.sql, and so on untilthe tenth query, w hich is to be saved in a file called 10.sql(the nam es are im portant!).



The first line of each query file m ust have the com m and:



.print Question X - CCID




w here X is the num ber of the query and C C ID is your C C ID . For exam ple, the first line of the third query file for the user w ith ccid 'drafiei'w illbe:




.print Question 3 - drafiei




The rest of each file m ust contain the S Q L query you are subm itting and nothing else.




Include w ith your subm ission a R E A D M E .txt file that has your nam e, ccid, lab section, and the list of people you collaborated w ith (as m uch as it is allow ed w ithin the course policy) or the line "I declare that I did not collaborate w ith anyone in this assignm ent". A subm ission w ithout a



R E A D M E .txt file or w ith m issing inform ation w illlose 5% of the totalm ark.




5. B undle allyour queries, insert statem ents (a2-data.sql) and scripts (a2-script.txt) into a single tarfile by executing the U nix com m and (everything should be on one line):




tar -czf a2.tgz README.txt a2-data.sql a2-script.txt 1.sql 2.sql 3.sql 4.sql 5.sql 6.sql 7.sql 8.sql 9.sql 10




S ubm it the file a2.tgz at the subm ission page after logging into eclass.



Eclass does not support versioning (unfortunately) and each new subm ission replaces your previous one. This m akes last m inute subm issions som ew hat risky. Avoid last m inute subm issions as m uch as you can, and check your subm issions after an upload to m ake sure the right content is uploaded. A com m on m istake is to use a w rong tar com m and and subm it a corrupt file.













Last m odified: M onday, 7 O ctober 2019, 2:10 PM







◄ Assignm ent 1 subm ission Jum p to... S Q L statem ents for "R el. A lgebra and S Q L" lab ►



















You are logged in as A run W oosaree (Log out)




C M PU T 291 (Fall2019 LEC A 1 A 2 EA 1 EA 2)




https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659741 2/3
08/10/2019 C M PU T 291 (Fall2019 LEC A 1 A 2 E A 1 E A 2): Assignm ent 2 S pec























































































































































































































































































































































https://eclass.srv.ualberta.ca/m od/page/view .php?id=3659741 3/3