Starting from:
$30
CMPUT291- Assignment 2 Solved
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 applicable) or null values. 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 prepared by <firstname lastname, <email address, and published on <date). If you are using data prepared by someone else, leave the identification line unchanged.
(10 marks) 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
Put all your SQL queries in a file named a2-queries.sql; Add the following line at the beginning of the file
.echo on
and the following line before each SQL query (replacing X with the query number).
--Question X
Run your queries on your data file as
sqlite3 a2.db <a2-queries.sql a2-script.txt
You will be submitting both a2-data.sql and a2-script.txt electronically as described in the instructions for submissions.
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 applicable) or null values. 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 prepared by <firstname lastname, <email address, and published on <date). If you are using data prepared by someone else, leave the identification line unchanged.
(10 marks) 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
Put all your SQL queries in a file named a2-queries.sql; Add the following line at the beginning of the file
.echo on
and the following line before each SQL query (replacing X with the query number).
--Question X
Run your queries on your data file as
sqlite3 a2.db <a2-queries.sql a2-script.txt
You will be submitting both a2-data.sql and a2-script.txt electronically as described in the instructions for submissions.
1 file (107.4KB)
