CIS200 – Homework #7 – Simple Queries



Name _____________________________ Lecture time ________________

CSE1111 – Homework #7 (15 points) – Joins

People

[pic]

Violations

[pic]

Types

[pic]

The tables above represent an insurance company’s 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 driver’s license number of the offender, the date the violation happened, the violation type, and the state where the violation occurred.

1. (1 Points) What is Referential Data Integrity? Based only on the data shown, has Referential Data been violated for any of the relationships? Explain your answer.

2. (2 Points) In the following questions, assume that you are using the tables DVDs and Circulation (found below) in a query and are joining them on the key DVD#.

DVDs Circulation

|DVD# |Movie Name | |DVD# |Member# |

|1 |Titanic | |2 |110 |

|2 |The Thomas Crown Affair | |3 |125 |

|3 |Life is beautiful | |4 |110 |

|4 |Nikita | | | |

a) (1 point) Briefly explain how Access goes about “joining” the above two tables in a query? Do not explain the steps you perform in Access to create the join, but what the computer does to create the resulting recordset.

b) (1/2 point) How many records will the master list contain when combining the above 2 tables? __________

c) (1/2 point) How many records will result in the final dynaset joining these 2 table on the foreign key field DVD# assuming there are no select criteria specified. ______________

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.

3. (2 Points) List the driver’s license number, the first and last name, ad violation type associated with each traffic violation. Sort the results by the license number in increasing order.

Tables Used ______________________________ Join on _____________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

4. (2 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 _____________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

5. (2 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 ____________________________

|Field | | | | | |

|Table | | | | | |

|Total | | | | | |

|Sort | | | | | |

|Show | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

6. (2 Point) Write a query to display the number of violations in each vehicle category.

Tables Used _______________________________ Join on _____________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

7. (2 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 ____________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

8. (2 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 _____________________

|Field | | | | | |

|Table | | | | | |

|Total | | | | | |

|Sort | | | | | |

|Show | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

-----------------------

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

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download