2- In order to construct and run the following SQL queries you must import professor Table.csv...

Transcribed Image Text:

2- In order to construct and run the following SQL queries you must import professor Table.csv and course Table.csv into the same database in PHPMyAdmin. You are required to show a screenshot of the results in addition to the query. a- Construct an SQL query that will show the assignedID, professorName, and the professor's home department for each professor that teaches a 3000 level course (all course codes that begin with a two). (2 points) Construct an SQL query that will show all the course IDs, lecture Times, and Lecture durations for the Engineering department. (2 points) b- c- Business Development 3332 is no longer being taught by assignedID = 77, it is being taught by assignedID = 88. Update the course table to reflect this change. (3 points) d- In this imaginary university, they have a rule that states any professor cannot lecture for more than 6 hours per week. Construct an SQL query that will find any professor that is lecturing for more than 6 hours per week (please note the result set may be empty). (3 points) e- In this imaginary university, an Instructor is paid $250 per teaching hour (lecture length), each course runs for 15 weeks, and there is a lecture very week. Write a Python script that will output the Instructor's name, ID, and pay at the end of the semester. Lastly, compare the average pay of Female instructor versus Male instructors (e.g., the average pay for a Male instructor is: XYZ, the average pay for a Female instructor is XYZ. (5 points) 3-For each SQL query you construct in Question 2 (a-d, not e), write the associated relational algebraic expression (8 points). 4-Convert the following database schema to 3NF, and show all your intermediate steps (i.e., INF and 2NF). Does the resultant database conform to BCNF? Why or why not? (7 points) order(orderID, customerID, name, address, orderDate) orderDetails (orderID, productID, description, quantity, unitPrice) 2- In order to construct and run the following SQL queries you must import professor Table.csv and course Table.csv into the same database in PHPMyAdmin. You are required to show a screenshot of the results in addition to the query. a- Construct an SQL query that will show the assignedID, professorName, and the professor's home department for each professor that teaches a 3000 level course (all course codes that begin with a two). (2 points) Construct an SQL query that will show all the course IDs, lecture Times, and Lecture durations for the Engineering department. (2 points) b- c- Business Development 3332 is no longer being taught by assignedID = 77, it is being taught by assignedID = 88. Update the course table to reflect this change. (3 points) d- In this imaginary university, they have a rule that states any professor cannot lecture for more than 6 hours per week. Construct an SQL query that will find any professor that is lecturing for more than 6 hours per week (please note the result set may be empty). (3 points) e- In this imaginary university, an Instructor is paid $250 per teaching hour (lecture length), each course runs for 15 weeks, and there is a lecture very week. Write a Python script that will output the Instructor's name, ID, and pay at the end of the semester. Lastly, compare the average pay of Female instructor versus Male instructors (e.g., the average pay for a Male instructor is: XYZ, the average pay for a Female instructor is XYZ. (5 points) 3-For each SQL query you construct in Question 2 (a-d, not e), write the associated relational algebraic expression (8 points). 4-Convert the following database schema to 3NF, and show all your intermediate steps (i.e., INF and 2NF). Does the resultant database conform to BCNF? Why or why not? (7 points) order(orderID, customerID, name, address, orderDate) orderDetails (orderID, productID, description, quantity, unitPrice)