CIS 452 - Homework #2

Due: Friday, March 13

Consider the automobile insurance sample database.
  1. Write relational algebra expressions to find:
    1. Names and ssn numbers of people who live in MA.
    2. Names of agents who earn a salary of more than $55,000.
    3. Names and ssn numbers of automobile owners who live in MA.
    4. Names and ssn numbers of people who own Toyotas.
    5. Ssn's of people who have been the driver in more than one accident.
    6. Names and ssn's of people who have never been in an accident.
    7. Names and ssn's of people in MA who do not own an automobile.
  2. Write SQL statements to find:
    1. Names and ssn numbers of people who live in MA.
    2. Names of agents who earn a salary of more than $55,000.
    3. Names and ssn numbers of automobile owners who live in MA.
    4. Names and ssn numbers of people who own Toyotas.
    5. Ssn's of people who have been the driver in more than one accident.
  3. Write an SQL statement to create a table, policy(pnum, vin, pdate, coverage, premium), to record information about insurance policies, including:
  4. Write SQL statements to add the data shown below to the policy table. Hint: In mysql date literals are written between single quotes in the yyyy-mm-dd format. For example, Feb. 6, 2009 is written as '2009-02-06'. Do not include commas in number literals.
    5. pnumvinpdatecoveragepremium
    02388Y48P96B235K2007-01-3115000300
    9421LW37HG894A2Z2005-08-039000155
    76325Y63HF28K9L12009-02-0612000247
  5. Write an SQL statement to change the coverage of policy 9421 to $11,000.
  6. Write an SQL statement to delete policy 7632.