数据库英语综合测试题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)
玩具市场U 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 lect 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 databa of a rearch 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 tho programmers who in some month spent more than 60 hours on a project sponsored by the EU.
C.Return the names of tho 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).
Suppo the following functional dependencies hold on the relation:
guestID → guestName, creditCard
roomNo → roomCat
roomNo, from 军训的内容→ guestID, to
roomNo, to → guestID, from.
A. Decompo 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 prerving? 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:
