Homework SixCS325 Database Management SystemsSpring 2009Date Assigned: 3-5-2009Problem One:Consider the database shown above.Specify the following queries on the relational schema shown above using SQL DML. Describe INENGLISH, how the query works. Also show the result of each query as it would apply to the databaseshown above.a) Retrieve the names of all senior students majoring in ‘CS’ (Computer Science)b) Retrieve the names of all courses taught by Professor King in 1998 and 1999.c) For each section taught by Professor King, retrieve the course number, semester, year, and number ofstudents who took the section.d) Retrieve the name and transcript of each senior student (Class = 5) majoring in CS. A transcript includescourse name, course number, credit hours, semester, year, and grade for each course completed by thestudent.e) Retrieve the names and major departments of all straight-A students (students who have a grade of A in allof their courses).f) Retrieve the names and major departments of all students who do not have a grade of A in any of theircourses.Grading Criteria:Each sub-problem receives a maximum of 5 points. Maximum grade for problem is 30 points. Each problem isgraded differently with the cost of an error weighted by the underlying principle of the problem.Problem Two:Consider the relational database shown above.Specify the following queries on the relational schema shown above using SQL DML. Describe INENGLISH, how the query works. Also show the result of each query as it would apply to the databaseshown above.a) Retrieve the department number, number of employees, combined salary, and average salary for eachdepartment.b) Retrieve the department number and number of employees for each department with a combined salaryover $100,000.c) Retrieve the department number and average salary for any department with more employees thandepartment 4.d) Consider the following view, DEPT_SUMMARY, defined on the COMPANY database:CREATE VIEW DEPT_SUMMARY(DNO, COUNT, SUM_SALARY, AVERAGE_SALARY)AS SELECT DNO, COUNT(*), SUM(SALARY), AVG(SALARY)FROM EMPLOYEEGROUP BY DNO;Is the following UPDATE possible? Why or why not?UPDATE DEPT_SUMMARYSET DNO = 3WHERE DNO = 4;e) Consider the view from problem (d).Is the following DELETE possible? Why or why not?DELETEFROM DEPT_SUMMARYWHERE COUNT > 4;Grading Criteria:Each sub-problem receives a maximum of 5 points. Maximum grade for problem is 30 points. Each problem isgraded differently with the cost of an error weighted by the underlying principle of the
View Full Document
Unlocking...