数据库英语综合测试题16

1、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

2、 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 bas

3、ic 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 table

4、s 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 list

5、s 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

6、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 program

7、mers 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 o

8、f 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 t

9、he 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 pro

10、jects?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, to roomNo, to g

11、uestID, 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 argue why.“

12、yes”, If your answer is “ no” , show which dependencies halovest.been8. Draw an ER diagram that captures all the following information:_ Patients are identified by an SSN, and their names, addressesand ages must be recorded._ Doctors are identified by an SSN. For each doctor, the name, specialty and

13、 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 f

14、ormula 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 patien

15、ts. 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 r

16、elational design as a relational diagram with arrows to indicate the foreign key relationships. Underline all attributes that correspond to primary keys.10. Consider the following employee database, where the primary keys are underlined.Employee(ename:string, streetstring, city:string);Works(employe

17、e:string, company:string, salary:real);Company(cname:string, city:string);Manages(employee:string, manager-name:string)Give a single SQL statement for each of the following queries:A. Find the names, street addresses, and cities of residence of all employees who work for First Bank Corporation"

18、 and earn more than $40,000.B. Find the names of all employees in the database who live in the same cities as the companies for which they work.C. Give all managers of First Bank Corporation" a 10 percent salary raise.D. Find the names of all employees in the database who earn more than any emp

19、loyee of Small Bank Corporation".E. Assume that the companies may be located in several cities. Find the names of all companies located in every city in which Small Bank Corporation" is located.F. Find the name of the company that has the most employees.G. Find those companies whose employ

20、ees earn a higher salary, on average, than the average salary at First Bank Corporation", display those companies' names in ascending order.11. Consider the following (simplified) relational schema for university study: Student(id:integer, family:string, given:string, degree:string, enrolle

21、d:date) Course(id:integer, code:string, session:string, title:string, syllabus:string) Enrolment(student:integer, course:string , mark:real, grade:string)A.For each of the following SQL queries, write an efficient relational algebra expression that might be used to implement the query. To make the e

22、xpressions clearer, you may use as many named intermediate temporary relations as you wish. Correct, but grossly inefficient, relational algebra expressions will be awarded only half marks.A. select given,family from StudentB. select * from Enrolment where student=2233456C. select given,family,cours

23、efrom Enrolment, Studentwhere Enrolment.student = Student.idD. select e.code, e.session, c.title, e.mark, e.gradefrom Enrolment e, Course c, Student swhere e.course = c.id and e.student = s.id and s.id = 223456712. Consider the following E/R diagram, modeling data about patients in a hospital:A. Per

24、form a conversion of the E/R diagram into relation schemas. You should eliminate relations that are not necessary (e.g., by combining relations).13. Given the interleaved schedules: Schedule 1T1RA.RC.WC.CommitT2RC.WC.RB.WB.CommitT3RC.RA.WA.CommitSchedule 2T1RA.RC.WA.Commi tT2RC.RB.WB.Commi tT3RB .WB

25、.Commi tSchedule 3T1RC.WA.WA.CommitT2WA.RB.WB.CommitA. Which of the following schedules are serializable? Give a serial schedule or identify possible anomalies.B. Draw the precedence graph for all three schedules and check whether they are conflict-serializable or not.C. Apply strict 2PL to the non-

26、conflict-serializable schedulesD. In one of the schedules a deadlock emerge s draw the waits-for-graph for this schedule after all transactions are captured in the deadlock situation.(Use X(.) to denote exclusive locks and S(.) to denote shared locks!)14. Consider a relational schema ABCDEFGHIJ, whi

27、ch contains the following FDs: AB C, D E, AE G, GD H, IF J.A. Check whether or not the functional dependencies entailABD GHABD HJABC GGD HEB. Let A denote a key for the aforementioned relation. Derive a lossless join, dependency preserving decomposition in 3NF!15. What do the ACID properties stand f

28、or? Give a brief description of the four characteristics.16. What are the serial schedule, equivalent schedules and serializable schedule?17. Let R and S are two relations shown as below:RABC123456789SBCD231023116712Write the results of the following queries:A. a,b+c x(R)B. b,c(R) - b,c(S)C. B B,sum

29、D.(S)D. b B,maxD.(R S)18. Consider the following relational schema for movie DVD rental store: customer (customer id, first name, last name) DVD (dvd id, title, genre, director, released year) borrow (customer id, dvd id, checkout date, duration)Use SQL to write the following queries:A. Create 3 tab

30、les for the above schema with appropriate domain and required contraints.B. Change the data type of the checkout date attribute of the borrow table to date.19. Consider the following gradebook relational schema describing the data for a grade book of a particular instructor catalog(cno, ctitle)stude

31、nts(sid, fname, lname, minit)courses(term, secno, cno, score)enrolls(sid, term, secno)Use relation algebra and SQL to write the following queries:A. Retrieve the names of students enrolled in the 'Database' class in the term of Fall 2009.B. Retrieve the names of students who have enrolled in

32、 CS226 or CS227.C. Retrieve the names of students who have not enrolled in any class.D. Retrieve the titles of courses whose average score of the whole class is more than 80.20. Consider a company database with the following relation schemas where primary keys are underlined:employee (first name, la

33、st name_id_no, birthday, gender, salary, supervisor id no, department no)department (department name, department no, manager id no)department locations (department no, department location)project (project name, project no, project location, department no)works on (id no, project no, hours)Use relati

34、on algebra and SQL to write the following queries:A. Retrieve the names of all employees in the 'Research' department who work more than 10 hours per week on the 'ProductX' project.B. Find the names of employees who are directly supervised by 'Avril Lavigne'.C. Retrieve the n

35、ames of employees who work on every project.D. Retrieve the names of all employees who work on at least one project located in Houston but whose department has no location in Houston.E. For each department, retrieve the department name, number of employees in that department, and the average salary

36、of employees working in that department.F. For each department whose average employee salary is more than $50000, retrieve the department name and the number of employees working for that department.G. Remove employees whose salary is more than $100000.H. Increase the pay of all employees in the 

37、9;Research' department by 5%21. Consider the following bank schema.account (account id, branch name, balance)borrower (customer id, loan number)branch (branch id, branch name, branch city, assets)customer (customer id, customer name, customer street, customer city)depositor (customer id, account

38、 number)loan (loan number, branch id, amount)Write SQL commands for the following query:A. Retrieve all different branch names.B. Retrieve all loan number that falls between 1000000 and 2000000.C. Retrieve all customer names in the' East Gate ' branch.D. Retrieve the branch name and number o

39、f accounts for each branch.22. Consider the following order table.order nodatecusto mer nocustomer nameite mproduct noproduct nameunitunit priceamo untprice06102482006-530VICRPVictor Corp.110001042rice3 Kg/bag15010150006102482006-530VICRPVictor Corp.210001072coke24cans/box4805240006102492006-68DONDI

40、Dondi Corp.110001014milk24 bottles60095400061022006-6-DONDondi2100010corn2472053600498DICorp.51chipsbags/box06102492006-615JENREJenren Corp.110001002beer24cans/box480104800where the keys are underlined.A. Normalize the above table to the 3 NF and draw the relational schema diagram and indicate the p

41、rimary keys and the referential constraints.B. Based on the above schema diagram draw the ER diagram.23. A student relation has 4 attributes: student id, name, email, and phone no. No two customers have the same student id and email.A. List keys, superkeys, and primary key for the student relation.B

42、. Explain the reason of choosing the primary key.24. Consider the following electronic store database:customer tablecustomer idnameC98022LadyGagaC98145Lily AllenC98262Taylor Swiftpurchase tablecustomer idorder noC98022O1234C98145O2234C98262O1681product tableproductproductpricnonameeP1168MP3 Player12

43、0 0P1234WII800 0P1688DVDPlayer300 0order tableorder noproduct noO1234P1168O2234P1234 1O1681P1688Use SQL to answer the following questions.A. Create the product table with the required constraints.B. Add a constraint of price > 0 in the product table.C. Insert ('P2348', 'Personal Compu

44、ter', 1200) into the product table.D. Find the all customers who have ordered a WII.E. Change the price of 'WII' from 8000 to 7500.F. Remove all orders that 'Lady Gaga" has put.25. Consider The database of a online game company has three relations: player, play, and game for sto

45、ring information about players who plays which game. The database schema of the game company is shown as follows: player (member no, name, level, phone, email) play (member no, game id, date, time) game (game id, title, type)Please draw the ER diagram.26. Consider a HollyWood Enterprise that require

46、s modeling information about the different type of peopleA. involved in the movie production.B. _ Each person should have person ID, name, phone, gender, and address.C. _ There are two main groups of persons: Movie professionals and celebrity. Each movie professionals work on someD. company.E. _ A m

47、ovie professionals can be either a director or a agent. Each director has her or his popularity and can direct aF. number of movies. Each agent has the agent fee.G. A celebrity can be a movie star, a model, or both. Each movie star has her or his movie style and play in someH. movies. Each model has

48、 her or his preferences.I. _ Each movie has the information about the title, released date, and language.J. Draw a EER diagram for the above HollyWood Enterprise.K. The owner of several apartment buildings is interested in a database to manage hisL. business.M. Buildings have one or more apartments.

49、 Every building has an address.N. Apartments have apt. nos., and are characterized by their size: 1BR, 2BR,etc.O. Tenants lease apartments. Each lease has a lease date and a period of lease.P. Tenant information is his/her name and phone number.Q. Each building has a manager. The manager has a name,

50、 telephone no. and salary.R. Each building has some parking spaces. Some tenants rent the parking spaces.Design an E-R diagram for the above. State any additional assumptions.Convert the above E-R diagram (Q.1) to relational model and write the SQL commands to create the tables for both the entities

51、 as well as relationships.27. The following questions are based on a Sporting Goods database described below:customer (id: int, name:string, city: string, country: string, rating: string, sales_rep_id:int )dept(id: int, name: string, region_id: string)sales_rep(id: int, last_name:string, first_name:

52、 string, dept_id: int, salary: int) order (id: int, customer_id: int, date_ordered:date, total: int)Write SQL queries for each of the following sub-questions.A. Display the name, city, country and rating of all customers whose number of orders exceeds the “ average " number of orderome ra custB

53、. Display the name of all the departments that have at least one employee.C. Display the first name and last name of all sales representatives who do not have customers.D. Find the countries in which there are no sales representatives. If required, makeany assumptions and state them.28. Consider the

54、 relation R, which has attributes that hold schedules of courses and sections at a university; R = CourseNo, SecNo, OfferingDept, CreditHours, CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents. Suppose that the following functional dependencies hold on R:CourseNo OfferingD

55、ept, CreditHours, CourseLevelCourseNo, SecNo, Semester, Year Days_Hours, RoomNo, NoOfStudents, InstructorSSNRoomNo, Days_Hours, Semester, Year InstructorSSN, CourseNo, SecNoA. Try to determine which sets of attributes form keys of R. How would you normalize this relation?29. Consider the relational

56、database schema and write the SQL statements according to the this model.Part(PartNo, PartName, ProjNo Price, Weight)Project(ProjNo, ProjName, Location, departNo)Emp(Ssn, Name, Surname, departNo, Addres, salary)Work(ssn, ProjNo, Hour)A. Listing the workers info, according to the PartNo=24 that is us

57、ed in the one project.B. Listing the project names and locations, according to the employees address includes “ Bahcesehir ” .C. Alter the emp table and add a new column which is corresponding the empi s birth date.D. List the emp info, according to the his salaries greater than avarage salary of the emp table.30. Branch(branch-name, branch-city, assets)Account(account-number, branch-name,balance)