数据库英语综合测试题16

Part 3: Questions and Answers

1.Consider the following relational schema:

student (student no, _rst name, last name)

book (isbn, title, authors, publisher, year)

loan (student no, isbn, checkout date, duration)

Use SQL to write the following queries:

A. Create the table for the book table appropriate domain and required contraints.

B. Change the data type of the checkout date attribute of the loan table to date.

C. Add a constraint into the loan table to make sure the loan duration is no more than 180 days.

D. Grant Arvil and Amy select and update authrorization on the book table.

2.What are 6 basic operators of relational algebra?

3.Explain how natural-join operation can be accomplished by basic relational algebra operations?

4.Explain how the division operation can be accomplished by basic relational algebra operations?

5.The database of a research center contains the following three tables about employees,projects, and the time spent by the employees on the projects. Employee(ssn: int, name: string, jobTitle: string)

Project(pid: int, name: string, sponsor: string, startYear: int, endYear: int) WorkedOn(ssn: int, pid: int, year: int, month: int, noHours: int).

The table Employee lists all the employees of the centre. The table Project lists all the projects of the centre with their sponsor and the start and end year of the project. The table WorkedOn records how many hours the employees have spent on which project in which month. For each table, the attributes that make up the primary key are underlined.

Express each of the following queries in relational algebra.

A. Return the names of the projects that were active in 2008.

B. Return the names of those programmers who in some month spent more than 60 hours on a project sponsored by the EU.

C.Return the names of those programmers who never worked on a project sponsored by the EU.

6.Consider the relational schema of Question 5. Write SQL queries over this schema that answer the following questions.

A. How many projects that were active in 2008 were sponsored by the EU?

B. For each project, year, and month, how many hours of work have been spent? (Return only data for a project, year and month if some time has been spent.)

C. How many programmers are there who have experience in working on a project sponsored by the EU? (Note that a programmer who worked on two or more projects should be counted only once.)

D. Return the names of the programmers who worked on no more than two projects sponsored by the EU. (Note that this includes the programmers who never worked on any project sponsored by the EU.)

E. Which programmer(s) spent the maximal total number of hours on EU projects