2- In order to construct and run the following SQL queries you must import professor Table.csv...
Fantastic news! We've Found the answer you've been seeking!
Question:
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)
Expert Answer:
Related Book For
Statistics For Managers Using Microsoft Excel
ISBN: 9780133130805
7th Edition
Authors: David M. Levine, David F. Stephan, Kathryn A. Szabat
Posted Date:
Students also viewed these databases questions
-
QUESTON THREE China's rise over the past several decades has lifted more than half of its population out of poverty and reshaped the global economy. What has caused this dramatic transformation? In...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
During one week, four production employees of Martinez Manufacturing Company worked the hours shown below. All these employees receive overtime pay at one and one-half times their regular hourly rate...
-
Superheated water exists at 1.86 MPa and 420C. Determine its specific enthalpy using: i) The steam tables in the appendix (a double interpolation is needed) ii) The IRC Calculator iii) The equation h...
-
The Anti-Masons Political Party, founded in 1828, did not make a major impact, because it: was created as a reaction to the growing influence of secret groups like the Masons supported Andrew Jackson...
-
At the beginning of 2016, John Cornell decided to quit his job as a construction company ey supervisor and formed his own residential housing construction company. When he resigned, he had a contract...
-
Sports Emporium has two operating segments: sporting goods and sports apparel. The income statement for each operating segment is presented below. Required: 1. Complete the % columns to be used in a...
-
The lifetime of a new 6S hard-drive follows a Uniform distribution over the range of [1.5, 3.0 years]. A 6S hard-drive has been used for 2 years and is still working. What is the probability that it...
-
1. Name at least three ways that Shu could automate her asset management. Suggest at least one option for retirement savings, general savings, and general convenience. 2. What major factors should...
-
= tan 'a. log (x + y)+b tan), prove a'u, o'u ax Oy %3D
-
The system shown has an initial velocity of 20.0 m/s down the incline and it comes to rest after travelling 40.0 m. The incline is not smooth. (a) Draw a Free-Body Diagram for each mass. (b)...
-
16-5 A portfolio consists of stocks from three companies: General Dynamics, Target and ConAgra Foods. The portfolio's is held for 1 year. The number of shares and the stock prices at the beginning...
-
Demonstrate how the Graph data structure is implemented using adjacency Lists. Use appropriate coding to implement the correct methods to perform: * BFS graph traversal. * Calculating the minimum...
-
Sleep King Manufacturing Company ( SKMC ) is a public manufacturing company specializing in the fabrication of mattresses. The company has been operating from its headquarters in Toronto since 1 9 9...
-
Seller owns a parcel of investment land which Seller purchased four years ago for $2,000. Seller sells it to Buyer under an arrangement where Buyer pays Seller $2,000 cash in the current year and...
-
1. A small business is installing a new customer relationship management (CRM) system. You are the project manager and have drawn up an outline project plan for the main project activities, as shown...
-
Consider the sections of two circuits illustrated above. Select True or False for all statements.After connecting a and b to a battery, the voltage across R1 always equals the voltage across R2.Rcd...
-
What are the differences among the various measures of variation, such as the range, interquartile range, variance, standard deviation, and coefficient of variation, and what are the advantages and...
-
For this problem, use the following multiple regression equation: a. Interpret the meaning of the slopes. b. Interpret the meaning of the V intercept 50 2x7X
-
A vice president for marketing recruits 20 college graduates for management training. Each of the 20 individuals is randomly assigned, to one of two groups (10 in each group). A "traditional" method...
-
Presented below are the beginning-of-year common share price (P t-1 ), the end-of-year common share price (P t ), and the annual dividend (D t ) for three competitorsGeneral Electric, Philipps...
-
Compute the missing amounts in the following financial statements. You may assume that accounts receivable relate only to credit sales and that accounts payable relate only to credit purchases of...
-
Compute the missing amounts in the following financial statements. You may assume that accounts receivable relate only to credit sales and that accounts payable relate only to credit purchases of...
Study smarter with the SolutionInn App