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 dom
2、ain 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 o
3、perators 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 ab
4、out 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 t
5、he 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
6、 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 wh
7、o 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
8、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
9、 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.
10、 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, fr
11、om.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
12、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
13、 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 mu
14、st 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 doc
15、tor 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
16、 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, street:string, city:string);Works(employee:string
17、, 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“ and earn mo
18、re 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 employee of “Small B
19、ank 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 employees earn a higher salary,
20、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, enrolled:date)Course(id:integer, code:strin
21、g, 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 expressions clearer, you may use as man
22、y 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,course from Enrolment, Studentwhere Enrolme
23、nt.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. Perform a conversion of the E/R diagram
24、into relation schemas. You should eliminate relations that are not necessary (e.g., by combining relations).13. Given the interleaved schedules: Schedule 1Schedule 2T1 RA. RC. WC. CommitT2 RC. WC. RB. WB. CommitT3 RC. RA. WA. CommitSchedule 3A. Which of the following schedules are serializable? Give
25、 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-conflict-serializable schedules D. In one of the schedules a deadlock emerges draw the waits-for-graph for t
26、his 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, which contains the following FDs: ABC, DE, AEG, GDH, IFJ. A. Check whether or not the functional dependencies
27、entail ABDGH ABDHJ ABCG GDHE B. 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 for? Give a brief description of the four characteristics. 16. What are the serial schedule, equivalent schedu
28、les and serializable schedule?17. Let R and S are two relations shown as below:R A B C1 2 34 5 67 8 9T1 RA. RC. WA. Commit T2 RC. RB. WB. Commit T3 RB. WB. CommitT1 RC. WA. WA. Commit T2 WA. RB. WB. CommitSB C D2 3 102 3 116 7 12Write the results of the following queries:A. A,B+C X(R)B. B,C(R) -B,C(
29、S)C. B B,sumD.(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. Cre
30、ate 3 tables 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 instructorcatalog(cno, ctit
31、le)students(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 C
32、S226 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, last
33、 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 relatio
34、n 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 names of employees who work on e
35、very 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 of employees working in that de
36、partment.F. For each department whose average employee salary is more than $50000, retrieve the department name andthe 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 Research department by 5%21. Consid
37、er 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 number)loan (loan number, branch id, amou
38、nt)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 of accounts for each branch.22. Consider the following
39、 order table.where the keys are underlined.A. Normalize the above table to the 3 NF and draw the relational schema diagram and indicate the primary keys and the referential constraints.B. Based on the above schema diagram draw the ER diagram.order no date customer no customer name item product no pr
40、oduct name unit unit price amount price 0610248 2006-5-30 VICRP Victor Corp. 1 10001042 rice 3 Kg/bag 150 10 1500 0610248 2006-5-30 VICRP Victor Corp. 2 10001072 coke 24 cans/box 480 5 2400 0610249 2006-6-8 DONDI Dondi Corp. 1 10001014 milk 24 bottles 600 9 5400 0610249 2006-6-8 DONDI Dondi Corp. 2
41、10001051 corn chips 24 bags/box 720 5 3600 0610249 2006-6-15 JENRE Jenren Corp. 1 10001002 beer 24 cans/box 480 10 4800 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
42、 student relation.B. Explain the reason of choosing the primary key.24. Consider the following electronic store database:Use 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
43、Computer, 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 storing information abo
44、ut 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 requires modeling information
45、about the different type of peoplecustomer table customer id name C98022 Lady Gaga C98145 Lily Allen C98262 Taylor Swift purchase table customer id order no C98022 O1234 C98145 O2234 C98262 O1681 order table order no product no O1234 P1168 O2234 P1234 O1681 P1688 product table product no product nam
46、e price P1168 MP3 Player 1200 P1234 WII 8000 P1688 DVD Player 3000 A. 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. compan
47、y.E. _ A movie 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
48、 model has 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 a
49、partments. 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, 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 an
