Assignment 2 Spec CMPUT 291 - Introduction to File and Database Management Fall 2019 Assignment Two (individual assignment) Due date: Oct 11 at 5pm (submission details) Clarifications: No clarification will be posted here after 5pm on Oct 10th. Oct 8. In Q8, You will be returning the cartesian product of makes and models that are tied. This means if there are 2 makes and 3 colors that are the most frequent in 2019, then you will return 6 rows for 2019. Oct 7. Here is a marking rubric for the Assignment 2. Oct 7. Q5 should read "..., i.e. the sum of their demerit points within the past two years is at least 15" Oct 4. In Q10, the text "...one of those tickets involves..." should be interpreted as "...at least one of those tickets involves..." Oct 2. Q2 is asking for people who are born to either the same father or the same mother as Michael Fox. Oct 3. In Q4, Michael Fox is treated as the father. Introduction The goal of this assignment is to improve your skills of writing declarative queries on a relational database, in general, and also to improve your fluency in SQL (and SQLite).
You have been provided with the following relational schema. persons(fname, lname, bdate, bplace, address, phone) births(regno, fname, lname, regdate, regplace, gender, f_fname, f_lname, m_fname, m_lname) marriages (regno, regdate, regplace, p1_fname, p1_lname, p2_fname, p2_lname) vehicles(vin,make,model,year,color) registrations(regno, regdate, expiry, plate, vin, fname, lname) tickets(tno,regno,fine,violation,vdate) demeritNotices(ddate, fname, lname, points, desc) The tables are derived from the specification of Assignment 1 and the names of the tables and columns should give the semantics, except minor differences which are explicit in table definitions, insert statements or queries. Creating the database Using the SQL statements provided, create the above tables in SQLite3 on Lab machines with some data. Here is a small initial data to get you started. (90 marks) Queries Write down the following queries in SQL and run them in SQLite3 over the database created.
You will be writing ONE SQL statement for every query (here One SQL statement starts with a SELECT and ends with a semicolon but may include multiple select statements combined in the form of subqueries and/or using set operations). Your SQL queries for questions 1-3 cannot use any of aggregation, grouping, or nesting (set operations are ok). When the query asks for name of a person, it means both first name and last name.
1. Find the names and the phone numbers of persons who have had a 1969 Chevrolet Camaro registered under their names at some point. 2. Find the names of people other than Michael Fox who are born to the same parent as Michael Fox. 3. Find the names of persons who have the same grandfather as Michael Fox. If X and Y denote the grandfathers of Michael Fox (from mother and father sides respectively), then we want to find all grand children of X and Y. The result should exclude Michael Fox. 4. Who is the oldest child of Michael Fox. In case of ties, return all those ties. 5. Find the names of persons who have accumulated 15 or more demerit points within the past two years, i.e. the sum of their demerit points within the past two years is more than 15. Hint: Check out the date and time functions in SQLite. 6. Who is the partner of Michael Fox. In case of multiple marriages, return the one from the latest marriage.Hint: Check out the limit clause for sqlite. You may also find subqueries in the from clause useful. 7. For each color of a car with a registration that does not expire at least for another month, find the average number of tickets issued per registration, the average amount of fine given, and the maximum amount of fine given. Include colors with no tickets in the output with zero counts (if 10/10/2019 CMPUT 291 (Fall 2019 LEC A1 A2 EA1 EA2):
Hint: you may find outer join useful.
8. For each year of a car, find the most frequent make and the most frequent car color. In case of ties, list all those ties.
9. Create a view called personDetails with columns fname, lname, bdate, bplace, carsowned, and ticketsRcvd. The view includes for each person,
fname, lname, bdate, bplace, the number of different cars registered under the person name in the past year, and the number of different tickets
given to those registered cars within the past year. Include people who have no cars registered under their names or no tickets with zero values.
10. Using the view created in Q9, for every person who has received at least 3 different tickets within 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 name of the
person and the make and the model of the car for which the red violation ticket is given.
(upto 5 bonus marks for the first 3 people ) Preparing test data
Written queries should be tested for correctness and bug fixes, very much like programs written in any programming language. For testing, you need to
have enough data in your tables such that all your queries are meaningful and non-trivial (e.g. the returned answers are not empty). You are encouraged
to share your data with your classmates or use data prepared by them. To make this collaboration happen, there will be up to 5 bonus marks (at the
instructor's discretion) to the first 3 people who prepare a test data and share it with the rest of the class. Make sure your data is correct and meets the
expectation of the assignment. If you are sharing your test data, please post it to the course discussion forum. Put all your insert statements in a file called
a2-data.sql. Make sure to put down your name, email and a date when it is published or revised at the beginning of the file as a comment line (e.g. -- Data