View the step-by-step solution to:

INFO620: Enterprise Database Systems - Assignments Page |1 Name: _________________________________________ Date: __________________ Week 2 -...

CH 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification
#5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show the query results if applied to the database of Figure 3.6.
a. For each department whose average employee salary is more than \$30,000 retrieve the department name and the number of employees working for that department.
b. Suppose we want the number of male employees in each department rather than all employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not?
#5.6 - Specify the following queries in SQL on the database schema of Figure 1.2.
a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses).
b. Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.
#5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and the concepts described in this chapter.
a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.
b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555' for SSN.
c. Retrieve the names of employees who make at least \$10,000 more than the employee who is paid the least in the company.
CH 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS
#6.16 - Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6.
a. Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project.
b. List the names of employees who have a dependent with the same first name as themselves.
c. Find the names of employees that are directly supervised by 'Franklin Wong'.
d. For each project, list the project name and the total hours per week (by all employees) spent on that project.
e. Retrieve the names of employees who work on every project.
f. Retrieve the names of employees who do not work on any project.
g. For each department, retrieve the department name, and the average salary of employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston.
j. List the last names of department managers who have no dependents.
#6.27 - In a tuple relational calculus query with n tuple variables, what would be the typical minimum number of join conditions? Why? What is the effect of having a smaller number of join conditions?
#6.33 - State whether the following conclusion are true or false:
a. NOT (P(x) OR Q(x))  (NOT (P(x)) AND (NOT (Q(x)))
b. NOT ( x) (P(x))   x (NOT (P(x))
b. ( x) (P(x))   x ((P(x))
CH 7: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL
#7.17 - Composite and multi-valued attributes can be nested to any number of levels. Suppose we want to design an attribute for a STUDENT entity type to keep track of previous college education. Such an attribute will have one entry for each college previously attended, and this entry is composed of: college name, start and end dates, degree entries (degrees awarded at that college, if any), and transcript entries (courses completed at that college, if any). Each degree entry is formed of degree name and the month and year it was awarded, and each transcript entry is formed of a course name, semester, year, and grade. Design an attribute to hold this information. Use the conventions of Figure 7.5.
#7.19 - Consider the ER diagram of Figure 7.20, which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that resulted in this schema. Try to be as precise as possible in your requirements and constraints specification.
#7.22 - A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Try to design an ER schema diagram for this application, stating any assumptions you make. Choose your favorite sport (soccer, football, baseball ...).
#7.25 - Consider the ER diagram in Figure 7.23. Assume that a course may or may not use a textbook, but that a text by definition is a book that is used in some course. A course may not use more than five books. Instructors teach from two to four courses. Supply (min, max) constraints on this diagram. State clearly any additional assumptions you make. If we add the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints would you put on it? Why?

see attached for reference

I N F O 6 2 0 : E n t e r p r i s e D a t a b a s e S y s t e m s – A s s i g n m e n t s P a g e | 1 Name: _________________________________________ Date: __________________ Week 2 – Assignment #2: Chapters 5-7 Grade: CH 5: More SQL: Complex Queries, Triggers, Views, and Schema Modification #5.5 - Specify the following additional queries on the database of Figure 3.5 in SQL. Show the query results if applied to the database of Figure 3.6. a. For each department whose average employee salary is more than \$30,000 retrieve the department name and the number of employees working for that department. b. Suppose we want the number of male employees in each department rather than all employees (as in Exercise 5.4a). Can we specify this query in SQL? Why or why not? #5.6 - Specify the following queries in SQL on the database schema of Figure 1.2. a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses). b. Retrieve the names and major departments of all students who do not have any grade of A in any of their courses. #5.7 - In SQL, specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and the concepts described in this chapter. a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. b. Retrieve the names of all employees whose supervisor’s supervisor has '888665555' for SSN. c. Retrieve the names of employees who make at least \$10,000 more than the employee who is paid the least in the company. CH 6: THE RELATIONAL ALGEBRA AND RELATIONAL CALCULUS #6.16 - Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6. a. Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project. b. List the names of employees who have a dependent with the same first name as themselves. c. Find the names of employees that are directly supervised by 'Franklin Wong'. d. For each project, list the project name and the total hours per week (by all employees) spent on that project. e. Retrieve the names of employees who work on every project. f. Retrieve the names of employees who do not work on any project.
I N F O 6 2 0 : E n t e r p r i s e D a t a b a s e S y s t e m s – A s s i g n m e n t s P a g e | 2 g. For each department, retrieve the department name, and the average salary of employees working in that department. h. Retrieve the average salary of all female employees. i. Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston. j. List the last names of department managers who have no dependents. #6.27 - In a tuple relational calculus query with n tuple variables, what would be the typical minimum number of join conditions? Why? What is the effect of having a smaller number of join conditions? #6.33 - State whether the following conclusion are true or false: a. NOT (P( x ) OR Q( x )) (NOT (P( x )) AND (NOT (Q( x ))) b. NOT ( 5 x ) (P( x )) 2200 x (NOT (P( x )) b. ( 5 x ) (P( x )) 2200 x ((P( x )) CH 7: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL #7.17 - Composite and multi-valued attributes can be nested to any number of levels. Suppose we want to design an attribute for a STUDENT entity type to keep track of previous college education. Such an attribute will have one entry for each college previously attended, and this entry is composed of: college name, start and end dates, degree entries (degrees awarded at that college, if any), and transcript entries (courses completed at that college, if any). Each degree entry is formed of degree name and the month and year it was awarded, and each transcript entry is formed of a course name, semester, year, and grade. Design an attribute to hold this information. Use the conventions of Figure 7.5. #7.19 - Consider the ER diagram of Figure 7.20, which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that resulted in this schema. Try to be as precise as possible in your requirements and constraints specification. #7.22 - A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Try to design an ER schema diagram for this application, stating any assumptions you make. Choose your favorite sport (soccer, football, baseball . ..). #7.25 - Consider the ER diagram in Figure 7.23. Assume that a course may or may not use a textbook, but that a text by definition is a book that is used in some course. A course may not use more than five books. Instructors teach from two to four courses. Supply (min, max) constraints on this diagram. State clearly any additional assumptions you make. If we add the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints would you put on it? Why? 7.16

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
• -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents