CIS200 – Homework #7 – Simple Queries



Name _____________________________ Lecture time ________________

CSE1111 – Homework #5 (30 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 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.

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

3. (1 Point) List the driver’s 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 ________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

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

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

5. (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 ____ ______________________

|Field | | | | | |

|Table | | | | | |

|Total | | | | | |

|Sort | | | | | |

|Show | | | | | |

|Criteria | | | | | |

|OR | | | | | |

|OR | | | | | |

6. (4 Points) 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. (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 ___________________

|Field | | | | |

|Table | | | | |

|Total | | | | |

|Sort | | | | |

|Show | | | | |

|Criteria | | | | |

|OR | | | | |

|OR | | | | |

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

|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

Table name:

Violations

Primary Key

Table name:

People

Primary Key

Table name:

Type

Primary Key

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

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

Google Online Preview   Download