تكلم مع الذكاء الاصطناعي
Deck 6: Formal Relational Languages: The Algebra and Calculus
ملء الشاشة (f)
سؤال
Query 1. Retrieve the name and address of all employees who work for the 'Research' department.
Query 2. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date
Query 3. Find the names of employees who work on all the projects controlled by department number 5.
Query 4. Make a list of project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project.
Query 5. List the names of all employees with two or more dependents. Strictly speaking, this query cannot be done in the basic (original) relational algebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that
Query 6. Retrieve the names of employees who have no dependents. This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.
Query 7. List the names of managers who have at least one dependent
Show the result of each of the sample queries as it would apply to the database state in Figure 3.6.
سؤال
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.
سؤال
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
سؤال
Consider the two tables T1(X) and T2(X) shown in Figure 6.15. Show the results of the following operations:
سؤال
Exercise 6.16
Specify the following queries on the COMPANYrelational 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 in Figure 3.6.
a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
b. List the names of all employees who have a dependent with the same first name as themselves.
c. Find the names of all employees who 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 all employees who work on every project.
f. Retrieve the names of all employees who do not work on any project.
g. For each department, retrieve the department name and the average salary of all employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of all 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 all department managers who have no dependents
Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus.
Specify the following queries on the COMPANYrelational 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 in Figure 3.6.
a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
b. List the names of all employees who have a dependent with the same first name as themselves.
c. Find the names of all employees who 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 all employees who work on every project.
f. Retrieve the names of all employees who do not work on any project.
g. For each department, retrieve the department name and the average salary of all employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of all 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 all department managers who have no dependents
Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus.
سؤال
Exercise 6.18
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
Specify queries c, d, and e of Exercise 6.18 in both tuple and domain relational calculus.
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
Specify queries c, d, and e of Exercise 6.18 in both tuple and domain relational calculus.
سؤال
سؤال
List the birth date and address of the employee whose name is 'John B. Smith'.
Rewrite the domain relational calculus queries that followed Q0,in the style of the abbreviated notation of Q0A, where the objective is to minimize the number of domain variables by writing constants in place of variables wherever possible.
Rewrite the domain relational calculus queries that followed Q0,in the style of the abbreviated notation of Q0A, where the objective is to minimize the number of domain variables by writing constants in place of variables wherever possible.
سؤال
سؤال
A nested query is query within a query. More specifically, a nested query is a parenthesized query that can be used as a value in a number of places, such as instead of a relation or a selection condition. Specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and 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. List the names of all employees who work in the department that has the employee with the highest salary among all employees.
b. List the names of all employees whose supervisor's supervisor has '888665555' for SSN.
c. List the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
سؤال
فتح الحزمة
قم بالتسجيل لفتح البطاقات في هذه المجموعة!
Unlock Deck
Unlock Deck
1/11
العب
ملء الشاشة (f)
Deck 6: Formal Relational Languages: The Algebra and Calculus
1
Query 1. Retrieve the name and address of all employees who work for the 'Research' department.
Query 2. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date
Query 3. Find the names of employees who work on all the projects controlled by department number 5.
Query 4. Make a list of project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project.
Query 5. List the names of all employees with two or more dependents. Strictly speaking, this query cannot be done in the basic (original) relational algebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that
Query 6. Retrieve the names of employees who have no dependents. This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.
Query 7. List the names of managers who have at least one dependent
Show the result of each of the sample queries as it would apply to the database state in Figure 3.6.
(QUERY 1) Find the name and address of all employees who work for the 'Research'
department.
Result: FNAME LNAME ADDRESS
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
(QUERY 2) For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date.
Result:
PNUMBER DNUM LNAME ADDRESS BDATE
10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
30 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
(QUERY 3) Find the names of all employees who work on all the projects controlled by
department number 5.
Result: (empty because no tuples satisfy the result).
LNAME FNAME
(QUERY 4) Make a list of project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.
Result: PNO
1
2
(QUERY 5) List the names of all employees with two or more dependents.
Result: LNAME FNAME
Smith John
Wong Franklin
(QUERY 6) List the names of employees who have no dependents.
Result: LNAME FNAME
Zelaya Alicia
Narayan Ramesh
English Joyce
Jabbar Ahmad
Borg James
(QUERY 7) List the names of managers who have at least one dependent.
Result: LNAME FNAME
Wallace Jennifer
Wong Franklin
department.
Result: FNAME LNAME ADDRESS
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX
(QUERY 2) For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date.
Result:
PNUMBER DNUM LNAME ADDRESS BDATE
10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
30 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
(QUERY 3) Find the names of all employees who work on all the projects controlled by
department number 5.
Result: (empty because no tuples satisfy the result).
LNAME FNAME
(QUERY 4) Make a list of project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.
Result: PNO
1
2
(QUERY 5) List the names of all employees with two or more dependents.
Result: LNAME FNAME
Smith John
Wong Franklin
(QUERY 6) List the names of employees who have no dependents.
Result: LNAME FNAME
Zelaya Alicia
Narayan Ramesh
English Joyce
Jabbar Ahmad
Borg James
(QUERY 7) List the names of managers who have at least one dependent.
Result: LNAME FNAME
Wallace Jennifer
Wong Franklin
2
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.
In the relational algebra, as in other languages, it is possible to specify the same query
in multiple ways. We give one possible solution for each query. We use the symbol s for
SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION.
(a) EMP_W_X < -- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO)
(WORKS_ON)
EMP_WORK_10 < -- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS > 10 (EMP_W_X))
RESULT < -- P LNAME,FNAME ( s DNO=5 (EMP_WORK_10))
Result:
LNAME FNAME
Smith John
English Joyce
(b) E < -- (EMPLOYEE) J (SSN,FNAME),(ESSN,DEPENDENT_NAME) (DEPENDENT)
R < -- P LNAME,FNAME (E)
Result (empty):
LNAME FNAME
(c) WONG_SSN < -- P SSN ( s FNAME='Franklin' AND
LNAME='Wong' (EMPLOYEE))
WONG_EMPS < -- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN)
RESULT < -- P LNAME,FNAME (WONG_EMPS)
Result:
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce
(d) PROJ_HOURS(PNO,TOT_HRS) < -- PNO f SUM HOURS (WORKS_ON)
RESULT < -- P PNAME,TOT_HRS ( (PROJ_HOURS) J (PNO),(PNUMBER)
(PROJECT) )
Result:
PNAME TOT_HRS
ProductX 52.5
ProductY 37.5
ProductZ 50.0
Computerization 55.0
Reorganization 25.0
Newbenefits 55.0
(e) PROJ_EMPS(PNO,SSN) < -- P PNO,ESSN (WORKS_ON)
ALL_PROJS(PNO) < -- P PNUMBER (PROJECT)
EMPS_ALL_PROJS < -- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)
RESULT < -- P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)
Result (empty):
LNAME FNAME
(f) ALL_EMPS < -- P SSN (EMPLOYEE)
WORKING_EMPS(SSN) < -- P ESSN (WORKS_ON)
NON_WORKING_EMPS < -- ALL_EMPS - WORKING_EMPS (* DIFFERENCE*)
RESULT < -- P LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)
Result (empty):
LNAME FNAME
(g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) < -- DNO f AVG SALARY
(EMPLOYEE)
RESULT < -- P DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )
Result:
DNUMBER AVG_SAL
Research 33250
Administration 31000
Headquarters 55000
(h) RESULT(AVG_F_SAL) < -- f AVG SALARY ( s SEX='F' (EMPLOYEE) )
Result:
AVG_F_SAL
31000
(i) E_P_HOU(SSN) < --
P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston' (PROJECT)))
D_NO_HOU < --
P DNUMBER (DEPARTMENT) - P DNUMBER ( s
DLOCATION='Houston' (DEPARTMENT))
E_D_NO_HOU < -- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))
RESULT_EMPS < -- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)
RESULT < -- P LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME ADDRESS
Wallace Jennifer 291 Berry, Bellaire, TX
(j) DEPT_MANAGERS(SSN) < -- P MGRSSN (DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN) < -- P ESSN (DEPENDENT)
RESULT_EMPS < -- DEPT_MANAGERS - EMPS_WITH_DEPENDENTS
RESULT < -- P LNAME,FNAME (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME
Borg James
in multiple ways. We give one possible solution for each query. We use the symbol s for
SELECT, P for PROJECT, J for EQUIJOIN, * for NATURAL JOIN, and f for FUNCTION.
(a) EMP_W_X < -- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO)
(WORKS_ON)
EMP_WORK_10 < -- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS > 10 (EMP_W_X))
RESULT < -- P LNAME,FNAME ( s DNO=5 (EMP_WORK_10))
Result:
LNAME FNAME
Smith John
English Joyce
(b) E < -- (EMPLOYEE) J (SSN,FNAME),(ESSN,DEPENDENT_NAME) (DEPENDENT)
R < -- P LNAME,FNAME (E)
Result (empty):
LNAME FNAME
(c) WONG_SSN < -- P SSN ( s FNAME='Franklin' AND
LNAME='Wong' (EMPLOYEE))
WONG_EMPS < -- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN)
RESULT < -- P LNAME,FNAME (WONG_EMPS)
Result:
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce
(d) PROJ_HOURS(PNO,TOT_HRS) < -- PNO f SUM HOURS (WORKS_ON)
RESULT < -- P PNAME,TOT_HRS ( (PROJ_HOURS) J (PNO),(PNUMBER)
(PROJECT) )
Result:
PNAME TOT_HRS
ProductX 52.5
ProductY 37.5
ProductZ 50.0
Computerization 55.0
Reorganization 25.0
Newbenefits 55.0
(e) PROJ_EMPS(PNO,SSN) < -- P PNO,ESSN (WORKS_ON)
ALL_PROJS(PNO) < -- P PNUMBER (PROJECT)
EMPS_ALL_PROJS < -- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)
RESULT < -- P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)
Result (empty):
LNAME FNAME
(f) ALL_EMPS < -- P SSN (EMPLOYEE)
WORKING_EMPS(SSN) < -- P ESSN (WORKS_ON)
NON_WORKING_EMPS < -- ALL_EMPS - WORKING_EMPS (* DIFFERENCE*)
RESULT < -- P LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)
Result (empty):
LNAME FNAME
(g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) < -- DNO f AVG SALARY
(EMPLOYEE)
RESULT < -- P DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )
Result:
DNUMBER AVG_SAL
Research 33250
Administration 31000
Headquarters 55000
(h) RESULT(AVG_F_SAL) < -- f AVG SALARY ( s SEX='F' (EMPLOYEE) )
Result:
AVG_F_SAL
31000
(i) E_P_HOU(SSN) < --
P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston' (PROJECT)))
D_NO_HOU < --
P DNUMBER (DEPARTMENT) - P DNUMBER ( s
DLOCATION='Houston' (DEPARTMENT))
E_D_NO_HOU < -- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))
RESULT_EMPS < -- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)
RESULT < -- P LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME ADDRESS
Wallace Jennifer 291 Berry, Bellaire, TX
(j) DEPT_MANAGERS(SSN) < -- P MGRSSN (DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN) < -- P ESSN (DEPENDENT)
RESULT_EMPS < -- DEPT_MANAGERS - EMPS_WITH_DEPENDENTS
RESULT < -- P LNAME,FNAME (EMPLOYEE * RESULT_EMPS)
Result:
LNAME FNAME
Borg James
3
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
(Note: We will use S for SELECT, P for PROJECT, * for NATURAL JOIN, - for
SET DIFFERENCE, F for AGGREGATE FUNCTION)
(a) A < -- BOOKCOPIES * LIBRARY-BRANCH * BOOK
RESULT < -- P No_Of_Copies ( S BranchName='Sharpstown' and Title='The Lost
Tribe'
(A) )
Note: A better query would be to do the SELECTs before the JOIN as follows:
A < -- P No_Of_Copies ( ( S BranchName='Sharpstown' (LIBRARY-BRANCH) ) *
(BOOKCOPIES * ( S Title='The Lost Tribe'
(BOOK) ) ) )
(b) P BranchID,No_Of_Copies ( ( S Title='The Lost Tribe' (BOOK)) * BOOKCOPIES )
(c) NO_CHECKOUT_B < -- P CardNo (BORROWER) - P CardNo (BOOK_LOANS)
RESULT < -- P Name (BORROWER * NO_CHECKOUT_B)
(d) S < -- P BranchId ( S BranchName='Sharpstown' (LIBRARY-BRANCH) )
B_FROM_S < -- P BookId,CardNo ( ( S DueDate='today' (BOOKLOANS) ) * S )
RESULT < -- P Title,Name,Address ( BOOK * BORROWER * B_FROM_S )
(e) R(BranchId,Total) < -- BranchId FCOUNT(BookId,CardNo) (BOOK_LOANS)
RESULT < -- P BranchName,Total (R * LIBRARY_BRANCH)
(f) B(CardNo,TotalCheckout) < -- CardNo F COUNT(BookId) (BOOK_LOANS)
B5 < -- S TotalCheckout > 5 (B)
RESULT < -- P Name,Address,TotalCheckout ( B5 * BORROWER)
(g) SK(BookId,Title) < -- ( sAuthorName='Stephen King' ( BOOK_AUTHORS)) * BOOK
CENTRAL(BranchId) < -- sBranchName='Central' ( LIBRARY_BRANCH )
RESULT < -- P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL )
SET DIFFERENCE, F for AGGREGATE FUNCTION)
(a) A < -- BOOKCOPIES * LIBRARY-BRANCH * BOOK
RESULT < -- P No_Of_Copies ( S BranchName='Sharpstown' and Title='The Lost
Tribe'
(A) )
Note: A better query would be to do the SELECTs before the JOIN as follows:
A < -- P No_Of_Copies ( ( S BranchName='Sharpstown' (LIBRARY-BRANCH) ) *
(BOOKCOPIES * ( S Title='The Lost Tribe'
(BOOK) ) ) )
(b) P BranchID,No_Of_Copies ( ( S Title='The Lost Tribe' (BOOK)) * BOOKCOPIES )
(c) NO_CHECKOUT_B < -- P CardNo (BORROWER) - P CardNo (BOOK_LOANS)
RESULT < -- P Name (BORROWER * NO_CHECKOUT_B)
(d) S < -- P BranchId ( S BranchName='Sharpstown' (LIBRARY-BRANCH) )
B_FROM_S < -- P BookId,CardNo ( ( S DueDate='today' (BOOKLOANS) ) * S )
RESULT < -- P Title,Name,Address ( BOOK * BORROWER * B_FROM_S )
(e) R(BranchId,Total) < -- BranchId FCOUNT(BookId,CardNo) (BOOK_LOANS)
RESULT < -- P BranchName,Total (R * LIBRARY_BRANCH)
(f) B(CardNo,TotalCheckout) < -- CardNo F COUNT(BookId) (BOOK_LOANS)
B5 < -- S TotalCheckout > 5 (B)
RESULT < -- P Name,Address,TotalCheckout ( B5 * BORROWER)
(g) SK(BookId,Title) < -- ( sAuthorName='Stephen King' ( BOOK_AUTHORS)) * BOOK
CENTRAL(BranchId) < -- sBranchName='Central' ( LIBRARY_BRANCH )
RESULT < -- P Title,NoOfCopies ( SK * BOOKCOPIES * CENTRAL )
4
Consider the two tables T1(X) and T2(X) shown in Figure 6.15. Show the results of the following operations:
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
5
Exercise 6.16
Specify the following queries on the COMPANYrelational 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 in Figure 3.6.
a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
b. List the names of all employees who have a dependent with the same first name as themselves.
c. Find the names of all employees who 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 all employees who work on every project.
f. Retrieve the names of all employees who do not work on any project.
g. For each department, retrieve the department name and the average salary of all employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of all 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 all department managers who have no dependents
Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus.
Specify the following queries on the COMPANYrelational 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 in Figure 3.6.
a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project.
b. List the names of all employees who have a dependent with the same first name as themselves.
c. Find the names of all employees who 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 all employees who work on every project.
f. Retrieve the names of all employees who do not work on any project.
g. For each department, retrieve the department name and the average salary of all employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of all 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 all department managers who have no dependents
Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus.
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
6
Exercise 6.18
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
Specify queries c, d, and e of Exercise 6.18 in both tuple and domain relational calculus.
Consider the LIBRARY relational schema shown in Figure 6.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the notation of Figure 3.7. Write down relational expressions for the following queries on the LIBRARY database:
(a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"?
(b) How many copies of the book titled The Lost Tribe are owned by each library branch?
(c) Retrieve the names of all borrowers who do not have any books checked out.
(d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
(e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.
(g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
Specify queries c, d, and e of Exercise 6.18 in both tuple and domain relational calculus.
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
7
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?
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
8
List the birth date and address of the employee whose name is 'John B. Smith'.
Rewrite the domain relational calculus queries that followed Q0,in the style of the abbreviated notation of Q0A, where the objective is to minimize the number of domain variables by writing constants in place of variables wherever possible.
Rewrite the domain relational calculus queries that followed Q0,in the style of the abbreviated notation of Q0A, where the objective is to minimize the number of domain variables by writing constants in place of variables wherever possible.
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
9
Show how you can specify the following relational algebra operations in both tuple and domain relational calculus.
(a) SELECT A=c (R(A, B, C)):
(b) PROJECT < A, B > (R(A, B, C)):
(c) R(A, B, C) NATURAL JOIN S(C, D, E):
(d) R(A, B, C) UNION S(A, B, C):
(e) R(A, B, C) INTERSECT S(A, B, C):
(f) R(A, B, C) MINUS S(A, B, C):
(g) R(A, B, C) CARTESIAN PRODUCT S(D, E, F):
(h) R(A, B) DIVIDE S(A):
(a) SELECT A=c (R(A, B, C)):
(b) PROJECT < A, B > (R(A, B, C)):
(c) R(A, B, C) NATURAL JOIN S(C, D, E):
(d) R(A, B, C) UNION S(A, B, C):
(e) R(A, B, C) INTERSECT S(A, B, C):
(f) R(A, B, C) MINUS S(A, B, C):
(g) R(A, B, C) CARTESIAN PRODUCT S(D, E, F):
(h) R(A, B) DIVIDE S(A):
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
10
A nested query is query within a query. More specifically, a nested query is a parenthesized query that can be used as a value in a number of places, such as instead of a relation or a selection condition. Specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and 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. List the names of all employees who work in the department that has the employee with the highest salary among all employees.
b. List the names of all employees whose supervisor's supervisor has '888665555' for SSN.
c. List the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
11
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))
C) (∃ x) (P(x)) ⇒ ∀ x ((P(x))
A)NOT (P(x) OR Q(x)) ⇒ (NOT (P(x)) AND (NOT (Q(x)))
B)NOT (∃ x) (P(x)) ⇒ ∀ x (NOT (P(x))
C) (∃ x) (P(x)) ⇒ ∀ x ((P(x))
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
فتح الحزمة
k this deck
فتح الحزمة
افتح القفل للوصول البطاقات البالغ عددها 11 في هذه المجموعة.
