数据库英语综合测试题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 among all programmers working on EU projects?
7.Consider the following relation that keeps track of the bookings in a hotel:
Booking(guestID, guestName, creditCard, roomNo, roomCat, from, to).
Suppose the following functional dependencies hold on the relation:
guestID → guestName, creditCard
roomNo → roomCat
roomNo, from → guestID, to
roomNo, to →schedule英语怎么发音 guestID, from.
A. Decompose the relation in smaller relations such that
– each of the smaller relations is in BNCF with respect to the projection of the original dependencies;
– the decomposition is a lossless join decomposition.
B.Is your decomposition dependency preserving? If your answer is “yes”, argue why. If your answer is “no”, show which dependencies have been lost.
8.Draw an ER diagram that captures all the following information:

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。