Homework 3 Instructions

1. Create the following tables in your database. Make sure to include the appropriate foreign keys. Make sure that the data types and sizes are appropriate. It is a good idea to read problem 2 before doing this.

employee(ID, person_name, street, city)

works(employee_id, company_name, salary)

company(company_name, city)

manager(employee_id, manager_id)

*BOLD IS PRIMARY KEYS*

2. Write python code to insert the data from the file homework_3.problem_2.data.txt into the tables created above. Your code must test to see if a row of data exists in the table you are inserting it into before doing the insert (count = 0). Make sure to commit the inserts when they are all done. If there is an error, you must do a rollback.

3. Using the tables above, write python code to get the name of each employee and the city that they work in.

a. Write code that uses a single SQL query

b. Write code that uses at least 2 SQL queries: use the output from one query in the 2nd query.

4. Using the tables above, write python code to get the name of each employee and the name of their manager.

a. Write code that uses a single SQL query

b. Write code that uses at least 2 SQL queries: use the output from one query in the 2nd query.

TXT FILE:

Table Id Name Street,City employee 111 John Green St Manchester employee 222 Sam Green St Manchester employee 333 Jill Orange St Portland employee 444 Erica Apple Ave Portland employee 555 James Blueberry St Atlanta employee 666 Sam Dogwood St Oakland employee 777 Alex Maple St Chicago employee 444 Erica Apple Ave Portland employee 888 Kate Oak Ave Austen

Table Id Company Salary works 111 First Bank 900000 works 222 First Bank 150000 works 333 2nd Bank 90000 works 444 3rd Bank 110000 works 555 My Bank 75000 works 666 Not a bank 80000 works 777 Banksie 95000 works 888 Texas Bank 11000

Table Company City company First Bank Hartford company 2nd Bank Portland company 3rd Bank Portland company My Bank Atlanta company Not a bank Oakland company Banksie Chicago company Texas Bank Austen

Table Employee Id Manager Id manager 111 222 manager 333 444 manager 555 666 manager 777 666