数据库英语综合测试题16

Part 3: Questions and Answers1.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 projectsamong 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, creditCardroomNo → roomCatroomNo, from → guestID, toroomNo, to → 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 y our answer is “no”, show which dependencies have been lost.8.Draw an ER diagram that captures all the following information:_ Patients are identified by an SSN, and their names, addresses and ages must be recorded._ Doctors are identified by an SSN. For each doctor, the name, specialty and years of experience must be recorded._ Each pharmacy has a name, address and phone number. A pharmacy must have a manager._ A pharmacist is identified by an SSN, he/she can only work for one pharmacy. For eachpharmacist, the name, qualification must be recorded._ For each drug, the trade name and formula must be recorded._ Every patient has a primary physician. Every doctor has at least one patient._ Each pharmacy sells several drugs, and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another._ Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and quantity associated with it.9.Convert the following E/R design (for a simple banking application) into a relational design. Give the relational design as a relational diagram with arrows to indicate the foreign key relationships. Underline all attributes that correspond to primary keys.。